SQL Reference

COMMENT ON

The COMMENT ON statement adds or replaces comments in the catalog descriptions of various objects.

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared.

Authorization

The privileges that must be held by the authorization ID of the COMMENT ON statement must include one of the following:

SYSADM or DBADM
definer of the object (underlying table for column or constraint) as recorded in the DEFINER column of the catalog view for the object (OWNER column for a schema)
ALTERIN privilege on the schema (applicable only to objects allowing more than one-part names)
CONTROL privilege on the object (applicable to index, package, table and view objects only)
ALTER privilege on the object (applicable to table objects only)

Note that for table space or nodegroup the authorization ID must have SYSADM or SYSCTRL authority.

Syntax

>>-COMMENT ON--------------------------------------------------->
 
>-----+-| objects |--IS--string-constant----------------------------------+>
      |                      .-,-----------------------------------.      |
      |                      V                                     |      |
      '--+-table-name-+---(-----column-name--IS--string-constant---+---)--'
         '-view-name--'
 
>--------------------------------------------------------------><
 
objects
 
|--------+-ALIAS--alias-name--------------------------------------------+----->
         +-COLUMN--+-table-name.column-name-+---------------------------+
         |         '-view-name.column-name--'                           |
         +-CONSTRAINT--table-name.constraint-name-----------------------+
         +-FUNCTION----function-name--+-------------------------------+-+
         |                            '-(--   .-,-----------.         | |
         |                                 |-------------------+---)--' |
         |                                 '----data-type---+--'        |
         +-SPECIFIC FUNCTION--specific-name-----------------------------+
         +-INDEX--index-name--------------------------------------------+
         +-NODEGROUP--nodegroup-name------------------------------------+
         +-PACKAGE--package-name----------------------------------------+
         +-PROCEDURE--procedure-name--+-------------------------------+-+
         |                            '-(--   .-,-----------.         | |
         |                                 |-------------------+---)--' |
         |                                 '----data-type---+--'        |
         +-SPECIFIC PROCEDURE--specific-name----------------------------+
         +-SCHEMA--schema-name------------------------------------------+
         +-TABLE--+-table-name-+----------------------------------------+
         |        '-view-name--'                                        |
         +-TABLESPACE--tablespace-name----------------------------------+
         +-TRIGGER--trigger-name----------------------------------------+
         '-+----------------+---TYPE--type-name-------------------------'
           |          (1)   |
           '-DISTINCT-------'
 
>---------------------------------------------------------------|

Notes:

The keyword DATA can be used as a synonym for DISTINCT.

Description

ALIAS alias-name

Indicates a comment will be added or replaced for an alias. The alias-name must identify an alias that is described in the catalog (SQLSTATE 42704). The comment replaces the value of the REMARKS column of the SYSCAT.TABLES catalog view for the row that describes the alias.

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

Indicates a comment will be added or replaced for a column. The table-name.column-name or view-name.column-name combination must identify a column and table combination that is described in the catalog (SQLSTATE 42704). The comment replaces the value of the REMARKS column of the SYSCAT.COLUMNS catalog view for the row that describes the column.

A comment cannot be made on a column of an inoperative view. (SQLSTATE 51024).

CONSTRAINT table-name.constraint-name

Indicates a comment will be added or replaced for a constraint. The table-name.constraint-name combination must identify a constraint and the table that it constrains; they must be described in the catalog (SQLSTATE 42704). The comment replaces the value of the REMARKS column of the SYSCAT.TABCONST catalog view for the row that describes the constraint.

FUNCTION

Indicates a comment will be added or replaced for a function. The function instance specified must be a user-defined function described in the catalog.

There are several different ways available to identify the function instance:

FUNCTION function-name

Identifies the particular function, and is valid only if there is exactly one function with the function-name. The function thus identified may have any number of parameters defined for it. In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifer for an unqualified object name. In static SQL statements the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names. If no function by this name exists in the named or implied schema, an error (SQLSTATE 42704) is raised. If there is more than one specific instance of the function in the named or implied schema, an error (SQLSTATE 42854) is raised.

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

Provides the function signature, which uniquely identifies the function to be commented upon. The function selection algorithm is not used.

function-name

Gives the function name of the function to be commented upon. In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifer for an unqualified object name. In static SQL statements the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names.

(data-type,...)

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 for which to add or replace the comment.

If the data-type is unqualified, the type name is resolved by searching the schemas on the SQL path. This also applies to data type names specified for a REFERENCE type.

It is not necessary to specify the length, precision or scale for the parameterized data types. Instead an empty set of parentheses may be coded to indicate that these attributes are to be ignored when looking for a data type match.

FLOAT() cannot be used (SQLSTATE 42601) since the parameter value indicates different data types (REAL or DOUBLE).

However, if length, precision, or scale is coded, the value must exactly match that specified in the CREATE FUNCTION statement.

A type of FLOAT(n) does not need to match the defined value for n since 0 <n<25 means REAL and 24<n<54 means DOUBLE. Matching occurs based on whether the type is REAL or DOUBLE.

(Note that the FOR BIT DATA attribute is not considered part of the signature for matching purposes. So, for example, a CHAR FOR BIT DATA specified in the signature would match a function defined with CHAR only, and vice versa.)

If no function with the specified signature exists in the named or implied schema, an error (SQLSTATE 42883) is raised.

SPECIFIC FUNCTION specific-name

Indicates that comments will be added or replaced for a function (see FUNCTION for other methods of identifying a function). Identifies the particular user-defined function that is to be commented upon, using the specific name either specified or defaulted to at function creation time. In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifer for an unqualified object name. In static SQL statements the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names. The specific-name must identify a specific function instance in the named or implied schema; otherwise, an error (SQLSTATE 42704) is raised.

It is not possible to comment on a function that is either in the SYSIBM schema or the SYSFUN schema (SQLSTATE 42832).

The comment replaces the value of the REMARKS column of the SYSCAT.FUNCTIONS catalog view for the row that describes the function.

INDEX index-name

Indicates a comment will be added or replaced for an index. The index-name must identify a distinct index that is described in the catalog (SQLSTATE 42704). The comment replaces the value for the REMARKS column of the SYSCAT.INDEXES catalog view for the row that describes the index.

NODEGROUP nodegroup-name

Indicates a comment will be added or replaced for a nodegroup. The nodegroup-name must identify a distinct nodegroup that is described in the catalog (SQLSTATE 42704). The comment replaces the value for the REMARKS column of the SYSCAT.NODEGROUPS catalog view for the row that describes the nodegroup.

PACKAGE package-name

Indicates a comment will be added or replaced for a package. The package-name must identify a distinct package that is described in the catalog (SQLSTATE 42704). The comment replaces the value for the REMARKS column of the SYSCAT.PACKAGES catalog view for the row that describes the package.

PROCEDURE

Indicates a comment will be added or replaced for a procedure. The procedure instance specified must be a stored procedure described in the catalog.

There are several different ways available to identify the procedure instance:

PROCEDURE procedure-name

Identifies the particular procedure, and is valid only if there is exactly one procedure with the procedure-name in the schema. The procedure thus identified may have any number of parameters defined for it. In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifer for an unqualified object name. In static SQL statements the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names. If no procedure by this name exists in the named or implied schema, an error (SQLSTATE 42704) is raised. If there is more than one specific instance of the procedure in the named or implied schema, an error (SQLSTATE 42854) is raised.

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

This is used to provide the procedure signature, which uniquely identifies the procedure to be commented upon.

procedure-name

Gives the procedure name of the procedure to be commented upon. In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifer for an unqualified object name. In static SQL statements the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names.

(data-type,...)

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 for which to add or replace the comment.

If the data-type is unqualified, the type name is resolved by searching the schemas on the SQL path. This also applies to data type names specified for a REFERENCE type.

FLOAT() cannot be used (SQLSTATE 42601) since the parameter value indicates different data types (REAL or DOUBLE).

However, if length, precision, or scale is coded, the value must exactly match that specified in the CREATE PROCEDURE statement.

A type of FLOAT(n) does not need to match the defined value for n since 0<n<25 means REAL and 24<n<54 means DOUBLE. Matching occurs based on whether the type is REAL or DOUBLE.

If no procedure with the specified signature exists in the named or implied schema, an error (SQLSTATE 42883) is raised.

SPECIFIC PROCEDURE specific-name

Indicates that comments will be added or replaced for a procedure (see PROCEDURE for other methods of identifying a procedure). Identifies the particular stored procedure that is to be commented upon, using the specific name either specified or defaulted to at procedure creation time. In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifer for an unqualified object name. In static SQL statements the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names. The specific-name must identify a specific procedure instance in the named or implied schema; otherwise, an error (SQLSTATE 42704) is raised.

The comment replaces the value of the REMARKS column of the SYSCAT.PROCEDURES catalog view for the row that describes the procedure.

SCHEMA schema-name

Indicates a comment will be added or replaced for a schema. The schema-name must identify a schema that is described in the catalog (SQLSTATE 42704). The comment replaces the value of the REMARKS column of the SYSCAT.SCHEMATA catalog view for the row that describes the schema.

TABLE table-name or view-name

Indicates a comment will be added or replaced for a table or view. The table-name or view-name must identify a distinct table or view (not an alias) that is described in the catalog (SQLSTATE 42704). The comment replaces the value for the REMARKS column of the SYSCAT.TABLES catalog view for the row that describes the table or view.

TABLESPACE tablespace-name

Indicates a comment will be added or replaced for a table space. The tablespace-name must identify a distinct table space that is described in the catalog (SQLSTATE 42704). The comment replaces the value for the REMARKS column of the SYSCAT.TABLESPACES catalog view for the row that describes the table space.

TRIGGER trigger-name

Indicates a comment will be added or replaced for a trigger. The trigger-name must identify a distinct trigger that is described in the catalog (SQLSTATE 42704). The comment replaces the value for the REMARKS column of the SYSCAT.TRIGGERS catalog view for the row that describes the trigger.

TYPE type-name

Indicates a comment will be added or replaced for a user-defined type. The type-name must identify a user-defined type that is described in the catalog (SQLSTATE 42704). If DISTINCT is specified, type-name must identify a distinct type that is described in the catalog (SQLSTATE 42704). The comment replaces the value of the REMARKS column of the SYSCAT.DATATYPES catalog view for the row that describes the user-defined type.

In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifer for an unqualified object name. In static SQL statements the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names.

IS string-constant

Specifies the comment to be added or replaced. The string-constant can be any character string constant of up to 254 bytes. (Carriage return and line feed each count as 1 byte.)

table-name|view-name ( { column-name IS string-constant } ... )

This form of the COMMENT ON statement provides the ability to specify comments for multiple columns of a table or view. The column names must not be qualified, each name must identify a column of the specified base table or view, and the table or view must be described in the catalog.

A comment cannot be made on a column of an inoperative view (SQLSTATE 51024).

Examples

Example 1: Add a comment for the EMPLOYEE table.

  COMMENT ON TABLE EMPLOYEE  
   IS 'Reflects first quarter reorganization'

Example 2: Add a comment for the EMP_VIEW1 view.

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

Example 3: Add a comment for the EDLEVEL column of the EMPLOYEE table.

  COMMENT ON COLUMN EMPLOYEE.EDLEVEL
   IS 'highest grade level passed in school'

Example 4: Add comments for two different columns of the EMPLOYEE table.

  COMMENT ON EMPLOYEE
    (WORKDEPT IS 'see DEPARTMENT table for names',
     EDLEVEL IS 'highest grade level passed in school' )

Example 5: Pellow wants to comment on the CENTRE function, which he created in his PELLOW schema, using the signature to identify the specific function to be commented on.

  COMMENT ON FUNCTION CENTRE (INT,FLOAT)
     IS 'Frank''s CENTRE fctn, uses Chebychev method'

Example 6: McBride wants to comment on another CENTRE function, which she created in the PELLOW schema, using the specific name to identify the function instance to be commented on:

  COMMENT ON SPECIFIC FUNCTION PELLOW.FOCUS92 IS
     'Louise''s most triumphant CENTRE function, uses the
      Brownian fuzzy-focus technique'

Example 7: Comment on the function ATOMIC_WEIGHT in the CHEM schema, where it is known that there is only one function with that name:

  COMMENT ON FUNCTION CHEM.ATOMIC_WEIGHT
     IS 'takes atomic nbr, gives atomic weight'

Example 8: Eigler wants to comment on the SEARCH procedure, which he created in his EIGLER schema, using the signature to identify the specific procedure to be commented on.

  COMMENT ON PROCEDURE SEARCH (CHAR,INT)
     IS 'Frank''s mass search and replace algorithm'

Example 9: Macdonald wants to comment on another SEARCH function, which he created in the EIGLER schema, using the specific name to identify the procedure instance to be commented on:

  COMMENT ON SPECIFIC PROCEDURE EIGLER.DESTROY IS
     'Patrick''s mass search and destroy algorithm'

Example 10: Comment on the procedure OSMOSIS in the BIOLOGY schema, where it is known that there is only one procedure with that name:

  COMMENT ON PROCEDURE BIOLOGY.OSMOSIS
     IS 'Calculations modelling osmosis'

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

[ DB2 List of Books | Search the DB2 Books ]