Jeff's Cluttered Mind

Here are some SQL scripts you can use to query the repository. Most of these I got from an Informatica employee, others I put together. Use them as a base to create your own queries or reports.

One word of caution, use a profile with READ ONLY permission to access the repository and NEVER update the repository using SQL unless advised to do so by Informatica support.

The scripts use MS SQL Server syntax, you may have to adjust them to fit your database type and version. These scripts have been tested on PowerCenter repository version 8.6 and 9.1

Find ALL mappings that generate SAP ABAP

Use this to find all mappings that have SAP ABAP.

SELECT A.SUBJECT_AREA,A.MAPPING_NAME,A.MAPPING_LAST_SAVED,
       B.PROGRAM_NAME,B.PROGRAM_TYPE, B.INSTALL_TIME,
       B.HOST_MACHINE,B.USER_NAME, B.CLIENT_SPACE
FROM  dbo.REP_ALL_MAPPINGS A, dbo.OPB_PROGRAM_INFO B
WHERE A.MAPPING_ID = B.MAPPING_ID

Monitor Session errors or rejected rows

This will show "most" errors in the repository as well as sessions with rejected rows for the past 24 hours.

SELECT C.SUBJECT_AREA,A.WORKFLOW_NAME,A.START_TIME,A.END_TIME,
       A.USER_NAME,B.SESSION_NAME,B.MAPPING_NAME,           
       B.SUCCESSFUL_ROWS,B.FAILED_ROWS,B.SUCCESSFUL_SOURCE_ROWS,
       B.FAILED_SOURCE_ROWS, B.FIRST_ERROR_CODE,B.FIRST_ERROR_MSG,
       B.ACTUAL_START
FROM dbo.REP_WFLOW_RUN A, dbo.REP_SESS_LOG B, dbo.REP_SUBJECT C
WHERE A.SUBJECT_ID = B.SUBJECT_ID AND B.SUBJECT_ID = C.SUBJECT_ID
AND   A.WORKFLOW_ID = B.WORKFLOW_ID
AND   A.WORKFLOW_RUN_ID = B.WORKFLOW_RUN_ID
AND   A.START_TIME >= DATEADD(hh,-24,GetDate())
AND   ( B.FAILED_ROWS > 0 OR FIRST_ERROR_CODE <> 0 )
ORDER BY 1,3

Monitor Mapping Changes

Make your auditor happy with this one. Keep track of changes.

SELECT subject_area,mapping_name,mapping_last_saved
FROM dbo.REP_ALL_MAPPINGS
WHERE mapping_last_saved > current_timestamp - 24

Average Workflow Run Time

See avg. wf run times for the past 3 months and find your long running jobs. This script is also good to run before and after a PowerCenter upgrade to see if the upgrade made your workflows run faster or slower.

It would be a good idea to run this query monthly or quarterly and log the results into a database, then you can graph the results and look for trends.

SELECT  SUBJECT_AREA as Folder, WORKFLOW_NAME, 
       Avg( DateDiff(minute,START_TIME,END_TIME) ) as Avg_Run_Time
FROM   dbo.REP_WFLOW_RUN
WHERE START_TIME >= DATEADD(mm, -3,getdate())
Group By SUBJECT_AREA, WORKFLOW_NAME

Workflow Run Times with row count

SELECT a.SUBJECT_AREA as Folder, a.WORKFLOW_NAME, DATEADD(dd, DATEDIFF(dd, 0, START_TIME), 0), DateDiff(minute,a.START_TIME,END_TIME) as Run_Time_Minutes, sum(successful_rows) as Row_Count
FROM INFPRD9.dbo.REP_WFLOW_RUN A, dbo.REP_SESS_LOG B
WHERE A.SUBJECT_ID = B.SUBJECT_ID
AND   A.WORKFLOW_ID = B.WORKFLOW_ID
AND   A.WORKFLOW_RUN_ID = B.WORKFLOW_RUN_ID
AND START_TIME >= DATEADD(dd,-30,getdate())
Group By a.SUBJECT_AREA,  a.WORKFLOW_NAME, DATEADD(dd, DATEDIFF(dd, 0, START_TIME), 0), DateDiff(minute,a.START_TIME,END_TIME)

What's Scheduled

This will give you an idea of what's scheduled. The result set will show you what is actually scheduled but will also show workflows with schedules but not currently scheduled. I can't quite figure out how to limit this to only currently scheduled jobs.

SELECT A.SUBJECT_AREA, A.WORKFLOW_NAME, A.SCHEDULER_NAME , A.SUBJECT_ID, 
       A.START_TIME, B.Run_Options
FROM  dbo.REP_WORKFLOWS a, dbo.OPB_SCHEDULER b
WHERE  A.Scheduler_ID = b.Scheduler_id AND B.Run_Options  NOT IN ( 1,3 )
ORDER BY A.SUBJECT_AREA 
--Run_Options 1 = run on demand
--            2 = Run Once
--            3 = Run on Demand
--            4 = Run Evry
--            8 = Customized Repeat
--A.START_TIME IS NOT NULL

Find Invalid Objects

List invalid sessions, mappings & workflows. You can use the output from this as the input for the pmrep validate command.

SELECT SUBJECT_AREA, SUBJECT_ID, TASK_NAME, TASK_ID, IS_VALID, 
       LAST_SAVED, IS_REUSABLE, TASK_TYPE, TASK_TYPE_NAME 
FROM dbo.REP_ALL_TASKS
WHERE TASK_TYPE IN (68, 70, 71) AND IS_VALID = 0

Thank you for your support

or