Pages

Wednesday, July 13, 2022

Find how to update the long comment column

sql comment on

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.

1 comment:

  1. Thanks for the post Simon. Especially, the mention of wrapping. I have been using COMMENT ON for years now and have it as part of my snippets to create any DDL object. What I would like to add is that you no longer need to be constrained by the 92 width, meaning 6 for date, 6 for sequence and 80 for data. A source file can go out to 32754. Even for RPG if you want. I would suggest using 240. Why? Because you can still use PDM to view it if you so choose. This leaves 228 characters for data, which depending on your monitor size, is about a full screen in RDi. Along with this I would like to add that IBM has enhanced the RUNSQLSTM. The default length used to be 80. Now you can specify *SRCFILE, which will automatically adjust to the width of your source file. - https://www.ibm.com/support/pages/node/6579401 This is part of the TR6 Technology refresh for V7R4 - https://www.ibm.com/support/pages/node/1119129. Personally, I believe this should be the default for RUNSQLSTM but I don't believe it is. If you change the default for the IBM command, then you don't have to worry about modify any applications, unless you specified a number other than the IBM shipped default of 80.

    The underlying point is why constraint yourself. Modernize and go as wide as you would like. We have an application that generates query statements into a source file and have gone the max to 32754, no need to worry about our application having to wrap here.

    Thanks,
    Ray Richert

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.