Find how to update the long comment column

Those of you who have used the SYSCOLUMNS and SYSTABLES views must have noticed that there is a column called LONG_COMMENT. On the partitions I work on this column is usually null. What is purpose of this column? And how is it updated?

A quick search in IBM's documentation introduced me to the COMMENT ON SQL statement. Before I give examples of how to use it, I am going to need a SQL DDL table to use:

01 CREATE OR REPLACE TABLE MYLIB.FIRST_TABLE 02 FOR SYSTEM NAME "TABLE1" 03 (FIRST_COLUMN FOR "FIRST" VARCHAR(20), 04 SECOND_COLUMN FOR "SECOND" VARCHAR(20)) 05 ON REPLACE DELETE ROWS ; 06 LABEL ON COLUMN FIRST_TABLE ( 07 FIRST_COLUMN IS 'First col', 08 SECOND_COLUMN IS 'Column heading' 09 ) ; 10 LABEL ON COLUMN FIRST_TABLE ( 11 FIRST_COLUMN TEXT IS 'First column', 12 SECOND_COLUMN TEXT IS 'Column text' 13 ) ; 14 LABEL ON TABLE FIRST_TABLE IS 'This is the first table' ;

Line 1: I am using the CREATE OR REPLACE TABLE so if I need to re-create the table I don't have to delete it first. As am not concerned with the contents of this table in this post I have, on line 5, ON REPLACE DELETE ROWS which will delete any rows in the table when it is re-created.

I am also using long names for the table and the columns contained within, and I am giving the short system names too.

Lines 6 – 9: The LABEL ON statement is adding the equivalent of column headings to the columns.

Lines 10 – 13: It does look the same as the previous section of code. As there is the TEXT IS this will generate the column text for the columns.

Line 14: This is the text that is applied to the table.

As all of the above is in a source member I use the Run SQL Statement command, RUNSQLSTM, which will execute all of the statements.

When I use the SYSTABLES view I am only interested in a few of the columns it contains:

• TABLE_NAME:  Name of the table
• TABLE_TEXT:  The text added using line 14, above
• LONG_COMMENT  The long comment

I am going to use the following statement. I am only interested in the table I have created; therefore, its name and library are used in the WHERE clause:

SELECT TABLE_NAME,TABLE_TEXT,LONG_COMMENT FROM QSYS2.SYSTABLES WHERE TABLE_SCHEMA = 'MYLIB' AND TABLE_NAME = 'FIRST_TABLE' ;

The results look like:

TABLE_NAME TABLE_TEXT LONG_COMMENT ----------- ----------------------- ------------ FIRST_TABLE This is the first table <NULL>

As you can see I have table text, and the long comment is null.

I retrieve the details for the columns within the table from the SYSCOLUMNS view. I am only interested in the following columns from this view:

• COLUMN_NAME:  Long name of the column
• COLUMN_HEADING:  The column headings I created using the LABEL ON
• COLUMN_TEXT:  The column text I created using the second LABEL ON
• LONG_COMMENT  The long comment

My SQL statement is:

SELECT COLUMN_NAME,COLUMN_HEADING,COLUMN_TEXT,LONG_COMMENT FROM QSYS2.SYSCOLUMNS WHERE TABLE_SCHEMA = 'MYLIB' AND TABLE_NAME = 'FIRST_TABLE' ;

And the results are:

COLUMN_NAME COLUMN_HEADING COLUMN_TEXT LONG_COMMENT -------------- --------------------------- ------------ ------------ FIRST_COLUMN First col First column <NULL> SECOND_COLUMN Column heading Column text <NULL>

The long comment in this view is null too.

As I mentioned about I found the COMMENT ON statement that will update the long comment.

For example, I changed the long comment for the first column using the following in ACS's Run SQL scripts:

COMMENT ON COLUMN FIRST_TABLE.FIRST_COLUMN IS 'This is the first column in the FIRST_TABLE table' ;

I use the COMMENT ON COLUMN and give the table and column names separated by a dot.

I reduced my SQL statement for SYSCOLUMNS as we all know that the other columns have not changed, only the long comment. My statement is now:

SELECT COLUMN_NAME,LONG_COMMENT FROM QSYS2.SYSCOLUMNS WHERE TABLE_SCHEMA = 'MYLIB' AND TABLE_NAME = 'FIRST_TABLE' ;

The results show that the long comment for the first column has changed:

COLUMN_NAME LONG_COMMENT ------------- ------------------------------------------------- FIRST_COLUMN This is the first column in the FIRST_TABLE table SECOND_COLUMN <NULL>

The long comment for the first column was changed.

I the statement below in Run SQL Scripts too to change the long comment for the table:

COMMENT ON TABLE FIRST_TABLE IS 'This is the first table with the long comment' ;

As I am adding the comment to a table I use COMMENT ON TABLE.

I change the SQL statement for SYSTABLES as now I only care about the table name and the long column:

SELECT TABLE_NAME,LONG_COMMENT FROM QSYS2.SYSTABLES WHERE TABLE_SCHEMA = 'MYLIB' AND TABLE_NAME = 'FIRST_TABLE' ;

The results show that the long comment was updated:

TABLE_NAME LONG COMMENT ----------- --------------------------------------------- FIRST_TABLE This is the first table with the long comment

I did not change the long comment for the second column as I wanted to show how to do this in a source member. I always put the SQL statements for any DDL type object (table, view, index, etc.) in a source member. What would I do if I wanted to add a comment that was longer than the width used by SQL in a source member?

Here are the lines I added to my SQL table's source member:

15 COMMENT ON COLUMN FIRST_TABLE.FIRST_COLUMN IS 'This is the first column in the FIRST_TABLE table' ; ...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 16 COMMENT ON COLUMN FIRST_TABLE.SECOND_COLUMN IS 'This is the long comment. It can be up to 2,000 characters in length. But I a m not going to do that. I just want to make it long enough to show what it does in the SYSCOLUMNS view' ; 17 COMMENT ON TABLE FIRST_TABLE IS 'This is the first table with the long comment' ;

Lines 15 and 17: These are the same as the comments I ran in Run SQL scripts.

Line 16: The long comment can contain up to 2,000 characters. I see no reason why I would ever need that number. This line shows how to enter a comment that is longer that the allowed width of the source code. I have inserted a ruler line above it so that I can see that the allowed width of any source line in a SQL command is 80 characters. All I need to do is continue of the line below. There is no nice line wrapping, if I reach the 80th character in the middle of a word I need to continue it without spaces on the line below.

I use the RUNSQLSTM again to execute all of the statements in the source member.

After it has finished I can query SYSCOLUMNS again to see the change long description for the second column:

COLUMN_NAME LONG_COMMENT ------------- ------------------------------------------------------------- FIRST_COLUMN This is the first column in the FIRST_TABLE table SECOND_COLUMN This is the long comment. It can be up to 2,000 characters in length. But I am not going to do that. I just want to make it long enough to show what it does in the SYSCOLUMNS view

I have wrapped the contents of the long comment column so that you see the value for the second column.

So now I know how to update the long comment. I can see this being useful to give more of a description to a SQL object or column, not limited by the 50 characters of the text column. And yes, you can use the COMMENT ON on other SQL object types. Just check the IBM documentation that is linked below.

 

You can learn more about the COMMENT SQL statement from the IBM website here.

 

This article was written for IBM i 7.5, and should work for some earlier releases too.