Querying the Informatica PowerCenter Repository

The following are a collection of SQL Server Scripts for querying the Informatica PowerCenter Repository. Please note that this is not supported by Informatica, so do so at your own peril!!

Mappings with Pre or Post SQL:

SELECT FLD.subject_area AS folder, MAP.mapping_name, ATR.attr_name, ATR.attr_value
FROM rep_widget_attr ATR
, rep_all_mappings MAP
, rep_subject FLD
WHERE MAP.mapping_id = ATR.mapping_id
AND MAP.subject_id = FLD.subject_id
AND ATR.attr_value IS NOT NULL
AND ATR.attr_name IN ( 'Pre SQL', 'Post SQL' );

Mappings Using a Column:

SELECT sub.SUBJ_NAME AS folder, map.MAPPING_NAME, wi.INSTANCE_NAME, tf.TARGET_NAME AS field_name
FROM OPB_MAPPING AS map INNER JOIN
OPB_SUBJECT AS sub ON map.SUBJECT_ID = sub.SUBJ_ID INNER JOIN
OPB_WIDGET_INST AS wi ON map.MAPPING_ID = wi.MAPPING_ID INNER JOIN
OPB_OBJECT_TYPE AS ot ON wi.WIDGET_TYPE = ot.OBJECT_TYPE_ID INNER JOIN
OPB_TARG AS targ ON wi.WIDGET_ID = targ.TARGET_ID INNER JOIN
OPB_WIDGET_DEP AS wd ON map.MAPPING_ID = wd.MAPPING_ID AND wi.INSTANCE_ID = wd.TO_INSTANCE_ID INNER JOIN
OPB_TARG_FLD AS tf ON wi.WIDGET_ID = tf.TARGET_ID AND wd.TO_FIELD_ID = tf.FLDID
WHERE 1=1
AND (tf.TARGET_NAME LIKE '%XYZ%')
AND sub.SUBJ_NAME = 'My Folder'

Lookup Properties:

SELECT PARENT_SUBJECT_AREA
, SUBJECT_AREA
, mapping_name
, Lkp_name
, ATTR_VALUE
, ValType
FROM (SELECT all_map.PARENT_SUBJECT_AREA
, all_map.SUBJECT_AREA
, CAST(all_map.MAPPING_NAME AS VARCHAR(255)) AS mapping_name
, wid.INSTANCE_NAME AS Lkp_name
, widat.ATTR_VALUE
, CASE widat.attr_id WHEN 2 THEN 'Table_name' WHEN 6 THEN 'src_tgt' WHEN 5 THEN 'join_approach' WHEN 3 THEN 'lkp_Cached' END AS ValType
, all_map.MAPPING_VERSION_NUMBER
, wid.VERSION_NUMBER
, widat.ATTR_ID
, RANK() OVER(PARTITION BY all_map.mapping_name, wid.INSTANCE_NAME ORDER BY wid.VERSION_NUMBER DESC) Ranking
FROM dbo.REP_ALL_MAPPINGS AS all_map 
INNER JOIN dbo.REP_WIDGET_INST AS wid ON all_map.MAPPING_ID = wid.MAPPING_ID 
INNER JOIN dbo.OPB_WIDGET_ATTR AS widat ON wid.WIDGET_ID = widat.WIDGET_ID AND widat.WIDGET_TYPE = wid.WIDGET_TYPE
WHERE (wid.WIDGET_TYPE = 11) AND (widat.ATTR_ID IN (2, 6, 5, 3))) AS VALS
WHERE (1 = 1) AND (Ranking = 1) AND (mapping_name = 'XYZ')
ORDER BY PARENT_SUBJECT_AREA, SUBJECT_AREA, mapping_name

Records Processed per Second

SELECT WORKFLOW_NAME
, session_name
, session_start
, session_end
, Duration_S
, source_applied_rows
, source_affected_rows
, source_rejected_rows
, target_applied_rows
, target_affected_rows
, target_rejected_rows
, CASE source_applied_rows WHEN 0 THEN 0 ELSE source_applied_rows / Duration_S END AS Rows_Per_Sec
FROM (SELECT wr.WORKFLOW_NAME
    , tir.INSTANCE_NAME AS session_name
    , tir.START_TIME AS session_start
    , tir.END_TIME AS session_end
    , DATEDIFF(s, tir.START_TIME, tir.END_TIME) AS Duration_S
    , SUM(CASE WHEN ot.object_type_name = 'Source Qualifier' THEN sl.applied_rows ELSE 0 END) AS source_applied_rows
    , SUM(CASE WHEN ot.object_type_name = 'Source Qualifier' THEN sl.affected_rows ELSE 0 END) AS source_affected_rows
    , SUM(CASE WHEN ot.object_type_name = 'Source Qualifier' THEN sl.rejected_rows ELSE 0 END) AS source_rejected_rows
    , SUM(CASE WHEN ot.object_type_name = 'Target Definition' THEN sl.applied_rows ELSE 0 END) AS target_applied_rows
    , SUM(CASE WHEN ot.object_type_name = 'Target Definition' THEN sl.affected_rows ELSE 0 END) AS target_affected_rows
    , SUM(CASE WHEN ot.object_type_name = 'Target Definition' THEN sl.rejected_rows ELSE 0 END) AS target_rejected_rows
FROM  dbo.OPB_SWIDGINST_LOG AS sl
INNER JOIN dbo.OPB_TASK_INST_RUN AS tir ON sl.TASK_INSTANCE_ID = tir.INSTANCE_ID AND sl.WORKFLOW_RUN_ID = tir.WORKFLOW_RUN_ID
INNER JOIN dbo.OPB_WFLOW_RUN AS wr ON wr.WORKFLOW_RUN_ID = tir.WORKFLOW_RUN_ID
INNER JOIN dbo.OPB_OBJECT_TYPE AS ot ON sl.WIDGET_TYPE = ot.OBJECT_TYPE_ID
WHERE (1 = 1) AND (wr.START_TIME > DATEADD(m, - 2, GETDATE())) AND (tir.END_TIME <> '1753-01-01 00:00:00.000')
GROUP BY wr.WORKFLOW_NAME, wr.START_TIME, tir.INSTANCE_NAME, tir.START_TIME, tir.END_TIME) AS Vals

Mapping Properties

SELECT all_map.SUBJECT_AREA
, all_map.MAPPING_NAME
, wid.WIDGET_ID
, wid.WIDGET_TYPE_NAME
, wid.INSTANCE_NAME
, widat.ATTR_ID
, widat.ATTR_NAME
, widat.ATTR_VALUE
FROM dbo.REP_ALL_MAPPINGS AS all_map
INNER JOIN dbo.REP_WIDGET_INST AS wid ON all_map.MAPPING_ID = wid.MAPPING_ID
INNER JOIN dbo.OPB_WIDGET AS wdgt ON wdgt.WIDGET_ID = wid.WIDGET_ID AND wdgt.RU_VERSION_NUMBER = wid.VERSION_NUMBER AND wdgt.IS_VISIBLE = 1
INNER JOIN dbo.REP_WIDGET_ATTR AS widat ON widat.WIDGET_ID = wid.WIDGET_ID AND widat.WIDGET_TYPE = wid.WIDGET_TYPE AND widat.VERSION_NUMBER = wdgt.VERSION_NUMBER
ORDER BY all_map.SUBJECT_AREA, all_map.MAPPING_NAME