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
I am feeling great to read this.you gave a nice info for us.please update more.
ReplyDeletePython 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