COMMENT
The COMMENT statement adds or replaces comments in the descriptions of various objects in the DB2® catalog at the current server.
Invocation
This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.
Authorization
For a comment on the following objects, the privilege set must include at least one of the listed authorities or privileges:
- Table, view, alias, index, or column:
- 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.
- Distinct type, stored procedure, trigger, or user-defined function:
- 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
- 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:
- Ownership of the sequence
- The ALTER privilege for the 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 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
>>-COMMENT ON---------------------------------------------------> >--+-+-ALIAS--alias-name------------------------------------------------+--IS--string-constant-+->< | +-COLUMN--+-table-name-+--.column-name-----------------------------+ | | | '-view-name--' | | | | .-ACTIVE VERSION--------------. | | | +-| function-designator |--+-----------------------------+---------+ | | | '-VERSION--routine-version-id-' | | | +-INDEX--index-name------------------------------------------------+ | | +-PACKAGE--collection-id.package-name--+-------------------------+-+ | | | | .-VERSION-. | | | | | '-+---------+--version-id-' | | | +-PLAN--plan-name--------------------------------------------------+ | | | .-ACTIVE VERSION--------------. | | | +-PROCEDURE--procedure-name--+-----------------------------+-------+ | | | '-VERSION--routine-version-id-' | | | +-ROLE--role-name--------------------------------------------------+ | | +-SEQUENCE--sequence-name------------------------------------------+ | | +-TABLE--+-table-name-+--------------------------------------------+ | | | '-view-name--' | | | +-TRIGGER--trigger-name--------------------------------------------+ | | +-TRUSTED CONTEXT--context-name------------------------------------+ | | +-TYPE--distinct-type-name-----------------------------------------+ | | +-MASK--mask-name--------------------------------------------------+ | | '-PERMISSION--permission-name--------------------------------------' | '-| multiple-column-list |------------------------------------------------------------------'
multiple-column-list
.-,--------------------------------. V | >>-+-table-name-+--(----column-name--IS--string-constant-+--)-->< '-view-name--'
function-designator
>>-+-FUNCTION--function-name--+--------------------------------------+-+->< | | .-,--------------------------. | | | | V | | | | '-(----+------------------------+-+--)-' | | '-+--------------------+-' | | '-| parameter-type |-' | | | | | '-SPECIFIC FUNCTION--specific-name----------------------------------'
parameter-type
>>-| data-type |--+----------------+--------------------------->< | (1) | '-AS LOCATOR-----'
- 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 |--+-------------------------------------->< '-distinct-type-name-'
built-in-type
>>-+-+-SMALLINT----+-------------------------------------------------------------------------------------------------+->< | +-+-INTEGER-+-+ | | | '-INT-----' | | | '-BIGINT------' | | .-(5,0)--------------------. | +-+-DECIMAL-+--+--------------------------+-----------------------------------------------------------------------+ | +-DEC-----+ '-(integer-+-----------+-)-' | | '-NUMERIC-' '-, integer-' | | .-(53)------. | +-+-FLOAT--+-----------+--+---------------------------------------------------------------------------------------+ | | '-(integer)-' | | | +-REAL------------------+ | | | .-PRECISION-. | | | '-DOUBLE--+-----------+-' | | .-(34)-. | +-DECFLOAT--+------+----------------------------------------------------------------------------------------------+ | '-(16)-' | | .-(1)-------. | +-+-+-+-CHARACTER-+--+-----------+----------+----+--------------------+--+----------------------+---------------+-+ | | | '-CHAR------' '-(integer)-' | '-CCSID--+-ASCII---+-' '-FOR--+-SBCS--+--DATA-' | | | | '-+-+-CHARACTER-+--VARYING-+--(integer)-' +-EBCDIC--+ +-MIXED-+ | | | | | '-CHAR------' | '-UNICODE-' '-BIT---' | | | | '-VARCHAR----------------' | | | | .-(1M)-------------. | | | '-+-+-CHARACTER-+--LARGE OBJECT-+--+------------------+----+--------------------+--+----------------------+---' | | | '-CHAR------' | '-(integer-+---+-)-' '-CCSID--+-ASCII---+-' '-FOR--+-SBCS--+--DATA-' | | '-CLOB------------------------' +-K-+ +-EBCDIC--+ '-MIXED-' | | +-M-+ '-UNICODE-' | | '-G-' | | .-(1)-------. | +-+-GRAPHIC--+-----------+-------+--+--------------------+--------------------------------------------------------+ | | '-(integer)-' | '-CCSID--+-ASCII---+-' | | +-VARGRAPHIC--(--integer--)----+ +-EBCDIC--+ | | | .-(1M)-------------. | '-UNICODE-' | | '-DBCLOB--+------------------+-' | | '-(integer-+---+-)-' | | +-K-+ | | +-M-+ | | '-G-' | | .-(1)-------. | +-+-BINARY--+-----------+-------------------------+---------------------------------------------------------------+ | | '-(integer)-' | | | +-+-BINARY VARYING-+-(integer)------------------+ | | | '-VARBINARY------' | | | | .-(1M)-------------. | | | '-+-BINARY LARGE OBJECT-+--+------------------+-' | | '-BLOB----------------' '-(integer-+---+-)-' | | +-K-+ | | +-M-+ | | '-G-' | +-+-DATE------------------------------------------------+---------------------------------------------------------+ | +-TIME------------------------------------------------+ | | | .-(--6--)-------. .-WITHOUT TIME ZONE-. | | | '-TIMESTAMP--+---------------+--+-------------------+-' | | '-(--integer--)-' '-WITH TIME ZONE----' | '-ROWID-----------------------------------------------------------------------------------------------------------'
Description
- ALIAS alias-name
- Identifies the alias to which the comment applies. alias-name must identify an alias that exists at the current server. The comment is placed in the REMARKS column of the SYSIBM.SYSTABLES 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.
- Empty parentheses indicate that DB2 is
to ignore the attribute when determining whether the data types match.
- 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 DB2 Application Programming and SQL Guide.
- 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.
- 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 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.
- 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
- 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
Examples
COMMENT ON TABLE DSN8A10.EMP
IS 'REFLECTS 1ST QTR 81 REORG';
COMMENT ON TABLE DSN8A10.VDEPT
IS 'VIEW OF TABLE DSN8A10.DEPT';
COMMENT ON COLUMN DSN8A10.DEPT.DEPTNO
IS 'DEPARTMENT ID - UNIQUE';
COMMENT ON DSN8A10.DEPT
(MGRNO IS 'EMPLOYEE NUMBER OF DEPARTMENT MANAGER',
ADMRDEPT IS 'DEPARTMENT NUMBER OF ADMINISTERING DEPARTMENT');
COMMENT ON TYPE DOCUMENT
IS 'CONTAINS DATE, TABLE OF CONTENTS, BODY, INDEX, and GLOSSARY';
COMMENT ON FUNCTION CHEM.ATOMIC_WEIGHT
IS 'TAKES ATOMIC NUMBER AND GIVES ATOMIC WEIGHT';
COMMENT ON FUNCTION CENTER (INTEGER, FLOAT)
IS 'USES THE CHEBYCHEV METHOD';
COMMENT ON SPECIFIC FUNCTION JOHNSON.FOCUS97
IS 'USES THE SQUARING TECHNIQUE';
COMMENT ON PROCEDURE BIOLOGY.OSMOSIS
IS 'CALCULATIONS THAT MODEL OSMOSIS';
COMMENT ON TRIGGER BONUS
IS 'LIMITS BONUSES TO 10% OF SALARY';
COMMENT ON COLLIDA.MYPKG
IS 'THIS IS MY PACKAGE';
COMMENT ON ROLE ROLE1
IS 'Role defined for trusted context, ctx1';
COMMENT ON TRUSTED CONTEXT CTX1
IS 'WEBSPHERE SERVER';
COMMENT ON MASK M1
IS 'Column mask for column EMP.SALARY';