x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

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

;

Print | posted on Thursday, May 04, 2006 3:08 PM | Filed Under [ DB2 ]

Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET