Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2006
    Posts
    4

    Question Unanswered: Is it possible to query the data type of a column?

    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.

  2. #2
    Join Date
    Nov 2004
    Posts
    374

    coltype

    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'
    Best Regards, Guy Przytula
    DB2/ORA/SQL Services
    DB2 DBA & Advanced DBA Certified
    DB2 Dprop Certified
    http://users.skynet.be/przytula/dbss.html

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    or SYSCAT.COLUMNS view ...
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Jan 2003
    Posts
    1,605
    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

  5. #5
    Join Date
    Feb 2006
    Posts
    4
    Thanks for all your replies. The suggestion from przytula works perfectly.

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    IIRC, IBM's recommendation is to use the Catalog views (Schema SYSCAT) in preference to the Catalog Tables(SYSIBM)

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Feb 2006
    Posts
    4
    Another similar problem, this time with Cloudscape. I managed to get the data types of the table columns with the following SQL query:

    Code:
    SELECT c.columnname, c.columndatatype FROM sys.syscolumns c,sys.systables t 
      WHERE c.referenceid = t.tableid AND t.tablename = '<TABLE>'
    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.

  8. #8
    Join Date
    Feb 2006
    Posts
    4
    Figured it out, I found a table reference and "ORDER BY c.columnnumber" fixed it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •