Wednesday, 18 March 2009

Informatica Metadata Queries (Part 2)

In this post we will continue with informatica metadata queries

Purpose : To Find Tracing Level for Session
Query :
select task_name,decode (attr_value,0,'None',1,'Terse',2,'Normal',3,'Verbose Initialisation',4,'Verbose Data','') Tracing_Level
and tsk.TASK_TYPE=68
and attr_id=204 and attr_type=6
Description : This query will give tracing information along with session names.This query is helpful in identifying the session which are having particular type of Tracing level like Verbose.

Purpose : To Find name of all stored procedure being used in stored procedure transformation
Query :
select attr_value from
where widget_type=6 and attr_id=1
Description : This query is helpful when you require to know name of all stored procedure being used in informatica.

Purpose : To find who saved mapping last time
Query :
SELECT substr(rpl.event_time,7,4) substr(rpl.event_time,6,1) substr(rpl.event_time,1,5) ' ' substr(rpl.event_time,12,11) "EventTimestamp" ,
usr.user_name "Username",
DECODE(rpl.object_type_id,21,s21.subj_name,('('rpl.object_type_id')')) "Folder",
obt.object_type_name "Type",
DECODE(rpl.object_type_id,21,map.mapping_name,('('rpl.object_type_id')')) "Object"
opb_reposit_log rpl,opb_object_type obt,
opb_subject fld,opb_mapping map,opb_users usr,opb_subject s21
WHERE obt.object_type_name = 'Mapping'
AND rpl.object_type_id = obt.object_type_id
AND rpl.object_id = map.mapping_id(+)
AND rpl.object_id = fld.subj_id(+)
AND rpl.event_uid = usr.user_id
AND map.subject_id = s21.subj_id(+)
ORDER BY(substr(rpl.event_time,7,4) substr(rpl.event_time,6,1)
substr(rpl.event_time,1,5) ' ' substr(rpl.event_time,12,11)) DESC
Description : This query is helpful when you want to know who saved the mapping last time .

If you need some information and need query about something different related to metadata query please mail us at My Profile

1 comment:

  1. Hi,

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