Querying the Repository

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  PayPal




IMPORTANT,  YOU are responsible for backing up your systems before trying or implementing any suggestions from this blog. I do not guarantee 100% accuracy of any code examples. I do not presume to know your system environment(s) or Security requirements; all code examples from this blog should be thoroughly tested before any attempted use on a production system.

5 Response to "Querying the Repository"

  1. Anonymous 3/27/15, 11:43 AM
    Is there a way to know, who saved a mapping last time?

    Regards
    Jagadish Petluri
  2. J. Kinzer 4/1/15, 2:06 PM
    Good question Jagadish, I thought you could right click on the mapping (or other object) in the Repository Manager and see the last modified user in the Properties but you cannot.

    I could not find this last modified user in the repository either. It may be available but after a quick look in the repository and I could not find it. Sorry.
  3. Bouke van Boesschoten 4/30/15, 7:37 AM
    Jagadish,

    Check out this query:

    select C.SUBJ_NAME ,
    B.OBJECT_NAME ,
    TO_DATE(LAST_SAVED, 'mm/dd/yyyy hh24:mi:ss') DATETIME ,
    a.USER_NAME
    from REP_USERS a ,
    REP_VERSION_PROPS B ,
    opb_subject c
    where 1 =1
    --AND B.OBJECT_NAME = 'REXP_set_map_var'
    AND A.USER_ID = B.USER_ID
    AND b.SUBJECT_ID = c.SUBJ_ID
    ORDER BY to_date(last_saved, 'mm/dd/yyyy hh24:mi:ss') DESC ;
  4. J. Kinzer 5/5/15, 7:14 PM
    This comment has been removed by the author.
  5. J. Kinzer 5/5/15, 7:19 PM
    Bouke,

    Thank you for the query! I'm glad you found the last saved date!!! Here's your query with a few changes, I added Object Type Description and changed the date conversion syntax to SQL Server 2012.

    Jeff

    SELECT C.SUBJ_NAME, B.OBJECT_NAME , b.OBJECT_TYPE, [OBJECT_TYPE_NAME],
    LAST_SAVED, CONVERT(datetime, Last_Saved) as Last_Saved_Date,
    a.USER_NAME
    FROM REP_USERS a ,
    REP_VERSION_PROPS B ,
    opb_subject c,
    [OPB_OBJECT_TYPE] d
    WHERE 1 = 1
    AND A.USER_ID = B.USER_ID
    AND b.SUBJECT_ID = c.SUBJ_ID
    and b.OBJECT_TYPE = d.OBJECT_TYPE
    ORDER BY CONVERT(datetime, Last_Saved) DESC ;

Post a Comment