Description of a DB2 Table

 

Description of a DB2 Table
 
The describe command does not work on mainframe DB2. Generally we use the following command on UDB database or database on oracle
 
DESCRIBE TABLE tablename
 
But this does not work with mainframe DB2.
 
You can use the following command to get the description of columns in a table. The following query will list the description of  table tablename whose creator is nameofcreator
 
SELECT * FROM SYSIBM.SYSCOLUMNS
WHERE TBNAME='tablename'
AND TBCREATOR='nameofcreator'                            
 
 
If you wish to fetch only the column names in a table then you can use the following query
 
 
SELECT * FROM tablename WHERE 1=2

Comments

Anonymous

Wed, 06/29/2011 - 07:14

if I use ---SELECT * FROM SYSIBM.SYSCOLUMNS WHERE TBNAME='tablename' AND TBCREATOR='nameofcreator' I cannot find my table name there. If I use--SELECT * FROM tablename WHERE 1=2 it is showing 0 rows displayed sql return code 100

siteadmin

Wed, 06/29/2011 - 07:22

For your first query, I suppose you might be giving some incorrect information. I would suggest you to browse the table SYSIBM.SYSCOLUMNS and find for your table and this way you would know the creator 

 

SELECT * FROM tablename WHERE 1=2

would return 0 rows only, but you would see the column names displayed.

 

 

SELECT * FROM SYSIBM.SYSTABLES WHERE 1=2;                                      
---------+---------+---------+---------+---------+---------+---------+--------
NAME                                                                          
---------+---------+---------+---------+---------+---------+---------+--------

 

DSNE610I NUMBER OF ROWS DISPLAYED IS 0                                        
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100                   
 
Highlighted section will display the column names.

Anonymous

Fri, 03/09/2012 - 04:57

In above select statement getting only column name from the table not getting the length and datatype of that column

Anonymous

Wed, 09/12/2012 - 09:37

Select * from Sysibm.syscolumns where tbname = 'your table Name';

This would return u the table structure.

Also the command DESCRIBE TABLE schemaname.tablename is not working

 

~Ankur