Informatica Metadata Queries (Part 3)

We will continue to publish new metadata queries

Purpose :  To Find Truncate Table Option

Query : 

select task_name,'Truncate Target Table' ATTR,decode(attr_value,1,'Yes','No') Value 

 from OPB_EXTN_ATTR OEA,REP_ALL_TASKS RAT  

where OEA.SESSION_ID=rat.TASK_ID 

and attr_id=9 

Description : This query will give all sessions where Truncate Table Option is on

Purpose : This query will give count of Mapping,Instance where Sqloverride has been done

Query : WITH detail AS

(SELECT c.subject_area, c.mapping_name,

d.instance_name source_qualifier_name,

CASE WHEN a.attr_value IS NOT NULL THEN 1 ELSE 0 END as OVR_OK

FROM rep_all_mappings c,

opb_widget_inst d,

opb_widget_attr a 

WHERE c.mapping_id = d.mapping_id

AND c.mapping_version_number = d.version_number

AND d.widget_type = 3

AND d.widget_id = a.widget_id

AND a.widget_type = d.widget_type

AND a.attr_id = 1

)

SELECT subject_area, 'SQ_OVERIDE' STATUS,

COUNT (DISTINCT mapping_name) NO_OF_Mapping,

COUNT (DISTINCT (mapping_name || source_qualifier_name)) NO_OF_SQ_IN_MAPPING,

COUNT (DISTINCT (source_qualifier_name)) NO_OF_DISTINCT_SQ

FROM detail

WHERE OVR_OK =1

GROUP BY subject_area

UNION

SELECT subject_area, 'SQ_NON_OVERIDE',

COUNT (DISTINCT mapping_name) nb_mapping,

COUNT (DISTINCT (mapping_name || source_qualifier_name)) nb_map_inst,

COUNT (DISTINCT (source_qualifier_name)) nb_inst

FROM detail

WHERE OVR_OK =0 

GROUP BY subject_area

Description : This query will count of Mapping,SQ in mappings,SQ where override has been done.

Purpose : This query will SQL Override queries upto 4000 length

Query :

SELECT subj_name, mapping_name,widget_id,

replace(replace(replace (next_attr_val0||next_attr_val1,'   ',' '),chr(13),' '),chr(10),' ')  QUERY_OP_1

fROM 

(

select distinct SUB.subj_name subj_name, MAP.mapping_name mapping_name,wid.widget_id,

WID.attr_value next_attr_val0,

LEAD(WID.ATTR_VALUE,1,'') OVER ( PARTITION BY wid.WIDGET_ID,wid.MAPPING_ID ORDER BY wid.WIDGET_ID,wid.MAPPING_ID,wid.LINE_NO) NEXT_ATTR_VAL1,

WID.LINE_NO

from  opb_widget_attr WID,  opb_mapping MAP,  opb_widget_inst WIDINST,

opb_subject SUB

where WID.widget_id = WIDINST.widget_id

and WID.widget_type = WIDINST.widget_type

and WID.widget_type = 3

and WID.attr_id = 1

and WIDINST.mapping_id = MAP.mapping_id

and MAP.subject_id = SUB.subj_id

ORDER BY SUB.subj_name, MAP.mapping_name,wid.widget_id,WID.LINE_NO

)

WHERE  next_attr_val0||next_attr_val1 LIKE '%UPL%'

and line_no=1

Purpose : This query will SQL Override queries upto 4000 length.This query will work in oracle database

Please note that these queries has been tested in Oracle 10G.

If you need some information and need query about something different related to metadata query please mail us at support@itnirvanas.com.

My Profile

Read More

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
from REP_SESS_CONFIG_PARM CFG,opb_task TSK
WHERE CFG.SESSION_ID=TSK.TASK_ID
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
OPB_WIDGET_ATTR
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"
FROM
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 support@itnirvanas.com.My Profile

Read More

Informatica Metadata Queries (Part1)

Informatica Metadata Queries

In Informatica normally we come across situation like we need to find all mapping which use a particular table as source or something similar .It is very difficult to find it through Informatica tools.In such a situation Repository tables are very useful.

Repository tables name start with OPB and are continuously updated when we make changes.Along with OPB tables REP views are also present.

It is very dangerous to modify these tables so due care should be taken while dealing with OPB Tables.

In this article we will focus on some useful meta data queries

Purpose : Search for a table in Source Qualifiers Sql Override:

Query : 

select distinct SUB.subj_name, MAP.mapping_name

from  opb_widget_attr WID,  opb_mapping MAP,  opb_widget_inst WIDINST,

 opb_subject SUB

where WID.widget_id = WIDINST.widget_id

and WID.widget_type = WIDINST.widget_type

and WID.widget_type = 3

and WID.attr_id = 1

and WIDINST.mapping_id = MAP.mapping_id

and MAP.subject_id = SUB.subj_id

and upper(WID.attr_value) like '%TNAME%' ;

Description : This query will give list of all mappings where a particular table is being used in sql override.

Purpose : Search for a table in Sources and Targets :

Query : 

select SUB.subj_name, decode(WIDG.widget_type,1,'Source',2,'Target'),

MAP.mapping_name,

WIDG.instance_name

from  opb_widget_inst WIDG,  opb_mapping MAP, opb_subject SUB

where SUB.subj_id = MAP.subject_id

and WIDG.mapping_id = MAP.mapping_id

and WIDG.widget_type in (1,2)

and WIDG.instance_name like '%TNAME_%'

and SUB.subJ_NAME='YOUR_FOLDER_NAME'

Description : This query will give list of all folders,mappings where a particular table is being used as source or target instance.

Purpose : Query to give lookup information

Query :

Select distinct wid.WIDGET_ID, all_map.mapping_name, wid.INSTANCE_NAME Lkp_name, Decode(widat.attr_id,2,widat.attr_value) Table_name,

decode (widat.attr_id,6,widat.attr_value) src_tgt

FROM  rep_all_mappings ALL_MAP, rep_widget_inst wid,  OPB_WIDGET_ATTR widat

where all_map.mapping_id=wid.mapping_id

and wid.WIDGET_ID=widat.WIDGET_ID

and all_map.subject_area='DCM_SPP_UPL_DEVT'

and wid.WIDGET_TYPE=11

and widat.WIDGET_TYPE=11

and widat.ATTR_ID in (2,6)

Description : This query will give information about lookup transformations like lookup name,Tablename ,Mapping name etc. 

Purpose : Query to give Invalid workflows

Query :

select opb_subject.subj_name, opb_task.task_name

from  opb_task,   opb_subject

where task_type = 71 

and is_valid = 0 

and opb_subject.subj_id = opb_task.subject_id

and UPPER(opb_subject.SUBJ_NAME) like UPPER('YOUR_FOLDER_NAME')

Description : This query will list of all invalid 

If you have need some information and need query about something different related to metadata query please mail us at support@itnirvanas.com.

Read More