Hi friends,

Is there a way of getting a table DDL from syscat.tables??I know we can get storedprocedure,view,function,trigger DDL from syscat views.I am wondering if there is a way of getting DDL of a table or index from syscat


thanks

Have a look at the "db2look" utility.

Yeah db2look can be used to get DDL..I was wondering if we can get the DDL from syscat.tables or systat.tables views for tables and indexes.


Thanks

No, the text to create tables and indexes is not stored in the DB2 catalog views. I believe it is there for triggers, views, and routines because DB2 may have to re-process those objects at various points - but that's not the case for tables and indexes.

Thanks for the information Stolze.I was wondering how the thirdy party vendors like quest get that DDL for each table and each index separately..May be they use db2look?? Any ideas

All the information is in the DB2 catalog. You'd just have to piece it together - the same way db2look does it. I would assume that other vendors go against the catalog as well.

Thanks for the information Stolze

The DB2 Control Center will show you the table DDL for an individual table. If you hit the "Show SQL" button that shows how the CC got the DDL, you will see it uses db2look command using the opiton to extract DDL for the one table.

If you want to see the columns in a table (not necessarily the DDL), you can issue this command:

db2 describe table <table-name> show detail (show detail is optional)

db2 describe indexes for table <table-name> show detail (show detail is optional)