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,
sess.instance_name,
m.mapping_name,
w_inst.instance_name,
attr.line_no,
attr.attr_value
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

46 comments:

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,
connect_string,
f.user_name DB_USER_NAME,
i.task_name SESSION_NAME

from
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'
'Blah'
) 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.
Thanks.
Will wait on your response.
Ram

Uppu said...

Hi,

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

Thnanks,
Upen

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
(amber.mehrotra@gmail.com)

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

Hi

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.

Thanks
Chanukya

Anonymous 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.
PLEASE HELP!!

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

Anand

Unknown said...

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

Unknown said...

how to practice Informatica tool at home?

Pratish said...

Hi,

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?

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

Unknown 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

Sathya said...

Thank you for the fresh content and information you provide. This really helps learner like me. Keep sharing knowledge. Happy to read.


Dataware Housing Training in Chennai | Hadoop Training in Chennai

Tejuteju said...

Really nice blog post. provided a helpful information. I hope that you will post more updates like this Informatica Online Training

siva said...

Great Article… I love to read your articles because your writing style is too good,
its is very very helpful for all of us and I never get bored while reading your article because,
they are becomes a more and more interesting from the starting lines until the end.


Java training in Chennai

Java training in Bangalore

Java online training

Java training in Pune

sasitamil said...


This is quite educational arrange. It has famous breeding about what I rarity to vouch. Colossal proverb.
This trumpet is a famous tone to nab to troths. Congratulations on a career well achieved. This arrange is synchronous s informative impolites festivity to pity. I appreciated what you ok extremely here 


Selenium training in bangalore
Selenium training in Chennai
Selenium training in Bangalore
Selenium training in Pune
Selenium Online training

service care said...

Thanks for posting useful information.You have provided an nice article, Thank you very much for this one. And i hope this will be useful for many people.. and i am waiting for your next post keep on updating these kinds of knowledgeable things...Really it was an awesome article...very interesting to read..please sharing like this information......
mobile service center in chennai
mobile service center chennai
mobile service centre near me
mobile service centre chennai

meenati said...

I like your blog, I read this blog please update more content on hacking, Nice post
Informatica Online Training

Vishal DurgaIT said...
This comment has been removed by the author.
Durga IT Solutions said...
This comment has been removed by the author.
Quickbooks support said...

Contact Quickbooks 24 Hour Support Phone Number 800-901-6679 to acquire instant solution of your issues & queries. Get round the clock assistance from our highly skilled technicians. Whenever appear with technical glitches just make a call on the support anytime.

Realtime Experts said...


This is amazing and really inspiring goal.Real Time Experts Training in Bangalore

QuickBooks Payroll Support said...

QuickBooks Support Phone Number
Quickbooks Proadvisor Support Phone Number
QuickBooks Helpline Number

Julia Loi said...



Really appreciate this wonderful post that you have provided for us.Great site and a great topic as well I really get amazed to read this. It's really good.
I like viewing web sites which comprehend the price of delivering the excellent useful resource free of charge. I truly adored reading your posting. Thank you!.
mobile phone repair in Fredericksburg
mobile phone repair in Fredericksburg
mobile phone repair in Fredericksburg
mobile phone repair in Fredericksburg
mobile phone repair in Fredericksburg
mobile phone repair in Fredericksburg
mobile phone repair in Fredericksburg
mobile phone repair in Fredericksburg
mobile phone repair in Fredericksburg
mobile phone repair in Fredericksburg

James Zicrov said...

I think Informatica and some related tools always provide some base for solving very complex IT problems.

Informatica Read Soap Api

radhika said...

Excellent Blog!!! Such an interesting blog with clear vision, this will definitely help for beginner to make them update.
AWS training in Chennai

AWS Online Training in Chennai

AWS training in Bangalore

AWS training in Hyderabad

AWS training in Coimbatore

AWS training


Admin said...

Here is the site(bcomexamresult.in) where you get all Bcom Exam Results. This site helps to clear your all query.
Annamalai University BCOM 2nd Year Result 2020
BA 3rd year Result
BA Result 2020

suresh said...


Thanks for sharing such a great information..Its really nice and informative..

DevOps Training in Chennai

DevOps Course in Chennai


Ruhi Sukhla said...

Thanks for the marvelous posting! I definitely enjoyed reading it, you are a great author.I will be sure to bookmark your blog.

फैज़ाबाद अवध यूनिवर्सिटी बी.ए फर्स्ट रिजल्ट

Python said...

Thanks for Sharing.
Data Science Online Training
Python Online Training
Salesforce Online Training

salome said...

informative article.thank you
best-angular-training in chennai |

BA 3rd Year Time Table said...

First You got a great blog. I will be interested in more similar topics. I see you got really very useful topics, I will be always checking your blog thanks.

Emerging Technologies said...

It is perfect time to make some plans for the future and it is time to be happy. I’ve read this post and if I could I desire to suggest you few interesting things or tips. Perhaps you could write next articles referring to this article. I want to read more things about it! data science course in pune

main ratan pannel chart said...

Amazing or I can say this is a remarkable article

main bazar pannel chart | puna bazar pannel chart | mumbai morning pannel chart.

 
HTML Hit Counters