Generate History Tables in DB2 From the System Catalog
I'm currently setting up a process to audit changes to the database in DB2 OS/390. I have done this in the past with SQL Server (which was easier) but I know have to do it for this platform. Basically, for an UPDATE or a DELETE DML operation a trigger will fire and insert the rows into this table. The following SQL will genertae table creates for each table in a database. Just change the Creator for which ever database you want to audit.
SET CURRENT SQLID = 'BXHRSPDA';
SELECT SQL FROM (
SELECT NAME AS TABLE_NAME,1 AS ROWORDER, 1 AS COLORDER,
'CREATE TABLE H_'||SUBSTR(NAME,1,16)||' (' AS SQL
FROM SYSIBM.SYSTABLES
WHERE CREATOR = 'AXHRSPDA'
UNION ALL
SELECT TBNAME AS TABLE_NAME, 2 AS ROWORDER, 1 AS COLORDER,
' '||NAME||SPACE(19-LENGTH(NAME))
||CASE WHEN COLTYPE = 'CHAR'
THEN RTRIM(COLTYPE)||'('||RTRIM(CHAR(LENGTH))||')'
WHEN COLTYPE = 'VARCHAR'
THEN RTRIM(COLTYPE)||'('||RTRIM(CHAR(LENGTH))||')'
ELSE COLTYPE
END
||SPACE(20-LENGTH(
CASE WHEN COLTYPE = 'CHAR'
THEN RTRIM(COLTYPE)||'('||RTRIM(CHAR(LENGTH))||')'
WHEN COLTYPE = 'VARCHAR'
THEN RTRIM(COLTYPE)||'('||RTRIM(CHAR(LENGTH))||')'
ELSE COLTYPE
END))
||CASE WHEN NULLS = 'Y' THEN ' NULL'
WHEN NULLS = 'N' THEN ' NOT NULL'
ELSE '"'||COALESCE(NULLS,' ')||'"'
END
AS SQL
FROM SYSIBM.SYSCOLUMNS
WHERE TBCREATOR = 'AXHRSPDA'
AND COLNO = 1
UNION ALL
SELECT TBNAME AS TABLE_NAME, 2 AS ROWORDER, COLNO AS COLORDER,
' , '||NAME||SPACE(19-LENGTH(NAME))
||CASE WHEN COLTYPE = 'CHAR'
THEN RTRIM(COLTYPE)||'('||RTRIM(CHAR(LENGTH))||')'
WHEN COLTYPE = 'VARCHAR'
THEN RTRIM(COLTYPE)||'('||RTRIM(CHAR(LENGTH))||')'
ELSE COLTYPE
END
||SPACE(20-LENGTH(
CASE WHEN COLTYPE = 'CHAR'
THEN RTRIM(COLTYPE)||'('||RTRIM(CHAR(LENGTH))||')'
WHEN COLTYPE = 'VARCHAR'
THEN RTRIM(COLTYPE)||'('||RTRIM(CHAR(LENGTH))||')'
ELSE COLTYPE
END))
||CASE WHEN NULLS = 'Y' THEN ' NULL'
WHEN NULLS = 'N' THEN ' NOT NULL'
ELSE '"'||COALESCE(NULLS,' ')||'"'
END
AS SQL
FROM SYSIBM.SYSCOLUMNS
WHERE TBCREATOR = 'AXHRSPDA'
AND COLNO <> 1
UNION ALL
SELECT NAME AS TABLE_NAME,3 AS ROWORDER, 1 AS COLORDER,
' , HIST_ADD_TS TIMESTAMP NOT NULL DEFAULT'
AS SQL
FROM SYSIBM.SYSTABLES
WHERE CREATOR = 'AXHRSPDA'
UNION ALL
SELECT NAME AS TABLE_NAME,4 AS ROWORDER, 1 AS COLORDER,
' , HIST_ADD_BY CHAR(12) NOT NULL DEFAULT'
AS SQL
FROM SYSIBM.SYSTABLES
WHERE CREATOR = 'AXHRSPDA'
UNION ALL
SELECT NAME AS TABLE_NAME,5 AS ROWORDER, 1 AS COLORDER,
' , HIST_ADD_TYPE CHAR(1) NOT NULL DEFAULT'
AS SQL
FROM SYSIBM.SYSTABLES
WHERE CREATOR = 'AXHRSPDA'
UNION ALL
SELECT NAME AS TABLE_NAME, 99 AS ROWORDER, 1 AS COLORDER,
');'
AS SQL
FROM SYSIBM.SYSTABLES
WHERE CREATOR = 'AXHRSPDA'
) AS XXX
ORDER BY TABLE_NAME, ROWORDER, COLORDER
;