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

1 Comments:

At 3/18/2019 , Blogger sheela rajesh said...

I am feeling great to read this.you gave a nice info for us.please update more.
Python Training in Chennai
Python Classes in Chennai
JAVA Training in Chennai
Hadoop Training in Chennai
Selenium Training in Chennai
Python Training in Chennai
Python Course in Chennai

 

Post a Comment

Subscribe to Post Comments [Atom]

<< Home