[db2] How to view DB2 Table structure

How to view the table structure in DB2 database

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

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.

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

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.

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

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 =''"

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

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;

Follow this simple steps:

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

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

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'

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

In DB2, enter on db2 command prompt.

  db2  =>  describe  table MyTableName

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

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

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

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

You can Get the table meta data using this query

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