Informatica PowerCenter Repository Queries : Best 11 Queries That Help

Informatica PowerCenter Repository Queries

Executive Summary: Mastering Informatica PowerCenter Repository Queries

Informatica PowerCenter Repository Queries are SQL-based commands used to extract metadata and operational insights directly from the repository database, bypassing the limitations of the Workflow Monitor client. By querying tables like OPB_TASK or views like REP_WFLOW_RUN, developers and admins can troubleshoot failed sessions, track performance metrics, and audit mapping structures with higher speed and precision.

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 ;

FAQ:

1. What are Informatica PowerCenter Repository Queries?

They are SQL statements run directly against the repository database to extract metadata and execution statistics. These queries provide a faster alternative to the Informatica client tools for monitoring and auditing.

2. Why should I query the repository instead of using the Workflow Monitor?

Querying the database is often faster, allows for custom filtering, and is essential when the client tools are unavailable. It also enables bulk reporting that the GUI cannot easily perform.

3. Is it safe to run SELECT queries on the repository?

Yes, running SELECT queries is generally safe and recommended for read-only metadata extraction. However, you should never perform INSERT or UPDATE operations on OPB_ tables, as this can corrupt your repository.

4. What is the difference between OPB tables and REP views?

OPB tables are the core “base” tables where Informatica stores its raw data. REP views are user-friendly layers built over those tables that make querying much simpler and more readable.

5. How can I find the start and end times of a workflow via SQL?

You can query the REP_WFLOW_RUN view, which includes START_TIME and END_TIME columns. By subtracting these, you can calculate the total duration of the ETL process.

6. How do I identify which sessions failed in the last 24 hours?

Query the REP_SESS_LOG table and filter by RUN_STATUS_CODE where it does not equal 1 (Success). Adding a date filter like Actual_Start > SYSDATE - 1 will isolate the last 24 hours.

7. Can I find which mapping uses a specific source table?

Yes, by querying the REP_SRC_MAPPING view, you can filter by SOURCE_NAME to see all associated mappings. This is vital for performing impact analysis before database changes.

8. What database permissions do I need to run these queries?

You typically need read-access (SELECT) permissions on the schema where the Informatica repository resides. Contact your Database Administrator to request access to the REP_ views specifically.

9. How do I count the number of transformations in my repository?

Use a COUNT and GROUP BY statement on the REP_ALL_TRANSFORMS view. This will categorize and count every transformation type, from Expressions to Aggregators.

10. Can these queries be used for automated reporting?

Absolutely, these SQL scripts can be integrated into Tableau, Power BI, or simple shell scripts. This allows teams to create automated health dashboards for their ETL environment.

Related Articles

Picture of Technical Director

Technical Director

Tamer Shalaby is A highly experienced Oracle Certified Specialist Software Engineer with over two decades of international expertise. His career spans sixteen countries across Europe, the Middle East, and Africa, where he has specialized in data analytics, business intelligence, and database administration. The text details a comprehensive background in API integration, ETL processes, and cloud analytics, including a significant tenure at Oracle Corporation and various director-level roles. His portfolio includes successful project deliveries for major global entities in sectors such as telecommunications, banking, and government. Furthermore, the record highlights his academic credentials in computer software engineering alongside numerous technical certifications and prestigious industry awards.

My Personal Favourites