In DB2 Display a table's definition

Question

Hello everybody I am learning DB2 and would like to know how to see a table's characteristics after I create one.

Similar to the EXPLAIN TABLE command in MySQL.

Thank you.

Answer 1

All that metadata is held in the DB2 catalog tables in the SYSIBM 'schema'. It varies for the DB2/z mainframe product and the DB2/LUW distributed product but they're coming closer and closer with each release.

IBM conveniently place all their manuals up on the publib site for the world to access. My area of expertise, DB2/z, has the pages you want here.

There are a number of tables there that you'll need to reference:

SYSTABLES        for table information.
SYSINDEXES    \
SYSINDEXPART   + for index information.
SYSKEYS       /
SYSCOLUMNS       for column information.

The list of all information centers is here which should point you to the DB2/LUW version if that's your area of interest.

Answer 2

db2 describe table

or

select * from syscat.tables

or

select * from sysibm.tables

Answer 3

In addition to DESCRIBE TABLE, you can use DESCRIBE INDEXES FOR TABLE tablename SHOW DETAIL to get information about the table's indexes.

The most comprehensive detail about a table on DB2 for Linux, UNIX, and Windows can be obtained from the db2look utility, which you can run from a remote client or directly on the DB2 server as a local user. The tool produces the DDL and other information necessary to mimic tables and their statistical data. The docs for db2look in DB2 9.5 are here: http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.admin.cmd.doc/doc/r0002051.html

The following db2look command will connect to the SALESDB database and obtain the DDL commands necessary to recreate the ORDERS table

db2look -d SALESDB -e -t ORDERS