COMMENT ON

The COMMENT ON statement adds or replaces comments in the catalog descriptions of tables, views, aliases, user-defined types, functions, packages, parameters, procedures, indexes, or columns.

Invocation

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

Authorization

To comment on a table, view, alias, index, column, user-defined type, or package, the privileges held by the authorization ID of the statement must include at least one of the following:

For the table, view, alias, index, user-defined type, or package in the statement,
- The ALTER privilege on the table, view, alias, index, user-defined type, or package, and
- The system authority *EXECUTE on the library that contains the table, view, alias, index, user-defined type, or package
Administrative authority

The authorization ID of the statement has the ALTER privilege on the table, view, alias, index, user-defined type, or package when:

It is the owner of the table, view, alias, index, user-defined type, or package
It has been granted the ALTER privilege to the table, view, alias, user-defined type, or package, or
It has been granted the system authorities of either *OBJALTER or *OBJMGT to the table, view, alias, index, user-defined type, or package

To comment on a function, the privileges held by the authorization ID of the statement must include at least one of the following:

For the SYSFUNCS catalog view:
- The UPDATE privilege on the view, and
- The system authority *EXECUTE on library QSYS2
Administrative authority

To comment on a procedure, the privileges held by the authorization ID of the statement must include at least one of the following:

For the SYSPROCS catalog view:
- The UPDATE privilege on the view, and
- The system authority *EXECUTE on library QSYS2
Administrative authority

To comment on a parameter, the privileges held by the authorization ID of the statement must include at least one of the following:

For the SYSPARMS catalog table:
- The UPDATE privilege on the table, and
- The system authority *EXECUTE on library QSYS2
Administrative authority

The authorization ID of the statement has the UPDATE privilege on a table when:

It is the owner of the table,
It has been granted the UPDATE privilege on the table, or
It has been granted the system authorities of *OBJOPR and *UPD on the table.

>>-COMMENT ON---------------------------------------------------> >-----+--+-ALIAS--alias-name-------------------------------------------------------+---IS--string-constant--+> | +-COLUMN--+-table-name.column-name-+--------------------------------------+ | | | '-view-name.column-name--' | | | +-+---------------+---TYPE--type-name-------------------------------------+ | | | | (1) | | | | | '-DISTINCT------' | | | +--+-FUNCTION-+--function-name--+------------------------------------+----+ | | | '-ROUTINE--' '-(--+------------------------+---)--' | | | | | .-,----------------. | | | | | | V | | | | | | '----parameter-type---+--' | | | +-INDEX--index-name-------------------------------------------------------+ | | | (2) | | | +-PACKAGE-------package-name----------------------------------------------+ | | +-PARAMETER--+-routine-name.parameter-name-----------------------------+--+ | | | '-SPECIFIC--+-FUNCTION--+---specific-name.parameter-name--' | | | | +-PROCEDURE-+ | | | | '-ROUTINE---' | | | +--+-PROCEDURE-+---procedure-name--+------------------------------------+-+ | | | '-ROUTINE---' '-(--+------------------------+---)--' | | | | | .-,----------------. | | | | | | V | | | | | | '----parameter-type---+--' | | | +-SPECIFIC--+-FUNCTION--+---specific-name---------------------------------+ | | | +-PROCEDURE-+ | | | | '-ROUTINE---' | | | '-TABLE--+-table-name-+---------------------------------------------------' | | '-view-name--' | +-multiple-columns------------------------------------------------------------------------------------+ '-multiple-parameters---------------------------------------------------------------------------------' >-------------------------------------------------------------->< multiple-columns .-,-----------------------------------. .-COLUMN-. V | |---+--------+----+-table-name-+---(-----column-name--IS--string-constant---+---)--> '-view-name--' >---------------------------------------------------------------| multiple-parameters |---PARAMETER--+-SPECIFIC--+-FUNCTION--+---specific-name-----------------------------+-> | +-PROCEDURE-+ | | '-ROUTINE---' | | .-ROUTINE---. | '-+-----------+--routine-name--+------------------------------------+-' +-FUNCTION--+ '-(--+------------------------+---)--' '-PROCEDURE-' | .-,----------------. | | V | | '----parameter-type---+--' .-,--------------------------------------. V | >---(-----parameter-name--IS--string-constant---+---)-----------|

Notes:

The keyword DATA can be used when commenting on any user-defined
type.

The keyword PROGRAM can be used as a synonym for PACKAGE.

parameter-type |--+---BIGINT-------------------------------------------------------------------------------------------------------+-> +-+-INTEGER-+----------------------------------------------------------------------------------------------------+ | '-INT-----' | +---SMALLINT-----------------------------------------------------------------------------------------------------+ +--+-DECIMAL-+---+------------------------------------+----------------------------------------------------------+ | +-DEC-----+ | (1) | | | '-NUMERIC-' '-(--integer--+-----------+--)-------' | | '-, integer-' | +-+-FLOAT--+---------------------+-+-----------------------------------------------------------------------------+ | | | (1) | | | | | '-(--integer--)-------' | | | +-REAL---------------------------+ | | '-DOUBLE--+-----------+----------' | | '-PRECISION-' | +----+-BLOB----------------+-----+-----------------------------+---+------------+--------------------------------+ | '-BINARY LARGE OBJECT-' | (1) | '-AS LOCATOR-' | | '-(--integer--+---+---)-------' | | +-K-+ | | '-M-' | +--+--+--+-CHARACTER-+---+---------------------+------------------+---+-----------------+----------------------+-+ | | | '-CHAR------' | (1) | | +-FOR BIT DATA----+ | | | | | '-(--integer--)-------' | +-FOR SBCS DATA---+ | | | | | (1) | +-FOR MIXED DATA--+ | | | | '-----+-VARCHAR-------------------+---(--integer--)---------' '-CCSID--integer--' | | | | '--+-CHARACTER-+---VARYING--' | | | | '-CHAR------' | | | '----+-CLOB-------------------+-----+-----------------------------+---+-----------------+---+------------+--' | | +-CHAR LARGE OBJECT------+ | (1) | +-FOR BIT DATA----+ '-AS LOCATOR-' | | '-CHARACTER LARGE OBJECT-' '-(--integer--+---+---)-------' +-FOR SBCS DATA---+ | | +-K-+ +-FOR MIXED DATA--+ | | '-M-' '-CCSID--integer--' | +--+--+----GRAPHIC-----+---------------------+----------+---+-----------------+-------------+--------------------+ | | | | (1) | | '-CCSID--integer--' | | | | | '-(--integer--)-------' | | | | | | (1) | | | | | '-----+-VARGRAPHIC------+---(--integer--)---------' | | | | '-GRAPHIC VARYING-' | | | '----DBCLOB------+-----------------------------+---+-----------------+---+------------+--' | | | (1) | '-CCSID--integer--' '-AS LOCATOR-' | | '-(--integer--+---+---)-------' | | +-K-+ | | '-M-' | +---DATE---------------------------------------------------------------------------------------------------------+ +---TIME---------------------------------------------------------------------------------------------------------+ +---TIMESTAMP----------------------------------------------------------------------------------------------------+ +---DATALINK--+---------------------+---+-----------------+------------------------------------------------------+ | | (1) | '-CCSID--integer--' | | '-(--integer--)-------' | '-distinct-type--+-----------------+-----------------------------------------------------------------------------' | (2) | '-AS LOCATOR------' >---------------------------------------------------------------|

Notes:

The values that are specified for length, precision, or scale attributes
must match the values that were specified when the function or procedure was
created. Coding specific values is optional. Empty parentheses,
(), can be used instead to indicate that DB2 ignores the attributes when
determining whether data types match.

AS LOCATOR can be specified only for distinct type based on a LOB data
type.

Description

ALIAS

Indicates that a comment will be added to or replaced for an alias.

alias-name: Identifies the alias to which the comment applies. The name must identify an alias that exists at the current server.

COLUMN

Indicates that a comment will be added to or replaced for a column.

table-name.column-name or view-name.column-name: Identifies the column to which the comment applies. The table-name or view-name must identify a table or view that exists at the current server, and the column-name must identify a column of that table or view.

TYPE

Indicates that a comment will be added to or replaced for a user-defined type.

type-name: Identifies the user-defined type to which the comment applies. The name must identify a user-defined type that exists at the current server.

FUNCTION

Indicates that a comment will be added to or replaced for a function. Identifies the function to which the comment applies. You can identify the particular function by its name, function signature, or specific name. The rules for function resolution (and the SQL path) are not used.

FUNCTION function-name

The function-name must identify exactly one function that exists at the current server. The function may have any number of parameters defined for it. If there is more than one function of the specified name in the specified or implicit collection, an error is returned.

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

The function-name (parameter-type, ...) must identify a function with the specified function signature that exists at the current server. The specified 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 is used to identify the specific function instance to which the comment is applied. If function-name () is specified, the function identified must have zero parameters.

If an unqualified distinct type name is specified, DB2 searches the SQL path to resolve the collection name for the distinct type.

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.
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.
If length, precision, or scale is not explicitly specified, and empty parentheses are not specified, the default attributes of the data type are implied. For more information on the default attributes see CREATE TABLE.

For data types with a subtype or CCSID 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

The specific-name must identify a specific function that exists at the current server.

INDEX

Indicates that a comment will be added to or replaced for an index.

index-name: Identifies the index to which the comment applies. The name must identify an index that exists at the current server.

PACKAGE

Indicates that a comment will be added to or replaced for a package.

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

PARAMETER

Indicates that a comment will be added to or replaced for a parameter.

routine-name.parameter-name: Identifies the parameter to which the comment applies. The parameter could be for a procedure or a function. The routine-name must identify a procedure or function that exists at the current server, and the parameter-name must identify a parameter of that procedure or function.
specific-name.parameter-name: Identifies the parameter to which the comment applies. The parameter could be for a procedure or a function. The specific-name must identify a procedure or function that exists at the current server, and the parameter-name must identify a parameter of that procedure or function.

PROCEDURE

Indicates that a comment will be added to or replaced for a procedure. Identifies the procedure to which the comment applies. You can identify the particular procedure by its name, procedure signature, or specific name. The rules for procedure resolution (and the SQL path) are not used.

PROCEDURE procedure-name

The procedure-name must identify exactly one procedure that exists at the current server. The procedure may have any number of parameters defined for it. If there is more than one procedure of the specified name in the specified or implicit collection, an error is returned.

PROCEDURE procedure-name (parameter-type, ...)

The procedure-name (parameter-type, ...) must identify a procedure with the specified procedure signature that exists at the current server. The specified parameters must match the data types that were specified on the CREATE PROCEDURE statement in the corresponding position. The number of data types and the logical concatenation of the data types is used to identify the specific procedure instance which is to be dropped. If procedure-name () is specified, the procedure identified must have zero parameters.

If an unqualified distinct type name is specified, DB2 searches the SQL path to resolve the collection name for the distinct type.

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.
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 PROCEDURE statement.
If length, precision, or scale is not explicitly specified, and empty parentheses are not specified, the default attributes of the data type is implied. For more information on the default attributes see CREATE TABLE.

SPECIFIC PROCEDURE specific-name

The specific-name must identify a specific procedure that exists at the current server.

TABLE

Indicates that a comment will be added to or replaced for a table or view.

table-name or view-name: Identifies the table or view to which the comment applies. The name must identify a table or view that exists at the current server.

IS

Introduces the comment that you want to make.

string-constant: Can be any character-string constant of up to 2000 characters. The constant may contain SBCS or DBCS characters.

multiple-columns

To comment on more than one column in a table or view, specify the table or view name and then, in parenthesis, a list of the form:

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

The column name 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.

multiple-parameters

To comment on more than one parameter in a procedure or function, specify the procedure name, function name, or specific name, and then, in parenthesis, a list of the form:

   parameter-name IS string-constant,
   parameter-name IS string-constant, ...

The parameter name must not be qualified, each name must identify a parameter of the specified procedure or function, and that procedure or function must exist at the current server.

Examples

Example 1

Insert a comment for the EMPLOYEE table.

   COMMENT ON TABLE EMPLOYEE
     IS 'Reflects first quarter 1981 reorganization'

Example 2

Insert a comment for the EMP_VIEW1 view.

   COMMENT ON TABLE EMP_VIEW1
     IS 'View of the EMPLOYEE table without salary information'

Example 3

Insert a comment for the EMPNO column of the EMPLOYEE table.

   COMMENT ON COLUMN EMPLOYEE.EMPN
     IS 'Highest grade level passed in school'

Example 4

Enter comments on two columns in the CORPDATA.DEPARTMENT table.

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

Example 5

Insert a comment for the CORPDATA.PAYROLL package.

   COMMENT ON PACKAGE CORPDATA.PAYROLL
     IS 'This package is used for distributed payroll processing.'

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]