db2ibm-midrange

How to get column names from DB2


I would like how to get the column names of a table from a DB2-DB.

I don't mean like:

SELECT * FROM TABLE A;

where I would get:

|  ColumnA  |  ColumnB |
------------------------
  ValueA 1     ValueB 1

  ValueA 2     ValueB 2

  ValueA 3     ValueB 3

but more like:

SELECT column_name FROM TABLE A;

and get:

ColumnA, ColumnB

Solution

  • For DB2 for i, this information is in

    qsys2.syscolumns2  (for 7.1+, better performance if just looking at 1 table's columns)
    qsys2.syscolumns (for any recent version)
    

    ODBC/JDBC and DB2 LUW v8 compatible

    sysibm.sqlcolumns
    

    ANSI/ISO Standard

    INFORMATION_SCHEMA.columns
    

    (note INFORMATION_SCHEMA is a synonym for QSYS2)

    Full list of catalog views here: http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzcatalog.htm