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.

11 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 ;

  6. priya 1/24/18, 10:57 AM
    Hi,

    Can anyone provide query to get failed workflow status ?database is Ms SQL server
  7. Ganesh Ponna 4/18/18, 2:02 AM
    Marhaba,


    Thank you! Thank you! Thank you! Your blog was a total game changer!


    Are there any other options to use within Expression transformation in Mapping Architect for Vision?

    Say I have a table as,

    A_COLUMN_NAME string(50)
    B_COLUMN_NAME string(40)
    E_COLUMN_NAME NUMBER(15,5)
    C_COLUMN_NAME NUMBER(15,5)
    D_COLUMN_NAME DATE

    I have defined 2 sets of ports -

    $STRING_PORTS$=A_COLUMN, B_COLUMN
    $NUMBER_PORTS$=C_COLUMN





    Awesome! Thanks for putting this all in one place. Very
    useful!



    Obrigado,



  8. Abhiram Sharma 5/12/18, 5:44 AM
    Hello There,


    You make learning and reading addictive. All eyes fixed on you. Thank you being such a good and trust worthy guide.

    could you please check the link again that you've sent? I only get an error message that a SharePoint error had occurred.
    As of my understanding the Power Exchange products cannot be used with PowerCenter Express (PCX). PCX does provide quite a few connectivity options, so if you can find some ODBC or JDBC driver to access SFDC you might be able to get your job done. Otherwise I fear you will have to switch to one of the full-fledged PowerCenter or Informatics Data Quality products.
    But great job man, do keep posted with the new updates.


    Best Regards,
    Pranathi
  9. preethi Sharma 5/28/18, 6:02 AM
    Hi There,

    You make learning and reading addictive. All eyes fixed on you. Thank you being such a good and trust worthy guideInformatica Data quality training.

    We are working on integration of SFDC (SalesForce CRM) with Exact Target(ET) (SalesForce Marketing Clound) through Informatica PowerCenter 9.5.1.
    But facing below issues:
    1) While importing SalesForce object into PowerCenter : Getting an error "timed out".
    2) How to connect to Exact target from power Center using API (we tested this from Java by passing proxy to webservie but not sure how to pass proxy to webservice in power Center).

    Awesome! Thanks for putting this all in one place. Very useful!

    Many Thanks,
    Preethi.
  10. Morgan lee 5/29/18, 1:14 AM
    Greetings Kinzer,

    Nice to be visiting your blog again, it has been months for me. Well this article that i’ve been waited for so long.

    Given this, I would be surprised to see anything on the test that was not covered in the modules, as it relates to the Cloud tool and topics covered about SalesForce. I would presume questions that test general ETL transformation knowledge based on past PowerCentre tool experience.
    I started using Informatica IDQ training USA blog for my training practice.
    But great job man, do keep posted with the new updates.

    Kind Regards,
    Morgan
  11. Ganesh Ponna 5/29/18, 3:24 AM
    Salve


    Muchas Gracias Mi Amigo! You make learning so effortless. Anyone can follow you and I would not mind following you to the moon coz I know you are like my north star.

    How do you install a version 10.1.1 and import projects of previous versions ? When I tried (as a workaround) to import a full repository of a previous version in the new version, I have got an error Informatica IDQ training USA saying that I cannot do this. (I tried through the Admin console)






    I read multiple articles and watched many videos about how to use this tool - and was still confused! Your instructions were easy to understand and made the process simple.


    Thanks,

Post a Comment