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.

138 comments:

amar said...

Nice work Radhakrishna ..!!!

Shoaib 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.

addeapalli said...

Hi Sarma,

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

prasuna 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

Seetha 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

sudheer 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.

partha 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.

sriswathi 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?

Kavitha 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

Abhi 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.

robert 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

Srikant Kopparthi 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.

xizu xizu 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

vijayrajpavan kamireddy 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.

kishor mahakale said...

Really good piece of knowledge, I had come back to understand regarding your website from my friend Sumit, Hyderabad And it is very useful for who is looking for INFORMATICA.

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?

vamsi ramakrishna said...

Hi Radhakrishna,

how the table OPB_OBJECT_LOCKS populates?

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

Thanks,
Vamsi

sudheer1414 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

sudheer1414 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?

SivaPrakash Jayaraman 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

SivaPrakash Jayaraman 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

Meenakshi PS 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

naveen kumar 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.

training onlinesas said...

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

syed s said...

I was reading your blog this morning and noticed that you have a awesome
resource page. I actually have a similar blog that might be helpful or useful
to your audience.

Regards
sap sd and crm online training
sap online tutorials
sap sd tutorial
sap sd training in ameerpet

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

Ravindra Reddy 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

Vipin Chaudhary 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.

Online IT Guru 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??

kits online 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

Priya Kannan said...

Such a great articles in my carrier, It's wonderful commands like easiest understand words of knowledge in information's.
PEGA Training in Chennai

 
HTML Hit Counters