Friday, January 20, 2017

Informatica Metadata Queries - 5 TARGET TABLES


5.1         List of Target Tables


SELECT B.SUBJ_NAME,
A.TARGET_NAME,
DECODE(A.DBTYPE,
0,'VSAM',
1,'IMS',
2,'Sybase',
3,'Oracle',
4,'Informix',
5,'Microsoft SQL Server',
6,'DB2',
7,'Flat File',
8,'ODBC',
9,'SAP BW',
10,'PeopleSoft',
11,'SAP R/3',
12,'XML',
13,'MQSeries',
14,'Siebel',
15,'Teradata' ) as DB_TYPE
FROM
OPB_TARG A,
OPB_SUBJECT B
WHERE A.SUBJ_ID = B.SUBJ_ID
ORDER BY 1,2,3

5.2         List All Columns, DB, Length of a Target Table 


SELECT PARENT_SUBJECT_AREA,
  PARENT_TARGET_NAME,
  PARENT_TARGET_LAST_SAVED,
  PARENT_TARGET_DATABASE_TYPE,
  SUBJECT_AREA,
  TARGET_NAME,
  TARGET_LAST_SAVED,
  TARGET_FIELD_NAME,
  TARGET_FIELD_NUMBER,
  TARGET_FIELD_KEY_TYPE,
  TARGET_FIELD_DATATYPE,
  TARGET_FIELD_PRECISION
FROM REP_ALL_TARGET_FLDS
WHERE
Subject_Area = FOLDERNAME'
AND TARGET_NAME = 'TARGETTABLEANME'


5.3         List and count of tables in each folder by db type


SELECT B.SUBJ_NAME,
DECODE(A.DBTYPE,
0,'VSAM',
1,'IMS',
2,'Sybase',
3,'Oracle',
4,'Informix',
5,'Microsoft SQL Server',
6,'DB2',
7,'Flat File',
8,'ODBC',
9,'SAP BW',
10,'PeopleSoft',
11,'SAP R/3',
12,'XML',
13,'MQSeries',
14,'Siebel',
15,'Teradata' ) as DB_TYPE,
count(*)
FROM
OPB_TARG A,
OPB_SUBJECT B
WHERE A.SUBJ_ID = B.SUBJ_ID
GROUP BY B.SUBJ_NAME,A.DBTYPE
ORDER BY 1,2

5.4         List and count of table overall used


SELECT SOURCE_NAME, COUNT(SOURCE_NAME) FROM REP_TBL_MAPPING
GROUP BY SOURCE_NAME
ORDER BY 1,2 ASC

5.5         List Target table used in session level



SELECT SUBJECT_AREA,SESSION_NAME,SESSION_INSTANCE_NAME,WIDGET_NAME AS TARGET_TABLE_NAME,TYPE_NAME AS TARGET_TYPE FROM REP_SESS_TBL_LOG
ORDER BY 1,2,3

5.6         Truncate target Table Option


SELECT DISTINCT C.SUBJ_NAME,A.TASK_NAME SESSION_NAME, DECODE(B.ATTR_VALUE,1,'YES','NO')  TRUNCATE_TARGET_TABLE
FROM  OPB_TASK A , OPB_EXTN_ATTR B, OPB_SUBJECT C
WHERE B.SESSION_ID=A.TASK_ID
AND A.SUBJECT_ID = C.SUBJ_ID
AND A.TASK_TYPE = 68
AND B.ATTR_ID=9

ORDER BY 1,2,3