Identity, Initial Value, Increment data in Catalog Views

Identity, Initial Value, Increment data in Catalog Views

Post by John » Wed, 25 Jun 2003 04:14:39



Hi,

I am trying to reconstruct a CREATE TABLE SQL statment for a TABLE and
can't seem to match up the SYSCAT.SEQUENCES data and the
SYSCAT.COLUMNS data.

Is there a column which I can join on so that I can match up the
Identity information with the table information? Or is there any other
way to match the Identity information with the table or columns?

thanks
-John

 
 
 

Identity, Initial Value, Increment data in Catalog Views

Post by Paul Verno » Wed, 25 Jun 2003 18:02:23



Quote:> Hi,

> I am trying to reconstruct a CREATE TABLE SQL statment for a TABLE and
> can't seem to match up the SYSCAT.SEQUENCES data and the
> SYSCAT.COLUMNS data.

> Is there a column which I can join on so that I can match up the
> Identity information with the table information? Or is there any other
> way to match the Identity information with the table or columns?

> thanks
> -John

I used create_time when I coded the following view

-- View to gen column DDL from the catalg
CREATE VIEW DDL.COLUMNS AS
SELECT
 C.TABSCHEMA
, C.TABNAME
, C.COLNAME
, C.COLNO
, CASE
 WHEN TYPENAME = 'CHARACTER'
 THEN 'CHAR(' || RTRIM(CHAR(LENGTH)) || ')'
 WHEN TYPENAME IN ( 'VARCHAR', 'LONG VARCHAR','BLOB', 'CLOB', 'DBCLOB',
'GRAPHIC', 'VARGRAPHIC')
 THEN TYPENAME || '(' || RTRIM(CHAR(LENGTH)) || ')'
 WHEN TYPENAME IN ('DECIMAL')
 THEN 'DECIMAL(' || RTRIM(CHAR(LENGTH)) || ',' || RTRIM(CHAR(SCALE)) || ')'
 ELSE TYPENAME
 END
 || CASE WHEN TYPENAME IN ('CHARACTER', 'VARCHAR', 'LONG VARCHAR') AND
CODEPAGE = 0 THEN ' FOR BIT DATA' ELSE '' END
 || CASE WHEN NULLS = 'N' THEN ' NOT NULL' ELSE '' END
 || CASE WHEN "DEFAULT" IS NOT NULL THEN' WITH DEFAULT ' || "DEFAULT" ELSE ''
END
 || CASE WHEN LOGGED = 'N' THEN ' NOT LOGGED' ELSE '' END
 || CASE WHEN COMPACT = 'Y' THEN ' COMPACT' ELSE '' END
 || CASE GENERATED WHEN 'A' THEN ' GENERATED ALWAYS '
   WHEN 'D' THEN  ' GENERATED BY DEFAULT ' ELSE '' END
 || COALESCE(VARCHAR(TEXT,3000),'')
 || CASE WHEN IDENTITY = 'Y' THEN 'AS IDENTITY'
  || CASE WHEN S.CREATE_TIME IS NOT NULL THEN
   ' ( START WITH ' || CHAR(START) || ', INCREMENT BY ' || CHAR(INCREMENT)
  || ', CACHE ' || CHAR(CACHE)
  || ')' ELSE '' END
 ELSE '' END
  AS DDL
FROM
 SYSCAT.COLUMNS C
INNER JOIN
 SYSCAT.TABLES T
ON
 T.TABSCHEMA = C.TABSCHEMA
AND T.TABNAME = C.TABNAME
AND T.TYPE  = 'T'
LEFT OUTER JOIN
 SYSCAT.SEQUENCES S
ON
 T.CREATE_TIME = S.CREATE_TIME
;

Regards
Paul Vernon
Business Intelligence, IBM Global Services