x002548's Blog

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

May 2006 Blog Posts

Generate Fixed Width Format Cards

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)   +...

posted @ Monday, May 15, 2006 11:51 AM | Feedback (1) | Filed Under [ Code Generataion ]

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,...

posted @ Thursday, May 04, 2006 3:08 PM | Feedback (0) | Filed Under [ DB2 ]

Powered by:
Powered By Subtext Powered By ASP.NET