Informatica PowerCenter Repository Queries

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

My Personal Favourites