CODEWITHSUNDEEP
db2ibm-midrange
Joakim M
Joakim M

Reputation: 1803

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

Upvotes: 2

Views: 25291

Answers (5)

L. Astola
L. Astola

Reputation: 55

select distinct(name), ColType, COLNO, Length from Sysibm.syscolumns where tbname = 'My_View_Name' order by COLNO;

Upvotes: 0

Er. Binod Mehta
Er. Binod Mehta

Reputation: 63

The columns name of any table (PF) from db400 (IBM i), we can retrieve the columns name using java program. Sample code:

String sql = "select * from KFILNBB.V_OCADATA";
PreparedStatement pstmt = StaticUtil.getDB2Connection().prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
    
ResultSetMetaData rs1 = pstmt.getMetaData();
     
ResultSetMetaData rsmd = rs.getMetaData();
int numberOfColumns = rsmd.getColumnCount();
System.out.println(rsmd.getColumnLabel(1));

// -------------------db2 connection----------

    public static Connection getDB2Connection() throws ClassNotFoundException, SQLException {
            String DRIVER_NAME = "com.ibm.as400.access.AS400JDBCDriver";
            
            String URL = "jdbc:as400://ibm i IP address";
             String USERNAME = "username";
            String PASSWORD = "password";
            Class.forName(DRIVER_NAME);
            return DriverManager.getConnection(URL , USERNAME, PASSWORD);
        }

Upvotes: -1

The IT Guy
The IT Guy

Reputation: 86

Another way if you are doing db2 commands from a a Linux bash shell:

 $ db2 "DESCRIBE TABLE A" |awk '{printf "%s\t",$1} END{print}'

Upvotes: 0

Charles
Charles

Reputation: 23783

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

Upvotes: 15

user1919238
user1919238

Reputation:

This information is found in the table syscat.columns.

Upvotes: 0

Related Questions