COMMENT ON

The COMMENT ON statement adds or replaces descriptive text comments for aliases, columns, distinct types (currently not supported), stored procedures, tables, triggers, user-defined functions, and views located within the current XDB Server location. All comments are stored in the catalog tables belonging to that location.

Invocation

COMMENT ON is an executable command that can be issued interactively or embedded in a host language. COMMENT ON can be dynamically prepared.

Authorization

For a comment on a table, view, alias, index, or column, the privilege set that is defined below must include at least one of the following:

  • Ownership of the table, view, alias, or index
  • DBADM authority for its database (tables and indexes only)
  • SYSADM or SYSCTRL authority

For a comment on a distinct type, stored procedure, trigger, or user-defined function, the privilege set that is defined below must include at least one of the following:

  • Ownership of the distinct type, stored procedure, trigger, or user-defined function
  • The ALTERIN privilege on the schema (for the addition of comments)
  • SYSADM or SYSCTRL authority

For a comment on a plan or package, the privilege set that is defined below must include at least one of the following:

  • Ownership of the plan or package
  • SYSADM or SYSCTRL authority

For a comment on a sequence, the privilege set that is defined below must include at least one of the following:

  • Ownership of the sequence
  • The ALTER privilege for the sequence
  • The ALTERIN privilege on the schema
  • SYSADM or SYSCTRL authority

Syntax

COMMENT ON
{
TABLE {table-name | view-name} 
    | COLUMN {table-name | view-name}.column-name 
    | ALIAS alias-name 
    | DISTINCT TYPE distinct-type-name 
    | FUNCTION function-name [(parameter-type[,...])] 
    | PROCEDURE procedure-name 
    | SPECIFIC FUNCTION specific-name 
    | TRIGGER trigger-name
    | PACKAGE collection-id.package-name [VERSION | version-id]
    | PLAN plan-name
    | PROCEDURE procedure-name
    | SEQUENCE sequence-name
}
IS string-constant

or:

COMMENT ON {table-name | view-name}
(column-name IS string-constant [,...])

Parameters:

table-name or view-name or alias-name Identifies the table, view or alias affected.
column-name Identifies the name of the column affected. When the COLUMN keyword is used, column names must be qualified with the name of the table or view that contains them.
string-constant Specifies the comment you want to place in the system table. The string may be up to 254 characters in length, enclosed in double or single quotation marks.

Description

The COMMENT ON statement is used for adding a comment to, or replacing an existing comment in the REMARKS column of the SYSIBM.SYSCOLUMNS or SYSIBM.SYSTABLES table. These comments prove very useful in documenting the purpose of tables, views, columns and aliases, and may be selected using a SELECT command, and incorporated into application documentation. Remarks columns are defined as character fields, with a maximum length of 254 characters.

ALIAS alias-name

The ALIAS clause is used to comment on an alias. If you use the COMMENT ON ALIAS format, the comment is placed in the REMARKS column of the SYSIBM.SYSTABLES table, in the row describing the specified table or view.

COLUMN table-name.column-name or view-name.column-name

Identifies the column to which the comment applies. The name must identify a column of a table or view that exists at the current server. The name must not identify a column of a declared temporary table. The comment is placed into the REMARKS column of the SYSIBM.SYSCOLUMNS catalog table, for the row that describes the column.

Do not use TABLE or COLUMN to comment on more than one column in a table or view. Give the table or view name and then, in parentheses, a list in the form:

  • column-name IS string-constant,
  • column-name IS string-constant,...

The column names must not be qualified, each name must identify a column of the specified table or view, and that table or view must exist at the current server.

DISTINCT TYPE distinct-type-name

Identifies the distinct type to which the comment applies. distinct-type-name must identify a distinct type that exists at the current server.

The comment is placed in the REMARKS column of the SYSIBM.SYSDATATYPES catalog table for the row that describes the distinct type.

FUNCTION

Identifies the function to which the comment applies. The function must exist at the current server, and it must be a function that was defined with the CREATE FUNCTION statement or a cast function that was generated by a CREATE DISTINCT TYPE statement. The comment is placed in the REMARKS column of the SYSIBM.SYSROUTINES catalog table for the row that describes the function.

If the function was defined with a table parameter (the LIKE TABLE was specified in the CREATE FUNCTION statement to indicate that one of the input parameters is a transition table), the function signature cannot be used to identify the function. Instead, identify the function with its function name, if unique, or with its specific name.

FUNCTION function-name

Identifies the particular function, and is valid only if there is exactly one function with function-name.

FUNCTION function-name (parameter-type,...)

Provides the function signature, which uniquely identifies the function.

function-name Identifies the name of the function.
(parameter-type,...) Identifies the parameters of the function.

The data types of the parameters must match the data types that were specified on the CREATE FUNCTION statement in the

corresponding position. The number of data types and the logical concatenation of the data types are used to identify the specific function.

For data types that have a length, precision, or scale attribute, you can specify a value or use a set of empty parentheses:

  • Empty parentheses indicate that DB2 ignores the attribute when determining whether the data types match.

    FLOAT cannot be specified with empty parentheses because its parameter value indicates different data types (REAL or DOUBLE).

  • If you use a specific value for a length, precision, or scale attribute, the value must exactly match the value that was specified (implicitly or explicitly) in the CREATE FUNCTION statement.

    The specific value for FLOAT(n) does not have exactly match the defined value of the source function because 1<=n<= 21 indicates REAL and 22<=n<=53 indicates DOUBLE. Matching is based on whether the data type is REAL or DOUBLE.

  • If length, precision, or scale is not explicitly specified, and empty parentheses are not specified, the default length of the data type is implied. For example:

    CHAR CHAR(1)

    GRAPHIC GRAPHIC(1)

    DECIMAL DECIMAL(5,0)

    FLOAT DOUBLE (length of 8)

The implicit length must exactly match the value that was specified (implicitly or explicitly) in the CREATE FUNCTION statement.

For data types with a subtype or encoding scheme attribute, specifying the FOR DATA clause or CCSID clause is optional. Omission of either clause indicates that DB2 ignores the attribute when determining whether the data types match. If you specify either clause, it must match the value that was implicitly or explicitly specified in the CREATE FUNCTION statement.

SPECIFIC FUNCTION specific-name

Identifies the particular function using the specific name either specified or defaulted to when the function was created.

PROCEDURE procedure-name

Identifies the stored procedure to which the comment applies. procedure-name must identify a stored procedure that has been defined with the CREATE PROCEDURE statement at the current server. The comment is placed in the REMARKS column of the SYSIBM.SYSROUTINES catalog table for the row that describes the stored procedure.

TABLE table-name or view-name

Identifies the table or view to which the comment applies. table-name or view-name must identify a table, auxiliary table, or view that exists at the current server. table-name must not identify a declared temporary table. The comment is placed in the REMARKS column of the SYSIBM.SYSTABLES catalog table for the row that describes the table or view.

TRIGGER trigger-name

Identifies the trigger to which the comment applies. trigger-name must identify a trigger that exists at the current server. The comment is placed in the REMARKS column of the SYSIBM.SYSTRIGGERS catalog table for the row that describes the trigger.

PACKAGE collection-id.package-name

Identifies the package to which the comment applies. You must qualify the package name with a collection ID. collection-id.package-name must identify a package that exists at the current server. The name plus the implicitly or explicitly specified version-id must identify a package that exists at the current server. Omitting version-id is an implicit specification of the null version.

PLAN plan-name

Identifies the plan to which the comment applies. plan-name must identify a plan that exists at the current server.

PROCEDURE procedure-name

Identifies the stored procedure to which the comment applies. procedure-name must identify a stored procedure that has been defined with the CREATE PROCEDURE statement at the current server. The comment is placed in the REMARKS column of the SYSIBM.SYSROUTINES catalog table for the row that describes the stored procedure.

SEQUENCE sequence-name

Identifies the sequence to which the comment applies. The combination of name and implicit or explicit schema name must identify an existing sequence at the current server. If no sequence by this name exists in the explicitly or implicitly specified schema, an error occurs. sequence-name must not be the name of an internal sequence object that is generated by the system for an identity column. The comment is placed in the REMARKS column of the SYSIBM.SYSSEQUENCES catalog table for the row that describes the sequence.

IS string-constant

Introduces the comment that you want to make. string-constant can be any SQL character string constant of up to 254 characters.

Examples:

To place a comment on the PART table, use a command like:

COMMENT ON TABLE part 
    IS "Information on all parts in use."

To place a comment on a view named redparts use a command like:

COMMENT ON TABLE redparts 
    IS "View of parts table containing info on all red parts in use."

To place a comment on the CITY column of the SUPPLIER table, use a command like:

COMMENT ON COLUMN supplier.city 
    IS "City where supplier is headquartered."

To place a comment on both the COMPANY and BALANCE columns of the CUSTOMER table, use the command below:

COMMENT ON customer 
    (company IS "Corporate name of the client", 
     balance IS "Outstanding as of last billing")