CREATE PROC isp_GenFormatCards
AS
DECLARE FormatCard CURSOR FOR
SELECT FORMAT_CARD, TABLE_NAME, TABLE_SCHEMA FROM (
/*
SELECT '--' + TABLE_NAME AS FORMAT_CARD
, TABLE_NAME, null AS COLUMN_NAME, 0 AS SQLGroup, 1 AS RowGrouping
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_TYPE = 'BASE TABLE'
UNION ALL
*/
SELECT '7.0' AS FORMAT_CARD
, TABLE_NAME, TABLE_SCHEMA, null AS COLUMN_NAME, 1 AS SQLGroup, 1 AS RowGrouping
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_TYPE = 'BASE TABLE'
UNION ALL
SELECT CONVERT(varchar(5),MAX(ORDINAL_POSITION)) AS FORMAT_CARD
, c.TABLE_NAME, c.TABLE_SCHEMA, null AS COLUMN_NAME, 2 AS SQLGroup, 1 AS RowGrouping
FROM INFORMATION_SCHEMA.Columns c
INNER JOIN INFORMATION_SCHEMA.Tables t
ON c.TABLE_NAME = t.TABLE_NAME
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
AND TABLE_TYPE = 'BASE TABLE'
GROUP BY c.TABLE_NAME, c.TABLE_SCHEMA
UNION ALL
SELECT CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+'SQLCHAR'+CHAR(9)+'0'+CHAR(9)
+...
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,...