Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2008
    Posts
    45

    Unanswered: CREATE TABLE DDL from system catalog

    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

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Have a look at the "db2look" utility.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Jan 2008
    Posts
    45
    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

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Jan 2008
    Posts
    45
    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

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Jan 2008
    Posts
    45
    Thanks for the information Stolze

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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)
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •