How to fetch DDL for a particular table



IBM's flagship relational database management system

How to fetch DDL for a particular table

Postby prabu_krish » Fri May 27, 2011 8:22 pm

In DB2 how can i view DDL for particular table?
Thanks in advance!!
prabu_krish
 
Posts: 7
Joined: Tue May 24, 2011 12:10 pm
Has thanked: 0 time
Been thanked: 0 time

Re: How to fetch DDL for a particular table

Postby Akatsukami » Fri May 27, 2011 8:55 pm

prabu_krish wrote:In DB2 how can i view DDL for particular table?
Thanks in advance!!

You cannot view the DDL per se, as it is not kept. However, the information contained in the DDL is maintained in the catalog tables; e.g., to get information about the columns of a table, select from SYSIBM.SYSTABLES.
"You have sat too long for any good you have been doing lately ... Depart, I say; and let us have done with you. In the name of God, go!" -- what I say to a junior programmer at least once a day
User avatar
Akatsukami
Global moderator
 
Posts: 1058
Joined: Sat Oct 16, 2010 2:31 am
Location: Bloomington, IL
Has thanked: 6 times
Been thanked: 51 times

Re: How to fetch DDL for a particular table

Postby NicC » Fri May 27, 2011 9:14 pm

All place I have worked keep their DDL in an application PDS so ask someone in that applications support/development team.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

Re: How to fetch DDL for a particular table

Postby Steve Coalbran » Thu Jun 02, 2011 2:51 pm

Hi Nic!
Yes, storing the DDL in a PDS so you can Library Manage it is a damned good idea? :idea:
Anyhow, (and as a confirmed true-blue biggot I hate to say this but…)...
If you have Platinum’s “RC/Query” installed (it's within CA Database Management Solutions for DB2 for z/OS”), as I do on the Customer’s site that I am using at the moment, then you can get the DDL out using command “DDL” against a TABLE (or an INDEX or TABLESPACE).
RQTL   R14    --------------- RC/Q Table List --------------- 06-02-11   10:28
COMMAND ===>                                                  SCROLL ===> PAGE
                                                                               
 DB2 Object ===> T                       Option  ===> L    Where => N         
 Table Name ===> SAMPTABL             >  Creator ===> *                     > 
  Qualifier ===> *                    >  Grantor ===> *                     > 
Loc: LOCAL ---------- SSID: DB2A ----------XSTECOA -          LINE 1 OF 2    >
CMD      TABLE NAME          CREATOR   DATABASE  TBLSPACE       NUMBER OF ROWS
DDL_____ SAMPTABL            ACPT      PIDANORT  PISSNX45              157,785
________ SAMPTABL            PROD      PIDSNORT  PISSNX45              159,475
******************************* BOTTOM OF DATA ********************************


And this might look like this…
PTBPDI R14    -------- Batch Processor Display Input  -------- 06-02-11   10:39
COMMAND ===>                                                  SCROLL ===> PAGE 
                                                                               
                                                         Mode  ===> O ONLINE   
                                                                               
  The following Batch Processor input has been generated.  Press ENTER         
  to process, press END to cancel, or enter the EDIT command to edit.           
------------------------------------------------------------------------- XSTEC
                                                                               
 ********************************* TOP OF DATA *********************************
                                                                               
.CONNECT DB2A                                                                   
                                                                               
 SET CURRENT SQLID = 'ACPT';                                                   
                                                                               
 CREATE TABLE ACPT.SAMPTABL                                                   
        (DIVNUM                            INTEGER        NOT NULL             
  WITH DEFAULT                                                                 
        ,CONID                             CHARACTER(30)  FOR SBCS DATA         
                                                          NOT NULL             
  WITH DEFAULT                                                                 
        ,ALTERNATE_ID                      CHARACTER(30)  FOR SBCS DATA         
                                                          NOT NULL             
  WITH DEFAULT                                                                 
        ,ALTERNATE_ID_TYPE                 CHARACTER(50)  FOR SBCS DATA         
                                                          NOT NULL             
  WITH DEFAULT                                                                 
        ,BACKID                            CHARACTER(30)  FOR SBCS DATA         
                                                          NOT NULL             
  WITH DEFAULT                                                                 
 Press ENTER to process                            Press END (PF3/15) to quit
  *PTPBPDI   

I don’t know if any of the IBM Tools do this – thinking FileManager for DB2? :/

On the other hand one could do it by-hand and execute a DESCRIBE on a table and get a lot of the info that way?
(this is from memory so do an RTFM first? :geek: )…
In REXX…
. . .
PARSE VALUE 0 WITH . col. 1 col.0 typ. 1 typ.0 . sqlda.
table = 'SAMPTABL'
ADDRESS DSNREXX "EXECSQL DESCRIBE TABLE :TABLE INTO :SQLDA"
DO i = 1 TO sqlda.sqld                                   
   col.i = sqlda.i.sqlname                                 
   typ.i = sqlda.i.sqltype                                 
END
PARSE VALUE i-1 WITH col.0 1 typ.0
. . .
Steve
User avatar
Steve Coalbran
 
Posts: 138
Joined: Wed Apr 06, 2011 11:49 am
Location: Stockholm, Sweden
Has thanked: 13 times
Been thanked: 1 time

Re: How to fetch DDL for a particular table

Postby prabu_krish » Mon Oct 03, 2011 5:36 pm

Sorry for the late response..
Thanks all, for the valuable comments..:)

As steve said i used CA-DB2 tools to retrive the DDL information...On the other hand i used DCLGEN generator using DB2I..Both works fine..
prabu_krish
 
Posts: 7
Joined: Tue May 24, 2011 12:10 pm
Has thanked: 0 time
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post