ds-blog-icon.png

THE DATA DOWNLOAD

As a business intelligence consulting company, we pride ourselves on being able
to deliver on our projects as well as provide good quality content to our readers.

Querying the Informatica PowerCenter Repository

Posted by Sally McCormack on Apr 25, 2016 1:52:05 PM

PowerCenter Tips

PowerCenter by informaticaAs an Informatica PowerCenter administrator, you may often have the need to obtain a list of users and associated groups, workflows that have last run, mappings in a folder, default values within a mapping, etc. This information can be queried in the PowerCenter tools, however, a more efficient way of collecting this data is to query the repository metadata tables directly in the database. This method proves to be very helpful when performing a large repository upgrade or decommissioning an environment.

 For example, you may need a list of workflows that last ran in the past month to determine if these workflows should be upgraded. You can see what workflows are scheduled within Workflow Monitor, but what about the ones that ran on demand? Sure, you can look in Repository Manager, but do you want to click through every single folder? Here are basic SQL queries that can be run directly in the Informatica repository metatdata database to obtain that information:

--Get list of all workflows that have run since 11/1/2013
select distinct REP_WORKFLOWS.SUBJECT_AREA, REP_WORKFLOWS.WORKFLOW_NAME
from dbo.REP_WORKFLOWS
join dbo.REP_WFLOW_RUN
on REP_WORKFLOWS.WORKFLOW_ID = REP_WFLOW_RUN.WORKFLOW_ID
where REP_WFLOW_RUN.END_TIME > '2013-11-01 00:00:00'
order by REP_WORKFLOWS.SUBJECT_AREA asc, REP_WORKFLOWS.WORKFLOW_NAME asc;
 
--Get list of on demand workflows that have run since 11/1/2013
select distinct REP_WORKFLOWS.SUBJECT_AREA, REP_WORKFLOWS.WORKFLOW_NAME, REP_WFLOW_RUN.USER_NAME
from dbo.REP_WORKFLOWS
join dbo.REP_WFLOW_RUN
on REP_WORKFLOWS.WORKFLOW_ID = REP_WFLOW_RUN.WORKFLOW_ID
where REP_WFLOW_RUN.END_TIME > '2013-11-01 00:00:00' and REP_WFLOW_RUN.RUN_TYPE = 2
order by REP_WORKFLOWS.SUBJECT_AREA asc, REP_WORKFLOWS.WORKFLOW_NAME asc;

 

NOTE: The PowerCenter repository comes with a set of views, which can be queried directly within the database. It is recommended to use a read-only database account to query these views.

So, what else can you query? How do you know what tables to include your queries? There is a wealth of queries online from various sources including the Informatica Communities: https://community.informatica.com/index.jspa. You can easily search for SQL queries on this forum or even just perform a web search in your favorite search engine. There are a number of examples online, so use and tweak them to fit your needs.

Topics: Blog, Informatica PowerCenter

Written by Sally McCormack