How to view DB2 Table structure

Question

How to view the table structure in DB2 database

Answer 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

Answer 2

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 :)

Answer 3

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

Answer 4

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'

Answer 5

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.

Answer 6

In DB2, enter on db2 command prompt.

  db2  =>  describe  table MyTableName