Tuesday, June 15, 2010

SQL Overrides in Lookups, Source Qualifier etc

I have seen in many instances, you would want to know the SQL overrides written in a mapping Source Qualifier transformation or Lookup SQL override or Pre-SQL & Post-SQL etc. Below is the query that takes the workflow name & Folder name as input and gives you all the SQL overrides wherever they are in the workflow.

select folder, wf_name, 
sess_name, mapping_name,
transformation_name, attr_name,
line_no, sql_value
from (select f.subj_name folder,
wf.task_name wf_name,
sess.instance_name sess_name,
m.mapping_name mapping_name,
w_inst.instance_name transformation_name,
attr.line_no, attr.attr_value sql_value,
attr_type.attr_name attr_name,
row_number() over (partition by wf.task_name,
order by attr.session_task_id desc
) rn
from opb_task_inst wf_inst
,opb_task_inst sess
,opb_session s
,opb_mapping m
,opb_subject f
,opb_widget_attr attr
,opb_widget_inst w_inst
,opb_task wf
,(select o.object_type_id object_type_id,
o_attr.attr_id attr_id,
o.object_type_name||': '||o_attr.attr_name attr_name
from opb_attr o_attr,
opb_object_type o
where o.object_type_id = o_attr.object_type_id
and o_attr.attr_datatype = 2
and o_attr.attr_value is null
and upper(o_attr.attr_name) like '%SQL%'
) attr_type
where wf_inst.task_id = sess.task_id
and sess.task_type = 68
and sess.task_id = s.session_id
and wf.subject_id = f.subj_id
and s.mapping_id = m.mapping_id
and attr.widget_id = w_inst.widget_id
and w_inst.mapping_id = m.mapping_id
and w_inst.widget_type = attr_type.object_type_id
and wf_inst.workflow_id = wf.task_id
and wf.task_type = 71
and (attr.session_task_id = s.session_id
or attr.session_task_id = 0)
and attr.attr_id = attr_type.attr_id
and attr.attr_value is not null
and attr.attr_value <> '0'
and wf.task_name = 'WORKFLOW_NAME'
and f.subj_name = 'FOLDER_NAME'
where rn = 1
order by 1, 2, 3, 4, 5, 6, 7


Anonymous said...

hi dude, this query is giving me ORA-01775 (Looping chain of synonym) at line 35. Same issue with previous query on shortcuts at line 45.

Anonymous said...

Oh btw.. i am looking for a query to get all the Workflows> worklets> mappings> status (valid/invalid)> staus (enable/disable) for my repository cleanup. looking at your previous posts I belive you can help.

Anonymous said...

And also I am trying to get the list of Infa coonections (across whole repository) through below query. Do you find it appropraite?

select distinct subj_name Informatica_folder,
object_name Connection_Name,
h.user_name Connection_Owner,
f.user_name DB_USER_NAME,
i.task_name SESSION_NAME

pm_ro.opb_session a,
pm_ro.opb_mapping b,
pm_ro.opb_subject c,
pm_ro.opb_sess_cnx_refs e,
pm_ro.opb_cnx f,
pm_ro.opb_groups g,
pm_ro.opb_users h,
pm_ro.opb_task i

where a.mapping_id = b.mapping_id
and b.subject_id = c.subj_id
and a.session_id = e.session_id
and e.ref_object_id = f.object_id
and f.group_id = g.group_id
and f.owner_id = h.user_id
--and a.mapping_id = b.mapping_id
and a.session_id = i.task_id
order by connection_name, informatica_folder

Radhakrishna Sarma said...

Regarding "ORA-01775 (Looping chain of synonym) at line 35", it is a synonym error in your database. Have you created any public synonyms or just synonyms?

Radhakrishna Sarma said...

Regarding your query on objects and its status, etc..

select f.subj_name folder, t.task_name object_name,
decode(t.task_type, 68, 'Session',
70, 'Worklet',
71, 'Workflow'
) object
from opb_task t,
opb_subject f
where t.subject_id = f.subj_id
and t.task_type in ( 70-- Worklet
,68-- Session
,71-- Workflow
and (t.is_valid <> 1 or t.is_enabled <> 1)
union all
select f.subj_name, m.mapping_name, 'Mapping'
from opb_mapping m,
opb_subject f
where m.subject_id = f.subj_id
and (m.is_valid <> 1 or m.saved_valid <> 1)

Radhakrishna Sarma said...

Query for connections looks OK, haven't tested it myself, though

Anonymous said...

I tried to run this query it runs fine but in the results just gives 1 output row which is the sql query in first session.

Sarada Rao said...

Hi Radhakrishna,
Ur blog is very good.Am currently working on SAP+Informatica integration. Can u please help me in calling SAP function modules in mapping.
Thanks in advance...

uppu said...

Hi Sharma,
I have one question related to the statistic details.In one of the ETL, we are using the "TARGET UPDATE OVERRIDE" option and follows Type-2 design(lookup on target then insert/update).I noticed that APPLIED_ROWS>AFFECTED_ROWS.Can you tell me the possible scenario with above given constraints.

ram said...

Hi Radha,
Your blog is quite commendable with all your expert suggestions straight from your experience. I have a question about the way I should proceed with Informatica process I am developing to load an Oracle source data coming from some five tables to a xml target. But the catch here the xsd given for target xml is not regular xml but of subversion xml flavor which differs in the way it arranges in hierachies like so all these over hundred columns would get into part one element's attributes aka flattend row from several source columns. I am using set of joiners and then expression, a router to divide the columns into groups and using union to group all row columns into an out put string. Do you think I am on the right way? If not please suggest.
Will wait on your response.

Uppu said...


Do you have any documentation on the type of metadata tables and information they hold?


Anonymous said...

Hi Radha,

I am trying to add UserName and Connection string information from opb_cnx table in the inital SQL posted by you but is not able to. Kindly help.

Thanks & Regards,
Amber Mehrotra

Pradeep Kothakota said...
This comment has been removed by the author.
Anonymous said...


We have webservices in informatica and i would like to know what all the parameters used for a webservice call in day.

What is the metadata table in informatica which stores these details.

chanukya said...

Hi Sarma,

I have a question. Not related to this post. I am using 7x version.
I would like to know wat happens in the backend right from where we import a table and running the task. I have some idea but wanted to know in depth.


Naveen said...

Hey Radhakrishnan Sarma,
The query picks up all sq overrides but it doesn't pick the Pre SQL/Post SQL from a session (non reusable) in a workflow.

Thanks and appreciate your valuable work. Naveen

Sangram said...

SIR! i am in trouble.
I have developed an application for automated informatica deployment.
But now i am stuck at a point where i am not able to link global shared objects with their shortcuts in another repository by running queries on the rep db.
the global shortbut in my local rep has a negative target_id in rep_all_targets,its parent target_id is 0, and the actual object in the global rep has a different widget id.

Karin said...

Hello Radhakrishna,
this is a great blog with excellent info.
Just one question: I wonder why you are always refering to the OPB-tables, instead of the (documented) REP-Views? Any reasons for that?

Regards, Karin

Anand said...

Hi RadhaKrishna

Do you know if we can get the total memory used by a workflow for its latest run ? Currently I ma able to find it in session log for each transformation but not for the whole workflow.

Excellent blog ... great job Radha ...


tom smith said...

Thanks for giving good information about all informatica tutorial online in this blog. This is really nice etl blog.
Datastage Online Training

Trupti Tahasildar said...

how to practice Informatica tool at home?

Pratish said...


I am totally new to Informatica World. I would appreciate if you can provide more details as where should this sql query can be written in order to get the info about pre-sql, post-sql and sql overrides?

jake george said...

Informatica Online Training, ONLINE TRAINING – IT SUPPORT – CORPORATE TRAINING http://www.21cssindia.com/courses/informatica-online-training-31.html The 21st Century Software Solutions of India offers one of the Largest conglomerations of Software Training, IT Support, Corporate Training institute in India - +919000444287 - +917386622889 - Visakhapatnam,Hyderabad Informatica Online Training, Informatica Training, Informatica, Informatica Online Training| Informatica Training| Informatica| "Courses at 21st Century Software Solutions
Talend Online Training -Hyperion Online Training - IBM Unica Online Training - Siteminder Online Training - SharePoint Online Training - Informatica Online Training - SalesForce Online Training - Many more… | Call Us +917386622889 - +919000444287 - contact@21cssindia.com
Visit: http://www.21cssindia.com/courses.html"

Payoffers dotin said...

Earn from Ur Website or Blog thr PayOffers.in!


Nice to e-meet you. A very warm greetings from PayOffers Publisher Team.

I am Sanaya Publisher Development Manager @ PayOffers Publisher Team.

I would like to introduce you and invite you to our platform, PayOffers.in which is one of the fastest growing Indian Publisher Network.

If you're looking for an excellent way to convert your Website / Blog visitors into revenue-generating customers, join the PayOffers.in Publisher Network today!

Why to join in PayOffers.in Indian Publisher Network?

* Highest payout Indian Lead, Sale, CPA, CPS, CPI Offers.
* Only Publisher Network pays Weekly to Publishers.
* Weekly payments trough Direct Bank Deposit,Paypal.com & Checks.
* Referral payouts.
* Best chance to make extra money from your website.

Join PayOffers.in and earn extra money from your Website / Blog


If you have any questions in your mind please let us know and you can connect us on the mentioned email ID info@payoffers.in

I’m looking forward to helping you generate record-breaking profits!

Thanks for your time, hope to hear from you soon,
The team at PayOffers.in

Daniel Mason said...

It was really a nice article and I was really impressed by reading this article. We are also giving all software Course Online Training. The Informatica Online Training is one of the leading Online Training institute in the world.

Shalini said...

Superb. I really enjoyed very much with this article here. Really it is an amazing article I had ever read. I hope it will help a lot for all. Thank you so much for this amazing posts and please keep update like this excellent article.thank you for sharing such a great blog with us. expecting for your.
Digital Marketing Company in India
seo Company in India

Abiya Carol said...

I just see the post i am so happy to the communication science post of information's.So I have really enjoyed and reading your blogs for these posts.Any way I’ll be replay for your great thinks and I hope you post again soon.

Hadoop Training in Chennai
Dotnet Training in Chennai

HTML Hit Counters