Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to see the schema of a db2 table (file)

Tags:

file

schema

db2

As in subject... is there a way of looking at an empty table schema without inserting any rows and issuing a SELECT?

like image 835
tpk Avatar asked Sep 30 '08 16:09

tpk


4 Answers

SELECT *                
FROM SYSIBM.SYSCOLUMNS  
WHERE                   
TBNAME    = 'tablename'; 
like image 115
Amit Avatar answered Oct 24 '22 02:10

Amit


Are you looking for DESCRIBE?

db2 describe table user1.department

Table: USER1.DEPARTMENT

Column             Type        Type
name               schema      name               Length   Scale    Nulls
------------------ ----------- ------------------ -------- -------- --------
AREA               SYSIBM      SMALLINT                  2        0 No
DEPT               SYSIBM      CHARACTER                 3        0 No
DEPTNAME           SYSIBM      CHARACTER                20        0 Yes
like image 36
Plasmer Avatar answered Oct 24 '22 01:10

Plasmer


For DB2 AS/400 (V5R4 here) I used the following queries to examine for database / table / column metadata:

SELECT * FROM SYSIBM.TABLES -- Provides all tables

SELECT * FROM SYSIBM.VIEWS -- Provides all views and their source (!!) definition

SELECT * FROM SYSIBM.COLUMNS -- Provides all columns, their data types & sizes, default values, etc.

SELECT * FROM SYSIBM.SQLPRIMARYKEYS -- Provides a list of primary keys and their order

like image 7
brandon k Avatar answered Oct 24 '22 02:10

brandon k


Looking at your other question, DESCRIBE may not work. I believe there is a system table that stores all of the field information.

Perhaps this will help you out. A bit more coding but far more accurate.

like image 1
Mike Wills Avatar answered Oct 24 '22 01:10

Mike Wills