Hello, I have to evaluate the data types of various columns in a table (with Perl/DBI), is that possible with an SQL statement? My last option would be exporting the table structure and parsing it but if there's something easier...

Thanks in advance.

look at sysibm.syscolumns
NAME SYSIBM VARCHAR 128 0 No
TBNAME SYSIBM VARCHAR 128 0 No
TBCREATOR SYSIBM VARCHAR 128 0 No
REMARKS SYSIBM VARCHAR 254 0 Yes
COLTYPE SYSIBM CHARACTER 8 0 No

select name,coltype from sysibm.syscolumns where tbname='xxx' and tbcreator='yy'

or SYSCAT.COLUMNS view ...

SELECT TYPENAME, LENGTH, SCALE FROM SYSCAT.COLUMNS WHERE TABSCHEMA='SCHEMA_NAME' AND TABNAME='TABLE_NAME'

Replace SCHEMA_NAME with your table schema
Replace TABLE_NAME with your table name

Thanks for all your replies. The suggestion from przytula works perfectly.

IIRC, IBM's recommendation is to use the Catalog views (Schema SYSCAT) in preference to the Catalog Tables(SYSIBM)

Cheers
Sathyaram

Another similar problem, this time with Cloudscape. I managed to get the data types of the table columns with the following SQL query:

This works so far except for the fact that the output is sorted alphabetically. Anybody knows whether it's possible to view the data types on Cloudscape in the order they appear in the table (which is the default on DB2 UDB)? Thanks.

Figured it out, I found a table reference and "ORDER BY c.columnnumber" fixed it.