Unload data and turn into external table using Oracle10g datapump

-- create DIRECTORY

CREATE OR REPLACE DIRECTORY ETL_EXT_TABS AS 'C:\ext_tab\';
GRANT READ ON DIRECTORY ETL_EXT_TABS TO orion;
GRANT WRITE ON DIRECTORY ETL_EXT_TABS TO orion;

-- unload Table DEPT and turn into an external table TMP_EXT_TABLE1

create table TMP_EXT_TABLE1
organization external
(type oracle_datapump
default directory ETL_EXT_TABS
location('XX.TXT'))
as select * from DEPT;

-- unload table D_N_USER of Heterogeneous database(SQL2000)
-- and turn into an external table TMP_EXT_USER

create table TMP_EXT_USER
organization external
(type oracle_datapump
default directory ETL_EXT_TABS
location('D_N_USER.TXT'))
as select * from D_N_USER@SQLSVR;


Generate Table layouts using Oracle10g system tables

-- Generate table layouts

SELECT (CASE WHEN COLUMN_ID = 1 THEN 'CREATE TABLE ' || TABLE_NAME || '('
ELSE ',' END) || COLUMN_NAME || ' ' ||
DATA_TYPE ||
(CASE WHEN DATA_TYPE LIKE '%CHAR%' THEN '(' || RTRIM(TO_CHAR(CHAR_LENGTH)) || ')'
WHEN DATA_TYPE = 'NUMBER' AND DATA_PRECISION IS NOT NULL THEN
'(' || RTRIM(TO_CHAR(DATA_PRECISION)) || ',' || DATA_SCALE || ')'
END) ||
(CASE WHEN NULLABLE = 'Y' THEN '' ELSE ' NOT NULL ' END) ||
(CASE WHEN COLUMN_ID = MAX_COLNO THEN ');' ELSE '' END)
FROM ALL_TAB_COLUMNS,
(SELECT TABLE_NAME TBNAME,MAX(COLUMN_ID) MAX_COLNO
FROM ALL_TAB_COLUMNS
WHERE OWNER = 'ORION'
GROUP BY TABLE_NAME) T,
(SELECT TABLE_NAME UTABLE
FROM ALL_TABLES
WHERE OWNER = 'ORION'
AND DROPPED = 'NO'
AND TABLE_NAME NOT IN (SELECT TABLE_NAME FROM ALL_SNAPSHOTS)
AND TABLE_NAME NOT IN (SELECT TABLE_NAME FROM ALL_EXTERNAL_TABLES)) T1
WHERE OWNER = 'ORION'
AND TABLE_NAME = TBNAME
AND TABLE_NAME = UTABLE
ORDER BY TABLE_NAME,COLUMN_ID;


-- GENERATE ALTER PRIMARY KEY

SELECT (CASE WHEN POSITION = MINPO THEN
'ALTER TABLE ' || RTRIM(T0.TABLE_NAME) || ' ADD CONSTRAINT ' || T0.CONSTRAINT_NAME ||
' PRIMARY KEY ('
ELSE ',' END) || COLUMN_NAME ||
(CASE WHEN POSITION = MAXPO THEN ');' ELSE '' END)
FROM ALL_CONS_COLUMNS,
(SELECT CONSTRAINT_NAME,TABLE_NAME
FROM ALL_CONSTRAINTS
WHERE OWNER = 'ORION'
AND CONSTRAINT_TYPE = 'P'
) T0,
(SELECT CONSTRAINT_NAME,MIN(POSITION) MINPO,MAX(POSITION) MAXPO
FROM ALL_CONS_COLUMNS
WHERE OWNER = 'ORION'
AND POSITION IS NOT NULL
GROUP BY CONSTRAINT_NAME
) T1
WHERE OWNER = 'ORION'
AND ALL_CONS_COLUMNS.CONSTRAINT_NAME = T0.CONSTRAINT_NAME
AND T0.CONSTRAINT_NAME = T1.CONSTRAINT_NAME
AND ALL_CONS_COLUMNS.TABLE_NAME NOT IN (SELECT TABLE_NAME FROM ALL_TABLES WHERE DROPPED <> 'NO');

-- GENERATE FOREIGN KEY

SELECT STRSQL
FROM
(
SELECT (CASE WHEN ALL_CONS_COLUMNS.POSITION = R1.MINPO THEN
'ALTER TABLE ' || RTRIM(R0.TABLE_NAME) || ' ADD CONSTRAINT ' || R0.CONSTRAINT_NAME ||
' FOREIGN KEY ('
ELSE ',' END) || ALL_CONS_COLUMNS.COLUMN_NAME ||
(CASE WHEN ALL_CONS_COLUMNS.POSITION = R1.MAXPO THEN ')' ELSE '' END) STRSQL,
ALL_CONS_COLUMNS.TABLE_NAME,R_CONSTRAINT_NAME,0 OID
FROM ALL_CONS_COLUMNS,
(SELECT CONSTRAINT_NAME,TABLE_NAME,R_CONSTRAINT_NAME
FROM ALL_CONSTRAINTS
WHERE OWNER = 'ORION'
AND CONSTRAINT_TYPE = 'R') R0,
(SELECT CONSTRAINT_NAME,MIN(POSITION) MINPO,MAX(POSITION) MAXPO
FROM ALL_CONS_COLUMNS
WHERE OWNER = 'ORION'
AND POSITION IS NOT NULL
GROUP BY CONSTRAINT_NAME) R1
WHERE ALL_CONS_COLUMNS.OWNER = 'ORION'
AND ALL_CONS_COLUMNS.CONSTRAINT_NAME = R0.CONSTRAINT_NAME
AND R0.CONSTRAINT_NAME = R1.CONSTRAINT_NAME
AND ALL_CONS_COLUMNS.TABLE_NAME NOT IN (SELECT TABLE_NAME FROM ALL_TABLES WHERE DROPPED <> 'NO')
UNION ALL
SELECT (CASE WHEN ALL_CONS_COLUMNS.POSITION = P1.MINPO THEN
' REFERENCES ' || RTRIM(P1.PNAME) || ' ('
ELSE ',' END) || ALL_CONS_COLUMNS.COLUMN_NAME ||
(CASE WHEN ALL_CONS_COLUMNS.POSITION = P1.MAXPO THEN ');' ELSE '' END) STRSQL,
ALL_CONS_COLUMNS.TABLE_NAME,P1.CONSTRAINT_NAME,1 OID
FROM (SELECT CONSTRAINT_NAME,TABLE_NAME,R_CONSTRAINT_NAME
FROM ALL_CONSTRAINTS
WHERE OWNER = 'ORION'
AND CONSTRAINT_TYPE = 'R') R0,
(SELECT CONSTRAINT_NAME,TABLE_NAME PNAME,MIN(POSITION) MINPO,MAX(POSITION) MAXPO
FROM ALL_CONS_COLUMNS
WHERE OWNER = 'ORION'
AND POSITION IS NOT NULL
GROUP BY CONSTRAINT_NAME,TABLE_NAME) P1,
ALL_CONS_COLUMNS
WHERE R0.R_CONSTRAINT_NAME = P1.CONSTRAINT_NAME
AND ALL_CONS_COLUMNS.OWNER = 'ORION'
AND ALL_CONS_COLUMNS.CONSTRAINT_NAME = R0.R_CONSTRAINT_NAME
AND ALL_CONS_COLUMNS.TABLE_NAME NOT IN (SELECT TABLE_NAME FROM ALL_TABLES WHERE DROPPED <> 'NO')
) TMP
ORDER BY R_CONSTRAINT_NAME,OID;

-- EASIEST WAY TO GENERATE DOCUMENT OF CURRENT TABLES LAYOUTS

SELECT 'TableName: ' || TABLE_NAME TNAME,'Description: ' || COALESCE(COMMENTS,'') TDESC
FROM ALL_TAB_COMMENTS
WHERE OWNER = 'ORION'
AND TABLE_TYPE = 'TABLE'
AND TABLE_NAME NOT IN (SELECT TABLE_NAME FROM ALL_TABLES WHERE DROPPED <> 'NO')
AND TABLE_NAME NOT IN (SELECT TABLE_NAME FROM ALL_SNAPSHOTS)
AND TABLE_NAME NOT IN (SELECT TABLE_NAME FROM ALL_EXTERNAL_TABLES)
ORDER BY TABLE_NAME;



SELECT * FROM ALL_COL_COMMENTS WHERE OWNER = 'ORION'


SELECT TMP.TABLE_NAME,TMP.COLUMN_NAME,COMMENTS,DATA_TYPE,NULLABLE,
COALESCE(PCTYPE,'N') PK,COALESCE(RCTYPE,'N') FK
FROM
(
SELECT TABLE_NAME,COLUMN_NAME,
DATA_TYPE ||
(CASE WHEN DATA_TYPE LIKE '%CHAR%' THEN '(' || RTRIM(TO_CHAR(CHAR_LENGTH)) || ')'
WHEN DATA_TYPE = 'NUMBER' AND DATA_PRECISION IS NOT NULL THEN
'(' || RTRIM(TO_CHAR(DATA_PRECISION)) || ',' || DATA_SCALE || ')'
END) DATA_TYPE,NULLABLE,COLUMN_ID
FROM ALL_TAB_COLUMNS,
(SELECT TABLE_NAME UTABLE
FROM ALL_TABLES
WHERE OWNER = 'ORION'
AND DROPPED = 'NO'
AND TABLE_NAME NOT IN (SELECT TABLE_NAME FROM ALL_SNAPSHOTS)
AND TABLE_NAME NOT IN (SELECT TABLE_NAME FROM ALL_EXTERNAL_TABLES)) T1
WHERE OWNER = 'ORION'
AND TABLE_NAME = UTABLE
) TMP
LEFT JOIN ALL_COL_COMMENTS ON OWNER = 'ORION'
AND ALL_COL_COMMENTS.TABLE_NAME = TMP.TABLE_NAME
AND ALL_COL_COMMENTS.COLUMN_NAME = TMP.COLUMN_NAME
LEFT JOIN (SELECT ALL_CONSTRAINTS.TABLE_NAME PNAME,'Y' PCTYPE,
COLUMN_NAME PCNAME
FROM ALL_CONSTRAINTS,ALL_CONS_COLUMNS
WHERE ALL_CONSTRAINTS.CONSTRAINT_NAME = ALL_CONS_COLUMNS.CONSTRAINT_NAME
AND ALL_CONSTRAINTS.OWNER = 'ORION'
AND ALL_CONSTRAINTS.CONSTRAINT_TYPE = 'P') P ON P.PNAME = TMP.TABLE_NAME
AND P.PCNAME = TMP.COLUMN_NAME
LEFT JOIN (SELECT ALL_CONSTRAINTS.TABLE_NAME RNAME,'Y' RCTYPE,
COLUMN_NAME RCNAME
FROM ALL_CONSTRAINTS,ALL_CONS_COLUMNS
WHERE ALL_CONSTRAINTS.CONSTRAINT_NAME = ALL_CONS_COLUMNS.CONSTRAINT_NAME
AND ALL_CONSTRAINTS.OWNER = 'ORION'
AND ALL_CONSTRAINTS.CONSTRAINT_TYPE = 'R') R ON R.RNAME = TMP.TABLE_NAME
AND R.RCNAME = TMP.COLUMN_NAME
ORDER BY TMP.TABLE_NAME,TMP.COLUMN_ID;


Generate context of procedures、views、functions using DB2 UDB8.2 system tables

/*
generate context of funtions
*/

SELECT FUNCSCHEMA,FUNCNAME,SPECIFICNAME,FUNCID,TYPE,METHOD,VARCHAR(BODY)
FROM SYSCAT.FUNCTIONS
WHERE FUNCSCHEMA = 'DB2ADMIN'
AND TYPE = 'S'

/*
generate context of views
*/

SELECT VIEWSCHEMA,VIEWNAME,VALID,VARCHAR(TEXT)
FROM SYSCAT.VIEWS
WHERE VIEWSCHEMA = 'DB2ADMIN'

/*
generate context of stored procedures
*/

SELECT ROUTINENAME,VARCHAR(TEXT)
FROM SYSIBM.SYSROUTINES
WHERE ROUTINESCHEMA='DB2ADMIN'

/*
using export to generate procedure code when context is very large
check SPBODY.001 and procedures are in it
*/

EXPORT TO C:\SP.SQL OF DEL LOBS TO C:\ LOBFILE SPBODY MODIFIED BY NOCHARDEL LOBSINFILE
"SELECT TEXT FROM SYSIBM.SYSROUTINES WHERE ROUTINESCHEMA='DB2ADMIN'"


Generate table layouts using DB2 UDB8.2 system tables

/*
The quickest and the easiest way to do reverse engineering is using SQL scripts. For example, SQL Script that generating the latest layouts of tables could be looks like this:
*/

SELECT COLSTR || COALESCE(IDENT_STR,'') FROM
(
SELECT (CASE WHEN COLNO = 0 THEN 'CREATE TABLE ' || TBNAME || '( ' ELSE ',' END) || NAME || ' ' ||
(CASE WHEN COLTYPE = 'TIMESTMP' THEN 'TIMESTAMP' ELSE RTRIM(COLTYPE) END) ||
(CASE WHEN COLTYPE IN ('VARCHAR','CHAR') THEN '(' || RTRIM(CHAR(LENGTH)) || ')'
WHEN COLTYPE = 'DECIMAL' THEN '(' || RTRIM(CHAR(LENGTH)) || ',' || RTRIM(CHAR(SCALE)) || ')'
ELSE ''
END) ||
(CASE WHEN NULLS = 'N' THEN ' NOT NULL ' ELSE '' END) ||
(CASE WHEN COLNO = CNO THEN ') IN USERSPACE1;' ELSE '' END) COLSTR,TBNAME,NAME,COLNO
FROM SYSIBM.SYSCOLUMNS,
(SELECT TBNAME TNAME,MAX(COLNO) CNO
FROM SYSIBM.SYSCOLUMNS
GROUP BY TBNAME) TB,
(SELECT NAME TABLENAME
FROM SYSIBM.SYSTABLES
WHERE CREATOR = 'DB2ADMIN'
AND TYPE = 'T') TB2
WHERE TBNAME = TNAME
AND TBNAME = TABLENAME
) TBTMP
LEFT JOIN (SELECT TABNAME,COLNAME,
' GENERATED ALWAYS AS IDENTITY (START WITH ' || RTRIM(CHAR(INT(START))) ||
' INCREMENT BY ' || RTRIM(CHAR(INT(INCREMENT))) || ', NO CACHE)' IDENT_STR
FROM SYSCAT.COLIDENTATTRIBUTES
WHERE TABSCHEMA = 'DB2ADMIN') TB3 ON TBTMP.TBNAME = TB3.TABNAME
AND TBTMP.NAME = TB3.COLNAME
ORDER BY TBNAME,COLNO;

/*
And you could find any primary keys you've altered or created in SYSIBM.SQLPRIMARYKEYS system table. And SQL statement could be this way:
*/

SELECT (CASE WHEN KEY_SEQ = MINSNO THEN 'ALTER TABLE ' || RTRIM(TABLE_NAME) || ' ADD PRIMARY KEY ('
ELSE ',' END) || COLUMN_NAME ||
(CASE WHEN KEY_SEQ = MAXSNO THEN ');' ELSE '' END)
FROM SYSIBM.SQLPRIMARYKEYS,
(
SELECT PK_NAME PK,TABLE_NAME TNAME,MIN(KEY_SEQ) MINSNO,MAX(KEY_SEQ) MAXSNO
FROM SYSIBM.SQLPRIMARYKEYS
WHERE TABLE_SCHEM = 'DB2ADMIN'
GROUP BY PK_NAME,TABLE_NAME
) T
WHERE TABLE_SCHEM = 'DB2ADMIN'
AND TABLE_NAME = TNAME
AND PK_NAME = PK;