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

.-SQL-. .-LOCAL--.
>>-SET--+-----+--DESCRIPTOR--+--------+--SQL-descriptor-name---->
'-GLOBAL-'
.-,---------------.
V |
>--+---set-header-info-+----------------------------+----------><
| .-,-------------. |
| V | |
'-VALUE--+-integer----------+----set-item-info-+-'
'-integer-variable-'

set-header-info
|--COUNT-- = --+-constant-1-+-----------------------------------|
'-variable-1-'
set-item-info
|--+-CARDINALITY---------------+-- = --+-constant-2-+-----------|
+-DATA----------------------+ '-variable-2-'
+-DATETIME_INTERVAL_CODE----+
+-DB2_CCSID-----------------+
+-INDICATOR-----------------+
+-LENGTH--------------------+
+-LEVEL---------------------+
+-PRECISION-----------------+
+-SCALE---------------------+
+-TYPE----------------------+
+-USER_DEFINED_TYPE_CATALOG +
+-USER_DEFINED_TYPE_NAME --+
'-USER_DEFINED_TYPE_SCHEMA -'
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 enables, 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 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, and FLOAT. 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.
Table 1. Default LENGTH, PRECISION, and
SCALE| 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 |
|
|
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;