Informatica Metadata Repository Queries

Informatica metadata repository stores and maintains information about all the objects in Informatica. Among other things, they contain details of connection information, users, folders, mappings, sources, targets etc. These information can be accessed through SQL query and are highly valuable for various troubleshooting and maintenance activities. We present here a few examples of type of tasks you may perform using repository metadata.

Search for Truncate Table Option

Suppose you want to find out on which of your sessions, truncate target table option is set on. Instead of manually opening each task and checking if the truncate table option is on, you may use the below query:

select 
   task_name,
   'Truncate Target Table' ATTR,
   decode(attr_value,1,'Yes','No') Value 
from OPB_EXTN_ATTR OEA,
     REP_ALL_TASKS RAT  
where 
      OEA.SESSION_ID=rat.TASK_ID 
      and attr_id=9 

Find Mappings where SQL Override is used

Below query will give you count of mapping instance where SQL Override has been used. The count is presented folder by folder.

WITH detail AS
(SELECT 
       c.subject_area, 
       c.mapping_name,
       d.instance_name source_qualifier_name,
       CASE WHEN a.attr_value IS NOT NULL 
            THEN 1 ELSE 0 
       END as OVR_OK
FROM 
       rep_all_mappings c,
       opb_widget_inst d,
       opb_widget_attr a 
WHERE 
       c.mapping_id = d.mapping_id
       AND c.mapping_version_number = d.version_number
       AND d.widget_type = 3
       AND d.widget_id = a.widget_id
       AND a.widget_type = d.widget_type
       AND a.attr_id = 1
)
SELECT 
       subject_area, 
        'SQ_OVERIDE' STATUS,
       COUNT (DISTINCT mapping_name) NO_OF_Mapping,
       COUNT (DISTINCT (mapping_name || source_qualifier_name)) NO_OF_SQ_IN_MAPPING,
       COUNT (DISTINCT (source_qualifier_name)) NO_OF_DISTINCT_SQ
FROM detail
WHERE OVR_OK =1
GROUP BY subject_area
UNION
SELECT 
       subject_area, 'SQ_NON_OVERIDE',
       COUNT (DISTINCT mapping_name) nb_mapping,
       COUNT (DISTINCT (mapping_name || source_qualifier_name)) nb_map_inst,
       COUNT (DISTINCT (source_qualifier_name)) nb_inst
FROM detail
WHERE OVR_OK =0 
GROUP BY subject_area

Find Tracing Levels for Informatica Sessions

Sessions can have different tracing levels (Terse to Verbose). Often in Development environment we test our mappings with verbose tracing levels and then forget to reduce the level while promoting to Production environments. This creates issues like abnormal "SessLogs" growth, slower mapping performance etc.

This query will give tracing information along with session names so that you can quickly identify unintended tracing levels without opening each sessions manually.

select 
       task_name,
       decode (attr_value,
                0,'None',
                1,'Terse',
                2,'Normal',
                3,'Verbose Initialisation',
                4,'Verbose Data','') Tracing_Level
from 
     REP_SESS_CONFIG_PARM CFG, 
     opb_task TSK
WHERE 
      CFG.SESSION_ID=TSK.TASK_ID
      and tsk.TASK_TYPE=68
      and attr_id=204 
      and attr_type=6

Find name of all stored procedure used in stored procedure transformation

This query is helpful when you require to know name of all stored procedure being used ininformatica stored procedure transformation

select 
       attr_value 
from
      OPB_WIDGET_ATTR
where 
      widget_type=6 
      and attr_id=1

Find who modified (saved) a mapping last time

This information is available under mapping properties. But the below query lists down this information for all the mappings in one place.

SELECT 
       substr(rpl.event_time,7,4) || substr(rpl.event_time,6,1) ||
       substr(rpl.event_time,1,5) ||  ' ' substr(rpl.event_time,12,11) "EventTimestamp" ,
       usr.user_name "Username",
       DECODE(rpl.object_type_id,21,s21.subj_name,('('rpl.object_type_id')')) "Folder",
       obt.object_type_name "Type",
       DECODE(rpl.object_type_id,21,map.mapping_name,('('rpl.object_type_id')')) "Object"
FROM
       opb_reposit_log rpl,
       opb_object_type obt,
       opb_subject fld,
       opb_mapping map,
       opb_users usr,
       opb_subject s21
WHERE 
       obt.object_type_name = 'Mapping'
       AND rpl.object_type_id = obt.object_type_id
       AND rpl.object_id = map.mapping_id(+)
       AND rpl.object_id = fld.subj_id(+)
       AND rpl.event_uid = usr.user_id
       AND map.subject_id = s21.subj_id(+)
ORDER BY
       1 DESCFind Lookup Information from Repository

This query will give information about lookup transformations like lookup name,Tablename ,Mapping name etc.

Select distinct 
       wid.WIDGET_ID, 
       all_map.mapping_name, 
       wid.INSTANCE_NAME Lkp_name, 
       Decode(widat.attr_id,2,widat.attr_value) Table_name,
       decode (widat.attr_id,6,widat.attr_value) src_tgt
FROM  
       rep_all_mappings ALL_MAP, 
       rep_widget_inst wid,  
       OPB_WIDGET_ATTR widat
where 
       all_map.mapping_id=wid.mapping_id
       and wid.WIDGET_ID=widat.WIDGET_ID
       and wid.WIDGET_TYPE=11
       and widat.WIDGET_TYPE=11
       and widat.ATTR_ID in (2,6)

Find all Invalid workflows from Metadata repository

This query will list of all invalid workflows under the given subject area (folder)

select 
       opb_subject.subj_name, 
       opb_task.task_name
from  
       opb_task,   
       opb_subject
where 
       task_type = 71 
       and is_valid = 0 
       and opb_subject.subj_id = opb_task.subject_id
       and UPPER(opb_subject.SUBJ_NAME) like UPPER('YOUR_FOLDER_NAME')

Generate a list of failed sessions from the repository

Here is a query that will display list of failed / aborted / terminated sessions from each of the folders in the repository

SELECT 
       RSL.SUBJECT_AREA AS FOLDER, 
       RW.WORKFLOW_NAME AS WORKFLOW, 
       RSL.SESSION_NAME AS SESSION_NAME, 
       DECODE(RSL.RUN_STATUS_CODE,
              3,'FAILED',
              4,'STOPPED',
              5,'ABORTED',
              15,'TERMINATED','UNKNOWN') AS STATUS, 
       RSL.FIRST_ERROR_CODE AS FIRST_ERROR, 
       RSL.FIRST_ERROR_MSG AS ERROR_MSG, 
       RSL.ACTUAL_START AS START_TIME, 
       RSL.SESSION_TIMESTAMP AS END_TIME 
FROM 
       REP_SESS_LOG RSL,
       REP_WORKFLOWS RW 
WHERE 
       RSL.RUN_STATUS_CODE IN (3,4,5,14,15) 
       AND RW.WORKFLOW_ID = RSL.WORKFLOW_ID 
       AND RW.SUBJECT_ID = RSL.SUBJECT_ID AND
       RSL.SUBJECT_AREA ='CDW_FDR2_SERVICEQUALITY'
ORDER BY 
       RSL.SESSION_TIMESTAMP DESC