ODI Metadata Queries

Query to find Work Repository version

select REP_SHORT_ID, REP_NAME, REP_TYPE, REP_TIMESTAMP, REP_VERSION, MIN_EXE_VERSION,
 IND_INSTALL_OK from SNP_LOC_REPW;
SELECT DISTINCT
--SNP_PROJECT.PROJECT_NAME AS PROJECT_NAME,
GGpfol.folder_name as GGparent_folder,
Gpfol.folder_name as Gparent_folder,
pfol.folder_name as parent_folder,
SNP_FOLDER.FOLDER_NAME AS FOLDER_NAME,
SNP_POP.POP_NAME AS INTERFACE_NAME,
CASE
WHEN SNP_POP.WSTAGE='E' THEN 'TABLE_TO_TABLE_INF'
ELSE 'TEMP_INTERFACE' END AS INTERFACE_TYPE,
SNP_POP.LSCHEMA_NAME AS TARGET_SCHEMA, SNP_POP.TABLE_NAME AS TARGET_TABLE, SNP_POP_COL.COL_NAME AS TARGET_COLUMN,
SNP_SOURCE_TAB.LSCHEMA_NAME AS SOURCE_SCHEMA, SNP_TABLE.TABLE_NAME AS SOURCE_TABLE, SNP_COL.COL_NAME AS SOURCE_COLUMN ,
SNP_COL.SOURCE_DT||' ('||SNP_COL.LONGC||')' AS SRC_DATATYPE,
rowtocol('SELECT DISTINCT S.TXT FROM SNP_TXT S WHERE S.I_TXT='||S_TXT.i_txt ) AS COLUMN_MAPPING
FROM SNP_PROJECT
LEFT OUTER JOIN SNP_FOLDER ON SNP_FOLDER.I_PROJECT=SNP_PROJECT.I_PROJECT
LEFT OUTER JOIN SNP_FOLDER PFOL ON SNP_FOLDER.PAR_I_FOLDER=PFOL.I_FOLDER
LEFT OUTER JOIN SNP_FOLDER GPFOL ON PFOL.PAR_I_FOLDER=GPFOL.I_FOLDER
LEFT OUTER JOIN SNP_FOLDER GGPFOL ON GPFOL.PAR_I_FOLDER=GGPFOL.I_FOLDER
LEFT OUTER JOIN SNP_POP ON SNP_POP.I_FOLDER=SNP_FOLDER.I_FOLDER
LEFT OUTER JOIN SNP_POP_COL ON SNP_POP_COL.I_POP=SNP_POP.I_POP 
LEFT OUTER JOIN SNP_POP_CLAUSE ON SNP_POP_CLAUSE.I_POP=SNP_POP.I_POP 
LEFT OUTER JOIN SNP_TXT S_TXT ON S_TXT.I_TXT=SNP_POP_CLAUSE.I_TXT_SQL
LEFT OUTER JOIN SNP_TXT ON SNP_TXT.I_TXT= SNP_POP_COL.I_TXT_MAP 
LEFT OUTER JOIN SNP_SOURCE_TAB ON SNP_SOURCE_TAB.I_POP=SNP_POP.I_POP 
LEFT OUTER JOIN SNP_TXT_CROSSR ON SNP_TXT_CROSSR.I_TXT=SNP_TXT.I_TXT
LEFT OUTER JOIN SNP_COL ON SNP_COL.I_COL=SNP_TXT_CROSSR.I_COL 
LEFT OUTER JOIN SNP_TABLE ON SNP_TABLE.I_TABLE= SNP_COL.I_TABLE 
LEFT OUTER JOIN SNP_COL T_COL ON T_COL.I_COL=SNP_POP_COL.I_COL
WHERE
--SNP_POP.POP_NAME LIKE '%Sq_W_PRODUCT_DS%'
SNP_POP.TABLE_NAME LIKE '%W_XACT_TYPE_D'
and SNP_POP_COL.COL_NAME like 'W_XACT_TYPE_CODE'
ORDER BY SNP_POP.POP_NAME;

 

SELECT
 e.pop_name,
 a.i_src_set
 ,a.i_data_set
 ,a.i_pop_clause
 ,a.i_txt_sql
 ,a.join_type -- C Cross Join J Join, N Natural Join
 ,CASE WHEN a.clause_type = 3 THEN 'Filter' ELSE 'JOIN' END clause_type
 ,CASE WHEN join_type = 'J' AND ind_outer1 = 1 AND ind_outer2 = 1
 THEN 'Full Outer Join'
 WHEN join_type = 'J' AND ind_outer1 = 1 AND ind_outer2 = 0
 THEN 'Left Outer Join'
 WHEN join_type = 'J' AND ind_outer1 = 0 AND ind_outer2 = 1
 THEN 'Right Outer Join'
 WHEN join_type = 'J' AND ind_outer1 = 0 AND ind_outer2 = 0
 THEN 'Inner Join'
 WHEN join_type = 'C' THEN 'Cross Join'
 ELSE 'N/A'
 END ansi_join
 ,ord_clause
 ,a.ind_enable active_clause
 ,b.string_pos
 ,b.string_elt
 ,b.tab_alias
 ,b.i_col
 ,c.full_text
 FROM snp_pop_clause a
 LEFT JOIN snp_txt_crossr b
 ON a.i_txt_sql = b.i_txt
 LEFT JOIN snp_txt_header c
 ON a.i_txt_sql = c.i_txt
 LEFT JOIN SNP_POP_COL d
 ON d.i_col = b.i_col
 LEFT JOIN SNP_POP e
 ON d.i_pop = e.i_pop
 order by e.pop_name nulls last;

 

Advertisements