Add a field description to a DB2/400 file

Question

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 1

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

Answer 2

As an additional hint, you can use iSeries Navigator to get the SQL statement for that file. If you fire up this program, log on to the iSeries, go to database, go to schemas, and go to tables, then you can find your file. You'll find the button "generate SQL" somewhere. This creates an exact SQL statement for you to create the table. You can use this SQL statement to work with this table.

For your questions, the "LABEL" from another answer is sufficient. This answer is usefull if you want to do more stuff, that can not be done with ALTER.