SET DESCRIPTOR
The SET DESCRIPTOR statement sets information in an SQL descriptor.
Invocation
This statement can only be embedded in an application program, SQL function, SQL procedure, or trigger. It cannot be issued interactively. It is an executable statement that cannot be dynamically prepared. It must not be specified in REXX.
Authorization
None required.
Syntax
Description
- LOCAL
- Specifies the scope of the name of the descriptor to be local to program invocation. The information provided is set into the descriptor known in this local scope.
- GLOBAL
- Specifies the scope of the name of the descriptor to be global to the SQL session. The information provided is set into the descriptor known to any program that executes using the same database connection.
- SQL-descriptor-name
- Names the SQL descriptor. The name must identify a descriptor that already exists with the specified scope.
- set-header-info
- Sets attributes into the SQL descriptor. The same descriptor item must not be specified more than once in a single SET DESCRIPTOR statement.
- VALUE
- Specifies the item number for which the specified information
is set. If the item number is greater than the maximum number of items
allocated for the descriptor or the item number is less than 1, an
error is returned.
- integer
- An integer constant in the range of 1 to the number of items allocated in the SQL descriptor.
- integer-variable
- Identifies a variable declared in the program in accordance with the rules for declaring variables. It must not be a global variable. The data type of the variable must be SMALLINT, INTEGER, BIGINT, or DECIMAL or NUMERIC with a scale of zero. The value of variable must be in the range of 1 to the maximum number of items allocated in the SQL descriptor.
- set-item-info
- Sets information about a specific item into the SQL descriptor. The same descriptor item must not be specified more than once in a single SET DESCRIPTOR statement. Items that are not applicable to the specified type are ignored.
set-header-info
- COUNT
- A count of the number of items that will be specified in the descriptor.
- variable–1
- Identifies a variable declared in the program in accordance with the rules for declaring variables, but must not be a file reference variable or a global variable. The data type of the variable must be compatible with the COUNT header item as specified in Table 1. The variable is assigned (using storage assignment rules) to the COUNT header item. For details on the assignment rules, see Assignments and comparisons.
- constant–1
- Identifies a constant value used to set the COUNT header item. The data type of the constant must be compatible with the COUNT header item as specified in Table 1. The constant is assigned (using storage assignment rules) to the COUNT header item. For details on the assignment rules, see Assignments and comparisons.
set-item-info
- CARDINALITY
- Specifies the cardinality for the item. This is only allowed when TYPE is an array.
- DATA
- Specifies the value for the data described by the item descriptor. If the value of INDICATOR is negative, then the value of DATA is undefined. The assigned value cannot be a constant.
- DATETIME_INTERVAL_CODE
- Specifies
the specific datetime data type. DATETIME_INTERVAL_CODE must be specified
if TYPE is set to 9.
- 1
- DATE
- 2
- TIME
- 3
- TIMESTAMP
- DB2_CCSID
- Specifies
the CCSID of character, graphic, XML, or datetime data. The value
is not applicable for all other data types. If the DB2_CCSID is not
specified or 0 is specified:
- For XML data, the SQL_XML_DATA_CCSID QAQQINI option setting will be used.
- Otherwise, the CCSID of the variable will be determined by the CCSID of the job.
- INDICATOR
- Specifies
the value for the indicator. A non-negative indicates a DATA value
will be provided for this descriptor item. When extended indicator
variables are not enabled, a negative value indicates the value described
by this descriptor item is the null value. If not set, the value of
INDICATOR is 0. When extended indicator variables are enabled:
- -1, -2, -3, -4, or -6 indicates the value described by this descriptor item is the null value.
- -5 indicates the value described by this descriptor item is the DEFAULT value.
- -7 indicates the value described by this descriptor item is the UNASSIGNED value.
- 0 or a positive value indicates a DATA value will be provided for this descriptor item.
- LENGTH
- Specifies the maximum length of the data. If the data type is a character or graphic string type, XML type, or a datetime type, the length represents the number of characters (not bytes). If the data type is a binary string or any other type, the length represents the number of bytes. If LENGTH is not specified, a default length will be used. For a description of the defaults, see Table 1.
- LEVEL
- The
level of the item descriptor.
- 0
- Item is a primary descriptor entry.
- 1
- Item is for a secondary descriptor entry. This is for an array entry.
- PRECISION
- Specifies the precision for descriptor items of data type DECIMAL, NUMERIC, DECFLOAT, DOUBLE, REAL, FLOAT, and TIMESTAMP. If PRECISION is not specified, a default precision will be used. For a description of the defaults, see Table 1.
- SCALE
- Specifies the scale for descriptor items of data type DECIMAL or NUMERIC. If SCALE is not specified, a default scale will be used. For a description of the defaults, see Table 1.
- TYPE
- Specifies a data type code representing the data type of the descriptor item. For a description of the data type codes and lengths, see Table 2. Either TYPE or USER_DEFINED_TYPE_NAME and USER_DEFINED_TYPE_SCHEMA (but not both) must be specified for each descriptor item.
- USER_DEFINED_TYPE_CATALOG
- Specifies the server name of the user-defined type. If USER_DEFINED_TYPE_CATALOG is specified, it must be equal to the current server. Otherwise, the USER_DEFINED_TYPE_CATALOG is the current server.
- USER_DEFINED_TYPE_NAME
- Specifies the name of the user-defined data type. Either TYPE or USER_DEFINED_TYPE_NAME and USER_DEFINED_TYPE_SCHEMA (but not both) must be specified for each descriptor item.
- USER_DEFINED_TYPE_SCHEMA
- Specifies the schema containing the user-defined type. Either TYPE or USER_DEFINED_TYPE_NAME and USER_DEFINED_TYPE_SCHEMA (but not both) must be specified for each descriptor item.
- variable–2
- Identifies a variable declared in the program in accordance with
the rules for declaring variables, but must not be a file reference
variable or a global variable. The data type of
the variable must be compatible with the descriptor information item
as specified in Table 1. The
variable is assigned (using storage assignment rules) to the corresponding
descriptor item. For details on the assignment rules, see Assignments and comparisons.
When setting the DATA item, in general the variable must have the same data type, length, precision, scale, and CCSID as specified in Table 1. For variable-length types, the variable length must not be less than the LENGTH in the descriptor. For C nul-terminated types, the variable length must be at least one greater than the LENGTH in the descriptor.
- constant-2
- Identifies a constant value used to set the descriptor item. The
data type of the constant must have the same data type, length, precision,
scale, and CCSID as specified in Table 1. The
constant is assigned (using storage assignment rules) to the corresponding
descriptor item. For details on the assignment rules, see Assignments and comparisons.
If the descriptor item to be set is DATA, constant-2 cannot be specified.
Notes
Default values for descriptor items: The following table represents the default values for LENGTH, PRECISION, and SCALE, if they are not specified for a descriptor item.
Data Type | LENGTH | PRECISION | SCALE |
---|---|---|---|
DECIMAL and NUMERIC | 5 | 0 | |
FLOAT | 53 | 0 | |
DECFLOAT | 34 | ||
CHARACTER, VARCHAR, and CLOB | 1 | ||
GRAPHIC, VARGRAPHIC, and DBCLOB | 1 | ||
BINARY, VARBINARY, and BLOB | 1 | ||
DATE | 10 | ||
TIME | 8 | ||
TIMESTAMP | 26 | 6 | |
XML | 1 |
Example
Example 1: Set the number of items in descriptor 'NEWDA' to the value in :numitems.
EXEC SQL SET DESCRIPTOR 'NEWDA'
COUNT = :numitems;
Example 2: Set the value of the type and length for the first item descriptor of descriptor 'NEWDA'
SET DESCRIPTOR 'NEWDA'
VALUE 1 TYPE = :dtype,
LENGTH = :olength;