In DB2 how can i view DDL for particular table?
Thanks in advance!!
How to fetch DDL for a particular table
Previous topic • Next topic • 5 posts
• Page 1 of 1
How to fetch DDL for a particular table
by prabu_krish » Fri May 27, 2011 8:22 pm
- 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
by 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
-
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
by 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
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
by 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?
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).
And this might look like this…
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?
)…
In REXX…
Yes, storing the DDL in a PDS so you can Library Manage it is a damned good 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 ********************************
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
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?
)…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
. . .
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
-
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
by 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..
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
Previous topic • Next topic • 5 posts
• Page 1 of 1
-
- Related topics
- Replies
- Views
- Last post
-
- Converting multi table tablespaces into uni table tablespace
by suresh_48 » Mon Aug 15, 2011 7:06 pm - 2 Replies
- 2968 Views
- Last post by suresh48
Tue Aug 16, 2011 12:51 pm
- Converting multi table tablespaces into uni table tablespace
-
- How can u Fetch first row Only ?
by ragur.satheesh » Mon Dec 13, 2010 12:18 pm - 5 Replies
- 2739 Views
- Last post by chandrababun
Wed Dec 29, 2010 3:54 pm
- How can u Fetch first row Only ?
-
- FETCHING ROWS FROM ONE TABLE TO OTHER TABLE USING CURSORS
by avvarycobol » Wed Mar 23, 2011 8:05 pm - 7 Replies
- 4003 Views
- Last post by The Butcher
Sun Mar 27, 2011 8:36 pm
- FETCHING ROWS FROM ONE TABLE TO OTHER TABLE USING CURSORS
-
- First rows in Table A- process and write it to table
by raulravi » Thu Dec 13, 2012 4:48 pm - 6 Replies
- 2803 Views
- Last post by Akatsukami
Tue Dec 18, 2012 4:45 pm
- First rows in Table A- process and write it to table
-
- Fetch with UNION ALL
by rajas.abcdefg » Sun Jul 26, 2009 4:48 am - 9 Replies
- 10261 Views
- Last post by BChat
Mon Jul 27, 2009 10:14 pm
- Fetch with UNION ALL