Add a field description to a DB2/400 file

jwl picture jwl · Aug 11, 2009 · Viewed 9.7k times · Source

I have a set of files in a library on an AS/400/iSeries/System-I/whatever-IBM-wants-me-to-call-it-these-days which I do not have DDS for (created in SQL I gather) but to which I would like to add field descriptions. I cannot find a way to do this on the 400.

Does anyone know how I can add field description? Is directly updating SYSIBM.SQLCOLUMNS to set COLUMN_TEXT safe?

(I am not looking for general SQL answers here, only DB2/400 specific answers. Unfortunately given IBMs renamings of this platform and the very un-google-able name they chose, it is almost impossible to find answers (or especially to prove that there is NOT an answer) to such questions without spending ages looking through there voluminous, impenetrable documentation)

Answer

Tracy Probst picture Tracy Probst · Aug 11, 2009

Use the LABEL SQL command. If you are using the green-screen STRSQL command, you can prompt it. The LABEL command can be used to set both column text and column headings.

Here is an example of using the LABEL command to give column text for two fields in the file named TESTFILE1. The fields are named FIELD1 and FIELD2 in this example:

LABEL ON COLUMN TESTFILE1 
(FIELD1 TEXT IS 'My Field 1 text', FIELD2 TEXT IS 'My Field 2 text')

Here is an example of using the LABEL command to give column headings that would show up in query results:

LABEL ON COLUMN TESTFILE1
(FIELD1 IS 'My field            Heading 1',
 FIELD2 IS 'My field            Heading 2')

When creating column headings, you get 60 characters per field. The first 20 characters are line 1. The second 20 characters are line 2. The third 20 characters are line 3. In the above example, the field headings would look like this:

My field     My field
Heading 1    Heading 2