Useful Informatica PowerCenter Repository Queries

Every informatica PowerCenter Developer and admin need to do some monitoring and insights on the ETL environment.
Sometimes the Workflow monitoring client can be down , or not certified with the user’s machine.
The perfect approach here is to run SQL queries against informatica repository database schema.
In this article, we are providing some pre-tested queries that can help with some cases .
1-
/* Get Columns Types of a Specific Database Table */
SELECT table_name, column_name, data_type, data_length
FROM USER_TAB_COLUMNS
WHERE table_name = 'TMP_QRY_1' ; -- Put the table name here
2-
/* Get Database Column Table Name Using a Database Column */
SELECT table_name,  column_name
FROM USER_TAB_COLUMNS
where table_name in (select table_name from all_tables) and column_name in ('COUNTRY','REGION') ORDER BY 1
3-
/* Get Informatica Target Table Name */
SELECT FOLDER_NAME,
         WORKFLOW_NAME,
         SESSION_NAME,
         TARGET_NAME,
         TARGET_TABLE_NAME
    FROM (SELECT DISTINCT Z.SUBJ_NAME   FOLDER_NAME,
                          C.TASK_NAME   WORKFLOW_NAME,
                          A.INSTANCE_NAME SESSION_NAME,
                          D.INSTANCE_NAME TARGET_NAME,
                          B.ATTR_VALUE  TARGET_TABLE_NAME
            FROM OPB_TASK_INST   A,
                 OPB_SWIDGET_ATTR B,
                 OPB_TASK        C,
                 OPB_SUBJECT     Z,
                 OPB_SWIDGET_INST D,
                 OPB_TASK        T,
                 OPB_SESS_EXTNS  E
           WHERE     C.SUBJECT_ID = Z.SUBJ_ID
                 AND C.TASK_TYPE IN (70, 71)
                 AND C.TASK_ID = A.WORKFLOW_ID
                 AND A.TASK_ID = B.SESSION_ID
                 AND A.TASK_ID = D.SESSION_ID
                 AND D.WIDGET_TYPE = 2
                 AND D.SESS_WIDG_INST_ID = B.SESS_WIDG_INST_ID
                 AND B.ATTR_ID = 19
                 AND B.ATTR_VALUE IS NOT NULL
                 AND T.TASK_ID = D.SESSION_ID
                 AND E.SESSION_ID = D.SESSION_ID
                 AND E.SESS_WIDG_INST_ID = D.SESS_WIDG_INST_ID
                 AND C.IS_VISIBLE = 1
                 AND C.VERSION_NUMBER = A.VERSION_NUMBER
                 AND T.IS_VISIBLE = 1
                 AND D.VERSION_NUMBER = T.VERSION_NUMBER
                AND B.VERSION_NUMBER = T.VERSION_NUMBER
                 AND E.VERSION_NUMBER = D.VERSION_NUMBER
          UNION
          SELECT DISTINCT Z.SUBJ_NAME   FOLDER_NAME,
                          C.TASK_NAME   WORKFLOW_NAME,
                          A.INSTANCE_NAME SESSION_NAME,
                          D.INSTANCE_NAME TARGET_NAME,
                          B.ATTR_VALUE  TARGET_TABLE_NAME
            FROM OPB_TASK_INST   A,
                 OPB_WIDGET_ATTR B,
                 OPB_TASK        C,
                 OPB_SUBJECT     Z,
                 OPB_SWIDGET_INST D,
                 OPB_TASK        T,
                 OPB_SESS_EXTNS  E,
                 OPB_MAPPING     M
           WHERE     C.SUBJECT_ID = Z.SUBJ_ID
                 AND C.TASK_TYPE IN (70, 71)
                 AND C.TASK_ID = A.WORKFLOW_ID
                 AND A.TASK_ID = D.SESSION_ID
                 AND B.MAPPING_ID = D.MAPPING_ID
                 AND B.WIDGET_ID = D.WIDGET_ID
                 AND B.WIDGET_TYPE = D.WIDGET_TYPE
                 AND D.WIDGET_TYPE = 2
                 AND B.ATTR_ID = 19
                 AND B.ATTR_VALUE IS NOT NULL
                 AND T.TASK_ID = D.SESSION_ID
                 AND E.SESSION_ID = D.SESSION_ID
                 AND E.SESS_WIDG_INST_ID = D.SESS_WIDG_INST_ID
                 AND M.MAPPING_ID = B.MAPPING_ID
                 AND C.IS_VISIBLE = 1
                 AND C.VERSION_NUMBER = A.VERSION_NUMBER
                 AND T.IS_VISIBLE = 1
                 AND D.VERSION_NUMBER = T.VERSION_NUMBER
                 AND E.VERSION_NUMBER = D.VERSION_NUMBER
                 AND M.IS_VISIBLE = 1
                 AND B.VERSION_NUMBER = M.VERSION_NUMBER
                 AND NOT EXISTS
                         (SELECT *
                            FROM OPB_SWIDGET_ATTR
                           WHERE     SESSION_ID = D.SESSION_ID
                                 AND SESS_WIDG_INST_ID = D.SESS_WIDG_INST_ID
                                 AND ATTR_ID = 19
                                 AND VERSION_NUMBER = D.VERSION_NUMBER)) DUAL
ORDER BY 1,
         2,
         3,
         4,
         5;
4-
/* Get Informatica Target Table Name Info */
select * from BIAPPS_INFA_REP.rep_all_targets
where parent_target_name like '%ABF%';
5-
/* Get mapping(s) name(s) for a source table */
SELECT   SUBJECT_AREA , MAPPING_NAME
  FROM BIAPPS_INFA_REP.REP_ALL_MAPPINGS
  where MAPPING_NAME in (

<div>  SELECT MAPPING_NAME FROM BIAPPS_INFA_REP.REP_SRC_MAPPING

  where SOURCE_NAME  = :ENTER_SRC_TBL_NAME);
6-

/* Number of transformation */
--transofrmations :
select WIDGET_TYPE_NAME Transformation_Type,
count (*) Number_OF_Transformations
from  "BIAPPS_INFA_REP"."REP_ALL_TRANSFORMS"
group by WIDGET_TYPE_NAME ;

7-

/*  Get Informatica Mapping Info    */

select mapping_name from BIAPPS_INFA_REP.OPB_mapping                     -- GET TARGET TABLE NAME INFO
where mapping_name like '%Daily%';

8-

------------------ Get Infa Workflow Session run Status   ----------------

select * from biapps_infa_rep.rep_sess_log
order by session_timestamp desc ;

9-

/* Get Informatica Workflow Run Status */

select
SUBJECT_AREA folder_name,
WORKFLOW_NAME,
to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') as Current_Date_Time,
to_char(START_TIME, 'YYYY-MM-DD HH24:MI:SS') as Starting_Date_Time,
to_char(END_TIME, 'YYYY-MM-DD HH24:MI:SS')as End_Date_Time,
round((END_TIME-START_TIME) *24*60,2) Time_Taken_Min,
RUN_ERR_CODE,
RUN_ERR_MSG,
RUN_STATUS_CODE
from
biapps_infa_rep.REP_WFLOW_RUN
where workflow_name like '%SDE_Product_Dim%'     ---------------------    put the workflow name here or remove the linbe for all
order by start_time desc;

10-

/* Get Informatica Failed Sessions in the Past 10 Days */

SELECT Subject_Area AS Folder,
Session_Name,
Last_Error AS Error_Message,
DECODE (Run_Status_Code,  3, 'Failed',  4, 'Stopped',  5, 'Aborted')
AS Status,
Actual_Start AS Start_Time,
Session_TimeStamp
FROM biapps_infa_rep.rep_sess_log
WHERE     run_status_code <> 1
AND TRUNC (Actual_Start) BETWEEN TRUNC (SYSDATE - 10) AND TRUNC (SYSDATE)
ORDER BY 1, 2;

11-

/* Update Multiple Rows in a database table */

Alter Table WC_AGGR_F
add month_name varchar2(9) ;


UPDATE WC_AGGR_F
   SET MONTH_NAME =
       CASE MONTH
            WHEN 1 THEN 'January'
            WHEN 2 THEN 'Febreuary'
            WHEN 3 THEN 'March'
            WHEN 4 THEN 'April'
WHEN 5 THEN 'May'
WHEN 6 THEN 'June'
WHEN 7 THEN 'July'
WHEN 8 THEN 'August'
WHEN 9 THEN 'September'
WHEN 10  THEN 'October'
WHEN 11  THEN 'November'
WHEN 12  THEN 'December'
       END ;

Related Articles