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)

Solution 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

Solution 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.

Comments

[jwl]

btw, did you just know this, or was this something you looked up? and if you looked up, give me a clue on your methodology because I am always stumped googling for as400 stuff...

[Tracy Probst]

It's a mix. I glanced in the SQL command reference table of contents, saw the LABEL keyword and then remembered having seen it before. My first exposure to this keyword is when I used an API that generates DDL from AS/400 physical files and the DDL generated included LABEL commands. The first place I go for this stuff is the iSeries InfoCenter. Here is a link to the V5R4 version: publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp For the details and samples, I just went into STRSQL, created a table and then prompted some LABEL commands.

[Tracy Probst]

Update: The link to the IBM InfoCenter for AS/400 changed. That prior link will work, but will redirect. Here is the current link for all releases: www-01.ibm.com/support/knowledgecenter/ssw_ibm_i/welcome. After loading the page, select the version from the menu on the left.