8.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
8.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'
8.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
8.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
8.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
8.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