Metadata Manager Reports
This page will show you all the Available reports for Metadata Manager Reports, once you select a report name, it will open the report query page. copy and paste it to your PC and run it from, toad or any other DB tools you have.
Available reports
Business Intelligence
Lists all attributes and metrics by repository, schema, and dimension to which each attribute or metric belongs.
select “DIMENSION_NAME”,
“OBJECT_NAME”,
“LX_LOCATION”,
“REPOSITORY_NAME”,
“OBJECT_TYPE”,
“OBJECT_DESCRIPTION”
from (SELECT DISTINCT ODS_IMA_ELEMENT.REPOSITORY_NAME, ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME = ‘infa_type’ AND EA.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID ) as OBJECT_TYPE , ODS_IMA_ELEMENT.ELEMENT_NAME as DIMENSION_NAME, ODS_IMA_CHILD_ELEMENT.ELEMENT_NAME as OBJECT_NAME, ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME = ‘infa_description’ AND EA.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID ) as OBJECT_DESCRIPTION , ODS_IMA_CHILD_ELEMENT.NAME_PATH as LX_LOCATION FROM IMA_ELMNT_ASSOC ODS_IMA_CHILD_ELMNT_ASSOC, IMA_ELEMENT ODS_IMA_CHILD_ELEMENT, IMA_ELEMENT ODS_IMA_ELEMENT, IMA_ELMNT_ATTR ODS_IMA_CHILD_ELMNT_ATTR WHERE (ODS_IMA_CHILD_ELMNT_ASSOC.TO_ELEMENT_UID = ODS_IMA_CHILD_ELEMENT. ELEMENT_UID AND ODS_IMA_CHILD_ELMNT_ASSOC.FROM_ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID AND ODS_IMA_CHILD_ELMNT_ATTR.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID) AND (ODS_IMA_ELEMENT.PRODUCT_TYPE = ‘Business Intelligence’ AND ODS_IMA_ELEMENT.CLASS_TYPE IN (‘Dimension’, ‘Folder,Dimension’) AND ODS_IMA_ELEMENT.DELETED_FLAG = ‘N’ AND ODS_IMA_CHILD_ELEMENT.CLASS_TYPE IN (‘Attribute’, ‘Measure’) AND ODS_IMA_CHILD_ELMNT_ATTR.CLASS_ATTR_NAME = ‘name’ )) “query”
where “REPOSITORY_NAME” in (‘<Repository_Name>’)
order by “REPOSITORY_NAME”, “OBJECT_TYPE”, “DIMENSION_NAME”, “OBJECT_NAME”
Lists the hierarchies in a schema.
select “REPOSITORY_NAME”,
“LX_LOCATION”,
“HIERARCHY_NAME”,
“HIERARCHY_DESCRIPTION”
from (SELECT DISTINCT ODS_IMA_ELEMENT.REPOSITORY_NAME as REPOSITORY_NAME, ODS_IMA_ELEMENT.ELEMENT_NAME as HIERARCHY_NAME , ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME = ‘infa_description’ AND EA.ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID ) as HIERARCHY_DESCRIPTION , ODS_IMA_ELEMENT.NAME_PATH as LX_LOCATION FROM IMA_ELEMENT ODS_IMA_ELEMENT, IMA_ELMNT_ATTR ODS_IMA_ELMNT_ATTR WHERE (ODS_IMA_ELMNT_ATTR.ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID) AND (ODS_IMA_ELEMENT.CLASS_TYPE = ‘Hierarchy’ AND ODS_IMA_ELEMENT.DELETED_FLAG = ‘N’ AND ODS_IMA_ELEMENT.PRODUCT_TYPE = ‘Business Intelligence’ AND ODS_IMA_ELMNT_ATTR.CLASS_ATTR_NAME = ‘name’ )) “query”
order by “REPOSITORY_NAME”, “HIERARCHY_NAME”, “HIERARCHY_DESCRIPTION”, “LX_LOCATION”
Lists attributes and metrics contained in the report documents.
select “EXPRESSION”,
“OBJECT_TYPE”,
“OBJECT_DESCRIPTION”,
“OBJECT_NAME”,
“LX_LOCATION”,
“REPORT_DOCUMENT_NAME”,
“REPOSITORY_NAME”
from (SELECT DISTINCT ODS_IMA_ELEMENT.REPOSITORY_NAME, ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME = ‘infa_type’ AND EA.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID ) AS OBJECT_TYPE , ODS_IMA_ELEMENT.ELEMENT_NAME AS REPORT_DOCUMENT_NAME, ODS_IMA_CHILD_ELEMENT.ELEMENT_NAME AS OBJECT_NAME, ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME = ‘infa_description’ AND EA.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID ) as OBJECT_DESCRIPTION , ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME IN (‘definition’, ‘Select’) AND EA.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID ) as EXPRESSION , ODS_IMA_CHILD_ELEMENT.NAME_PATH as LX_LOCATION FROM IMA_ELMNT_ASSOC ODS_IMA_CHILD_ELMNT_ASSOC, IMA_ELEMENT ODS_IMA_CHILD_ELEMENT, IMA_ELEMENT ODS_IMA_ELEMENT, IMA_ELMNT_ATTR ODS_IMA_CHILD_ELMNT_ATTR WHERE (ODS_IMA_CHILD_ELMNT_ASSOC.TO_ELEMENT_UID = ODS_IMA_CHILD_ELEMENT. ELEMENT_UID AND ODS_IMA_CHILD_ELMNT_ASSOC.FROM_ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID AND ODS_IMA_CHILD_ELMNT_ATTR.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID) AND (ODS_IMA_ELEMENT.CLASS_TYPE = ‘Folder’ AND ODS_IMA_CHILD_ELEMENT.CLASS_TYPE = ‘ReportAttribute’ AND ODS_IMA_CHILD_ELMNT_ATTR.CLASS_ATTR_NAME = ‘name’ AND ODS_IMA_ELEMENT.DELETED_FLAG = ‘N’ )) “query”
order by “REPOSITORY_NAME”, “OBJECT_TYPE”, “REPORT_DOCUMENT_NAME”, “OBJECT_NAME”, “OBJECT_DESCRIPTION”
Lists reports by repository and folder to which it belongs.
select “REPORT_FOLDER_NAME”,
“REPORT_NAME”,
“REPOSITORY_NAME”,
“REPORT_DESCRIPTION”,
“LX_LOCATION”
from (SELECT DISTINCT ODS_IMA_ELEMENT.REPOSITORY_NAME, ODS_IMA_ELEMENT.ELEMENT_NAME AS REPORT_NAME, ODS_IMA_PARENT_ELEMENT.ELEMENT_NAME AS REPORT_FOLDER_NAME, ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME = ‘infa_description’ AND EA.ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID ) AS Report_DESCRIPTION , ODS_IMA_ELEMENT.NAME_PATH as LX_LOCATION FROM IMA_ELMNT_ASSOC ODS_IMA_ELMNT_ASSOC, IMA_ELEMENT ODS_IMA_ELEMENT, IMA_ELEMENT ODS_IMA_PARENT_ELEMENT, IMA_ELMNT_ATTR ODS_IMA_ELMNT_ATTR WHERE (ODS_IMA_ELMNT_ASSOC.TO_ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID AND ODS_IMA_ELMNT_ASSOC.FROM_ELEMENT_UID = ODS_IMA_PARENT_ELEMENT.ELEMENT_UID AND ODS_IMA_ELMNT_ATTR.ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID) AND (ODS_IMA_PARENT_ELEMENT.PRODUCT_TYPE = ‘Business Intelligence’ AND ODS_IMA_PARENT_ELEMENT.CLASS_TYPE = ‘Folder’ AND ODS_IMA_ELMNT_ATTR.CLASS_ATTR_NAME = ‘name’ AND ODS_IMA_ELEMENT.CLASS_TYPE = ‘Report’ AND ODS_IMA_ELEMENT.DELETED_FLAG = ‘N’ )) “query”
where “REPOSITORY_NAME” in (‘<Repository_Name>’)
order by “REPOSITORY_NAME”, “REPORT_NAME”, “REPORT_FOLDER_NAME”, “REPORT_DESCRIPTION”
Lists attributes and metrics by repository, schema, and dimension to which each attribute or metric belongs.
select “L2METRIC”,
“OBJECT_TYPE”,
“LX_LOCATION”,
“FOLDER_NAME”,
“OBJECT_DESCRIPTION”,
“REPOSITORY_NAME”,
“OBJECT_NAME”
from (SELECT DISTINCT ODS_IMA_ELEMENT.REPOSITORY_NAME, ODS_IMA_ELEMENT.ELEMENT_NAME AS FOLDER_NAME, ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME = ‘infa_type’ AND EA.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID ) AS OBJECT_TYPE , ODS_IMA_CHILD_ELEMENT.ELEMENT_NAME AS OBJECT_NAME, ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME = ‘infa_description’ AND EA.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID ) AS OBJECT_DESCRIPTION , ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME IN (‘definition’, ‘Select’) AND EA.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID ) AS L2METRIC , ODS_IMA_CHILD_ELEMENT.NAME_PATH as LX_LOCATION FROM IMA_ELMNT_ASSOC ODS_IMA_CHILD_ELMNT_ASSOC, IMA_ELEMENT ODS_IMA_CHILD_ELEMENT, IMA_ELEMENT ODS_IMA_ELEMENT, IMA_ELMNT_ATTR ODS_IMA_CHILD_ELMNT_ATTR, IMA_ELMNT_ASSOC ODS_IMA_ELMNT_ASSOC, IMA_ELEMENT ODS_IMA_PARENT_ELEMENT WHERE (ODS_IMA_CHILD_ELMNT_ASSOC.TO_ELEMENT_UID = ODS_IMA_CHILD_ELEMENT. ELEMENT_UID AND ODS_IMA_CHILD_ELMNT_ASSOC.FROM_ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID AND ODS_IMA_CHILD_ELMNT_ATTR.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID AND ODS_IMA_ELMNT_ASSOC.TO_ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID AND ODS_IMA_ELMNT_ASSOC.FROM_ELEMENT_UID = ODS_IMA_PARENT_ELEMENT.ELEMENT_UID) AND (ODS_IMA_PARENT_ELEMENT.PRODUCT_TYPE = ‘Business Intelligence’ AND ODS_IMA_ELEMENT.CLASS_TYPE IN (‘Folder’, ‘Folder,Dimension’, ‘Dimension’) AND ODS_IMA_CHILD_ELEMENT.CLASS_TYPE IN (‘Attribute’, ‘Measure’) AND ODS_IMA_CHILD_ELMNT_ATTR.CLASS_ATTR_NAME = ‘name’ AND ODS_IMA_ELEMENT.DELETED_FLAG = ‘N’ )) “query”
order by “REPOSITORY_NAME”, “FOLDER_NAME”, “OBJECT_TYPE”, “OBJECT_NAME”, “OBJECT_DESCRIPTION”, “L2METRIC”
Data Integration
Lists PowerCenter repository mappings by the folder in which each mapping resides.
select “NAME_PATH”,
“CHILD_ELEMENT_NAME”,
“ELMNT_ATTR_VALUE”,
“REPOSITORY_NAME”,
“ELEMENT_NAME”
from (SELECT DISTINCT ODS_IMA_ELEMENT.REPOSITORY_NAME, ODS_IMA_ELEMENT.ELEMENT_NAME, ODS_IMA_CHILD_ELEMENT.ELEMENT_NAME as CHILD_ELEMENT_NAME, ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME = ‘infa_description’ AND EA.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID ) as ELMNT_ATTR_VALUE , ODS_IMA_CHILD_ELEMENT.NAME_PATH as NAME_PATH FROM IMA_ELMNT_ASSOC ODS_IMA_CHILD_ELMNT_ASSOC, IMA_ELEMENT ODS_IMA_CHILD_ELEMENT, IMA_ELEMENT ODS_IMA_ELEMENT, IMA_ELMNT_ATTR ODS_IMA_CHILD_ELMNT_ATTR WHERE (ODS_IMA_CHILD_ELMNT_ASSOC.TO_ELEMENT_UID = ODS_IMA_CHILD_ELEMENT. ELEMENT_UID AND ODS_IMA_CHILD_ELMNT_ASSOC.FROM_ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID AND ODS_IMA_CHILD_ELMNT_ATTR.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID) AND (ODS_IMA_CHILD_ELMNT_ATTR.CLASS_ATTR_NAME = ‘name’ AND ODS_IMA_CHILD_ELEMENT.CLASS_TYPE = ‘Mapping’ AND ODS_IMA_ELEMENT.CLASS_TYPE = ‘Folder’ AND ODS_IMA_ELEMENT.DELETED_FLAG = ‘N’ )) “query”
where “REPOSITORY_NAME” in (‘<Repository_Name>’) and “ELEMENT_NAME” in (‘<Folder/Version_Name>’)
order by “REPOSITORY_NAME”, “ELEMENT_NAME”, “CHILD_ELEMENT_NAME”, “ELMNT_ATTR_VALUE”, “NAME_PATH”
Lists PowerCenter repository workflows by the folder in which each workflow resides.
select “FOLDER_NAME”,
“WORKFLOW_NAME”,
“TASK_NAME”,
“WORKFLOW_DESCRIPTION”,
“LOG_FILE_NAME”,
“LOG_FILE_DIRECTORY”,
“LX_LOCATION”,
“TASK_TYPE”,
“TASK_DESCRPTION”,
“REPOSITORY_NAME”,
“REUSABLE_INDICATOR”
from (SELECT DISTINCT ODS_IMA_ELEMENT.REPOSITORY_NAME, ODS_IMA_PARENT_ELEMENT.ELEMENT_NAME AS FOLDER_NAME, ODS_IMA_ELEMENT.ELEMENT_NAME AS WORKFLOW_NAME, ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME = ‘infa_description’ AND EA.ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID ) AS WORKFLOW_DESCRIPTION , ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME = ‘Workflow Log File Name’ AND EA.ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID ) AS LOG_FILE_NAME , ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME = ‘Workflow Log File Directory’ AND EA.ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID ) AS LOG_FILE_DIRECTORY , ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME = ‘infa_type’ AND EA.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID ) AS TASK_TYPE , ODS_IMA_CHILD_ELEMENT.ELEMENT_NAME AS TASK_NAME, ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME = ‘infa_description’ AND EA.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID ) AS TASK_DESCRPTION , ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME = ‘REUSABLE’ AND EA.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID ) AS REUSABLE_INDICATOR , ODS_IMA_CHILD_ELEMENT.NAME_PATH as LX_LOCATION FROM IMA_ELMNT_ASSOC ODS_IMA_CHILD_ELMNT_ASSOC, IMA_ELEMENT ODS_IMA_CHILD_ELEMENT, IMA_ELEMENT ODS_IMA_ELEMENT, IMA_ELMNT_ASSOC ODS_IMA_ELMNT_ASSOC, IMA_ELEMENT ODS_IMA_PARENT_ELEMENT, IMA_ELMNT_ATTR ODS_IMA_CHILD_ELMNT_ATTR, IMA_ELMNT_ATTR ODS_IMA_ELMNT_ATTR WHERE (ODS_IMA_CHILD_ELMNT_ASSOC.TO_ELEMENT_UID = ODS_IMA_CHILD_ELEMENT. ELEMENT_UID AND ODS_IMA_CHILD_ELMNT_ASSOC.FROM_ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID AND ODS_IMA_ELMNT_ASSOC.TO_ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID AND ODS_IMA_ELMNT_ASSOC.FROM_ELEMENT_UID = ODS_IMA_PARENT_ELEMENT.ELEMENT_UID AND ODS_IMA_CHILD_ELMNT_ATTR.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID AND ODS_IMA_ELMNT_ATTR.ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID) AND (ODS_IMA_PARENT_ELEMENT.PRODUCT_TYPE = ‘Data Integration’ AND ODS_IMA_PARENT_ELEMENT.CLASS_TYPE = ‘Folder’ AND ODS_IMA_ELEMENT.CLASS_TYPE = ‘Workflow’ AND ODS_IMA_ELMNT_ATTR.CLASS_ATTR_NAME = ‘name’ AND ODS_IMA_CHILD_ELMNT_ATTR.CLASS_ATTR_NAME = ‘name’ AND ODS_IMA_ELEMENT.DELETED_FLAG = ‘N’ )) “query”
order by “REPOSITORY_NAME”, “FOLDER_NAME”, “WORKFLOW_NAME”, “WORKFLOW_DESCRIPTION”, “LOG_FILE_NAME”
Data Modelling
Lists constraints, such as Unique Key, Primary Key, Foreign Key, and Rule constraints, by the table for which the constraint is defined. It also provides the columns that participate in each constraint.
select “LX_LOCATION”,
“MODEL_NAME”,
“TABLE_NAME”,
“REPOSITORY_NAME”,
“CONSTRAINT_TYPE”,
“COLUMN_NAME”,
“CONSTRAINT_NAME”
from (SELECT DISTINCT ODS_IMA_ELEMENT.REPOSITORY_NAME as REPOSITORY_NAME, ODS_IMA_PARENT_ELEMENT.ELEMENT_NAME as MODEL_NAME, ODS_CHILD_LVL2_ELMT.ELEMENT_NAME as CONSTRAINT_NAME , ODS_CHILD_LVL2_ELMT.CLASS_TYPE as CONSTRAINT_TYPE , ODS_IMA_ELEMENT.ELEMENT_NAME as TABLE_NAME , ODS_IMA_CHILD_ELEMENT.ELEMENT_NAME as COLUMN_NAME , ODS_IMA_CHILD_ELEMENT.NAME_PATH as LX_LOCATION FROM IMA_ELMNT_ASSOC ODS_IMA_ELMNT_ASSOC, IMA_ELEMENT ODS_IMA_PARENT_ELEMENT, IMA_ELEMENT ODS_IMA_ELEMENT, IMA_ELMNT_ASSOC ODS_CHILD_LVL2_ASOC, IMA_ELEMENT ODS_CHILD_LVL2_ELMT, IMA_ELEMENT ODS_IMA_CHILD_ELEMENT, IMA_ELMNT_ASSOC ODS_IMA_CHILD_ELMNT_ASSOC, IMA_ELMNT_ATTR ODS_CHILD_LVL2_ATTR, IMA_ELMNT_ATTR ODS_IMA_CHILD_ELMNT_ATTR WHERE (ODS_IMA_ELMNT_ASSOC.FROM_ELEMENT_UID = ODS_IMA_PARENT_ELEMENT.ELEMENT_UID AND ODS_IMA_ELMNT_ASSOC.TO_ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID AND ODS_CHILD_LVL2_ASOC.TO_ELEMENT_UID = ODS_CHILD_LVL2_ELMT.ELEMENT_UID AND ODS_CHILD_LVL2_ASOC.FROM_ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID AND ODS_IMA_CHILD_ELMNT_ASSOC.TO_ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID AND ODS_IMA_CHILD_ELMNT_ASSOC.FROM_ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID AND ODS_CHILD_LVL2_ATTR.ELEMENT_UID = ODS_CHILD_LVL2_ELMT.ELEMENT_UID AND ODS_IMA_CHILD_ELMNT_ATTR.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID) AND (ODS_IMA_PARENT_ELEMENT.PRODUCT_TYPE = ‘Data Modeling’ AND ODS_IMA_PARENT_ELEMENT.CLASS_TYPE = ‘Model’ AND ODS_IMA_ELEMENT.CLASS_TYPE = ‘Table’ AND ODS_IMA_CHILD_ELMNT_ATTR.CLASS_ATTR_NAME = ‘name’ AND ODS_CHILD_LVL2_ELMT.CLASS_TYPE IN (‘ForeignKey’, ‘PrimaryKey’, ‘UniqueKey’, ‘RuleConstraint’) AND ODS_IMA_CHILD_ELEMENT.CLASS_TYPE = ‘Column’ AND ODS_CHILD_LVL2_ATTR.CLASS_ATTR_NAME = ‘name’ AND ODS_IMA_ELEMENT.DELETED_FLAG LIKE ‘%N%’ )) “query”
order by “REPOSITORY_NAME”, “MODEL_NAME”, “CONSTRAINT_NAME”, “CONSTRAINT_TYPE”, “TABLE_NAME”
Lists tables by Model, and table column information, such as name, precision, scale, and datatype.
select “L2_PRECISION”,
“OBJECT_DESCRIPTION”,
“REPOSITORY_NAME”,
“MODEL_NAME”,
“LX_LOCATION”,
“IS_NULL_INDICATOR”,
“L2_SCALE”,
“OBJECTTYPE”,
“COLUMN_NAME”,
“OBJECT_NAM”,
“COLUMN_DESCRIPTION”,
“L2_DATATYPE”
from (SELECT DISTINCT ODS_IMA_ELEMENT.REPOSITORY_NAME, ODS_IMA_PARENT_ELEMENT.ELEMENT_NAME AS MODEL_NAME, ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME = ‘infa_type’ AND EA.ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID ) AS OBJECTTYPE , ODS_IMA_ELEMENT.ELEMENT_NAME AS OBJECT_NAM, ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME = ‘infa_description’ AND EA.ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID ) AS Object_DESCRIPTION , ODS_IMA_CHILD_ELEMENT.ELEMENT_NAME AS COLUMN_NAME, ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME = ‘infa_description’ AND EA.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID ) AS Column_DESCRIPTION , ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME IN (‘dataType’, ‘datatype’, ‘Data Type’, ‘DataType’, ‘Datatype’) AND EA.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID ) AS L2_DATATYPE , ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME = ‘precision’ AND EA.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID ) AS L2_PRECISION , ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME = ‘scale’ AND EA.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID ) AS L2_SCALE , ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME = ‘isNullable’ AND EA.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID ) AS IS_NULL_INDICATOR , ODS_IMA_CHILD_ELEMENT.NAME_PATH as LX_LOCATION FROM IMA_ELMNT_ASSOC ODS_IMA_ELMNT_ASSOC, IMA_ELEMENT ODS_IMA_PARENT_ELEMENT, IMA_ELEMENT ODS_IMA_ELEMENT, IMA_ELMNT_ATTR ODS_IMA_ELMNT_ATTR, IMA_ELMNT_ASSOC ODS_IMA_CHILD_ELMNT_ASSOC, IMA_ELEMENT ODS_IMA_CHILD_ELEMENT, IMA_ELMNT_ATTR ODS_IMA_CHILD_ELMNT_ATTR WHERE (ODS_IMA_ELMNT_ASSOC.FROM_ELEMENT_UID = ODS_IMA_PARENT_ELEMENT.ELEMENT_UID AND ODS_IMA_ELMNT_ASSOC.TO_ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID AND ODS_IMA_ELMNT_ATTR.ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID AND ODS_IMA_CHILD_ELMNT_ASSOC.TO_ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID AND ODS_IMA_CHILD_ELMNT_ATTR.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID AND ODS_IMA_CHILD_ELMNT_ASSOC.FROM_ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID) AND (ODS_IMA_PARENT_ELEMENT.PRODUCT_TYPE = ‘Data Modeling’ AND ODS_IMA_PARENT_ELEMENT.CLASS_TYPE = ‘Model’ AND ODS_IMA_ELEMENT.CLASS_TYPE IN (‘Table’, ‘View’) AND ODS_IMA_CHILD_ELEMENT.CLASS_TYPE = ‘Column’ AND ODS_IMA_ELMNT_ATTR.CLASS_ATTR_NAME = ‘name’ AND ODS_IMA_CHILD_ELMNT_ATTR.CLASS_ATTR_NAME = ‘name’ AND ODS_IMA_ELEMENT.DELETED_FLAG LIKE ‘%N%’ )) “query”
where “REPOSITORY_NAME” in (‘<Repository_Name>’)
order by “REPOSITORY_NAME”, “MODEL_NAME”, “OBJECTTYPE”, “OBJECT_NAM”, “OBJECT_DESCRIPTION”, “COLUMN_NAME”
Lists tables and views by Model.
select “MODEL_NAME”,
“OBJECT_NAME”,
“REPOSITORY_NAME”,
“OBJECT_TYPE”,
“LX_LOCATION”,
“DESCRIPTION”
from (SELECT DISTINCT ODS_IMA_ELEMENT.REPOSITORY_NAME, ODS_IMA_PARENT_ELEMENT.ELEMENT_NAME AS MODEL_NAME, ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME = ‘infa_type’ AND EA.ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID ) AS OBJECT_TYPE , ODS_IMA_ELEMENT.ELEMENT_NAME AS OBJECT_NAME, ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME = ‘infa_description’ AND EA.ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID ) AS DESCRIPTION , ODS_IMA_ELEMENT.NAME_PATH as LX_LOCATION FROM IMA_ELMNT_ASSOC ODS_IMA_ELMNT_ASSOC, IMA_ELEMENT ODS_IMA_ELEMENT, IMA_ELEMENT ODS_IMA_PARENT_ELEMENT, IMA_ELMNT_ATTR ODS_IMA_ELMNT_ATTR WHERE (ODS_IMA_ELMNT_ASSOC.TO_ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID AND ODS_IMA_ELMNT_ASSOC.FROM_ELEMENT_UID = ODS_IMA_PARENT_ELEMENT.ELEMENT_UID AND ODS_IMA_ELMNT_ATTR.ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID) AND (ODS_IMA_PARENT_ELEMENT.PRODUCT_TYPE = ‘Data Modeling’ AND ODS_IMA_ELEMENT.CLASS_TYPE IN (‘View’, ‘Table’) AND ODS_IMA_ELMNT_ATTR.CLASS_ATTR_NAME = ‘name’ AND ODS_IMA_ELEMENT.DELETED_FLAG LIKE ‘%N%’ )) “query”
where “REPOSITORY_NAME” in (‘<Repository_Name>’)
order by “REPOSITORY_NAME”, “MODEL_NAME”, “OBJECT_TYPE”, “OBJECT_NAME”
Database Management
Lists Database repository constraints, such as Unique Key, Primary Key, Foreign Key, and Rule constraints, by the table for which the constraint is defined. It also provides the columns that participate in each constraint.
select “TABLE_NAME”,
“CONSTRAINT_TYPE”,
“COLUMN_NAME”,
“LX_LOCATION”,
“CONSTRAINT_NAME”,
“REPOSITORY_NAME”,
“SCHEMA_NAME”
from (SELECT DISTINCT ODS_IMA_ELEMENT.REPOSITORY_NAME as REPOSITORY_NAME, ODS_IMA_PARENT_ELEMENT.ELEMENT_NAME as SCHEMA_NAME, ODS_CHILD_LVL2_ELMT.ELEMENT_NAME as CONSTRAINT_NAME , ODS_CHILD_LVL2_ELMT.CLASS_TYPE as CONSTRAINT_TYPE , ODS_IMA_ELEMENT.ELEMENT_NAME as TABLE_NAME , ODS_IMA_CHILD_ELEMENT.ELEMENT_NAME as COLUMN_NAME , ODS_IMA_CHILD_ELEMENT.NAME_PATH as LX_LOCATION FROM IMA_ELMNT_ASSOC ODS_IMA_ELMNT_ASSOC, IMA_ELEMENT ODS_IMA_PARENT_ELEMENT, IMA_ELEMENT ODS_IMA_ELEMENT, IMA_ELMNT_ASSOC ODS_CHILD_LVL2_ASOC, IMA_ELEMENT ODS_CHILD_LVL2_ELMT, IMA_ELMNT_ASSOC ODS_IMA_CHILD_ELMNT_ASSOC, IMA_ELEMENT ODS_IMA_CHILD_ELEMENT, IMA_ELMNT_ATTR ODS_CHILD_LVL2_ATTR, IMA_ELMNT_ATTR ODS_IMA_CHILD_ELMNT_ATTR WHERE (ODS_IMA_ELMNT_ASSOC.FROM_ELEMENT_UID = ODS_IMA_PARENT_ELEMENT.ELEMENT_UID AND ODS_IMA_ELMNT_ASSOC.TO_ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID AND ODS_CHILD_LVL2_ASOC.TO_ELEMENT_UID = ODS_CHILD_LVL2_ELMT.ELEMENT_UID AND ODS_IMA_CHILD_ELMNT_ASSOC.TO_ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID AND ODS_CHILD_LVL2_ASOC.FROM_ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID AND ODS_IMA_CHILD_ELMNT_ASSOC.FROM_ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID AND ODS_CHILD_LVL2_ATTR.ELEMENT_UID = ODS_CHILD_LVL2_ELMT.ELEMENT_UID AND ODS_IMA_CHILD_ELMNT_ATTR.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID) AND (ODS_IMA_PARENT_ELEMENT.PRODUCT_TYPE = ‘Database Management’ AND ODS_IMA_ELEMENT.CLASS_TYPE IN (‘Table’, ‘SnapshotTable’) AND ODS_IMA_CHILD_ELEMENT.CLASS_TYPE = ‘Column’ AND ODS_IMA_CHILD_ELMNT_ATTR.CLASS_ATTR_NAME = ‘name’ AND ODS_IMA_PARENT_ELEMENT.CLASS_TYPE IN (‘Schema’, ‘User’, ‘Database’) AND ODS_CHILD_LVL2_ELMT.CLASS_TYPE IN (‘CheckConstraint’, ‘ForeignKey’, ‘PrimaryKeyConstraint’, ‘UniqueConstraint’, ‘NotNullConstraint’) AND ODS_CHILD_LVL2_ATTR.CLASS_ATTR_NAME = ‘name’ AND ODS_IMA_ELEMENT.DELETED_FLAG LIKE ‘%N%’ )) “query”
order by “REPOSITORY_NAME”, “SCHEMA_NAME”
Lists database synonyms and the tables using those synonyms.
select “BASE_OBJECT_TYPE”,
“REPOSITORY_NAME”,
“OBJECT_DESCRIPTION”,
“SYNONYM_DESCRIPTION”,
“BASE_OBJECT_NAME”,
“SYNONYM_NAME”,
“SCHEMA_NAME1”
from (SELECT DISTINCT ODS_IMA_ELEMENT.REPOSITORY_NAME AS REPOSITORY_NAME, ODS_IMA_PARENT_ELEMENT.ELEMENT_NAME AS SCHEMA_NAME1 , ODS_IMA_ELEMENT.CLASS_TYPE AS Base_OBJECT_TYPE, ODS_IMA_CHILD_ELEMENT.ELEMENT_NAME AS SYNONYM_NAME, ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME = ‘infa_description’ AND EA.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID) AS SYNONYM_DESCRIPTION, ODS_IMA_ELEMENT.ELEMENT_NAME AS BASE_OBJECT_NAME, ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME = ‘infa_description’ AND EA.ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID) AS Object_DESCRIPTION FROM IMA_ELMNT_ASSOC ODS_IMA_CHILD_ELMNT_ASSOC, IMA_ELEMENT ODS_IMA_CHILD_ELEMENT, IMA_ELEMENT ODS_IMA_ELEMENT, IMA_ELMNT_ASSOC ODS_IMA_ELMNT_ASSOC, IMA_ELEMENT ODS_IMA_PARENT_ELEMENT, IMA_ELMNT_ATTR ODS_IMA_ELMNT_ATTR, IMA_ELMNT_ATTR ODS_IMA_CHILD_ELMNT_ATTR WHERE (ODS_IMA_CHILD_ELMNT_ASSOC.TO_ELEMENT_UID = ODS_IMA_CHILD_ELEMENT. ELEMENT_UID AND ODS_IMA_CHILD_ELMNT_ASSOC.FROM_ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID AND ODS_IMA_ELMNT_ASSOC.FROM_ELEMENT_UID = ODS_IMA_PARENT_ELEMENT.ELEMENT_UID AND ODS_IMA_ELMNT_ASSOC.TO_ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID AND ODS_IMA_ELMNT_ATTR.ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID AND ODS_IMA_CHILD_ELMNT_ATTR.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID) AND (ODS_IMA_PARENT_ELEMENT.PRODUCT_TYPE = ‘Database Management’ AND ODS_IMA_PARENT_ELEMENT.CLASS_TYPE IN (‘Schema’, ‘Database’, ‘User’) AND ODS_IMA_CHILD_ELEMENT.CLASS_TYPE = ‘Synonym’ AND ODS_IMA_ELEMENT.CLASS_TYPE IN (‘Table’, ‘View’) AND ODS_IMA_ELMNT_ATTR.CLASS_ATTR_NAME = ‘name’ AND ODS_IMA_CHILD_ELMNT_ATTR.CLASS_ATTR_NAME = ‘name’ AND ODS_IMA_ELEMENT.DELETED_FLAG = ‘N’ )) “query”
order by “REPOSITORY_NAME”, “SCHEMA_NAME1”, “BASE_OBJECT_TYPE”, “SYNONYM_NAME”, “SYNONYM_DESCRIPTION”
Lists the columns of database tables, views and indexes by schema.
select “L2_PRECISION”,
“COLUMN_NAME”,
“OBJECT_NAME”,
“LX_LOCATION”,
“IS_NULL_INDICATOR”,
“COLUMN_DESCRIPTION”,
“REPOSITORY_NAME”,
“L2_DATATYPE”,
“L2_SCALE”,
“SCHEMA_NAME”,
“OBJECT_TYPE”,
“OBJECT_DESCRIPTION”
from (SELECT DISTINCT ODS_IMA_ELEMENT.REPOSITORY_NAME, ODS_IMA_PARENT_ELEMENT.ELEMENT_NAME AS SCHEMA_NAME, ODS_IMA_ELEMENT.CLASS_TYPE AS OBJECT_TYPE, ODS_IMA_ELEMENT.ELEMENT_NAME AS OBJECT_NAME, ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME = ‘infa_description’ AND EA.ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID ) AS Object_DESCRIPTION , ODS_IMA_CHILD_ELEMENT.ELEMENT_NAME AS COLUMN_NAME, ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME = ‘infa_description’ AND EA.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID ) AS Column_DESCRIPTION , ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME IN (‘dataType’, ‘datatype’, ‘Data Type’, ‘DataType’, ‘Datatype’) AND EA.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID ) AS L2_DATATYPE , ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME = ‘precision’ AND EA.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID ) AS L2_PRECISION , ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME = ‘scale’ AND EA.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID ) AS L2_SCALE , ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME = ‘isNullable’ AND EA.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID ) AS IS_NULL_INDICATOR , ODS_IMA_CHILD_ELEMENT.NAME_PATH as LX_LOCATION FROM IMA_ELMNT_ASSOC ODS_IMA_ELMNT_ASSOC, IMA_ELEMENT ODS_IMA_PARENT_ELEMENT, IMA_ELEMENT ODS_IMA_ELEMENT, IMA_ELMNT_ATTR ODS_IMA_ELMNT_ATTR, IMA_ELMNT_ASSOC ODS_IMA_CHILD_ELMNT_ASSOC, IMA_ELEMENT ODS_IMA_CHILD_ELEMENT, IMA_ELMNT_ATTR ODS_IMA_CHILD_ELMNT_ATTR WHERE (ODS_IMA_ELMNT_ASSOC.FROM_ELEMENT_UID = ODS_IMA_PARENT_ELEMENT.ELEMENT_UID AND ODS_IMA_ELMNT_ASSOC.TO_ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID AND ODS_IMA_ELMNT_ATTR.ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID AND ODS_IMA_CHILD_ELMNT_ASSOC.TO_ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID AND ODS_IMA_CHILD_ELMNT_ASSOC.FROM_ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID AND ODS_IMA_CHILD_ELMNT_ATTR.ELEMENT_UID = ODS_IMA_CHILD_ELEMENT.ELEMENT_UID) AND (ODS_IMA_PARENT_ELEMENT.PRODUCT_TYPE = ‘Database Management’ AND ODS_IMA_PARENT_ELEMENT.CLASS_TYPE IN (‘Schema’, ‘Database’, ‘User’) AND ODS_IMA_ELEMENT.CLASS_TYPE IN (‘Index’, ‘Table’, ‘View’, ‘SnapshotTable’) AND ODS_IMA_CHILD_ELEMENT.CLASS_TYPE IN (‘Column’, ‘IndexColumn’) AND ODS_IMA_ELMNT_ATTR.CLASS_ATTR_NAME = ‘name’ AND ODS_IMA_CHILD_ELMNT_ATTR.CLASS_ATTR_NAME = ‘name’ AND ODS_IMA_ELEMENT.DELETED_FLAG LIKE ‘%N%’ )) “query”
where “REPOSITORY_NAME” in (‘<Repository_Name>’)
order by “REPOSITORY_NAME”, “SCHEMA_NAME”, “OBJECT_TYPE”
Lists database tables, views and indexes by schema.
select “REPOSITORY_NAME”,
“LEVEL1_ELEMENT”,
“OBJECT_NAME”,
“OBJECT_DESCRIPTION”,
“SCHEMA_NAME”,
“LX_LOCATION”
from (SELECT DISTINCT ODS_IMA_ELEMENT.REPOSITORY_NAME, ODS_IMA_PARENT_ELEMENT.ELEMENT_NAME AS SCHEMA_NAME, ODS_IMA_ELEMENT.CLASS_TYPE AS LEVEL1_ELEMENT, ODS_IMA_ELEMENT.ELEMENT_NAME AS OBJECT_NAME, ( SELECT EA.ELMNT_ATTR_VALUE FROM IMA_ELMNT_ATTR EA WHERE EA.CLASS_ATTR_NAME = ‘infa_description’ AND EA.ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID ) AS Object_DESCRIPTION , ODS_IMA_ELEMENT.NAME_PATH as LX_LOCATION FROM IMA_ELMNT_ASSOC ODS_IMA_ELMNT_ASSOC, IMA_ELEMENT ODS_IMA_ELEMENT, IMA_ELEMENT ODS_IMA_PARENT_ELEMENT, IMA_ELMNT_ATTR ODS_IMA_ELMNT_ATTR WHERE (ODS_IMA_ELMNT_ASSOC.TO_ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID AND ODS_IMA_ELMNT_ASSOC.FROM_ELEMENT_UID = ODS_IMA_PARENT_ELEMENT.ELEMENT_UID AND ODS_IMA_ELMNT_ATTR.ELEMENT_UID = ODS_IMA_ELEMENT.ELEMENT_UID) AND (ODS_IMA_PARENT_ELEMENT.PRODUCT_TYPE = ‘Database Management’ AND ODS_IMA_PARENT_ELEMENT.CLASS_TYPE IN (‘Schema’, ‘Database’, ‘User’) AND ODS_IMA_ELEMENT.CLASS_TYPE IN (‘Index’, ‘Table’, ‘View’, ‘SnapshotTable’) AND ODS_IMA_ELMNT_ATTR.CLASS_ATTR_NAME = ‘name’ AND ODS_IMA_ELEMENT.DELETED_FLAG = ‘N’ )) “query”
where “REPOSITORY_NAME” in (‘<Repository_Name>’)
order by “REPOSITORY_NAME”, “SCHEMA_NAME”, “LEVEL1_ELEMENT”
Metamodel/Metamodel Exploration
Lists all associations in a specified package. Associations identify the relationship between classes in a meta-model.
select “ORIGINATOR_NAME”,
“FROM_CLASS_NAME”,
“ASSOCIATION_NAME”,
“FROM_CARDINALITY”,
“MODIFIED_ON”,
“ASSOCIATION_DESC”,
“TO_CARDINALITY”,
“PACKAGE_NAME”,
“TO_CLASS_NAME”
from (SELECT DISTINCT MMR_ASSOC_PACKAGE.ORIGINATOR_NAME, MMR_ASSOC_PACKAGE.PACKAGE_NAME, MMR_IMA_ASSOCIATION.ASSOCIATION_NAME, MMR_IMA_ASSOCIATION.ASSOCIATION_DESC, MMR_FROM_CLASS.CLASS_NAME AS FROM_CLASS_NAME, MMR_IMA_ASSOCIATION.FROM_CARDINALITY, MMR_TO_CLASS.CLASS_NAME AS TO_CLASS_NAME, MMR_IMA_ASSOCIATION.TO_CARDINALITY, CONVERT(VARCHAR,MMR_IMA_ASSOCIATION.MODIFIED_ON,23) AS MODIFIED_ON FROM IMA_ASSOCIATION MMR_IMA_ASSOCIATION, IMA_CLASS MMR_TO_CLASS, IMA_PACKAGE MMR_ASSOC_PACKAGE, IMA_CLASS MMR_FROM_CLASS WHERE (MMR_IMA_ASSOCIATION.TO_CLASS_UID = MMR_TO_CLASS.CLASS_UID AND MMR_IMA_ASSOCIATION.PACKAGE_UID = MMR_ASSOC_PACKAGE.PACKAGE_UID AND MMR_IMA_ASSOCIATION.FROM_CLASS_UID = MMR_FROM_CLASS.CLASS_UID)) “query”
where “ORIGINATOR_NAME” in (‘<Originator_Name>’) and “PACKAGE_NAME” in (‘<Package_Name>’)
order by “ORIGINATOR_NAME”, “PACKAGE_NAME”, “ASSOCIATION_NAME”, “ASSOCIATION_DESC”, “FROM_CLASS_NAME”, “FROM_CARDINALITY”, “TO_CLASS_NAME”, “TO_CARDINALITY”, “MODIFIED_ON”
Displays all associations for the specified From Class. This report displays all classes associated with the specified From Class.
select “TO_PACKAGE_NAME”,
“PACKAGE_NAME”,
“FROM_PACKAGE_NAME”,
“ASSOCIATION_NAME”,
“FROM_CARDINALITY”,
“TO_CLASS_NAME”,
“FROM_CLASS_NAME”,
“ORIGINATOR_NAME”,
“TO_CARDINALITY”,
“MODIFIED_ON”
from (SELECT DISTINCT MMR_ASSOC_PACKAGE.ORIGINATOR_NAME, MMR_ASSOC_PACKAGE.PACKAGE_NAME, MMR_FROM_CLASS.CLASS_NAME AS FROM_CLASS_NAME, MMR_FROM_CLASS.PACKAGE_NAME FROM_PACKAGE_NAME, MMR_IMA_ASSOCIATION.ASSOCIATION_NAME, MMR_IMA_ASSOCIATION.FROM_CARDINALITY, MMR_TO_CLASS.CLASS_NAME as TO_CLASS_NAME, MMR_TO_CLASS.PACKAGE_NAME AS TO_PACKAGE_NAME, MMR_IMA_ASSOCIATION.TO_CARDINALITY, CONVERT(VARCHAR,MMR_IMA_ASSOCIATION.MODIFIED_ON,23) AS MODIFIED_ON FROM IMA_ASSOCIATION MMR_IMA_ASSOCIATION, IMA_CLASS MMR_TO_CLASS, IMA_PACKAGE MMR_ASSOC_PACKAGE, IMA_CLASS MMR_FROM_CLASS WHERE (MMR_IMA_ASSOCIATION.TO_CLASS_UID = MMR_TO_CLASS.CLASS_UID AND MMR_IMA_ASSOCIATION.PACKAGE_UID = MMR_ASSOC_PACKAGE.PACKAGE_UID AND MMR_IMA_ASSOCIATION.FROM_CLASS_UID = MMR_FROM_CLASS.CLASS_UID)) “query”
where “ORIGINATOR_NAME” in (‘<Originator_Name>’) and “PACKAGE_NAME” in (‘<Package_Name>’) and “FROM_CLASS_NAME” in (‘<Class_Name>’)
order by “ORIGINATOR_NAME”, “PACKAGE_NAME”, “ASSOCIATION_NAME”, “FROM_PACKAGE_NAME”, “FROM_CLASS_NAME”
Displays all attributes of the specified class, including its inherited attributes from parent classes.
select “CLASS_NAME”,
“ORIGINATOR_NAME”,
“CLS_ATTR_DEFAULT_VALUE”,
“EDITABLE_FLAG”,
“CLASS_ATTR_NAME”,
“PRNT_CLASS_NAME”,
“PACKAGE_NAME”
from (SELECT DISTINCT MMR_IMA_CLASS.ORIGINATOR_NAME, MMR_IMA_CLASS.PACKAGE_NAME, MMR_IMA_CLASS.CLASS_NAME, MMR_IMA_CLASS_ATTR.PRNT_CLASS_NAME, MMR_IMA_CLASS_ATTR.CLASS_ATTR_NAME, MMR_IMA_CLASS_ATTR.CLS_ATTR_DEFAULT_VALUE, MMR_IMA_CLASS_ATTR.EDITABLE_FLAG FROM IMA_CLASS MMR_IMA_CLASS, IMA_CLASS_ATTR MMR_IMA_CLASS_ATTR WHERE (MMR_IMA_CLASS.CLASS_UID = MMR_IMA_CLASS_ATTR.CLASS_UID)) “query”
where “ORIGINATOR_NAME” in (‘<Originator_Name>’) and “PACKAGE_NAME” in (‘<Package_Name>’) and “CLASS_NAME” in (‘<Class_Name>’)
order by “ORIGINATOR_NAME”, “PACKAGE_NAME”, “CLASS_NAME”, “PRNT_CLASS_NAME”, “CLASS_ATTR_NAME”
The Classes Exploration analytic workflow displays all classes in the metamodel and enables you to explore its contents, such as its attributes and packages.
select “EXTENDABLE_FLAG”,
“PACKAGE_NAME”,
“CLASS_DESC”,
“ORIGINATOR_NAME”,
“MODIFIED_ON”,
“CLASS_NAME”
from (SELECT DISTINCT MMR_IMA_CLASS.ORIGINATOR_NAME, MMR_IMA_CLASS.PACKAGE_NAME, MMR_IMA_CLASS.ORIGINATOR_ID, MMR_IMA_CLASS.PACKAGE_ID, MMR_IMA_CLASS.CLASS_ID, MMR_IMA_CLASS.CLASS_NAME, MMR_IMA_CLASS.CLASS_DESC, CONVERT(VARCHAR,MMR_IMA_CLASS.MODIFIED_ON,23) as MODIFIED_ON, MMR_IMA_CLASS.EXTENDABLE_FLAG FROM IMA_CLASS MMR_IMA_CLASS) “query”
where “ORIGINATOR_NAME” in (‘<Originator_Name>’) and “PACKAGE_NAME” in (‘<Package_Name>’) and “CLASS_NAME” in (‘<Class_Name>’)
order by “ORIGINATOR_NAME”, “PACKAGE_NAME”, “CLASS_NAME”, “CLASS_DESC”, “MODIFIED_ON”, “EXTENDABLE_FLAG”
select “EXTENDABLE_FLAG”,
“PACKAGE_NAME”,
“CLASS_DESC”,
“ORIGINATOR_NAME”,
“MODIFIED_ON”,
“CLASS_NAME”
from (SELECT DISTINCT MMR_IMA_CLASS.ORIGINATOR_NAME, MMR_IMA_CLASS.PACKAGE_NAME, MMR_IMA_CLASS.ORIGINATOR_ID, MMR_IMA_CLASS.PACKAGE_ID, MMR_IMA_CLASS.CLASS_ID, MMR_IMA_CLASS.CLASS_NAME, MMR_IMA_CLASS.CLASS_DESC, CONVERT(VARCHAR,MMR_IMA_CLASS.MODIFIED_ON,23) as MODIFIED_ON, MMR_IMA_CLASS.EXTENDABLE_FLAG FROM IMA_CLASS MMR_IMA_CLASS) “query”
where “ORIGINATOR_NAME” in (‘<Originator_Name>’) and “PACKAGE_NAME” in (‘<Package_Name>’) and “CLASS_NAME” in (‘<Class_Name>’)
order by “ORIGINATOR_NAME”, “PACKAGE_NAME”, “CLASS_NAME”, “CLASS_DESC”, “MODIFIED_ON”, “EXTENDABLE_FLAG”
The Repository Type Exploration analytic workflow displays all repository types in the metamodel and enables you to explore its contents, such as its packages and repository instances.
select “ORIGINATOR_NAME”,
“REPO_TYPE_NAME”,
“REPO_TYPE_DESC”,
“PRODUCT_TYPE”
from (SELECT DISTINCT MMR_IMA_REPO_TYPE.PRODUCT_TYPE, MMR_IMA_ORIGINATOR.ORIGINATOR_NAME, MMR_IMA_REPO_TYPE.REPO_TYPE_ID, MMR_IMA_ORIGINATOR.ORIGINATOR_ID, MMR_IMA_REPO_TYPE.REPO_TYPE_NAME, MMR_IMA_REPO_TYPE.REPO_TYPE_DESC FROM IMA_REPO_TYPE MMR_IMA_REPO_TYPE, IMA_REPO_PACKAGE MMR_IMA_REPO_PACKAGE, IMA_PACKAGE MMR_IMA_PACKAGE, IMA_ORIGINATOR MMR_IMA_ORIGINATOR WHERE (MMR_IMA_REPO_PACKAGE.REPO_TYPE_UID = MMR_IMA_REPO_TYPE.REPO_TYPE_UID AND MMR_IMA_REPO_PACKAGE.PACKAGE_UID = MMR_IMA_PACKAGE.PACKAGE_UID AND MMR_IMA_PACKAGE.ORIGINATOR_UID = MMR_IMA_ORIGINATOR.ORIGINATOR_UID)) “query”
order by “PRODUCT_TYPE”, “ORIGINATOR_NAME”, “REPO_TYPE_NAME”
Metamodel/Metamodel Usage
Provides the total number of elements for each class in the specified repository instance.
select “PACKAGE_NAME”,
“ORIGINATOR_NAME”,
“ELEMENT_UID”,
“CLASS_NAME”,
“CLASS_LABEL”,
“REPO_TYPE_NAME”,
“REPOSITORY_NAME”
from (SELECT MMR_IMA_REPO_PACKAGE.REPO_TYPE_NAME, MMR_IMA_ELEMENT.REPOSITORY_NAME, MMR_IMA_CLASS.CLASS_LABEL, MMR_IMA_CLASS.CLASS_NAME, MMR_IMA_CLASS.PACKAGE_NAME, MMR_IMA_CLASS.ORIGINATOR_NAME, (COUNT(MMR_IMA_ELEMENT.ELEMENT_UID)) as ELEMENT_UID FROM IMA_PACKAGE MMR_IMA_PACKAGE, IMA_REPO_PACKAGE MMR_IMA_REPO_PACKAGE, IMA_ELEMENT MMR_IMA_ELEMENT RIGHT OUTER JOIN IMA_CLASS MMR_IMA_CLASS ON MMR_IMA_ELEMENT.CLASS_ID = MMR_IMA_CLASS.CLASS_ID WHERE (MMR_IMA_CLASS.PACKAGE_UID = MMR_IMA_PACKAGE.PACKAGE_UID AND MMR_IMA_REPO_PACKAGE.PACKAGE_UID = MMR_IMA_PACKAGE.PACKAGE_UID) GROUP BY MMR_IMA_REPO_PACKAGE.REPO_TYPE_NAME, MMR_IMA_ELEMENT.REPOSITORY_NAME, MMR_IMA_CLASS.CLASS_LABEL, MMR_IMA_CLASS.CLASS_NAME, MMR_IMA_CLASS.PACKAGE_NAME, MMR_IMA_CLASS.ORIGINATOR_NAME HAVING (COUNT(MMR_IMA_ELEMENT.ELEMENT_UID)) > 0) “query”
where “REPO_TYPE_NAME” in (‘<Repository_Type_Name>’) and “REPOSITORY_NAME” in (‘<Repository_Name>’)
order by “REPO_TYPE_NAME”, “REPOSITORY_NAME”
Lists all classes that are not used by the metadata elements in the repositories of the specified repository type.
select “CLASS_NAME”,
“ORIGINATOR_NAME”,
“PACKAGE_NAME”,
“CLASS_DESC”,
“REPO_TYPE_NAME”,
“PRODUCT_TYPE”,
“MODIFIED_ON”
from (SELECT MMR_IMA_CLASS.ORIGINATOR_NAME, MMR_IMA_REPO_TYPE.PRODUCT_TYPE, MMR_IMA_REPO_PACKAGE.REPO_TYPE_NAME, MMR_IMA_CLASS.PACKAGE_NAME, MMR_IMA_CLASS.CLASS_NAME, MMR_IMA_CLASS.CLASS_DESC, MMR_IMA_CLASS.DELETED_FLAG, convert(varchar,MMR_IMA_CLASS.MODIFIED_ON,23) as MODIFIED_ON, (COUNT(MMR_IMA_ELEMENT.ELEMENT_UID)) as ELEMENT_UID FROM IMA_PACKAGE MMR_IMA_PACKAGE, IMA_REPO_PACKAGE MMR_IMA_REPO_PACKAGE, IMA_REPO_TYPE MMR_IMA_REPO_TYPE, IMA_ELEMENT MMR_IMA_ELEMENT RIGHT OUTER JOIN IMA_CLASS MMR_IMA_CLASS ON MMR_IMA_ELEMENT.CLASS_ID = MMR_IMA_CLASS.CLASS_ID WHERE (MMR_IMA_CLASS.PACKAGE_UID = MMR_IMA_PACKAGE.PACKAGE_UID AND MMR_IMA_REPO_PACKAGE.PACKAGE_UID = MMR_IMA_PACKAGE.PACKAGE_UID AND MMR_IMA_REPO_PACKAGE.REPO_TYPE_UID = MMR_IMA_REPO_TYPE.REPO_TYPE_UID) AND (MMR_IMA_CLASS.DELETED_FLAG IN (‘N’) ) GROUP BY MMR_IMA_CLASS.ORIGINATOR_NAME, MMR_IMA_REPO_TYPE.PRODUCT_TYPE, MMR_IMA_REPO_PACKAGE.REPO_TYPE_NAME, MMR_IMA_CLASS.PACKAGE_NAME, MMR_IMA_CLASS.CLASS_NAME, MMR_IMA_CLASS.CLASS_DESC, MMR_IMA_CLASS.DELETED_FLAG, MMR_IMA_CLASS.MODIFIED_ON HAVING (COUNT(MMR_IMA_ELEMENT.ELEMENT_UID)) = 0) “query”
where “PRODUCT_TYPE” in (‘<Repository_Type_Product_Type>’) and “REPO_TYPE_NAME” in (‘<Repository_Type_Name>’)
order by “ORIGINATOR_NAME”, “PRODUCT_TYPE”, “REPO_TYPE_NAME”, “PACKAGE_NAME”
I hope you will enjoy the content of my website.
Please leave me a comment below if you have any suggestion for improving