Description of columns in a DB2 table

Question

select 
  tabname,
  colname,
  typename,
  length,
  scale,
  default,
  nulls,
  identity,
  generated,
  remarks,
  keyseq 
from 
  syscat.columns 

SELECT 
TABLE_CAT, 
TABLE_SCHEM, 
TABLE_NAME, 
COLUMN_NAME, 
DATA_TYPE, 
TYPE_NAME, 
COLUMN_SIZE, 
COLUMN_TEXT
FROM "SYSIBM"."SQLCOLUMNS"
WHERE TABLE_SCHEM = 'SCHEMA' 
 AND TABLE_NAME = 'TABLE'

This is on DB2 V5R4, and is not a System Table but a SYSTEM VIEW. In case that you go nuts looking for it on the tables list.

-- NOTE: the where clause is case sensitive and needs to be uppercase

select
t.table_schema as Library
,t.table_name
,t.table_type
,c.column_name
,c.ordinal_position
,c.data_type
,c.character_maximum_length as Length
,c.numeric_precision as Precision
,c.numeric_scale as Scale
,c.column_default
,t.is_insertable_into
from sysibm.tables t
join sysibm.columns c
on t.table_schema = c.table_schema
and t.table_name = c.table_name
where t.table_schema = 'MYLIB'
and t.table_name = 'MYTABLE'
order by t.table_name, c.ordinal_position

-- to get a list of all the meta tables:

select * from sysibm.tables
where table_schema = 'SYSIBM'

SELECT COLNAME, REMARKS FROM SYSCAT.COLUMNS WHERE TABSCHEMA = 'MYSCHEMA' AND TABNAME = 'MYTABLENAME'

I work on an iSeries DB2 box (v5r4), it is a special flavor of DB2.

If you're on or connecting to an iSeries (AS/400), the link mentioned by Anton is most excellent (sorry, can't vote yet!)

Describe does not work on an iSeries, but will work with DB2 on the other platforms.

select T1.name,T1.creator from sysibm.systables T1,sysibm.syscolumns 
T2 where T1.name=T2.tbname and T1.creator=T2.tbccreator and 
T1.creator='CREATOR NAME' and T2.name='COLUMN NAME' 

describe table schema.table_name ;

DB2 Describe Command