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;
