+ Reply to Thread
Results 1 to 3 of 3

Selecting Field Description With DB2 and SQL

  1. Selecting Field Description With DB2 and SQL

    Is there a way to select the text description of a set of fields in a
    table? I am looking for a result set like the following:

    fieldName | fieldDesc
    ------------------------------------------
    RMCUST Customer Number
    RMADDR Customer Address
    RMMZIP Customer Zip


    I can currently use the COLUMNS table to grab the field names but not
    the description.

    ex:

    SELECT COLUMN_NAME FROM qsys2.columns WHERE TABLE_NAME = 'CUSTMAST'

    In MySQL you can just use 'DESC tablename'. That does not seem to work
    in DB2.

    Thanks!

    -Nick


  2. Re: Selecting Field Description With DB2 and SQL

    On Mon, 13 Aug 2007 20:02:24 +0000, direct151 scribbled:

    > Is there a way to select the text description of a set of fields in a
    > table? I am looking for a result set like the following:
    >
    > fieldName | fieldDesc
    > ------------------------------------------
    > RMCUST Customer Number
    > RMADDR Customer Address
    > RMMZIP Customer Zip
    >
    >
    > I can currently use the COLUMNS table to grab the field names but not
    > the description.
    >
    > ex:
    >
    > SELECT COLUMN_NAME FROM qsys2.columns WHERE TABLE_NAME = 'CUSTMAST'


    You don't want to use QSYS2.COLUMNS. That's just an ANSI/ISO
    compatibility view that leaves out most of the interesting stuff -
    basically it's just a view on top of the "real" columns catalog table.
    The "real" catalog tables on iSeries (and incidentally zSeries where they
    live under the SYSIBM schema) usually have "SYS" at the front of their
    name.

    In this case, you want the QSYS2.SYSCOLUMNS table, which contains two
    columns of interest:

    COLUMN_TEXT is a VARCHAR(50) column that holds the string supplied by the
    LABEL statement. This appears to be a means of attaching a (very short)
    comment to database object.

    LONG_COMMENT is a VARCHAR(2000) column that holds the string supplied by
    the COMMENT statement, which simply appears to be a more verbose version
    of the LABEL statement.

    Hence, I think you could do the following:

    COMMENT ON CUSTMAST (
    RMCUST IS 'Customer Number',
    RMADDR IS 'Customer Address',
    RMMZIP IS 'Customer Zip'
    );

    SELECT COLUMN_NAME, LONG_COMMENT
    FROM QSYS2.SYSCOLUMNS
    WHERE TABLE_NAME = 'CUSTMAST'

    COLUMN_NAME LONG_COMMENT
    ----------- -----------------------------------------
    RMCUST Customer Number
    RMADDR Customer Address
    RMMZIP Customer Zip


    Alternatively, you could use the LABEL statement and the COLUMN_TEXT
    column in QSYS2.SYSCOLUMNS if you wanted shorter labels. Personally, I'd
    recommend sticking to the COMMENT statement though, partly because it
    permits longer comments but also because LABEL isn't supported on DB2 for
    LUW (in case you ever feel like migrating), although it does appear to be
    supported by DB2 for zSeries (albeit with a tiny 30 character limit).

    Just to summarize each platform:

    DB2 for iSeries:
    * LABEL supported with 60 char limit
    * LABEL strings stored in QSYS2.SYSx.x_TEXT columns (e.g.
    QSYS2.SYSCOLUMNS.COLUMN_TEXT, QSYS2.SYSTABLES.TABLE_TEXT)
    * LABEL documentation: http://publib.boulder.ibm.com/infocenter/iseries/
    v5r4/topic/db2/rbafzmstlabelon.htm
    * COMMENT supported with 2000 (!) char limit
    * COMMENT strings stored in QSYS2.SYSx.LONG_COMMENT columns (e.g.
    QSYS.SYSCOLUMNS.LONG_COMMENT, QSYS2.SYSTABLES.LONG_COMMENT)
    * COMMENT documentation: http://publib.boulder.ibm.com/infocenter/iseries/
    v5r4/topic/db2/rbafzmstcomnt.htm
    * Catalog documentation: http://publib.boulder.ibm.com/infocenter/iseries/
    v5r4/topic/db2/rbafzmstcatalogtbls.htm

    DB2 for zSeries
    * LABEL supported with 30 char limit
    * LABEL strings stored in SYSIBM.SYSx.LABEL columns (e.g.
    SYSIBM.SYSCOLUMNS.LABEL, SYSIBM.SYSTABLES.LABEL)
    * LABEL documentation: http://publib.boulder.ibm.com/infocenter/dzichelp/
    v2r2/topic/com.ibm.db2.doc.sqlref/rlabl.htm#rlabl
    * COMMENT supported with 762 (?!) char limit
    * COMMENT strings stored in SYSIBM.SYSx.REMARKS columns (e.g.
    SYSIBM.SYSCOLUMNS.REMARKS, SYSIBM.SYSTABLES.REMARKS)
    * COMMENT documentation: http://publib.boulder.ibm.com/infocenter/
    dzichelp/v2r2/topic/com.ibm.db2.doc.sqlref/rcmnt.htm#rcmnt
    * Catalog documentation: http://publib.boulder.ibm.com/infocenter/
    dzichelp/v2r2/topic/com.ibm.db2.doc.sqlref/rctabls.htm#rctabls

    DB2 for Linux/UNIX/Windows:
    * No LABEL support
    * COMMENT supported with 254 char limit
    * COMMENT strings stored in SYSCAT.x.REMARKS columns (e.g.
    SYSCAT.COLUMNS.REMARKS, SYSCAT.TABLES.REMARKS)
    * COMMENT documentation: http://publib.boulder.ibm.com/infocenter/db2luw/
    v9/topic/com.ibm.db2.udb.admin.doc/doc/r0000901.htm
    * Catalog documentation: http://publib.boulder.ibm.com/infocenter/db2luw/
    v9/topic/com.ibm.db2.udb.admin.doc/doc/r0011297.htm


    HTH,

    Dave.

  3. Re: Selecting Field Description With DB2 and SQL

    On Aug 13, 5:04 pm, Dave Hughes wrote:
    > On Mon, 13 Aug 2007 20:02:24 +0000, direct151 scribbled:
    >
    >
    >
    > > Is there a way to select the text description of a set of fields in a
    > > table? I am looking for a result set like the following:

    >
    > > fieldName | fieldDesc
    > > ------------------------------------------
    > > RMCUST Customer Number
    > > RMADDR Customer Address
    > > RMMZIP Customer Zip

    >
    > > I can currently use the COLUMNS table to grab the field names but not
    > > the description.

    >
    > > ex:

    >
    > > SELECT COLUMN_NAME FROM qsys2.columns WHERE TABLE_NAME = 'CUSTMAST'

    >
    > You don't want to use QSYS2.COLUMNS. That's just an ANSI/ISO
    > compatibility view that leaves out most of the interesting stuff -
    > basically it's just a view on top of the "real" columns catalog table.
    > The "real" catalog tables on iSeries (and incidentally zSeries where they
    > live under the SYSIBM schema) usually have "SYS" at the front of their
    > name.
    >
    > In this case, you want the QSYS2.SYSCOLUMNS table, which contains two
    > columns of interest:
    >
    > COLUMN_TEXT is a VARCHAR(50) column that holds the string supplied by the
    > LABEL statement. This appears to be a means of attaching a (very short)
    > comment to database object.
    >
    > LONG_COMMENT is a VARCHAR(2000) column that holds the string supplied by
    > the COMMENT statement, which simply appears to be a more verbose version
    > of the LABEL statement.
    >
    > Hence, I think you could do the following:
    >
    > COMMENT ON CUSTMAST (
    > RMCUST IS 'Customer Number',
    > RMADDR IS 'Customer Address',
    > RMMZIP IS 'Customer Zip'
    > );
    >
    > SELECT COLUMN_NAME, LONG_COMMENT
    > FROM QSYS2.SYSCOLUMNS
    > WHERE TABLE_NAME = 'CUSTMAST'
    >
    > COLUMN_NAME LONG_COMMENT
    > ----------- -----------------------------------------
    > RMCUST Customer Number
    > RMADDR Customer Address
    > RMMZIP Customer Zip
    >
    > Alternatively, you could use the LABEL statement and the COLUMN_TEXT
    > column in QSYS2.SYSCOLUMNS if you wanted shorter labels. Personally, I'd
    > recommend sticking to the COMMENT statement though, partly because it
    > permits longer comments but also because LABEL isn't supported on DB2 for
    > LUW (in case you ever feel like migrating), although it does appear to be
    > supported by DB2 for zSeries (albeit with a tiny 30 character limit).
    >
    > Just to summarize each platform:
    >
    > DB2 for iSeries:
    > * LABEL supported with 60 char limit
    > * LABEL strings stored in QSYS2.SYSx.x_TEXT columns (e.g.
    > QSYS2.SYSCOLUMNS.COLUMN_TEXT, QSYS2.SYSTABLES.TABLE_TEXT)
    > * LABEL documentation:http://publib.boulder.ibm.com/infocenter/iseries/
    > v5r4/topic/db2/rbafzmstlabelon.htm
    > * COMMENT supported with 2000 (!) char limit
    > * COMMENT strings stored in QSYS2.SYSx.LONG_COMMENT columns (e.g.
    > QSYS.SYSCOLUMNS.LONG_COMMENT, QSYS2.SYSTABLES.LONG_COMMENT)
    > * COMMENT documentation:http://publib.boulder.ibm.com/infocenter/iseries/
    > v5r4/topic/db2/rbafzmstcomnt.htm
    > * Catalog documentation:http://publib.boulder.ibm.com/infocenter/iseries/
    > v5r4/topic/db2/rbafzmstcatalogtbls.htm
    >
    > DB2 for zSeries
    > * LABEL supported with 30 char limit
    > * LABEL strings stored in SYSIBM.SYSx.LABEL columns (e.g.
    > SYSIBM.SYSCOLUMNS.LABEL, SYSIBM.SYSTABLES.LABEL)
    > * LABEL documentation:http://publib.boulder.ibm.com/infocenter/dzichelp/
    > v2r2/topic/com.ibm.db2.doc.sqlref/rlabl.htm#rlabl
    > * COMMENT supported with 762 (?!) char limit
    > * COMMENT strings stored in SYSIBM.SYSx.REMARKS columns (e.g.
    > SYSIBM.SYSCOLUMNS.REMARKS, SYSIBM.SYSTABLES.REMARKS)
    > * COMMENT documentation:http://publib.boulder.ibm.com/infocenter/
    > dzichelp/v2r2/topic/com.ibm.db2.doc.sqlref/rcmnt.htm#rcmnt
    > * Catalog documentation:http://publib.boulder.ibm.com/infocenter/
    > dzichelp/v2r2/topic/com.ibm.db2.doc.sqlref/rctabls.htm#rctabls
    >
    > DB2 for Linux/UNIX/Windows:
    > * No LABEL support
    > * COMMENT supported with 254 char limit
    > * COMMENT strings stored in SYSCAT.x.REMARKS columns (e.g.
    > SYSCAT.COLUMNS.REMARKS, SYSCAT.TABLES.REMARKS)
    > * COMMENT documentation:http://publib.boulder.ibm.com/infocenter/db2luw/
    > v9/topic/com.ibm.db2.udb.admin.doc/doc/r0000901.htm
    > * Catalog documentation:http://publib.boulder.ibm.com/infocenter/db2luw/
    > v9/topic/com.ibm.db2.udb.admin.doc/doc/r0011297.htm
    >
    > HTH,
    >
    > Dave.


    Awesome! Exactly what I was looking for and more!!

    That worked great.

    Thank you very much for the help!

    Nick


+ Reply to Thread