How to view DB2 Table structure

Question

How to view the table structure in DB2 database

Solution 1

I got the answer from the sysibm.syscolumns

Select distinct(name), ColType, Length from Sysibm.syscolumns where tbname = 'employee';

Solution 2

Generally it's easiest to use DESCRIBE.

DESCRIBE TABLE MYSCHEMA.TABLE

or

DESCRIBE INDEXES FOR MYSCHEMA.TABLE SHOW DETAIL

etc.

See the documentation: DESCRIBE command

Solution 3

In DB2, enter on db2 command prompt.

  db2  =>  describe  table MyTableName

Solution 4

Also the following command works:

describe SELECT * FROM table_name;

Where the select statement can be replaced with any other select statement, which is quite useful for complex inserts with select for example.

Solution 5

How to view the table structure in db2 database

Open db2 command window, connect to db2 with following command.

> db2 connect to DATABASE_NAME USER USERNAME USING PASSWORD

Once you connected successfully, issue the following command to view the table structure.

> db2 "describe select * from SCHEMA_NAME.TABLE_NAME"

The above command will display db2 table structure in tabular format.

Note: Tested on DB2 Client 9.7.11

View more solutions

Comments

[Ambat bhath]

I couldn't get the result for the above given.

[Ambat bhath]

Thanks for your answer. But i couldn't get the result

[Ian Bjorhovde]

What platform are you using? Based on your comments, I suspect it may be zOS.

[Piro]

It may differ by version as it seems. We are running V7R1 and table is SYSIBM.COLUMNS. Also columns are different, so i uses DSPFFD FILE(SYSIBM/COLUMNS) to find correct columns

[SSC]

I tried executing DESCRIBE TABLE statement but there is error: 11:42:25 [DESCRIBE - 0 row(s), 0.000 secs] [Error Code: -104, SQL State: 42601] An unexpected token "TABLE" was found following "DESCRIBE ". Expected tokens may include: "JOIN <joined_table>".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.13.127 ... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 1 errors] I am running this on DBVisualizer. Please advise

[Arunchunaivendan]

IBM DB2 is not responding to this query ! It says- Error : DB21033E The command is not valid for this DB2 database server.

[devXen]

Added 'order by COLNO' because the output order is not the same as the actual column order. Select distinct(name), COLNO, ColType, Length from Sysibm.syscolumns where tbname = 'EMPLOYEE' order by COLNO

[KC Baltz]

This worked for me on V7R1 and it selects for a specific library: select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGth, NUMERIC_PRECISION from SYSIBM/COLUMNS where TBNAME = 'TABLE' and TABLE_SCHEMA='LIBRARY';

[CRPence]

The only way? Wow! Someone needs to dig into the SQL some more ;-) Try some of the suggestions in prior answers; admittedly some are not going to function on the DB2 for IBM i. Mostly because the authors failed to preface with "For DB2 LUW", unlike this answer did, by so nicely starting with essentially "For DB2 for IBM i". But typically the responses that will have directed to use of the SQL catalogs for obtaining the descriptive information [e.g. from COLUMNS or SYSCOLUMNS; typically VIEWs with metadata for DB2 objects], those should be similar across all DB2 variants.

[KC Baltz]

The other ways work with the exception of the column name description. This was the only method I found that gave me that. I'm not saying there aren't others because I stopped looking when I found what I needed.

[CRPence]

Depends on what is meant by "column name description", I suppose. But the VIEW SYSCOLUMNS in QSYS2 has defined, a COLUMN_TEXT FOR LABELTEXT column, that is derived from the data DBITXT of the System Database Cross Reference file QADBIFLD in QSYS that should be the same as the data obtained from the WHFTXT column of the system-supplied record format QWHDRFFD of the model-file QADSPFFD in QSYS; i.e. the dynamically maintained information for that "column name description" can be obtained directly from the catalog rather than having to use the Display File Field Descriptions (DSPFFD)

[Tunde Pizzle]

I wanted to get this in a console rather than in control center

[Admin]

table name is case sensitive for me.

[TheBeginner]

@ssc describe here is db2 command not sql statement. if you are trying through ibm_studio you will hit this error

[Krishna]

NULLS can be added to know mandatory columns