DronaBlog

Monday, October 7, 2019

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

What are differences between multimerge and merge API in Informatica MDM

                Are you interested in knowing what is the use of multimerge and merge APIs? Are you also would like to know the difference b...