COMMENT statement

The COMMENT statement adds or replaces comments in the descriptions of various objects in the Db2 catalog at the current server.

Invocation for COMMENT

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

Authorization for COMMENT

For a comment on the following objects, the privilege set must include at least one of the listed authorities or privileges:

Table, view, index, column, or alias for a table or view:

Ownership of the table, view, alias, or index
DBADM authority for its database (tables and indexes only)
SYSADM or SYSCTRL authority
System DBADM
SECADM authority (if the table has an activated row permission or column access control)

If the database is implicitly created, the database privileges must be on the implicit database or on DSNDB04.

User-defined type, global variable, stored procedure, trigger, or user-defined function:

Ownership of the object
The ALTERIN privilege on the schema (for the addition of comments)
SYSADM or SYSCTRL authority
System DBADM

Secure trigger or secure user-defined function:

SECADM authority
CREATE_SECURE_OBJECT privilege

Package:

Ownership of the package
The BINDAGENT privilege granted from the package owner
PACKADM authority for the collection or for all collections
SYSADM or SYSCTRL authority
System DBADM

Role or a trusted context:

Ownership of the object
SYSADM or SYSCTRL authority
SECADM

If the installation parameter SEPARATE SECURITY is NO, SYSADM authority has implicit SECADM and SYSCTRL authority and can drop a role or trusted context.

Sequence or alias for a sequence:

Ownership of the sequence
The ALTER privilege for the sequence if the target is a sequence
The ALTERIN privilege on the schema
SYSADM or SYSCTRL authority
System DBADM

The authorization ID that matches the schema name implicitly has the ALTERIN privilege on the schema.

Row permission or column mask:

SECADM authority

Privilege set:

If the statement is embedded in an application program, the privilege set is the privileges that are held by the owner of the plan or package. If the statement is dynamically prepared, the privilege set is determined by the DYNAMICRULES behavior in effect (run, bind, define, or invoke) and is summarized in Dynamic SQL statement preparation and execution. (For more information on these behaviors, including a list of the DYNAMICRULES bind option values that determine them, see Authorization IDs and dynamic SQL.)

Syntax for COMMENT

alias-designator

Notes:

¹ If PUBLIC is specified, FOR SEQUENCE must also be specified.

multiple-column-list

function-designator

parameter-type

Notes:

¹ AS LOCATOR can be specified only for a LOB data type or a distinct type that is based on a LOB data type.

data-type

built-in-type

Description for COMMENT

alias-designator

ALIAS alias-name: Indicates a comment will be added or replaced for an alias. The name must identify an alias that exists at the current server.
If the PUBLIC keyword is specified, alias-name must identify a public alias that exists at the current server. The comment is applied to a public alias.
FOR TABLE: Specifies that the alias is for a table or a view. The comment replaces the value of the REMARKS column of the SYSIBM.SYSTABLES catalog table for the row that describes the alias.
FOR SEQUENCE: Specifies that the alias is for a sequence. The comment replaces the value of the REMARKS column of the SYSIBM.SYSSEQUENCES catalog table for the row that describes the alias.

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.

FUNCTION or SPECIFIC 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 TYPE statement. The comment is placed in the REMARKS column of the SYSIBM.SYSROUTINES catalog table for the row that describes the function.

The function can be identified by its name, function signature, or specific name. 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), you must identify the function with its function name, if it is unique, or with its specific name.

FUNCTION function-name

Identifies the function by its function name. There must be exactly one function with function-name in the schema. The function can have any number of input parameters. If the schema does not contain a function with function-name, or if the schema contains more than one function with this name, and error is returned.

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

Identifies the SQL function by its function signature, which uniquely identifies the function. A function with the function signature must exist in the explicitly or implicitly specified schema.

If function-name() is specified, the function that is identified must have zero parameters.

function-name

Identifies the name of the function. If the function was defined with a table parameter (the LIKE TABLE name AS LOCATOR clause 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 uniquely identify the function. Instead, use one of the other syntax variations to identify the function with its function name, if unique, or with its specific name.

(parameter-type,...)

Specifies the number of input parameters of the function and the name and data type of each parameter.

(data-type,...)

Identifies the number of input parameters of the function and the data type of each parameter. The data type of each parameter must match the data type that was specified in the CREATE FUNCTION statement for the parameter in the corresponding position. The number of data types and the logical concatenation of the data types are used to uniquely identify the function.

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

Empty parentheses indicate that Db2 is to ignore the attribute when determining whether the data types match.
For example, DEC() will be considered a match for a parameter of a function defined with a data type of DEC(7,2). Similarly DECFLOAT() will be considered a match for DECFLOAT(16) or DECFLOAT(34).

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 to 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. 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 subtype DATA clause or the CCSID clause is optional. Omission of either clause indicates that Db2 is to ignore 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.

AS LOCATOR

Specifies that the function is defined to receive a locator for this parameter. If AS LOCATOR is specified, the data type must be a LOB or a distinct type based on a LOB.

SPECIFIC FUNCTION specific-name

Identifies a particular user-defined function by its specific name. The name is implicitly or explicitly qualified with a schema name. A function with the specific name must exist in the schema. If the specific name is not qualified, it is implicitly qualified with a schema name as described in the description for FUNCTION function-name.

ACTIVE VERSION: Specifies that the comment applies to the currently active version of the routine that is specified by function-name.
ACTIVE VERSION is the default.
VERSION routine-version-id: Specifies that the comment applies only to the version of the routine that is identified by routine-version-id. routine-version-id must identify a version of the specified routine that already exists at the current server.

INDEX index-name

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

MASK mask-name

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

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. Omission of the version-id is an implicit specification of the null version.

The name must not identify a trigger package or a package that is associated with an SQL routine. Specify this clause to comment on a package that was created as the result of a BIND COPY command used to deploy a version of a native SQL procedure.

VERSION version-id

version-id is the version identifier that was assigned to the package's DBRM when the DBRM was created. If version-id is not specified, a null version is used as the version identifier.

Delimit the version identifier when it:

Is generated by the VERSION(AUTO) precompiler option
Begins with a digit
Contains lowercase or mixed-case letters

For more on version identifiers, see the information on preparing an application program for execution in Creating a package version.

PERMISSION permission-name

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

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 procedure to which the comment applies. procedure-name must identify a procedure that exists at the current server.

ACTIVE VERSION: Specifies that the comment applies to the currently active version of the routine that is specified by procedure-name.
ACTIVE VERSION is the default.
VERSION routine-version-id: Specifies that the comment applies only to the version of the routine that is identified by routine-version-id. routine-version-id must identify a version of the specified routine that already exists at the current server.

ROLE role-name

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

SEQUENCE sequence-name

Identifies the sequence to which the comment applies.

sequence-name must identify a sequence that exists at the current server. sequence-name must not be the name of an internal sequence object that is used by Db2. The comment is placed in the REMARKS column of the SYSIBM.SYSSEQUENCES catalog table for the row that describes the sequence.

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 or version of the trigger. Start of change

ACTIVE VERSION

Specifies that the comment applies to the currently active version of the trigger that is specified by trigger-name.

ACTIVE VERSION is the default.

Start of change ACTIVE VERSION must only be specified for an advanced trigger. End of change

VERSION trigger-version-id

FL 500Specifies that the comment applies only to the version of the trigger that is identified by trigger-version- id. trigger-version-id must identify a version of the specified trigger that already exists at the current server.

VERSION must only be specified for an advanced trigger.

TRUSTED CONTEXT context-name

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

TYPE type-name

Identifies the user-defined type to which the comment applies. type-name must identify a user-defined 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 user-defined type.

VARIABLE variable-name

Identifies the global variable to which the comment applies. variable-name must identify a global variable that exists at the current server. variable-name must not identify a built-in global variable.

IS string-constant

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

multiple-column-list

To comment on more than one column in a table or view with a single COMMENT statement, specify the table or view name, followed by a list in parentheses of the form:

(column-name IS string-constant, 
 column-name IS string-constant, 
  ...)

Each column name must not be qualified, and must identify a column of the specified table or view that exists at the current server.

Notes for COMMENT

Alternative syntax and synonyms:

To provide compatibility with previous releases of Db2 or other products in the Db2 family, Db2 supports the following syntax alternatives:

DATA TYPE or DISTINCT TYPE as a synonym for TYPE
COMMENT ON ALIAS SYSPUBLIC.name can be specified as an alternative to COMMENT ON PUBLIC ALIAS SYSPUBLIC.name

Examples for COMMENT

Example 1: Enter a comment on table DSN8C10.EMP.

   COMMENT ON TABLE DSN8C10.EMP
     IS 'REFLECTS 1ST QTR 81 REORG';

Example 2: Enter a comment on view DSN8C10.VDEPT.

   COMMENT ON TABLE DSN8C10.VDEPT
     IS 'VIEW OF TABLE DSN8C10.DEPT';

Example 3: Enter a comment on the DEPTNO column of table DSN8C10.DEPT.

   COMMENT ON COLUMN DSN8C10.DEPT.DEPTNO
     IS 'DEPARTMENT ID - UNIQUE';

Example 4: Enter comments on the two columns in table DSN8C10.DEPT.

   COMMENT ON DSN8C10.DEPT
     (MGRNO IS 'EMPLOYEE NUMBER OF DEPARTMENT MANAGER',
      ADMRDEPT IS 'DEPARTMENT NUMBER OF ADMINISTERING DEPARTMENT');

Example 5: Assume that you are SMITH and that you created the distinct type DOCUMENT in schema SMITH. Enter comments on DOCUMENT.

   COMMENT ON TYPE DOCUMENT
     IS 'CONTAINS DATE, TABLE OF CONTENTS, BODY, INDEX, and GLOSSARY';

Example 6: Assume that you are SMITH and you know that ATOMIC_WEIGHT is the only function with that name in schema CHEM. Enter comments on ATOMIC_WEIGHT.

   COMMENT ON FUNCTION CHEM.ATOMIC_WEIGHT
     IS 'TAKES ATOMIC NUMBER AND GIVES ATOMIC WEIGHT';

Example 7: Assume that you are SMITH and that you created the function CENTER in schema SMITH. Enter comments on CENTER, using the signature to uniquely identify the function instance.

   COMMENT ON FUNCTION CENTER (INTEGER, FLOAT)
     IS 'USES THE CHEBYCHEV METHOD';

Example 8: Assume that you are SMITH and that you created another function named CENTER in schema JOHNSON. You gave the function the specific name FOCUS97. Enter comments on CENTER, using the specific name to identify the function instance.

   COMMENT ON SPECIFIC FUNCTION JOHNSON.FOCUS97
     IS 'USES THE SQUARING TECHNIQUE';

Example 9: Assume that you are SMITH and that procedure OSMOSIS is in schema BIOLOGY. Enter comments on OSMOSIS. Your comments will apply to the currently active version of the procedure OSMOSIS.

   COMMENT ON PROCEDURE BIOLOGY.OSMOSIS
     IS 'CALCULATIONS THAT MODEL OSMOSIS';

Example 11: Assume that you are SMITH and that trigger BONUS is in your schema. Enter comments on BONUS.

   COMMENT ON TRIGGER BONUS
     IS 'LIMITS BONUSES TO 10% OF SALARY';

Example 12: Provide a comment for package MYPKG, which is in collection COLLIDA.

   COMMENT ON COLLIDA.MYPKG
     IS 'THIS IS MY PACKAGE';

Example 14: Provide a comment on role ROLE1:

   COMMENT ON ROLE ROLE1 
     IS 'Role defined for trusted context, ctx1';

Example 15: Provide a comment on trusted context CTX1:

   COMMENT ON TRUSTED CONTEXT CTX1 
     IS 'WEBSPHERE SERVER';

Example 15: Provide a comment on column mask M1:

   COMMENT ON MASK M1 
     IS 'Column mask for column EMP.SALARY';