Retrieve DDL Stmts through REXX

diwa_thilak · Posted: Tue Oct 23, 2007 1:05 pm

Hi Friends,

I need to retrieve the DDL statements of the tables. Is it possible to retrieve the DDL statements ?

1. How to retrieve the DDL statements ? any table holds the statements ?

Any suggestions ?

enrico-sorichetti · Posted: Tue Oct 23, 2007 1:14 pm

Not directly...

You can rebuild the ddl incrementally navigating a few tables

for the basic create table You can retrieve the info
from SYSIBM.SYSCOLUMNS
and SYSIBM.SYSINDEXES

I did it once and it worked, I am afraid that the REXX script has gone..
I would have gladly posted it !

diwa_thilak · Posted: Fri Oct 26, 2007 9:53 am

Thanks Buddy.

You have any idea, where the DDL statements were retireved for a given table( when we access platinum(tool) to retrieve DDL stmts) ?

Is it taken care by DBA's to specify location for the DDL ?

My current requirements is ,

I have around 800 tables and i need to take a personnel copy of these tabes under my qualifier in my database ?

So My tool has to accept the highlevel qualifier of prodcution table name, my database name and the qualifier.

It needs to create jobs for this unload and load process?

You got any suggestion about how to approach this problem ?

enrico-sorichetti · Posted: Fri Oct 26, 2007 12:17 pm

diwa_thilak · Posted: Fri Oct 26, 2007 12:39 pm

Yeah Buddy you are rt.

But DDL's have been built upon the qualifier, so my job becomes double.

I need to extract the DDL's , write a rexx to convert the qualifiers to mine and submit the same.

Is there anyother different option/view i can approach this problem?

enrico-sorichetti · Posted: Fri Oct 26, 2007 12:46 pm

You mean that the dummy DBA' s of Your organization

wrote ...
CREATE TABLE(useless_qualifier.TABLE_NAME)
everywere

it' s hard to believe!!

How do they manage development, system_test and production ???

anyway the easiest thing is to put all the ddl' s in a single pds and then
1) change the useless_qualifier to nothing,
2) change it to Your userid.

Number 1 would be the best choice
You would get the result You need,
You would teach the dummies how to do things the proper way

diwa_thilak · Posted: Fri Oct 26, 2007 12:52 pm

Yeah buddy.

I am going by your way, Now extracting DDL's manually becomes a problem.

Any automation we can do ?

i have a routine to change te the qualifiers and prepare unload/load jobs.

May be we two can automate this one and release the code in our forum.

kgumraj2 · New User Joined: 01 Aug 2007 Posts: 42 Location: Hyderabad

Hi,
If you want only the DDL please use DCLGEN.