Informatica MDM - Helpful SQL Queries

This article provides the SQL queries which will be helpful for daily usage for monitoring jobs, analyzing data as well as developing custom components.

1. Query to get Landing and Base Object tables

SELECT table_name

  FROM cmx_ors.C_repos_table

  where table_name not like '%_CTL' AND table_name not like '%_DRTY' AND table_name not like '%_EMI'

  AND table_name not like '%_EMO' AND table_name not like '%_HIST' AND table_name not like '%_HMRG'

  AND table_name not like '%_HPCT' AND table_name not like '%_HVXR' AND table_name not like '%_HXRF'

  AND table_name not like '%_MTCH' AND table_name not like '%_HTIP' AND table_name not like '%_STRP'

  AND table_name not like '%_MTIP' AND table_name not like '%_VCT'  AND table_name not like '%_VXR'

  AND table_name not like '%_XREF' AND table_name not like 'C_MT%' AND table_name not like 'C_RBO%'

  AND table_name not like 'C_REPOS%' AND table_name not like 'C_REPAR%' AND table_name not like 'C_S%' 

  AND table_name not like 'PKG%'

2. Query to get Column names

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH,

NULLABLE, DATA_DEFAULT, SYS_IND, PKEY_IND

FROM CMX_ORS.DBO.C_REPOS_COLUMN

WHERE TABLE_NAME IN (

SELECT table_name

  FROM cmx_ors.C_repos_table

  where table_name not like '%_CTL' AND table_name not like '%_DRTY' AND table_name not like '%_EMI'

  AND table_name not like '%_EMO' AND table_name not like '%_HIST' AND table_name not like '%_HMRG'

  AND table_name not like '%_HPCT' AND table_name not like '%_HVXR' AND table_name not like '%_HXRF'

  AND table_name not like '%_MTCH' AND table_name not like '%_HTIP' AND table_name not like '%_STRP'

  AND table_name not like '%_MTIP' AND table_name not like '%_VCT'  AND table_name not like '%_VXR'

  AND table_name not like '%_XREF' AND table_name not like 'C_MT%' AND table_name not like 'C_RBO%'

  AND table_name not like 'C_REPOS%' AND table_name not like 'C_REPAR%' AND table_name not like 'C_S%' 

  AND table_name not like 'PKG%')

3. Query to get jobs status, run, failure

SELECT TABLE_DISPLAY_NAME, (END_RUN_DATE - START_RUN_DATE) AS TOTAL_TIME

, START_RUN_DATE, END_RUN_DATE, STATUS_MESSAGE

, ST.JOB_STATUS_DESC,FT.OBJECT_FUNCTION_TYPE_DESC

FROM CMX_ORS.C_REPOS_JOB_CONTROL JC,

     CMX_ORS.C_REPOS_JOB_STATUS_TYPE ST,

     CMX_ORS.C_REPOS_OBJ_FUNCTION_TYPE FT

WHERE JC.RUN_STATUS = ST.JOB_STATUS_CODE

     AND JC.OBJECT_FUNCTION_TYPE_CODE = FT.OBJECT_FUNCTION_TYPE_CODE;

4. Query to get match rule set using Match table

select * from cmx_ors.c_repos_match_set where rowid_match_set in (
select rowid_match_Set from cmx_ors.c_repos_match_rule where ROWID_MATCH_RULE in
(select distinct ROWID_MATCH_RULE from cmx_ors.c_bo_party_mtch)
)

5. Query to get match rule set using ROWID_MATCH_RULE

SELECT * FROM CMX_ORS.C_REPOS_MATCH_SET WHERE ROWID_MATCH_SET IN (

SELECT ROWID_MATCH_SET FROM CMX_ORS.C_REPOS_MATCH_RULE WHERE ROWID_MATCH_RULE='SVR1.1V4T6'  

)

6. Query to get ROWID_MATCH_RULE using Match Set and Rule Number

SELECT * FROM CMX_ORS.C_REPOS_MATCH_RULE WHERE ROWID_MATCH_SET IN (  

SELECT ROWID_MATCH_SET FROM CMX_ORS.C_REPOS_MATCH_SET WHERE MATCH_SET_NAME='IDL'

) AND RULE_NO=1

7. Query to get Match column names using ROWID_MATCH_RULE

select MS.MATCH_SET_NAME, MR.RULE_NO, MC.MATCH_COLUMN_NAME from CMX_ORS.C_REPOS_MATCH_RULE_COMPONENT c,

 CMX_ORS.C_REPOS_MATCH_RULE MR,

 CMX_ORS.C_REPOS_MATCH_COLUMN MC,

 CMX_ORS.C_REPOS_MATCH_SET MS

where c.ROWID_MATCH_RULE = MR.ROWID_MATCH_RULE

and c.ROWID_MATCH_COLUMN = MC.ROWID_MATCH_COLUMN

and MR.ROWID_MATCH_SET = MS.ROWID_MATCH_SET

and MR.ROWID_MATCH_RULE like  '%SVR1.ABC%'

8. Query to get a count of records processed for each job

SELECT JM.CREATE_DATE, FT.OBJECT_FUNCTION_TYPE_DESC, JC.TABLE_DISPLAY_NAME, MT.METRIC_TYPE_DESC, JM.METRIC_VALUE AS COUNT

FROM CMX_ORS.C_REPOS_JOB_METRIC JM,

CMX_ORS.C_REPOS_JOB_METRIC_TYPE MT,

CMX_ORS.C_REPOS_JOB_CONTROL JC,

CMX_ORS.C_REPOS_OBJ_FUNCTION_TYPE FT

WHERE JM.METRIC_TYPE_CODE = MT.METRIC_TYPE_CODE

AND JM.ROWID_JOB = JC.ROWID_JOB

AND JC.OBJECT_FUNCTION_TYPE_CODE = FT.OBJECT_FUNCTION_TYPE_CODE;

9. How to check Timeline Granularity

SELECT TIMELINE_GRANULARITY FROM C_REPOS_DB_RELEASE; 

Value	Type of Granularity
0	Seconds
1	Minutes
2	Hours
3	Days
4	Months
5	Years