Data Integration Available Reports.
This page will show you all the Available reports for Data Integration, 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
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”