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

Try the following:

DESCRIBE SELECT * FROM TABLE_name

Answer 2

Syntax for Describe table

db2 describe table <tablename>

or For all table details

select * from syscat.tables

or For all table details

 select * from sysibm.tables

Answer 3

DB2 Version 11.0

Columns:
--------
SELECT NAME,COLTYPE,NULLS,LENGTH,SCALE,DEFAULT,DEFAULTVALUE FROM SYSIBM.SYSCOLUMNS where TBcreator ='ME' and TBNAME ='MY_TABLE' ORDER BY COLNO;

Indexes:
--------
SELECT P.SPACE, K.IXNAME, I.UNIQUERULE, I.CLUSTERING, K.COLNAME, K.COLNO, K.ORDERING
FROM SYSIBM.SYSINDEXES I
    JOIN SYSIBM.SYSINDEXPART P
        ON I.NAME = P.IXNAME
        AND I.CREATOR = P.IXCREATOR
    JOIN SYSIBM.SYSKEYS K
        ON P.IXNAME = K.IXNAME
        AND P.IXCREATOR = K.IXCREATOR
WHERE I.TBcreator ='ME' and I.TBNAME ='MY_TABLE'
ORDER BY K.IXNAME, K.COLSEQ;

Answer 4

I know this is an old question, but this will do the job.

SELECT colname, typename, length, scale, default, nulls
  FROM syscat.columns
 WHERE tabname = '<table name>'
   AND tabschema = '<schema name>'
 ORDER BY colno

Answer 5

In addition to DESCRIBE TABLE, you can use the command below

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 11.5 are here.

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

db2look -d SALESDB -e -t ORDERS

Answer 6

db2look -d <db_name> -e -z <schema_name> -t <table_name> -i <user_name> -w <password> > <file_name>.sql

For more information, please refer below:

    db2look [-h]

    -d: Database Name: This must be specified

    -e: Extract DDL file needed to duplicate database
   -xs: Export XSR objects and generate a script containing DDL statements
 -xdir: Path name: the directory in which XSR objects will be placed
    -u: Creator ID: If -u and -a are both not specified then $USER will be used
    -z: Schema name: If -z and -a are both specified then -z will be ignored
    -t: Generate statistics for the specified tables
   -tw: Generate DDLs for tables whose names match the pattern criteria (wildcard characters) of the table name
   -ap: Generate AUDIT USING Statements
  -wlm: Generate WLM specific DDL Statements
  -mod: Generate DDL statements for Module
  -cor: Generate DDL with CREATE OR REPLACE clause
 -wrap: Generates obfuscated versions of DDL statements
    -h: More detailed help message
    -o: Redirects the output to the given file name
    -a: Generate statistics for all creators
    -m: Run the db2look utility in mimic mode
        -c: Do not generate COMMIT statements for mimic
        -r: Do not generate RUNSTATS statements for mimic
    -l: Generate Database Layout: Database partition groups, Bufferpools and Tablespaces
    -x: Generate Authorization statements DDL excluding the original definer of the object
   -xd: Generate Authorization statements DDL including the original definer of the object
    -f: Extract configuration parameters and environment variables
   -td: Specifies x to be statement delimiter (default is semicolon(;))
    -i: User ID to log on to the server where the database resides
    -w: Password to log on to the server where the database resides