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

User · Answer

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

User · Answer

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     lt table name gt      AND tabschema     lt schema name gt    ORDER BY colno

User · Answer

you can use the below command to see the complete characteristics of DB  db2look -d  lt DB NAme gt -u walid -e -o   you can use the below command to see the complete characteristics of Schema   db2look -d  lt DB NAme gt  -u walid -z  lt Schema Name gt  -e -o   you can use the below command to see the complete characteristics of table  db2look -d  lt DB NAme gt  -u walid -z  lt Schema Name gt  -t  lt Table Name gt -e -o   you can also visit the below link for more details  https   publib boulder ibm com infocenter db2luw v9 index jsp topic  2Fcom ibm db2 udb admin doc 2Fdoc 2Fr0002051 htm

User · Answer

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

User · Answer

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

User · Answer

Describe table syntax  describe table schemaName TableName

User · Answer

db2look -d  lt db name gt  -e -z  lt schema name gt  -t  lt table name gt  -i  lt user name gt  -w  lt password gt     lt file name gt  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

User · Answer

Syntax for Describe table  db2 describe table  lt tablename gt    or For all table details  select   from syscat tables   or For all table details   select   from sysibm tables

User · Answer

I just came across this query to describe a table in winsql  select NAME TBNAME COLTYPE LENGTH REMARKS SCALE from sysibm syscolumns where tbcreator    Schema name  and tbname  Table name

User · Answer

Right-click the table in DB2 Control Center and chose Generate DDL     That will give you everything you need and more

User · Answer

Try the following   DESCRIBE SELECT   FROM TABLE name

[db2] In DB2 Display a table's definition

Examples related to db2