How to view DB2 Table structure

How to view the tabl开发者_如何学Pythone structure in DB2 database

I got the answer from the sysibm.syscolumns

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

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

In DB2, enter on db2 command prompt.

  db2  =>  describe  table MyTableName

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.

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;

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

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

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

You can Get the table meta data using this query

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

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

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

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

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

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

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

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'

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

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

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.

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.