LABEL
The LABEL statement adds or replaces labels 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 label a table, view, alias, column, type, package, sequence, view, 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, XSR object, function or procedure identified in the statement,
- The ALTER privilege on the table, view, alias, type, package, sequence, variable, XSR object, function or procedure, and
- The system authority *EXECUTE on the library containing the table, view, alias, type, package, sequence, variable, XSR object, function or procedure
- Database administrator authority
To label 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 system authority *EXECUTE on the library that contains the subject table
- Database administrator authority
To label 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 system authority *EXECUTE on the library containing the index.
- Database administrator authority
To label 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 system authority *EXECUTE on library QSYS2
- Database administrator authority
To label 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 system authority *EXECUTE on library QSYS2
- Database administrator authority
- The authorization ID of the statement must have security administrator authority. See Administrative authority.
To label 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
- Database administrator authority
The authorization ID of the statement has the ALTER privilege on an alias when:
- It is the owner of the alias, or
- It has been granted the system authorities of either *OBJALTER or *OBJMGT to the alias
To label 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,
- The system authority *EXECUTE on library QSYS2
- Database administrator authority
To label 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,
- The system authority *EXECUTE on library QSYS2
- Database administrator 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 Sequence, and Corresponding System Authorities When Checking Privileges to a Package.
Syntax
Description
- ALIAS
- Specifies
that the label is for an alias. Labels on aliases are implemented
as system object text.
- alias-name
- Identifies the alias to which the label applies. The name must identify an alias that exists at the current server.
- COLUMN
- Specifies
that the label is for a column. Labels on columns are implemented
as system column headings or column text. Column headings are used
when displaying or printing query results.
- table-name.column-name or view-name.column-name
- Identifies the column to which the label 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.
- TEXT
- Specifies that IBM® i column text is specified. If TEXT is omitted, a column heading is specified.
- CONSTRAINT
- Specifies
that the label is for a constraint.
- constraint-name
- Identifies the constraint to which the label applies. The constraint-name must identify a constraint that exists at the current server.
- FUNCTION or SPECIFIC FUNCTION
- Identifies
the function on which the label 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 label. 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
- Specifies
that the label is for an index. Labels on indexes are implemented
as system object text.
- index-name
- Identifies the index to which the label applies. The name must identify an index that exists at the current server.
- MASK
- Specifies
that the label is for a mask.
- mask-name
- Identifies the mask to which the label applies. The name must identify a mask that exists at the current server.
- PACKAGE
- Specifies
that the label is for a package. Labels on packages are implemented
as system object text.
- package-name
- Identifies the package to which the label applies. The name must identify a package that exists at the current server.
- 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.
- PERMISSION
- Specifies
that the label is for a permission.
- permission-name
- Identifies the permission to which the label applies. The name must identify a permission that exists at the current server.
- PROCEDURE or SPECIFIC PROCEDURE
- Identifies the procedure to which the label 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 labeled
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
- Specifies
that the label is for a sequence. Labels on sequences are implemented
as system object text.
- sequence-name
- Identifies the sequence on which you want to add a label. The sequence-name must identify a sequence that exists at the current server.
- TABLE
- Specifies
that the label is for a table or a view. Labels on tables or views
are implemented as system object text.
- table-name or view-name
- Identifies the table or view on which you want to add a label. 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
- Specifies
that the label is for a trigger.
- trigger-name
- Identifies the trigger on which you want to add a label. 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 label 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 label applies. The variable-name must identify a variable that exists at the current server.
- XSROBJECT xsrobject-name
- Identifies the XSR object to which the label applies. The xsrobject-name must identify an XSR object that exists at the current server.
- IS
- Introduces
the label you want to provide.
- string-constant
- Can
be any SQL character-string constant of up to either 60 bytes in length
for column headings or 50 bytes in length for object text or column
text. The constant may contain single-byte and double-byte characters.
The label for a column heading consists of three 20-byte segments. Interactive SQL, the Query for IBM i, IBM DB2® Query Manager and SQL Development Kit for i, and other products can display or print each 20-byte segment on a separate line. If the label for a column contains mixed data, each 20-byte segment must be a valid mixed data character string. The shift characters must be paired within each 20-byte segment.
Notes
Column headings: Column headings are used when displaying or printing query results. The first column heading is displayed or printed on the first line, the second column heading is displayed or printed on the second line, and the third column heading is displayed or printed on the third line. The column headings can be up to 60 bytes in length, where the first 20 bytes is the first column heading, the second 20 bytes is the second column heading, and the third 20 bytes is the third column heading. Blanks are trimmed from the end of each 20-byte column heading.
All 60 bytes of column heading information are available in the catalog view SYSCOLUMNS; however, only the first column heading is returned in an SQLDA on a DESCRIBE or DESCRIBE TABLE statement.
Column text is not returned on a DESCRIBE or DESCRIBE TABLE statement. When the database manager changes the column heading information in a record format description that is shared, the change is reflected in all files sharing the format description. To find out if a file shares a format with another file, use the RCDFMT parameter on the CL command, Display Database Relations (DSPDBR).
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: Enter a label on the DEPTNO column of table DEPARTMENT.
LABEL ON COLUMN DEPARTMENT.DEPTNO
IS 'DEPARTMENT NUMBER'
Example 2: Enter a label on the DEPTNO column of table DEPARTMENT where the column heading is shown on two separate lines.
LABEL ON COLUMN DEPARTMENT.DEPTNO
IS 'Department Number'
Example 3: Enter a label on the PAYROLL package.
LABEL ON PACKAGE PAYROLL
IS 'Payroll Package'