How to view DB2 Table structure

Question

How to view the table structure in DB2 database

score 68 · Accepted Answer · edited Feb 21 '14 at 12:25

68

I got the answer from the sysibm.syscolumns

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

edited Feb 21 '14 at 12:25

flipchart

6,548
4
28
53

answered Jun 07 '10 at 06:49

Ambat bhath

1,425
1
12
23

4

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 – Piro Aug 15 '13 at 08:18
2

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** – devXen Aug 30 '15 at 15:22
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'; – KC Baltz Apr 08 '16 at 16:52
table name is case sensitive for me. – Jan 05 '19 at 19:38
NULLS can be added to know mandatory columns – Krishna Aug 18 '21 at 11:40

score 27 · Answer 2 · answered Jun 04 '10 at 16:23

27

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

answered Jun 04 '10 at 16:23

Ian Bjorhovde

10,916
1
28
25

Thanks for your answer. But i couldn't get the result – Ambat bhath Jun 07 '10 at 06:50
1

What platform are you using? Based on your comments, I suspect it may be zOS. – Ian Bjorhovde Jun 09 '10 at 16:51
3

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 ".. 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 – SSC Jun 12 '14 at 06:43
2

IBM DB2 is not responding to this query ! It says- Error : DB21033E The command is not valid for this DB2 database server. – Arunchunaivendan Feb 10 '15 at 06:16
@ssc describe here is db2 command not sql statement. if you are trying through ibm_studio you will hit this error – TheBeginner Mar 16 '21 at 14:05

score 9 · Answer 3 · edited Aug 04 '14 at 06:33

9

In DB2, enter on db2 command prompt.

  db2  =>  describe  table MyTableName

edited Aug 04 '14 at 06:33

DJo

2,133
4
30
46

answered Jul 11 '12 at 19:25

binhn

99
1
1

score 7 · Answer 4 · edited May 13 '14 at 07:22

7

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.

edited May 13 '14 at 07:22

Ajinkya

22,324
33
110
161

answered Oct 03 '13 at 14:52

Boris

71
1
3

score 6 · Answer 5 · answered Jan 17 '17 at 13:35

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

score 5 · Answer 6 · answered Mar 12 '16 at 06:42

5

FOR TABLE DESCRIPTION IN IBM DB2 10.7 VERSION I TRIED THIS AND IT WORKED FINE

SELECT NAME,COLTYPE,NULLS,LONGLENGTH FROM SYSIBM.SYSCOLUMNS where TBcreator =SCHEMANAME and TBNAME =TABLENAME;

answered Mar 12 '16 at 06:42

Vinit

95
2
7

score 4 · Answer 7 · edited Nov 10 '14 at 18:26

4

1.use db2 describe table

  db2 describe table tabschema.tabname

2.use db2 describe output

  db2 "describe select * from tabschema.tabname"

3.use db2look utility

  db2look -d dbname -e -t tabname

4.find rows in db2 syscat

  db2 "Select * from syscat.columns wher tabname='' and tabschema =''"

edited Nov 10 '14 at 18:26

emmanuel

9,607
10
25
38

answered Nov 10 '14 at 18:05

Andrew

41
1

score 3 · Answer 8 · edited Oct 30 '12 at 22:13

php example for iSeries (as400) db2, yes this worked!

$i5 = db2_connect($database, $user, $password, array("i5_lib"=>"qsys2"));

$querydesc = "select * from qsys2.syscolumns where table_schema = '".$library."' and table_name = '".$table_name."' ";

$result = db2_exec($i5, $querydesc);

also if you just want to list all tables with their descriptions

$query = "select TABLE_NAME, TABLE_TEXT from systables where table_schema = '$library' ";

$result = db2_exec($i5, $query);

score 3 · Answer 9 · edited Sep 23 '16 at 08:00

3

You can Get the table meta data using this query

SELECT * FROM SYSIBM.COLUMNS WHERE TABLE_NAME = 'ASTPCLTEXT';

edited Sep 23 '16 at 08:00

mhasan

3,703
1
18
37

answered Mar 02 '15 at 19:42

R K

31
1

score 1 · Answer 10 · answered Nov 26 '13 at 07:38

1

Control Center already got the feature of that. It's just below the table list.

enter image description here

answered Nov 26 '13 at 07:38

zawhtut

8,335
5
52
76

I wanted to get this in a console rather than in control center – Tunde Pizzle Jan 31 '18 at 14:27

Dhanish Jose · Answer 11 · 2014-01-03T07:13:17.837

1

Use the below to check the table description for a single table

DESCRIBE TABLE Schema Name.Table Name

join the below tables to check the table description for a multiple tables, join with the table id syscat.tables and syscat.columns

You can also check the details of indexes on the table using the below command describe indexes for table . show detail

edited Jan 03 '14 at 07:13

answered Dec 27 '13 at 06:30

Dhanish Jose

739
1
8
19

score 1 · Answer 12 · answered Jun 04 '10 at 10:49

1

to get all tables: (You may want to restrict schema to your schema)

select * from syscat.tables

to get all columns: (where tabname = your_tabname)

select * from syscat.columns

answered Jun 04 '10 at 10:49

Peter Miehle

5,984
2
38
55

I couldn't get the result for the above given. – Ambat bhath Jun 07 '10 at 06:48

score 1 · Answer 13 · answered Jun 12 '15 at 17:49

1

if you're using Aqua Data studio, simply write select * from table_name and instead of pressing execute,, press ctrl +D .

You shall be able to see the description for the table

answered Jun 12 '15 at 17:49

Shubhangi Tandon

36
3

score 0 · Answer 14 · answered Jul 02 '14 at 08:00

The easiest way as many have mentioned already is to do a DESCRIBE TABLE

However you can also get some the same + additional information from

db2> SELECT * SYSCAT.TABLES

db2> SELECT * FROM SYSCAT.COLUMNS

I usually use SYSCAT.COLUMNS to find the related tables in the database where I already know the column name :)

Another good way if you want to get the DDL of a particular table or the whole database is to use the db2look

# db2look -d *dbname* -t *tablename* > tablestructure.out

This will generate the ".out" file for you which will contain the particular table's DDL script.

# db2look -d *dbname* -e > dbstructure.out

This will generate the entire database's DDL as a single script file, this is usually used to replicate the database, "-e" is to indicate that one wants to export DDL suitable recreate exact same setup in a new database.

Hope this can help someone looking for such answers :)

score 0 · Answer 15 · answered Feb 08 '15 at 16:48

I am running DB2/LINUXX8664 10.5.3 and describe select * from schema_name.table_name works for me.

However, describe table schema_name.table_name fails with this error:

SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000

score 0 · Answer 16 · answered Sep 11 '15 at 22:03

drop view lawmod9t.vdesc

create view lawmod9t.vDesc as select 
       upper(t.table_cat) as Catalog, 
       upper(t.table_schem) as Schema, 
       upper(t.table_name) as table, 
       t.table_text as tableDesc, 
       c.system_column_name as colname_short, 
       c.column_name as colname_long, 
       c.column_text as coldesc, 
       c.Type_Name as type, 
       c.column_Size as size
from sysibm.SQLColumns c
inner join sysibm.sqltables t
on c.table_schem = t.table_schem
and c.table_name = t.table_name

select * from vdesc where table = 'YPPPOPL'

score 0 · Answer 17 · answered Jan 14 '16 at 22:49

I am using Aquadata Studio 12.0.23, which is several versions short of the newest. So your experience may be better than mine. I found that the best way to get an overview was to use the ERD generator. It took a couple of hours, since normalization was not a concept used in the design of this database almost 30 years ago. I was able to get definitions for all of the objects in a few hours, with a file for each.

score 0 · Answer 18 · answered Oct 11 '16 at 23:32

0

The OP doesn't mention if this is DB2/400 being discussed, but I found that the only way I could get the table structure including the column name descriptions was to use DSPFFD.

DSPFFD FILE(TBNAME) OUTPUT(*OUTFILE) OUTFILE(SOMELIB/TBDESC)

This puts the description of TBNAME in a table called TBDESC in the SOMELIB library. You can then query that with:

select * from SOMELIB/TBDESC

answered Oct 11 '16 at 23:32

KC Baltz

1,498
1
13
22

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. – CRPence Oct 12 '16 at 23:13
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. – KC Baltz Oct 15 '16 at 00:56
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) – CRPence Oct 15 '16 at 05:00

score 0 · Answer 19 · answered Oct 13 '22 at 09:46

The Db2 catalogs hold lots of information about table structure. You can query them to find (most) things out about the structure of a table.

For example, you can generate (approximate) DDL for Db2 LUW tables from the catalog with SQL such as contained in this view from the IBM samples

https://github.com/IBM/db2-samples/blob/master/db_library/views/db_table_quick_ddl.sql

Noor · Answer 20 · 2022-11-18T20:53:27.693

0

One way to get list of columns and their detail using SQL editor is:

SELECT * FROM QSYS2.SYSCOLUMNS WHERE SYS_TNAME ='YOURTABLE';

Describe works for MariaDB, but not for IBM or at least I do not know.

edited Nov 18 '22 at 20:53

answered Nov 18 '22 at 20:52

Noor

1
2

score 0 · Answer 21 · answered Mar 07 '23 at 07:16

0

describe output select * from TABLE(WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES('',-2))

answered Mar 07 '23 at 07:16

Ritu Gupta

2,249
1
18
11

score -1 · Answer 22 · edited Aug 04 '14 at 06:45

-1

Follow this simple steps:

Select the Browsers window.
Extract (expand) it.
Select and extract (expand) the table list.
Select the required table and extract (expand) it.
On double click the code option, it opens the table structure.

edited Aug 04 '14 at 06:45

MarmiK

5,639
6
40
49

answered Jun 14 '10 at 11:19

Ambat bhath

1,425
1
12
23

How to view DB2 Table structure

22 Answers22

Linked