Selecting Field Description With DB2 and SQL
This is a discussion on Selecting Field Description With DB2 and SQL within the DB2 forums, part of the Database Server Software category; --> Is there a way to select the text description of a set of fields in a table? I am ...
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-27-2008, 01:04 PM
| ||||
| ||||
 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      |
|
02-27-2008, 01:04 PM
| |||
| |||
| 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. |
|
02-27-2008, 01:04 PM
| ||||
| ||||
| Re: Selecting Field Description With DB2 and SQL On Aug 13, 5:04 pm, Dave Hughes <d...@waveform.plus.com> 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 |
Linear Mode
