Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2007
    Posts
    50

    Question Unanswered: Get information about a table

    Is there any easy straight-forward way of getting information about an object on DB2 (especially a table)?
    Something like the sp_help sp in Ms SQL?
    Is querying the system tables the only way?
    If so, what are the tables? Does anybody have some queries I can use?

    Cheers,
    Daniel

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    What's wrong with the DB2 catalog views? Other tables/views wouldn't make much sense because you would loose information (compared to the catalog). And if you only need some specific information, you can query only the interesting parts.

    There are a few tools that format the catalog information in different ways:
    • db2look - it extracts catalog information and generates a LaTeX script for pretty-printing or a SQL-script
    • db2 describe table <tbname> - it gives a short summary of the columns in the table
    • db2 describe <query> - since everything is a table in SQL, so are query results; you can get a description of the query result
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Sep 2007
    Posts
    50
    Thanks stolze.
    However, I forgot to mention that I am not an as400 user, I only access it through iSeries navigator using SQL commands.
    Is there any way to achieve this in SQL, besides querying the system tables?
    which I guess would be something like:

    select
    *
    from
    syscolumns
    where
    table_schema = 'MYSCHEMA' and
    table_name = 'MYTABLE';

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You didn't mention at all that you are working on iSeries. My answer was for DB2 LUW, actually.

    However: what's wrong with the system catalog tables? All other tools just query those catalog tables anyway. This is the central place to get schema information. That's the whole purpose of the catalog.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Sep 2007
    Posts
    50
    Thanks. Seems to do the job.
    can you point me to more documentation on the system catalog by any chance?

    Cheers,
    Dan

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Not sure about DB2 for iSeries, but it is usually in the appendecies of the SQL Reference manual.
    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
  •