Informatica metadata repository stores and maintains information about all the objects in Informatica. They contain details of connection information, users, folders, mappings, sources, targets etc. These information can serve many purposes while accessed through external SQL query.

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 in informatica 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 DESC

 

Find 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


Have a question on this subject?

Ask questions to our expert community members and clear your doubts. Asking question or engaging in technical discussion is both easy and rewarding.

Are you on Twitter?

Start following us. This way we will always keep you updated with what's happening in Data Analytics community. We won't spam you. Promise.

  • Implementing Informatica Incremental Aggregation

    Using incremental aggregation, we apply captured changes in the source data (CDC part) to aggregate calculations in a session. If the source changes incrementally and we can capture the changes, then we can configure the session to process those...

  • APEAR - A tool for automating Informatica Performance Tuning

    DWBIConcepts is launching APEAR – an Automated Performance Evaluation and Reporting tool for Informatica. As the name suggests, this tool will help you tune the performance of Informatica sessions fully automatically. Now don't waste your precious...

  • Loading Flatfiles delimited by comma and double quotes

    In this article let us take up a very trivial but an important aspect that we as DW developers usual face. This is related to loading flat file sources. Whenever we have flat file sources we usual ask source systems for a specific type of field...

  • Informatica Join Vs Database Join

    In this yet another "DWBI Concepts' Original article", we test the performance of Informatica PowerCentre 8.5 Joiner transformation versus Oracle 10g database join. This article gives a crucial insight to application developer in order to take...

  • Calling C executable from Java Transform

    We are going to do is, to call C++ Executable from Informatica, using Passive Java Transform and capture the output of the C++ using Java and write the result to corresponding target column.

  • All about Informatica Lookup

    A Lookup is a Passive, Connected or Unconnected Transformation used to look up data in a relational table, view, synonym or flat file. The integration service queries the lookup table to retrieve a value based on the input source value and the...

  • Best Informatica Interview Questions & Answers

    Welcome to the finest collection of Informatica Interview Questions with standard answers that you can count on. Read and understand all the questions and their answers below and in the following pages to get a good grasp in Informatica. If you...

  • How to Tune Performance of Informatica Joiner Transformation

    Joiner transformation allows you to join two heterogeneous sources in the Informatica mapping. You can use this transformation to perform INNER and OUTER joins between two input streams. For performance reasons, I recommend you ONLY use JOINER...

  • Informatica Dynamic Lookup Cache

    A LookUp cache does not change its data once built. But what if the underlying table upon which lookup was done changes the data after the lookup cache is created? Is there a way so that the cache always remain up-to-date even if the underlying...

  • Informatica Performance Tuning - Complete Guide

    This article is a comprehensive guide to the techniques and methodologies available for tuning the performance of Informatica PowerCentre ETL tool. It's a one stop performance tuning manual for Informatica.