MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Query to get the DDL sentence

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
monaco
Beginner


Joined: 20 May 2004
Posts: 77
Topics: 31

PostPosted: Thu May 12, 2005 9:52 am    Post subject: Query to get the DDL sentence Reply with quote

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
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose

PostPosted: Thu May 12, 2005 10:13 am    Post subject: Reply with quote

Monaco,

The DCLGEN (Generate SQL and source language declarations) gives you the structure of the table with column characterstics.

And the parameters like AUDITing and ENCODING_SCHEME (ebcdic, ascii..) can be found from sysibm.systable catalog table.

Check this link for sysibm.systables column explanation.

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNSQH11/D.73?DT=20010718164132

Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
monaco
Beginner


Joined: 20 May 2004
Posts: 77
Topics: 31

PostPosted: Thu May 12, 2005 10:28 am    Post subject: Reply with quote

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
View user's profile Send private message
bauer
Intermediate


Joined: 10 Oct 2003
Posts: 317
Topics: 50
Location: Germany

PostPosted: Thu May 12, 2005 10:32 am    Post subject: Reply with quote

Hi monaco,

is your request to get the create table statmt. for an existing table from the db2 catlog ?

bauer
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose

PostPosted: Thu May 12, 2005 10:40 am    Post subject: Reply with quote

Monaco,

The information is available in sysibm.syscolumns and you have to tweak the sql to get the desired results. I will TRY to post something in afternoon. Mean while check this link for sysibm.syscolumns

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNSQH11/D.18?DT=20010718164132



Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
bauer
Intermediate


Joined: 10 Oct 2003
Posts: 317
Topics: 50
Location: Germany

PostPosted: Fri May 13, 2005 2:22 am    Post subject: Reply with quote

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
View user's profile Send private message
bauer
Intermediate


Joined: 10 Oct 2003
Posts: 317
Topics: 50
Location: Germany

PostPosted: Fri May 13, 2005 2:25 am    Post subject: Reply with quote

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
View user's profile Send private message
Mervyn
Moderator


Joined: 02 Dec 2002
Posts: 415
Topics: 6
Location: Hove, England

PostPosted: Fri May 13, 2005 3:25 am    Post subject: Reply with quote

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
View user's profile Send private message
bauer
Intermediate


Joined: 10 Oct 2003
Posts: 317
Topics: 50
Location: Germany

PostPosted: Fri May 13, 2005 3:35 am    Post subject: Reply with quote

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
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose

PostPosted: Fri May 13, 2005 6:00 am    Post subject: Reply with quote

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
View user's profile Send private message Send e-mail Visit poster's website
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose

PostPosted: Fri May 13, 2005 11:31 am    Post subject: Reply with quote

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
View user's profile Send private message Send e-mail Visit poster's website
bauer
Intermediate


Joined: 10 Oct 2003
Posts: 317
Topics: 50
Location: Germany

PostPosted: Tue May 17, 2005 12:16 am    Post subject: Reply with quote

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
View user's profile Send private message
monaco
Beginner


Joined: 20 May 2004
Posts: 77
Topics: 31

PostPosted: Wed May 18, 2005 9:40 am    Post subject: Reply with quote

Thanks a lot for your reply.

They are very usefull.

Thanks a lot.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group