Wednesday, September 2, 2009

Informatica PowerCenter Repository tables

I am sure every PowerCenter developer either has an intention or necessity to know about the Informatica metadata tables and where information is stored etc. For the starters, all the objects that we create in Informatica PowerCenter - let them be sources, targets, mappings, workflows, sessions, expressions, be it anything related to PowerCenter, will get stored in a set of database tables (call them as metadata tables or OPB tables or repository tables).

* I want to know all the sessions in my folder that are calling some shell script/command in the Post-Session command task.
* I want to know how many mappings have transformations that contain "STOCK_CODE" defined as a port.
* I want to know all unused ports in my repository of 100 folders.

In repositories where you have many number of sessions or workflows or mappings, it gets difficult to achieve this with the help of Informatica PowerCenter client tools. After all, whole of this data is stored in some form in the metadata tables. So if you know the data model of these repository tables, you will be in a better position to answer these questions.

Before we proceed further, let me clearly urge for something very important. Data in the repository/metadata/OPB tables is very sensitive and that the modifications like insert or updates are to be made using the PowerCenter tools ONLY. DO NOT DIRECTLY USE UPDATE OR INSERT COMMANDS AGAINST THESE TABLES.

Please also note that there is no official documentation from Informatica Corporation on how these tables act. It is purely based on my assumption, research and experience that I am providing these details. I will not be responsible to any of the damages caused if you use any statement other than the SELECT, knowing the details from this blog article. This is my disclaimer. Let us move on to the contents now.

There around a couple of hundred OPB tables in 7.x version of PowerCenter, but in 8.x, this number crosses 400. In this regard, I am going to talk about few important tables in this articles. As such, this is not a small topic to cover in one article. I shall write few more to cover other important tables like OPB_TDS, OPB_SESSLOG etc.

We shall start with OPB_SUBJECT now.

OPB_SUBJECT - PowerCenter folders table

This table stores the name of each PowerCenter repository folder.

Usage: Join any of the repository tables that have SUBJECT_ID as column with that of SUBJ_ID in this table to know the folder name.

OPB_MAPPING - Mappings table

This table stores the name and ID of each mapping and its corresponding folder.

Usage: Join any of the repository tables that have MAPPING_ID as column with that of MAPPING_ID in this table to know the mapping name.

OPB_TASK - Tasks table like sessions, workflow etc

This table stores the name and ID of each task like session, workflow and its corresponding folder.

Usage: Join any of the repository tables that have TASK_ID as column with that of TASK_ID/SESSION_ID in this table to know the task name. Observe that the session and also workflow are stored as tasks in the repository. TASK_TYPE for session is 68 and that of the workflow is 71.

OPB_SESSION - Session & Mapping linkage table

This table stores the linkage between the session and the corresponding mapping. As informed in the earlier paragraph, you can use the SESSION_ID in this table to join with TASK_ID of OPB_TASK table.

OPB_TASK_ATTR - Task attributes tables

This is the table that stores the attribute values (like Session log name etc) for tasks.

Usage: Use the ATTR_ID of this table to that of the ATTR_ID of OPB_ATTR table to find what each attribute in this table means. You can know more about OPB_ATTR table in the next paragraphs.

OPB_WIDGET - Transformations table

This table stores the names and IDs of all the transformations with their folder details.

Usage: Use WIDGET_ID from this table to that of the WIDGET_ID of any of the tables to know the transformation name and the folder details. Use this table in conjunction with OPB_WIDGET_ATTR or OPB_WIDGET_EXPR to know more about each transformation etc.

OPB_WIDGET_FIELD - Transformation ports table

This table stores the names and IDs of all the transformation fields for each of the transformations.

Usage: Take the FIELD_ID from this table and match it against the FIELD_ID of any of the tables like OPB_WIDGET_DEP and you can get the corresponding information.

OPB_WIDGET_ATTR - Transformation properties table

This table stores all the properties details about each of the transformations.

Usage: Use the ATTR_ID of this table to that of the ATTR_ID of OPB_ATTR table to find what each attribute in this transformation means.

OPB_EXPRESSION - Expressions table

This table stores the details of the expressions used anywhere in PowerCenter.

Usage: Use this table in conjunction with OPB_WIDGET/OPB_WIDGET_INST and OPB_WIDGET_EXPR to get the expressions in the Expression transformation for a particular, mapping or a set.

OPB_ATTR - Attributes

This table has a list of attributes and their default values if any. You can get the ATTR_ID from this table and look it up against any of the tables where you can get the attribute value. You should also make a note of the ATTR_TYPE, OBJECT_TYPE_ID before you pick up the ATTR_ID. You can find the same ATTR_ID in the table, but with different ATTR_TYPE or OBJECT_TYPE_ID.

OPB_COMPONENT - Session Component

This table stores the component details like Post-Session-Success-Email, commands in Post-Session/pre-Session etc.

Usage: Match the TASK_ID with that of the SESSION_ID in OPB_SESSION table to get the SESSION_NAME and to get the shell command or batch command that is there for the session, join this table with OPB_TASK_VAL_LIST table on TASK_ID.

OPB_CFG_ATTR - Session Configuration Attributes

This table stores the attribute values for Session Object configuration like "Save Session log by", Session log path etc.

184 comments:

Unknown said...

Nice work Radhakrishna ..!!!

Unknown said...

hi radhakrishna, its really cool,
can u pleaselet me know how cani pull out the details of workflow x.

i want to pull information of workflow x( its sources, its target,source read rows target read rows)

Radhakrishna Sarma said...

Hi Shoaib,
You will get all that information from OPB_SESS_LOG table.

Cheers
Sarma.

Ravz said...

Hi Radhakrishna,
I read your blogs on informatica blog too, and found them very useful. Thanks for your sharing knowledge.
I have one query, hope you may help me out.
what will happen if the order of columns in Fixed width file or delimited file is changed?
I mean e.g. we are using a flat file in informatica with columns as A,B,C,D. What if the incoming file while running session has columns as B,A,C,D. You may assume that there will be header info in file itself?

Radhakrishna Sarma said...

Hi Ravz,

Thank you for visiting my blog, but what do you mean by what will happen? Do you mean to the source definition in PowerCenter or to the mapping etc? Or if you just change in the actual physical file and the expected behaviour of Informatica mapping built on this? Can you explain clearly what you meant?

Cheers
Sarma.

Ravz said...

Hey thanks for quick revert.. I meant that can informatica still read the correct values from flat file. Even if order of column changes. This question was asked in one interview.

Radhakrishna Sarma said...

Hi Ravz,

Probably the order should reflect in the mapping as well? I am not sure. These are the types of questions that you will have to try and work. According to me this is not a question to be asked. This question in no way tests a person's PowerCenter capabilities.

Cheers
Sarma.

Ravz said...
This comment has been removed by the author.
Ravz said...

Yeah I agree with you... This question will only check whether I faced such scenario or not.. anyway thanks for quick revert..

Genie said...

Nice Work !

I have a question ....I want to pull source tables and the associated ports along with data type,precision and scale information......which tables can i leverage ?

Radhakrishna Sarma said...

Hi genie,

There is already, a pre-defined view that gives you all the required details.

select * from rep_all_source_flds

Cheers
Sarma.

Genie said...

yeah there is.....but if i create a new folder in informatica with new mapping....it's information is not added to this view....

also....the opb_targ_fld tables has that information for targets but opb_src_fld does not.....why is it so??

Radhakrishna Sarma said...

What do you mean by information not added to the view? A view is a just a window to the underlying tables' data. So if the data is added to the tables, then it will be seen in the view. I think you need to know the distinction between a table and a view.

Anyway, for your other question, I am just assuming that Informatica treats a source as a widget and you should find all the source fields from the table OPB_WIDG_FIELD_ATTR or some table similar.

Cheers
Sarma.

Genie said...

Thanks for your quick and useful responses.... I have another one...

How can we get the field dependencies of the target ports in a mapping ??????

Radhakrishna Sarma said...

Can you elaborate on what you mean by field dependency?

Cheers
Sarma.

Genie said...

I meant that if if want to know that from where ever target port originated.....like in informatica when we right click on the target port there is an option call show field dependencies !

Radhakrishna Sarma said...

Hi genie,

I don't remember exactly, but there is a table by name OPB_WIDG_FLD_ATTR or something similar whoch has each transformation and its TO_FIELD and FROM_FIELD. I think that helps to get to the bottom of that.

Having said that, I have just identified certain tables to be useful for our requirements. Its not that we can replicate each functionality the client tool provides, using our SQL join queries on the OPB tables. When we do that it is called "reinventing the wheel". I hope you understand that.

Cheers
Sarma.

Tienda de informatica said...

great post I learn a lot thank's

Sara

Tienda informatica - placas base

Anonymous said...

Genial dispatch and this fill someone in on helped me alot in my college assignement. Gratefulness you for your information.

Anonymous said...

Amiable brief and this fill someone in on helped me alot in my college assignement. Thanks you for your information.

Unknown said...

Hi Sarma,

Thanks for the information. Any idea of what is the full name of OPB?

Unknown said...

Hi Radha Krishna,

In one interview ..they asked me..the metadata will be stored in which format???can i know thw answer please??

Radhakrishna Sarma said...

Hi Prasuna,

As my article above says, it is stored in database tables.

Cheers
Radha

Anonymous said...

very informative.. thanks for sharing your knowledge.. I have one question in my repostitory DB I have a lot of duplicate tables like for mapping I have "OPB_MAPPING" and "OPB_MAPPING_" is it something generic or may be it is created by someone from our DBA team.

Radhakrishna Sarma said...

It's just your instance that has the duplicate tables.

Anonymous said...

Thanks for quick reply on duplicate tables..

Anonymous said...

Thanks for you quick reply on duplicate tables

Seethalakshmi Sivasubramanian said...

Hi Radha,

All your posts are very informative..thanks for sharing your learnings..
I have a doubt. I need to pull only the source columns which are connected to a Port. Can I make use of REP_ALL_SOURCE_FLDS view? I couldnt find any column storing the port info in this..

Regards,
Seethalakshmi.S

Radhakrishna Sarma said...

Hi Seetha Laksmi,

There are 3-4 tables to be jopined before you can get the information relating to your query.

opb_widget_field - Field name, Field ID (port), Widget ID etc.

OPB_WIDGET_INST - Widget ID, Instance ID, mapping id etc.

OPB_WIDGET_DEP - Mapping ID, From Instance ID, To Instance ID, From Field ID, To Field ID.

But this is a source definition and you wouldn't find the WIDGET_ID in OPB_WIDGET and so you should check it against the SRC_ID in OPB_SRC table.

I hope this helps.

Cheers
Sarma

vijay said...

Hi, How to copy one repository folder objects to another repository folder at time?

Sudipa said...

Hi Radha,

Could you please provide information regarding the DataModel which shows the
relation between the Informatica Repository Tables and Views?

We have an urgent requirement on finding check in /check out information of any object through a mapping...

Thanks.
Sudipa

Unknown said...

Hi Radha,

I need to find the sessions which is having override tracing=verbose and need to update the repo tables to set none/normal.
OPB_ATTR is having some info but it not enough for me.

Please let me know if you have any idea.

Thanks

Sam

Anonymous said...

Hi Radhakrishna,
Can you please let me know how to capture the sequence of workflow tasks that are executed using these repository tables?
The Informatica Workflows in the current system are several level deep (involving several layers of Worklets). We are looking for a way to get a list of the operations performed in each of the workflow in the sequential order. This will enable us to design the new orchestration process.

Unknown said...

Hi Radhakrishna
Can you please let me know from which tables I can query to find the command used in the command task in an Informatica Workflow?
Can u pls help, this is URGENT.

Thanks
Partha.

Unknown said...

Hi Radha,
First time i wanted to know about Repository tables and the link provided me with bundle of information.Thanks for provinding the info. If i wnat to know more about the rep tables and the link to map source to targets to cilumn level granularity how do i get that?

Unknown said...

Hi Radhakrishna,

Thanks for providing the details of Informatica Powercenter repository

Metadata details. Will it be possible to get the dates for each action

taken place while chaning the mapping,session,etc., logics/conditions

for audit trail.

Thanks
Kavitha

abdulmalik said...

Thanks a lot RadhaKrishna for sharing this knowledge.

I've looked into many table and it's attributes, however unable to find the bulk load option column, it's value and table in which it could be present. Could you please help us in this.

Thanks & Regards.
Malik Ahmer.

Radhakrishna Sarma said...

Hi Malik Ahmer,

The Bulk/Normal option is an external attribute. So you will find it in opb_extn_attr table where attr_id = 3 for a given sess_widg_inst_id, which can be derived from

select sess_widg_inst_id from opb_swidget_inst
where widget_id =
and instance_name =
and session_id = ;

Take this and get the attr_value. If the attr_value = 1, then it is Bulk and if it is 0, it is Normal.

select attr_value
from opb_extn_attr
where attr_id = 3
and session_id =
and sess_widg_inst_id = ;

Cheers

Abeesh said...

Hi Radhakrishna,
I need to get the throughput(bytes/sec) of session. Can u tell me which OPB tables stores this info. I can get the throughput(rows/sec) from rep_sess_tbl_log but bytes/sec is not available. Please help

Riyas said...

Hi Radha,
How to find out all the connection names,connection server,folder name, worklow name, mapping name,target and source details for a particular connection server ?
Can you please provide the query for the same

Pradeep Kothakota said...

Hi Radhakrishna,
Nice Work.
I have set of workflows.I want to check whether "Fail Parent If this task fails" is checked in all sessions or not. How can i check this using Repository tables?

Pradeep Kothakota said...

Hi Radha,

This is Pradeep working in HP,Bangalore.

I have a doubt. I need to know workflow names, session names , source names and target names in a folder . How do I get it by querying on repository tables.

This is My query to get wf name, session names in a folder:

select f.SUBJ_NAME AS FOLDER_NAME,wf.TASK_NAME as WORKFLOW_NAME, sess.INSTANCE_NAME AS SESSION_NAME from opb_subject f,opb_task wf,opb_task_inst sess,opb_task_inst wf_inst
where wf.subject_id = f.subj_id
AND wf_inst.task_id = sess.task_id AND wf_inst.workflow_id = wf.task_id AND f.subj_name = 'usr_mohantde' AND
wf.task_type = 71 AND sess.task_type = 68

Radhakrishna Sarma said...

Hi Pradeep,

I can't give you a complete query now, but it is worth checking OPB_TDS, OPB_SRC and then match the widget_id against the opb_widget_dep or something like that.

Pradeep Kothakota said...

Thanks Radha

Pradeep Kothakota said...

Hi Radha,

Following query is giving me WF, session, mapping, src instance and target instance names. I want to get the original source and target names. How do I get it..? Which table gives me the original table names..?

select
F.SUBJ_NAME AS FOLDER_NAME,
WF.TASK_NAME AS WORKFLOW_NAME,
SE.INSTANCE_NAME AS SESSION_NAME,
M.MAPPING_NAME,
SRC.INSTANCE_NAME AS SOURCE_NAME,
TGT.INSTANCE_NAME AS TARGET_NAME
from
OPB_SUBJECT F,
OPB_TASK WF,
(SELECT
WORKFLOW_ID,
INSTANCE_ID,
TASK_ID,
TASK_TYPE,
INSTANCE_NAME,
MAX(VERSION_NUMBER)
FROM OPB_TASK_INST SESS
WHERE
SESS.TASK_TYPE=68
GROUP BY
WORKFLOW_ID,INSTANCE_ID,TASK_ID,TASK_TYPE,INSTANCE_NAME) SE,
(SELECT SESSION_ID,MAPPING_ID,MAX(VERSION_NUMBER) FROM OPB_SESSION GROUP BY SESSION_ID,MAPPING_ID) S,
opb_mapping m,
(SELECT MAPPING_ID,INSTANCE_NAME ,WIDGET_TYPE,MAX(VERSION_NUMBER) from opb_widget_inst GROUP BY MAPPING_ID,INSTANCE_NAME,WIDGET_TYPE) SRC,
(SELECT MAPPING_ID,INSTANCE_NAME ,WIDGET_TYPE,MAX(VERSION_NUMBER) from opb_widget_inst GROUP BY MAPPING_ID,INSTANCE_NAME,WIDGET_TYPE) TGT
where WF.IS_VISIBLE = 1
AND WF.SUBJECT_ID = F.SUBJ_ID
AND SE.WORKFLOW_ID = WF.TASK_ID
AND WF.TASK_TYPE = 71
AND se.task_id = s.session_id
AND s.mapping_id = m.mapping_id
AND M.IS_VISIBLE = 1
AND SRC.MAPPING_ID=m.mapping_id
AND SRC.WIDGET_TYPE=1
AND TGT.MAPPING_ID=m.mapping_id
AND TGT.WIDGET_TYPE=2
AND F.SUBJ_NAME = 'usr_kothakop'

Bijjala said...

Hi Radhakrishna
can you please let me know in which OPB or REP table we will find "suspend on error" details, as we need to know which all workflow dont have this enabled. thanks in advance

Radhakrishna Sarma said...

Kumar - There is a table called opb_swidginst_log which has THRUPUT for through put. You just have to match the task id for session, its target & sources with the swidget_inst_id & you can map the target id or source id with the widget_id in opb_swidget_inst. I hope you can write the query with this information.

Cheers
Radha

manu said...

HI Radha, i am new to informatica and b4 going throuh ur blog my knowledge was limited to mapppin,session and workfllow...
thanks for all learning and sharing..

nikila said...

Hi Radhika,
1 question, Can you suggest me the query where i can get all the folder names and group or individual user whoz having access to that folder?

FRank said...

Hi Radha Krishna,

Can you please let me know which opb or rep table have the source qualifier sql query information.

Thanks,
Frank

Radhakrishna Sarma said...

Frank - The table is OPB_WIDGET_ATTR and the query is going to be in attr_value column where attr_id = 1. Widget_type you can check against the OPB_OBJECT and take the object_type_id from there.

Cheers
Radha

Anonymous said...

Hi Radha,

I hope you can advise me...

I am trying to get a list of all the mappings that use a particular Target table. I have written the following query:

SELECT DISTINCT m.MAPPING_NAME, t.TARGET_NAME
FROM OPB_MAP_TARG_INFO mt,
OPB_MAPPING m,
OPB_TARG t
WHERE mt.TARGET_INSTANCE_ID=t.SUBJ_ID
AND m.MAPPING_ID=mt.MAPPING_ID
AND t.TARGET_NAME = 'EIM_FN_ASSET'
ORDER BY m.MAPPING_NAME, t.TARGET_NAME

This seems to work well, except there is a mapping missing from the listing which I know for sure has this target table.

Have I joined the right tables/columns? Am I missing something out? Could it be a problem because the Target table is a global object in another folder and is used as a shortcut in the mapping in question?

Thanks in advance for your help
(we are using Informatica 8.5.1)

Regards,
Shazia

Radhakrishna Sarma said...

Shazia,

select distinct m.mapping_name
from opb_widget_inst w, opb_mapping m, opb_targ t
where m.mapping_id = w.mapping_id
and t.target_id = w.widget_id
and t.target_name = 'EIM_FN_ASSET'

Cheers
Radha

Anonymous said...

That's brilliant! Thank you.

Is there any place which has a definitive data model of the OPB tables and how they link up? Clearly it's not intuitive and very confusing for those of us who don't know much about them.

Thanks again
Shazia

Anonymous said...

PS - I think I need to also use widget_type=2 to get only tables which are type of "target" otherwise I get an odd result

Anonymous said...

hi my ? is in if task got failed and it has been re-queued and it get succeeded but how to find the long run time for that particular session for current and long days back also....

vikas said...

Hi Radha,

I had an repository which stored all workflow, sessions, source tabels, target tables. The repository got corrupt & deleted. Recreated the repository with the same name. Is there any way to get all my work previously created in repository i.e mappings, source definition, target definition, workflow & session details.

Your help would be highly appreciated. I am new to informatica. my mail id vikasshah1979@gmail.com

Thanks & eagerly waiting for your reply.

Anonymous said...

Hi Radha,

I have a question, I want to know if a certain db connection is being used by one or more sessions, which table should I look into?

Thanks and Regards,
Baldi

Chaitanya said...

Hi Radha, Very much appreciable..
It was very helpful .

I want to know if the current running session details are stored in any of the tables?

Best Regards,
Chaitanya

Raj said...

Hi Radha,

Can I know if there is any flag which indicates if the target is deleted from repository on one of these tables?

Anonymous said...

Hi RadhaKrishna,

Thanks for your valuable information on Repository tables.

I need a query to fetch users list having access/permissions to folder.can you please suggest it as soon as possible.

Thanks
Krishna

Electra said...

Hi Radha,
Your blog is quite informative. I've a question on the Repo tables pertaining to the User roles.
Can you please advise which metadata tables/views should be queried to get the Roles of infa users along with the user names? Thanks in advance.

-S

bnr said...

Hi Radha,
Can you give me sql to find the sources used in given session name.

I wanted to find out all the sources (source tables) with in my session.

Created nice resource.

hari said...

Hi Radha,

Thanks for sharing your knowledge.
I have a one question.
In My project folder there are arnd 60 reusable sessions and my requirement is need to change the path of all Sources(My source is file) can this happen thru a single shell script??
In which table can i see present path

Anonymous said...

Hi Radha,

I think you are the guy to help with what I am looking for. I am looking for the pre/post sql data override on the sessions. I checked your query which gets the pre/post sql from transformations in the mapping, but in this case I am looking for the overrides at session level, is there a table I could look into?

Thanks,
Juan

Anonymous said...

I just found the table: OPB_SWIDGET_ATTR, I was able to find pre/post-sql overrides at session level in that table..

Regards,
Juan

Gamer said...

Good article. Keep it up
For informatica scenarios: informatica scenarios

Anonymous said...

HI,i want sql query to know the cureent running tasks sessiond id and instance id details which are running in informatica for particular execution plan.Thanks

Abhishek Kotah Jaiswal said...

Could you please let me know how do i collect data such as the wf name, wklt name, session names and the start time and end time for each of them... particularly at the session level. is there a query for the OPB tables that will pull all this data through join them. please let me know. Thanks in advance.

Mahender said...
This comment has been removed by the author.
Mahender said...

Hi Abhi,

You can pull this information by querying Informatica metadata tables REP_TASK_INST_RUN and OPB_SESS_TASK_LOG.
http://etllabs.com/informatica/informatica-metadata-tables/1053/
I believe by adding some attributes in given SQL will be able to get u desired results.

bfr closing wanted to check, when r you throwing a new post Radha? we haven't heard you from quite a few time ?

Mahender

harikrishna said...

Hi,

I need query like Wofkflow_name|Sessions(no.of sessions under each workflow)|Source_connection|Target_connection
using meta data
from which tables we can use and fetch those details

Munna said...

Can you please any one let me know that how to get the connetion names for sources and targets in each session across all the folders. The output results should be like below

Folder_name|Workflow_name|session_names|source_name|source_connection_name|target_name|target_connection_name

Ritesh said...

Hi,
Can you help me in getting list of workflows to which Integartion service is not assigned?

Rupert Holden said...

Hi there - just found your blog and really impressed.
I have a query that I am trying to work out and do not think you have already covered elsewhere....
I am trying to write some sql to emulate some of the information shown in the spreadsheet when you select "Export Metadata" for a mapping from within the Repository manager.
This "Informatica" spreadsheet includes a tab called "Target" and I am trying to write the code to get the physical names of the table and field of the targets along with the formula/expression and type shown in the "Transformations" section of this tab.
I am thinking that this uses the "OPB_WIDEGET_xxx" tables and "OPB_MAPPING" tables but am struggling to reproduce the information as shown in the spreadsheet.
Any guidance greatly appreciated, many thanks, Rupert

Shiva said...

Hi Radhakrishna, i found your blog very useful. I need some help on finding a sql query to find which integration service is most loaded.Could you please help me in this regard?

we have 4 integration services and want to create a script to automate the process of finding which one is most loaded?

Thanks,

Shiva.

Jeevan said...

Hi Radhakrishna, seems like your blog has become a stop for all repository db queries! very helpful information.

I have hundreds of mappings with mapping level source qualifier override in almost each and every mapping. I want to list the origination point of all target table fields. I am able to get the sql override, source and target columns. But, is there a way to link the target field to specific field mentioned in the sql override?

Example - [Target] FieldX might appear to have been connected to [Source] Field1 but, the sql qry overrides this to have been sourced from FieldP. In the list I am trying to create, I want to have FieldP (not Field1) opposite to FieldX

Looking for your response.

abdulmalik said...

Hi Radha,

Thanks for sharing your knowledge and helping many guys like me!
Here are my ?
1) How could we get the Target/Source connection details from repository table related to particular sessions? I could get all the session details from the below query:
SELECT * FROM OPB_TASK WHERE SUBJECT_ID = 53 AND TASK_TYPE = 68 AND (VERSION_NUMBER, TASK_NAME) IN
(SELECT MAX(VERSION_NUMBER), TASK_NAME FROM OPB_TASK WHERE SUBJECT_ID = 53 AND TASK_TYPE = 68
GROUP BY TASK_NAME)
2)Some of the source qualifier queries has been referenced to db schema in itself, like select ....schema.tablename, I've all the mapping, session, folder information, how could we get the source qualifier which includes above scenerio (i.e. schema.tablename)?

Thanks for helping me in previous posts.

Manish Kumar Poddar said...

Hi Radhakrishna,
I have a requirement of fetching mapping list and its src and target tables and also find out which mappings does not have workflow associated with it. I am new to Informatica. which tables can help me.

Your help is appreciated.....
Manish.

Anonymous said...

Hello Radhakrishna, I need to create a control table to see if workflow has been processed or failed, if rows have been transfered to target or not, how can I handle it ?

Serge

DP Acharya said...

Hi Radha, I want to get the details of all the tracing level overrides made to transformations at session level ( I got information for the same at mapping level) . How can We get that ?

Thanks,
Prasanna

Anonymous said...

Hi All,
I wanted to get the File watch name in the event wait. I tried digging OPB_ATTR and OPB_WIDGET_ATTR which has only session related information.Please help me which table i refer to.

Anonymous said...

Hi All,

I want to find out which user has last saved / checkin the mappings and workflows. Is there a way out ?

Thanks

Anitha said...

Hi,
I am new to Informatica,
I am learning a lot from your blog.
Plz let me know wht are the different tables/views type are there and give me a breief explanation
such as OPB?? REP?? PL?? is ther other tabesl start with some other??

Ibrahim said...

Hi Radhakrishnan,

I would like to know the tables which has the informatica sequence generator details such as start , next value and increment value. And also please tell me what tables are get affected when i edit a reusable sequence generator and change its start and next value in a versioned informatica environment. I kindly appreciate your help.


Thanks,
Ibrahim

Anonymous said...

Hi Radhakrishnan,
how do i find a list of all sessinos which have a pre-session command or a post sessnion success command in them ?
THanks,
NIkhil.

Anonymous said...

I have one question ..please answer it ..as this is related to my project development.

How can we fetch mapping names and correspoding names in flat file of a folder at mapping level

Anonymous said...

Hi All,

I need to retrieve session Log file Name for a particular session.

Can sum1 help me with the query please. How to identify exact Attrib_ID for Log file name.
Thanks in advance.

Anonymous said...

Hi ,

I have one question

If one of my mapping is used by mutliple workflows.How can I figure out that how many such workflows are presently running.

Anonymous said...

Is there a way to get a list of task name with its connection information from an Informatica repository query? What I am looking for is the remote directory and remote filename when the connection type = ftp. When I am in the workflow manager, I find this info under FTP connection editor.

Unknown said...

I am William..I just browsing through some blogs and came across yours! Excellent blog, good to see someone actually uses for quality posts. Your site kept me on for a few minutes unlike the rest :) Keep up the good work!Thanks for sharing a important information on informatica

Unknown said...
This comment has been removed by the author.
baji said...

Hi All,

I need a query which gives all source tables and target tables that are not used in any mappings.
Please reply.

Regards,
Venkat.

Unknown said...

Hi Radhakrishna,
I read your blogs on informatica and find them very interesting. Thanks for your sharing knowledge.
Need to help me out regarding a query.I would like to have list of codes(mappin) those were modified in a particular date.ie say list of mapping those were Checked-Out,underwent change and then Checked-In today.
If possible wud also like to add in the checked-out comments.
Plz help me with this..also do mail me reg solution if any at xizu77@gmail.com

Unknown said...

Hey Radha ,
Can i use any of these repository tables as source/target in my mappings ?

Tracy said...

Thank you so much for this info

Rash said...

Highly informative post.
I needed help with one of the pmprep commands.
I came across the command pmrep truncatelog command that deletes folder /workflow level logs from repository. I wanted to know the exact tables that are cleaned when the command runs successfully.

Srikrishnan Chandrasekaran said...

Very Informative
Thanks a lot for sharng ur wisdom with us

akhil singh said...

Hi Everyone,
1: I want to know session name of a next session in previous session. How can i get the same. And the more important thing is that i want generic solution, No hardcoding required.

2: Can i get session name in pre sql of session?

Waiting for quick responses from you guys!!!

attain said...

This is nice blog and thanks share to us.we providing Informatica online training

sudheer said...

The information which you have provided is very good. It is very useful and easily understood. who is looking for Informatica Online Training.

sudheer said...

The information which you have provided is very good and easily understood.
It is very useful who is looking for Informatica Online Training.

D said...

Hi Radha,

I am using PowerCenter 9.1.0.
This is related to calculating memory configuration.

Is there any table or query that would help me to calculate the memory configuration, whether is it set correct or not?

I saw somewhere an online doc, where it says, we can calculate, if we get the below details for a particular session :
1. lookup - no of unique rows
2. aggregator - no of groups
3. sorter - no of rows to be sorted
4. rank - no of ranks specified inside mapping
5. maximum memory allocated for auto memory attributes
6. maximum percentage of total memory allowed for auto memory attributes

Let me know your comments/suggestions on this.

Thanks in Advance.
Regards,
sunil.dexter@gmail.com

Tutu said...

What is an intermediate table in a mapping is the same as a temporary table?

Unknown said...

Hi Radhakrishna,

how the table OPB_OBJECT_LOCKS populates?

When I queried on this table, it is returning 0 records

Thanks,
Vamsi

kumar said...




Thanks for sharing for this valuble information and it is very useful for informatica learners .123trainings also provide the bestinformatica Online Training you can see free demo Informatica Online Training Demo in Hyderabad India

kumar said...




Thanks for sharing for this valuble information and it is very useful for informatica learners .123trainings also provide the bestinformatica Online Training you can see free demo Informatica Online Training Demo in Hyderabad India

sudheer said...

Thanks for sharing this valuble information and itis useful for us .123trainings also provides the best online Informatica classes.to see free demo classonline Informatica training class in uk,canada

Naveen Kumar said...

HI Radhakrishna,

I want to find which table stores badfile names ?
file.bad is stored in which table?

Please let me know

Naveen Kumar said...

HI Radha,

Please help me to find which repo table stores badfile name?

ex file.bad (which is defined in session properties )is stored in which repo table?

Unknown said...

Hi Radha

I am new to informatica and I am not sure where would I query the tables that you have listed.

Can you please let me know how can i query these metadata tables ?

-Siva

Unknown said...

Hi Radha

I am new to informatica and I am not sure where would I query the tables that you have listed.

Can you please let me know how can i query these metadata tables ?

-Siva

Unknown said...

Hi Radha,

Is it possible to find out the list of mappings where a particular column say 'XYZ' is being populated in the target ? I have the query which will list the mappings loading particular target. But can we drill it down at the column level ? pl help

Unknown said...


Thank you provide valuable informations and iam seacrching same informations,and saved my time SAS Online Training

Anonymous said...

Hi Radha,

I have a question can you please tell me where to find all this table. Where all this tables are stored.

shijo said...

Hi Radha,

How can I get list of source tables,target tables,lookups tables and its connections from a workfow using repository query?

Rama Krishna said...

How can I check each mapping level run times? I am able to get the workflow run times from OPB_WFLOW_RUN, and joining onto OPN_SESS_TASK_LOG table I could get mapping level SOURCE_SUCCESSROWS and TARGET_SUCCESSROWS. But I am not getting the each mapping level run time. Please help.

peterjohn said...

I appreciate you sharing this article. Really thank you! Much obliged.
This is one awesome blog article. Much thanks again.

sap online training
sap sd online training
hadoop online training
sap-crm-online-training

Anonymous said...

HI

This was really helpful.

I have question,
How can i fetch source tables used in a mapping?
I couldnt find any common field to join tables OPB_mapping and OPB_SRC.
Can you please suggest on this

Regards
Meb

Unknown said...

The best kits online trainings,thanks for sharing

SAP HR Abap Corporate Training

SAP MM Corporate Training

SAP SD Corporate Training

SAP Success Factors Corporate Training

SAP WebDynpro Corporate Training

SCCM 2012 Corporate Training

SATHISH said...

Hi Radha,

In which table we get the mapping for session attribute "Fail parent if this task fails" and "Disable the task".



Regards,
Sathish

Balaji Gadi said...

hI radha,
I am using the below query

SELECT * FROM OPB_TARG_FLD F1 WHERE EXISTS(SELECT * FROM OPB_TARG_FLD F2 WHERE F1.FLDID=F2.FLDID AND F1.TARGET_ID<>F2.TARGET_ID)
AND VERSION_NUMBER= (SELECT VERSION_NUMBER FROM OPB_TARG T WHERE T.TARGET_ID=F1.TARGET_ID AND IS_VISIBLE=1) ORDER BY 1
But wiith this, I am getting fld ids. I want to know the table names as well.
Because with this query, I am not getting table names.
How to see the table names for these fld ids..?

Anonymous said...

Hi Radha,

Does any metadata tables get updated when a object say workflow in copied from one repo to another.

We need to check logs when a object is copied, Any idea how this can be accomplished without Deployment group.

Thanks

Unknown said...

I have workflow which is running in 10 instance. Each instance will run for 20 mins and thus it is running 24*7.

This workflow has 3 session. I want to know the whether 2nd session is running or not if not than need to check the status of 1st session if it is running than will check source rows and if source rows are 0 then will abort the workflow.

Please suggest unix script for the same.

Thanks in Advance.

OnlineITGuru said...

http://onlineitguru.com/android-online-training-placement.html
http://onlineitguru.com/core-java-online-training-placement.html
http://onlineitguru.com/ios-online-training-placement.html
http://onlineitguru.com/pega-online-training-placement.html
http://www.hadoop-big-data-online-training.com/2015/12/hadoop-big-data-developer-online-training.html

Unknown said...

Hi Radha,
Could you please help me? I want to get the list of lookups used in a mapplet. Though i am getting the list ,it's not corresponding to the latest version of the mapplet. For example, if a lookup is deleted later from the mapplet i am still getting that lookup name while listing.

Thanks in advance..

Anonymous said...

what is the query if we want to check if two filter are used one after another??

Unknown said...

very nice article.Thanks for sharing the post...!
Online Training

Abinitio Online Training

swaroop said...

Hi Radha,

Thanks for sharing information with us , Its very useful,
Currently i'm working on infa9.6.1, I would like to know information about the metadata of informatica developer client.
Can you please help me in it.

Myccnt said...

You post is very informative. In my company, we are trying to cleanup informatica. We are trying to do below

Lets say, I have a invalid mapping, I want to see the folder name, Mapping name, Session name & Workflow name

vice versa...If I have a invalid session, I would like to see above information.

Can you help?

Thanks

vignes said...

We share it amazing information.


Selenium Training in Chennai | Selenium Training Institutes in Chennai

Tek Classes said...

Awesome blog great information you given thanks for sharing.I would like to share few more information about Informatica Online Training.
https://goo.gl/mCK8s8

Unknown said...

Hi,
Could you please let me know where can i get list of all repository tables and its description and linkable columns between the tables

sathya said...

A nice article here with some useful tips for those who are not used-to comment that frequently. Thanks for this helpful information I agree with all points you have given to us. I will follow all of them.

Informatica Training in Chennai

Dataware Housing Training in Chennai

Unknown said...

It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me...
SEO Company in India

Unknown said...

Hi ,


How can search for all the workflows that has no condition over link ( among session tasks or cm tasks or any task) .

Thanks
gaurav

Ancy merina said...
This comment has been removed by the author.
Unknown said...


I really enjoy it, to reading this


Hadoop Training In Chennai | Sap MM Training In Chennai | ETL Testing Training In Chennai

Tejuteju said...

awesome post presented by you..your writing style is fabulous and keep updated with your blogs
Informatica Online Course Hyderabad

Unknown said...

Very nice post

Informatica Training in Chennai | Informatica Training Institute in Chennai

Anonymous said...

Thanks a lot very much for the high quality and results-oriented help. I won’t think twice to endorse your blog post to anybody who wants and needs support about this area.
digital marketing training in tambaram

digital marketing training in annanagar

digital marketing training in marathahalli

digital marketing training in rajajinagar

Digital Marketing online training

full stack developer training in pune

MOUNIKA said...

This is really interesting, You are a very skilled blogger. I have joined your feed and look forward to seeking more of your wonderful post. Also, I have shared your site in my social networks!
ORACLE Apps Technical Certification Training

MOUNIKA said...

Thank you so much for this kind of post. I аАа’аАТ‚аЂТ˜m very thinking about what you have to say. I will probably be back to see what other stuff you post.
Sailpoint Training From India

Jamess said...

QuickBooks Enterprise and also gives you the unlimited technical assistance atQuickBooks Enterprise Support

steffan said...

No matter if you are getting performance errors or you are facing any kind of trouble to upgrade your software to its latest version, you can quickly get help with QuickBooks Customer Service Phone Number. Every time you dial QuickBooks 2018 technical support phone number, your queries get instantly solved. Moreover, you can get in touch with our professional technicians via our email and chat support options for prompt resolution of all related issues.

steffan said...

Quickbooks Enhanced Payroll Customer Support could be the toll-free quantity of where our skilled, experienced and responsible team are available 24*7 at your service. There are a selection of errors that pop up in QuickBooks Payroll which are taken care of by our highly knowledgeable and dedicated customer support executives. There are numerous regularly occurring Payroll errors of this software that could be of only a little help to you.

steffan said...

The accounting the main many companies varies based on this package. You will find so many fields it covers like creating invoices, managing taxes, managing payroll etc. However exceptions are typical over, sometimes it creates the negative aspects and user wants Intuit QuickBooks Support Number.

kevin32 said...

You should arrive at us in terms of a number of software issues. The satisfaction could be high class with QuickBooks Customer Support Number. You can easily e mail us in several ways.

QuickBooks Payroll Support said...

Any QuickBooks user faces any sort of identified errors in their daily accounting routine; these errors may differ from 1 another to a large degree, so our dedicated QuickBooks Customer Support Number Pro-Advisers are very well loaded with their tools and expertise to give most effective resolutions very quickly to the customers.

kevin32 said...

Because of this we at QuickBooks Enterprise Support Phone Number gives you the essential reliable solution of the every single QuickBooks Enterprise errors.

Jamess said...

QuickBooks Support Number banking transaction need our service. Some of you are employing excel sheets for some calculations.

Jack Dorsey said...

You might be always able to relate with us at our QuickBooks Support Phone Number to extract the very best support services from our highly dedicated and supportive QuickBooks Support executives at any point of time as all of us is oftentimes prepared to work with you. Most of us is responsible and makes sure to deliver hundred percent assistance by working 24*7 to suit your needs. Go ahead and mail us at our quickbooks support email id whenever you are in need. You could reach us via call at our toll-free number.

JimGray said...

Enterprise support number gives you proper assistance when you want it. You can avail Enterprise Support using E-mail yet QuickBooks Enterprise Help Phone Number USA serves to be the ideal type of assistance. Here our experts will answer your call and offer you perfect solutions on QuickBooks Enterprise resolving all the issues faced by you.

Jamess said...

The deep real cause is likely to be found out. All the clients are extremely satisfied with us. We've got many businessmen who burn off our QuickBooks Support Phone Number service.

Mathew said...

We now have experienced individuals to give you the figure. We are going to also give you the figure of your respective budget which you yourself can get in the long term from now. This will be only possible with QuickBooks Technical Support Phone Number.

rdsraftaar said...

QuickBooks is an accounting software which will help in solving your financial management problems. This software offers to its QuickBooks Payroll Customer Service Number, which lets you ease out of the payroll functions. There are over a million individuals who are using this software. This software provides users with a user-friendly interface, that makes it an easy task to learn and use advanced accounting methods. Intuit offers partial service payroll through the QuickBooks software which can be well suited for small enterprises that have the full time and tax knowledge to do the method themselves. This payroll software offers a system to process employee payroll and manage taxes on your own level.

QuickBooks Payroll Support said...

QuickBooks Customer Support Number, Many of us is skilled, talented, knowledgeable and spontaneous. Without taking most of your time, our team gets you rid of most unavoidable errors for this software.

steffan said...

Although Intuit has comes up many accounting software like Quicken , Intuit Tax Online Accountant, QuickBooks GoPayment, Mint,TaxCaster by TurboTax,MyTaxRefund by TurboTax,TurboTax SnapTax,Online Payroll, QuickBooks Online but the users of Quickbooks are more than other products . Even if you make a search on the Google QuickBooks Support Number you will probably confused when a so many number comes up in the search results ,because Intuit is dealing with so many products that why each product and each region they having the different Tech Support official .

Mathew said...

The support specialist will identify the difficulty. The deep real cause may very well be found out. Each of the clients are extremely pleased with us. We've got many businessmen who burn off our QuickBooks Support Phone Number service. It is simple to come and discover the perfect service for your requirements.

steffan said...

If the problem persists, contact Intuit Technical Support and offer all of them with the following error codes: (https://answers.supportforerror.com/question/quickbooks-error-6000-301/). Click on the Details button to learn more to supply Intuit Technical Support to simply help diagnose the error.

Mathew said...

QuickBooks Support professionals are terribly dedicated and might solve your entire issues without the fuss. In the event that you call, you will be greeted by our client service representative when taking all of your concern he/she will transfer your preference in to the involved department.

Mathew said...

Problems are inevitable plus they usually do not come with a bang. Our team at QuickBooks Pro Support contact number is ready beforehand to deliver you customer-friendly assistance in the event that you talk with a problem using QuickBooks Support Phone Number Pro.

kevin32 said...

QuickBooks Enterprise Support Cell Phone Number Is A Toll-Free Number, That Can Be Dialed, any time Of The Day In Order To Resolve The Matter. The Technician Who Answers Your Choice And Provides Solutions On QuickBooks Enterprise Support Number are Both Trained And Certified.

Blogsilly said...

QuickBooks Support Phone Number: Readily Available For every QuickBooks Version
Consist of a beautiful bunch of accounting versions, viz., QuickBooks Pro, QuickBooks Premier, QuickBooks Enterprise, QuickBooks POS, QuickBooks Mac, QuickBooks Windows, and QuickBooks Payroll, QuickBooks has grown to become a dependable accounting software that one may tailor depending on your industry prerequisite. As well as it, our quickbooks customer support phone number will bring in dedicated and diligent back-end helps for you for in case you find any inconveniences in operating any of these versions.

Unknown said...


Hello everybody,

I would like a query that extracts all the objects (Session,Emails,Command, Control....etc) of a workflow. Could you please give me a query that can do it?

Thanks in advance

accountingwizards said...

Any QuickBooks user faces any sort of identified errors in their daily accounting routine; these errors may differ from 1 another to a large degree, so our dedicated QuickBooks Support Phone Number are very well loaded with their tools and expertise to give most effective resolutions very quickly to the customers.

QuickBooks Support Phone Number said...

Our company is here to Make Sure Your Accounting Software Runs Optimally. Do not need to go anyplace else to obtain a viable QuickBooks Support Number USA.

QuickBooks Payroll Support said...

Comprises of an attractive lot of accounting versions, viz., QuickBooks Pro, QuickBooks Premier, QuickBooks Enterprise, QuickBooks Support Phone Number, QuickBooks Mac, QuickBooks Windows, and QuickBooks Payroll, QuickBooks is becoming a dependable accounting software that one may tailor depending on your industry prerequisite.

QuickBooks Support Phone Number said...

How come us different is quality of one's services within the given time interval. The locus of the services should be based on delivering services in shortest span of that time, without compromising along with the QuickBooks Tech Support Number quality of one's services.

rdsraftaar said...

Have you been scratching the head and stuck along with your QuickBooks related issues, you will be just one single click definately not our expert tech support team for your QuickBooks related issues. We site name, are leading tech support team provider for your entire QuickBooks related issues. Either it is day or night, we offer QuickBooks Customer Support Phone Number team for QuickBooks and its particular associated software in minimum possible time. Our dedicated technical team is available to be able to 24X7, 365 days a year to make sure comprehensive support and services at any hour. We assure you the quickest solution of most your QuickBooks software related issues.

jameswill11 said...

Our experts give you effective solutions for basic, enhanced and full-service payroll. Whether or perhaps not the matter relates to the tax table update, service server, payroll processing timing, Intuit server struggling to respond, or QuickBooks Payroll Support Phone Number update issues; we assure anyone to deliver precise technical assist with you on time.

kevin32 said...

No Tax penalty guaranteed: If the data you provide is perhaps all correct together with QuickBooks Payroll Support Number fund is sufficient in that case your whole taxes must be paid on time that may help save you from almost any penalty.

HP Printer Support Number said...

The outflow vents are gusting out hot air while the intake vents are permitting the atmosphere to get in. Thus, for HP laptop overheating fix the HP Printer Support Phone Number user needs to lighten the load to see the behavioral changes. They could make an effort to clean the fan (it could be completed with or without compressed air, vacuum and opening the vents).

Mathew said...
This comment has been removed by the author.
Mathew said...

Are you facing the problem with decision making? The amount of is QuickBooks Technical Support Number possible to earn in four weeks? You ought to predict this before. Lots of people are not familiar with this.

Mathew said...

problem in upgrading the software in the newer version so that you can avail the most up-to-date QuickBooks Support Phone Number features, trouble in generating advanced reports, difficulty with opening company file in multi-user mode and so on and so forth.

accountingwizards said...

Last but not least, don’t hesitate to call us on our QuickBooks Online Help Number. We have been surely here for your needs. In closing, any error, any difficulty, any bug or anything else pertaining to QuickBooks related problem, just call our QuickBooks Tech Support Phone Number. Surely, call our QuickBooks Support telephone number

James Zicrov said...
This comment has been removed by the author.
James Zicrov said...

I think Informatica and its components are always very useful in cracking simple solutions for tough and complex IT problems.

Informatica Read Rest API

krishna kishore said...

do we have repository query to find the target field (TARGET EX:TRG_FIELD1) to source fields used (src_field1,src_field2)

krishna kishore said...

do we have repository query to find the target field (TARGET EX:TRG_FIELD1) to source fields used (src_field1,src_field2)

Quickbooks error said...

Quickbooks file doctor is coming up in-gathered Quickbooks transformation 2016 and later on, and simply helps the customer with data concerning issues

Block said...

Thanks a lot very much for the high-quality and results-oriented help.
I won’t think twice to endorse your blog post to anybody who wants
and needs support about this area.
best hadoop training in chennai
software testing courses in chennai
javascript training in Chennai

tallisquast said...

Casino Games - JTM Hub
At JTG, we offer everything 통영 출장샵 you need in 거제 출장안마 casino games. Whether you're playing in 시흥 출장샵 online slot 상주 출장마사지 machines, roulette, blackjack or poker, you've got one 김천 출장샵

 
HTML Hit Counters