COMMENT

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

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, column, type, package, sequence, variable, or XSR object, the privileges held by the authorization ID of the statement must include at least one of the following:

  • For the table, view, alias, type, package, sequence, variable, or XSR object in the statement,
    • The ALTER privilege on the table, view, alias, type, package, sequence, variable, or XSR object, and
    • The USAGE privilege on the schema that contains the table, view, alias, index, type, package, sequence, variable, or XSR object
  • Administrative authority

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

  • For the subject table of the constraint or trigger in the statement:
    • The ALTER privilege on the subject table, and
    • The USAGE privilege on the schema that contains the subject table
  • Database administrator authority

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

  • For the index identified in the statement,
    • The system authority *OBJALTER on the index, and
    • The USAGE privilege on the schema containing the index.
  • Administrative authority

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 and SYSROUTINES catalog view and table:
    • The UPDATE privilege on SYSROUTINES,
    • The system authority *OBJOPR on SYSFUNCS, and
    • The USAGE privilege on schema 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 and SYSROUTINES catalog view and table:
    • The UPDATE privilege on SYSROUTINES,
    • The system authority *OBJOPR on SYSPROCS, and
    • The USAGE privilege on schema 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 USAGE privilege on schema QSYS2
  • Administrative authority
To comment on a mask or permission:
  • The authorization ID of the statement must be authorized to the Database Security Administrator function of IBM® i. See Administrative authority.

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

  • *USE authority to the Change Data Area (CHGDTAARA), CL command
  • Administrative authority

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

  • For the SYSVARIABLES catalog table:
    • The UPDATE privilege on SYSVARIABLES, and
    • The USAGE privilege on schema QSYS2
  • Administrative authority

To comment on an XSR object, the privileges held by the authorization ID of the statement must also include at least one of the following:

  • For the XSROBJECTS catalog table:
    • The UPDATE privilege on XSROBJECTS, and
    • The USAGE privilege on schema QSYS2
  • Administrative authority

For information about the system authorities corresponding to SQL privileges, see Corresponding System Authorities When Checking Privileges to a Table or View, Corresponding System Authorities When Checking Privileges to a User-defined Type, Corresponding System Authorities When Checking Privileges to a Sequence, Corresponding System Authorities When Checking Privileges to a Variable , Corresponding System Authorities When Checking Privileges to a Package, and Corresponding System Authorities When Checking Privileges to an XSR object.

Syntax

Read syntax diagramSkip visual syntax diagramCOMMENTONALIASalias-nameCOLUMNtable-name.column-nameview-name.column-nameCONSTRAINTconstraint-nameFUNCTIONROUTINEfunction-name(,parameter-type)SPECIFICFUNCTIONROUTINEspecific-nameINDEXindex-nameMASKmask-namePACKAGEpackage-nameVERSIONversion-idPARAMETERroutine-name.parameter-nameSPECIFICFUNCTIONPROCEDUREROUTINEspecific-name.parameter-namePERMISSIONpermission-namePROCEDUREROUTINEprocedure-name(,parameter-type)SPECIFICPROCEDUREROUTINEspecific-nameSEQUENCEsequence-nameTABLEtable-nameview-nameTRIGGERtrigger-nameTYPEdistinct-type-namearray-type-nameVARIABLEvariable-nameXSROBJECTxsrobject-nameISstring-constantmultiple-column-listmultiple-parameter-list
multiple-column-list
Read syntax diagramSkip visual syntax diagram COLUMN table-nameview-name ( ,column-nameISstring-constant )
multiple-parameter-list
Read syntax diagramSkip visual syntax diagramPARAMETER SPECIFICFUNCTIONPROCEDUREROUTINEspecific-nameROUTINEFUNCTIONPROCEDUREroutine-name(,parameter-type) ( ,parameter-nameISstring-constant )
parameter-type
Read syntax diagramSkip visual syntax diagramdata-typeAS LOCATOR
data-type
Read syntax diagramSkip visual syntax diagrambuilt-in-typedistinct-type-namearray-type-name
built-in-type
Read syntax diagramSkip visual syntax diagramSMALLINTINTEGERINTBIGINTDECIMALDECNUMERICNUM(5,0)(integer,0, integer)FLOAT(53)(integer)REALDOUBLEPRECISIONDECFLOAT(34)(16)CHARACTERCHAR(1)(integer)CHARACTERCHARVARYINGVARCHAR(integer)FOR BIT DATAFOR SBCS DATAFOR MIXED DATAccsid-clauseCHARACTERCHARLARGE OBJECTCLOB(1M)(integerKMG)FOR SBCS DATAFOR MIXED DATAccsid-clauseGRAPHIC(1)(integer)GRAPHIC VARYINGVARGRAPHIC(integer)DBCLOB(1M)(integerKMG)ccsid-clauseNATIONAL CHARACTERNATIONAL CHARNCHAR(1)(integer)NATIONAL CHARACTERNATIONAL CHARNCHARVARYINGNVARCHAR(integer)NATIONAL CHARACTERNCHARLARGE OBJECTNCLOB(1M)(integerKMG)BINARY(1)(integer)BINARY VARYINGVARBINARY(integer)BLOBBINARY LARGE OBJECT(1M)(integerKMG)DATETIME(0)TIMESTAMP(6)(integer)DATALINK(200)(integer)ccsid-clauseROWIDXML
ccsid-clause
Read syntax diagramSkip visual syntax diagramCCSIDinteger

Description

ALIAS alias-name
Identifies the alias to which the comment applies. The alias-name must identify an alias that exists at the current server.
COLUMN
Specifies 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, but must not identify a declared temporary table. The column-name must identify a column of that table or view.
CONSTRAINT
Specifies that a comment will be added to or replaced for a constraint.
constraint-name
Identifies the constraint to which the comment applies. The constraint-name must identify a constraint that exists at the current server.
FUNCTION or SPECIFIC FUNCTION
Identifies the function on which the comment applies. The function must exist at the current server and it must be a user-defined function. The function can be identified by its name, function signature, or specific name.
FUNCTION function-name
Identifies the function by its name. The function-name must identify exactly one function. 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 schema, an error is returned.
FUNCTION function-name (parameter-type, ...)
Identifies the function by its function signature, which uniquely identifies the function. The function-name (parameter-type, ...) must identify a function with the specified function signature. The specified parameters must match the data types in the corresponding position that were specified when the function was created. The number of data types, and the logical concatenation of the data types is used to identify the specific function instance on which to comment. Synonyms for data types are considered a match. Parameters that have defaults must be included in this signature.

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

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

If an unqualified distinct type name is specified, the database manager searches the SQL path to resolve the schema name for the distinct type.

For data types that have a length, precision, or scale attribute, use one of the following:

  • Empty parentheses indicate that the database manager ignores 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). However, FLOAT cannot be specified with empty parenthesis because its parameter value indicates a specific data type (REAL or DOUBLE).
  • If a specific value for a length, precision, or scale attribute is specified, the value must exactly match the value that was specified (implicitly or explicitly) in the CREATE FUNCTION statement. If the data type is FLOAT, the precision does not have to exactly match the value that was specified because matching is based on the data type (REAL or DOUBLE).
  • If length, precision, or scale is not explicitly specified, and empty parentheses are not specified, the default attributes of the data type are implied. The implicit length must exactly match the value that was specified (implicitly or explicitly) in the CREATE FUNCTION statement.

Specifying the FOR DATA clause or CCSID clause is optional. Omission of either clause indicates that the database manager ignores the attribute when determining whether the data types match. If either clause is specified, 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 XML or a distinct type based on a LOB or XML. If AS LOCATOR is specified, FOR SBCS DATA or FOR MIXED DATA must not be specified.
SPECIFIC FUNCTION specific-name
Identifies the function by its specific name. The specific-name must identify a specific function that exists at the current server.
INDEX index-name
Identifies the index to which the comment applies. The index-name must identify an index that exists at the current server.
MASK mask-name
Identifies the mask to which the comment applies. The mask-name must identify a mask that exists at the current server.
PACKAGE package-name
Identifies the package to which the comment applies. The package-name must identify a package that exists at the current server. 1
VERSION version-id
version-id is the version identifier that was assigned to the package when it was created. If version-id is not specified, a null string is used as the version identifier.
PARAMETER
Specifies 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.
PERMISSION permission-name
Identifies the permission to which the comment applies. The permission-name must identify a permission that exists at the current server.
PROCEDURE or SPECIFIC PROCEDURE
Identifies the procedure to which the comment applies. The procedure-name must identify a procedure that exists at the current server.
PROCEDURE procedure-name
Identifies the procedure by its name. The procedure-name must identify exactly one procedure. 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 schema, an error is returned.
PROCEDURE procedure-name (parameter-type, ...)
Identifies the procedure by its procedure signature, which uniquely identifies the procedure. The procedure-name (parameter-type, ...) must identify a procedure with the specified procedure signature. The specified parameters must match the data types in the corresponding position that were specified when the procedure was created. 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 commented on. Synonyms for data types are considered a match. Parameters that have defaults must be included in this signature.

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

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

If an unqualified distinct type or array type name is specified, the database manager searches the SQL path to resolve the schema name for the distinct type or array type.

For data types that have a length, precision, or scale attribute, use one of the following:

  • Empty parentheses indicate that the database manager ignores the attribute when determining whether the data types match. For example, DEC() will be considered a match for a parameter of a procedure defined with a data type of DEC(7,2). However, FLOAT cannot be specified with empty parenthesis because its parameter value indicates a specific data type (REAL or DOUBLE).
  • If a specific value for a length, precision, or scale attribute is specified, the value must exactly match the value that was specified (implicitly or explicitly) in the CREATE PROCEDURE statement. If the data type is FLOAT, the precision does not have to exactly match the value that was specified because matching is based on the data type (REAL or DOUBLE).
  • If length, precision, or scale is not explicitly specified, and empty parentheses are not specified, the default attributes of the data type are implied. The implicit length must exactly match the value that was specified (implicitly or explicitly) in the CREATE PROCEDURE statement.

Specifying the FOR DATA clause or CCSID clause is optional. Omission of either clause indicates that the database manager ignores the attribute when determining whether the data types match. If either clause is specified, it must match the value that was implicitly or explicitly specified in the CREATE PROCEDURE statement.

AS LOCATOR
Specifies that the procedure is defined to receive a locator for this parameter. If AS LOCATOR is specified, the data type must be a LOB or XML or a distinct type based on a LOB or XML. If AS LOCATOR is specified, FOR SBCS DATA or FOR MIXED DATA must not be specified.
SPECIFIC PROCEDURE specific-name
Identifies the procedure by its specific name. The specific-name must identify a specific procedure that exists at the current server.
SEQUENCE sequence-name
Identifies the sequence to which the comment applies. The sequence-name must identify a sequence that exists at the current server.
TABLE table-name or view-name
Identifies the table or view to which the comment applies. The table-name or view-name must identify a table or view that exists at the current server, but must not identify a declared temporary table.
TRIGGER trigger-name
Identifies the trigger to which the comment applies. The trigger-name must identify a trigger that exists at the current server.
TYPE distinct-type-name or array-type-name
Identifies the distinct type or array type to which the comment applies. The distinct-type-name or array-type-name must identify a type that exists at the current server.
VARIABLE variable-name
Identifies the variable to which the comment applies. The variable-name must identify a variable that exists at the current server.
XSROBJECT xsrobject-name
Identifies the XSR object to which the comment applies. The xsrobject-name must identify an XSR object that exists at the current server.
IS
Introduces the comment that to be added or replaced.
string-constant
Can be any character-string constant of up to 2000 characters (500 for a sequence).

multiple-column-list

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-parameter-list

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.

Notes

Syntax alternatives: The following keywords are synonyms supported for compatibility to prior releases. These keywords are non-standard and should not be used:

  • The keyword PROGRAM can be used as a synonym for PACKAGE.
  • The keywords DATA TYPE or DISTINCT TYPE can be used as a synonym for TYPE.

Examples

Example 1: Insert a comment for the EMPLOYEE table.

   COMMENT ON TABLE EMPLOYEE
     IS 'Reflects first quarter 2000 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 EDLEVEL column of the EMPLOYEE table.

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

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

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

Example 5: Insert a comment for the PAYROLL package.

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

1 If the identified package has a version-id, the comment is limited to 176 bytes.