View previous topic :: View next topic |
Author |
Message |
monaco Beginner
Joined: 20 May 2004 Posts: 77 Topics: 31
|
Posted: Thu May 12, 2005 9:52 am Post subject: Query to get the DDL sentence |
|
|
Hi all,
Could someone provide me a the query for getting the DDL sentence of a simple table?
What i'm looking for is the sentence to the DB2 catalog (v7) where the result is for example:
Code: |
CREATE TABLE AISD3E5.KTCTGET
(CDTABLA CHAR(8) NOT NULL WITH DEFAULT,
CDEMPRES CHAR(4) NOT NULL WITH DEFAULT,
CDIDIOMA CHAR(2) NOT NULL WITH DEFAULT,
CDELEMEN CHAR(30) NOT NULL WITH DEFAULT,
DSELEMEN CHAR(254) NOT NULL WITH DEFAULT,
CDUSUARI CHAR(10) NOT NULL WITH DEFAULT,
CDEMPUSU CHAR(4) NOT NULL WITH DEFAULT,
NOPROGRA CHAR(8) NOT NULL WITH DEFAULT,
TSULTMOD TIMESTAMP NOT NULL WITH DEFAULT)
IN BDATC3E5.TSATCTGE
AUDIT NONE
CCSID EBCDIC
;
|
Thanks. |
|
Back to top |
|
 |
kolusu Site Admin

Joined: 26 Nov 2002 Posts: 12378 Topics: 75 Location: San Jose
|
|
Back to top |
|
 |
monaco Beginner
Joined: 20 May 2004 Posts: 77 Topics: 31
|
Posted: Thu May 12, 2005 10:28 am Post subject: |
|
|
Hi,
we'd like to create an automatic process for getting the DDL. Imagine a pannel asking you the table name and the output is the DDL.
I thought there is a select (join of few tables) and just with the table name you can get the DDL sentences.
Kind regards. |
|
Back to top |
|
 |
bauer Intermediate
Joined: 10 Oct 2003 Posts: 317 Topics: 50 Location: Germany
|
Posted: Thu May 12, 2005 10:32 am Post subject: |
|
|
Hi monaco,
is your request to get the create table statmt. for an existing table from the db2 catlog ?
bauer |
|
Back to top |
|
 |
kolusu Site Admin

Joined: 26 Nov 2002 Posts: 12378 Topics: 75 Location: San Jose
|
|
Back to top |
|
 |
bauer Intermediate
Joined: 10 Oct 2003 Posts: 317 Topics: 50 Location: Germany
|
Posted: Fri May 13, 2005 2:22 am Post subject: |
|
|
Hi monaco,
my understanding is: Create DDL for table creation by Selectstatmnt. from DB2 Catalog, correct ?
So the general solution looks like this, see below.
But this is only the general idea, not all things are coded in my SQL select. See syntax diagram for CREATE TABLE stmnt. in the IBM documentation.
Code: |
SELECT -1 AS SORT
, 'CREATE TABLE '
|| RTRIM('MYOWNER')
|| '.'
|| 'MYTABLE (' AS LINE
FROM SYSIBM.SYSDUMMY1
UNION
SELECT COL.COLNO AS SORT
,
' '
|| SUBSTR(COL.NAME || ' ',1,8)
||
SUBSTR(
CASE WHEN COL.COLTYPE = 'CHAR'
THEN 'CHAR('
|| DIGITS(COL.LENGTH)
|| ')'
ELSE COL.COLTYPE
END || ' '
,1,20)
|| ' '
|| CASE WHEN COL.NULLS = 'N'
THEN 'NOT NULL WITH DEFAULT'
ELSE ''
END
AS LINE
FROM SYSIBM.SYSTABLES TAB
INNER JOIN SYSIBM.SYSCOLUMNS COL
ON TAB.NAME = COL.TBNAME
AND TAB.CREATOR = COL.TBCREATOR
WHERE TAB.NAME = 'MYTABLE'
AND TAB.TYPE = 'T'
AND TAB.CREATOR = 'MYOWNER'
AND COL.COLNO = 1
UNION
SELECT COL.COLNO AS SORT
,
','
|| SUBSTR(COL.NAME || ' ',1,8)
||
SUBSTR(
CASE WHEN COL.COLTYPE = 'CHAR'
THEN 'CHAR('
|| DIGITS(COL.LENGTH)
|| ')'
ELSE COL.COLTYPE
END || ' '
,1,20)
|| ' '
|| CASE WHEN COL.NULLS = 'N'
THEN 'NOT NULL WITH DEFAULT'
ELSE ''
END
AS LINE
FROM SYSIBM.SYSTABLES TAB
INNER JOIN SYSIBM.SYSCOLUMNS COL
ON TAB.NAME = COL.TBNAME
AND TAB.CREATOR = COL.TBCREATOR
WHERE TAB.NAME = 'MYTABLE'
AND TAB.TYPE = 'T'
AND TAB.CREATOR = 'MYOWNER'
AND COL.COLNO <> 1
UNION
SELECT +99999 AS SORT
,' ) IN '
|| RTRIM(TAB.DBNAME) || '.' || RTRIM(TAB.TSNAME)
|| ';'
AS LINE
FROM SYSIBM.SYSTABLES TAB
WHERE NAME = 'MYTABLE'
AND CREATOR = 'MYOWNER'
AND TYPE = 'T'
ORDER BY SORT
;
|
regards,
bauer |
|
Back to top |
|
 |
bauer Intermediate
Joined: 10 Oct 2003 Posts: 317 Topics: 50 Location: Germany
|
Posted: Fri May 13, 2005 2:25 am Post subject: |
|
|
Hi,
how is it possible to post code in this forum ?
I pasted the sql from the clipboard, the result are similys in the sql, not sure, wether the sql is complete ?!
regards,
bauer |
|
Back to top |
|
 |
Mervyn Moderator

Joined: 02 Dec 2002 Posts: 415 Topics: 6 Location: Hove, England
|
Posted: Fri May 13, 2005 3:25 am Post subject: |
|
|
Bauer,
You need to insert [ code] at the beginning, and [ /code] at the end (without the space after the opening bracket). _________________ The day you stop learning the dinosaur becomes extinct |
|
Back to top |
|
 |
bauer Intermediate
Joined: 10 Oct 2003 Posts: 317 Topics: 50 Location: Germany
|
Posted: Fri May 13, 2005 3:35 am Post subject: |
|
|
Hi *,
now in a better readable way .......
Code: |
SELECT -1 AS SORT
, 'CREATE TABLE '
|| RTRIM('MYOWNER')
|| '.'
|| 'MYTABLE (' AS LINE
FROM SYSIBM.SYSDUMMY1
UNION
SELECT COL.COLNO AS SORT
,
' '
|| SUBSTR(COL.NAME || ' ',1,8)
||
SUBSTR(
CASE WHEN COL.COLTYPE = 'CHAR'
THEN 'CHAR('
|| DIGITS(COL.LENGTH)
|| ')'
ELSE COL.COLTYPE
END || ' '
,1,20)
|| ' '
|| CASE WHEN COL.NULLS = 'N'
THEN 'NOT NULL WITH DEFAULT'
ELSE ''
END
AS LINE
FROM SYSIBM.SYSTABLES TAB
INNER JOIN SYSIBM.SYSCOLUMNS COL
ON TAB.NAME = COL.TBNAME
AND TAB.CREATOR = COL.TBCREATOR
WHERE TAB.NAME = 'MYTABLE'
AND TAB.TYPE = 'T'
AND TAB.CREATOR = 'MYOWNER'
AND COL.COLNO = 1
UNION
SELECT COL.COLNO AS SORT
,
','
|| SUBSTR(COL.NAME || ' ',1,8)
||
SUBSTR(
CASE WHEN COL.COLTYPE = 'CHAR'
THEN 'CHAR('
|| DIGITS(COL.LENGTH)
|| ')'
ELSE COL.COLTYPE
END || ' '
,1,20)
|| ' '
|| CASE WHEN COL.NULLS = 'N'
THEN 'NOT NULL WITH DEFAULT'
ELSE ''
END
AS LINE
FROM SYSIBM.SYSTABLES TAB
INNER JOIN SYSIBM.SYSCOLUMNS COL
ON TAB.NAME = COL.TBNAME
AND TAB.CREATOR = COL.TBCREATOR
WHERE TAB.NAME = 'MYTABLE'
AND TAB.TYPE = 'T'
AND TAB.CREATOR = 'MYOWNER'
AND COL.COLNO <> 1
UNION
SELECT +99999 AS SORT
,' ) IN '
|| RTRIM(TAB.DBNAME) || '.' || RTRIM(TAB.TSNAME)
|| ';'
AS LINE
FROM SYSIBM.SYSTABLES TAB
WHERE NAME = 'MYTABLE'
AND CREATOR = 'MYOWNER'
AND TYPE = 'T'
ORDER BY SORT
;
|
|
|
Back to top |
|
 |
kolusu Site Admin

Joined: 26 Nov 2002 Posts: 12378 Topics: 75 Location: San Jose
|
Posted: Fri May 13, 2005 6:00 am Post subject: |
|
|
bauer,
That is an excellent query, however you need some corrections. Especially the COLTYPE and LENGTH columns.
EX: date, time, timestamp columns will have 4,3,10 bytes in length which is db2's internal reprenstation. However they don't hold good in a DDL.
Also for a decimal column you will need the SCALE which is the decimal portion of the column.
ex: a Column defined as DECIMAL (9,2) will have 9 in the length column and 2 in the scale column.
Also you will have to take into account the DEFAULTVALUE Column for some of the columns defined with a default value.
Kolusu _________________ Kolusu
www.linkedin.com/in/kolusu |
|
Back to top |
|
 |
kolusu Site Admin

Joined: 26 Nov 2002 Posts: 12378 Topics: 75 Location: San Jose
|
Posted: Fri May 13, 2005 11:31 am Post subject: |
|
|
Monaco,
The following Sql will just give you the column definitions of the table.
Code: |
SELECT (CASE COLNO
WHEN 1 THEN CHAR('(') ELSE CHAR(',') END)
,CHAR(NAME)
,CHAR(' ' )
,(CASE COLTYPE WHEN 'CHAR' THEN CHAR('CHARACTER')
WHEN 'TIMESTMP' THEN CHAR('TIMESTAMP')
ELSE CHAR(COLTYPE)
END)
,(CASE WHEN COLTYPE = 'SMALLINT' THEN CHAR(' ')
WHEN COLTYPE = 'INTEGER' THEN CHAR(' ')
WHEN COLTYPE = 'DATE' THEN CHAR(' ')
WHEN COLTYPE = 'TIME' THEN CHAR(' ')
WHEN COLTYPE = 'TIMESTMP' THEN CHAR(' ')
WHEN COLTYPE = 'FLOAT' THEN CHAR(' ')
WHEN COLTYPE = 'DECIMAL' THEN CHAR('(') CONCAT
DIGITS(LENGTH) CONCAT
CHAR(',') CONCAT
DIGITS(SCALE) CONCAT
CHAR(')')
ELSE CHAR('(') CONCAT
DIGITS(LENGTH) CONCAT
CHAR(')')
END)
,(CASE NULLS WHEN 'N' THEN CHAR(' NOT NULL ')
ELSE CHAR(' ') END)
,(CASE WHEN DEFAULT = 'Y' THEN CHAR(' WITH DEFAULT ')
WHEN DEFAULT = '1' THEN CHAR(' WITH DEFAULT ') CONCAT
CHAR('''') CONCAT
CHAR(DEFAULTVALUE,26) CONCAT
CHAR('''')
ELSE CHAR(' ')
END)
FROM SYSIBM.SYSCOLUMNS
WHERE TBNAME = 'KTCTGET'
AND TBCREATOR = 'AISD3E5'
ORDER BY COLNO
;
|
Hope this helps....
Cheers
Kolusu _________________ Kolusu
www.linkedin.com/in/kolusu |
|
Back to top |
|
 |
bauer Intermediate
Joined: 10 Oct 2003 Posts: 317 Topics: 50 Location: Germany
|
Posted: Tue May 17, 2005 12:16 am Post subject: |
|
|
Hi kolusu,
thank you very much for your reply.
Yes, of course, you are right. My SQL proposel was incomplete, just the general idea ......
Thank you for your update.
regards,
bauer |
|
Back to top |
|
 |
monaco Beginner
Joined: 20 May 2004 Posts: 77 Topics: 31
|
Posted: Wed May 18, 2005 9:40 am Post subject: |
|
|
Thanks a lot for your reply.
They are very usefull.
Thanks a lot. |
|
Back to top |
|
 |
|
|