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