The GET DESCRIPTOR statement gets information from 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--.
>>-GET--+-----+--DESCRIPTOR--+--------+--SQL-descriptor-name---->
'-GLOBAL-'
.-,---------------.
V |
>--+---get-header-info-+----------------------------+----------><
| .-,-------------. |
| V | |
'-VALUE--+-integer----------+----get-item-info-+-'
'-integer-variable-'

get-header-info
|--variable-1-- = --+-COUNT--------------------+----------------|
+-DB2_CURSOR_HOLDABILITY---+
+-DB2_CURSOR_RETURNABILITY-+
+-DB2_CURSOR_SCROLLABILITY-+
+-DB2_CURSOR_SENSITIVITY---+
+-DB2_CURSOR_UPDATABILITY--+
+-DB2_MAX_ITEMS------------+
+-DB2_RESULT_SETS_COUNT----+
+-DYNAMIC_FUNCTION---------+
+-DYNAMIC_FUNCTION_CODE----+
'-KEY_TYPE-----------------'
get-item-info
|--variable-2-- = --+-CARDINALITY----------------+--------------|
+-DATA-----------------------+
+-DATETIME_INTERVAL_CODE-----+
+-DB2_BASE_CATALOG_NAME------+
+-DB2_BASE_COLUMN_NAME-------+
+-DB2_BASE_SCHEMA_NAME-------+
+-DB2_BASE_TABLE_NAME--------+
+-DB2_CCSID------------------+
+-DB2_COLUMN_CATALOG_NAME----+
+-DB2_COLUMN_GENERATED-------+
+-DB2_COLUMN_GENERATION_TYPE-+
+-DB2_COLUMN_HIDDEN----------+
+-DB2_COLUMN_NAME------------+
+-DB2_COLUMN_ROW_CHANGE------+
+-DB2_COLUMN_SCHEMA_NAME-----+
+-DB2_COLUMN_TABLE_NAME------+
+-DB2_COLUMN_UPDATABILITY----+
+-DB2_CORRELATION_NAME-------+
+-DB2_CURSOR_NAME------------+
+-DB2_LABEL------------------+
+-DB2_PARAMETER_NAME---------+
+-DB2_RESULT_SET_LOCATOR-----+
+-DB2_RESULT_SET_ROWS--------+
+-DB2_SYSTEM_COLUMN_NAME ----+
+-INDICATOR------------------+
+-KEY_MEMBER-----------------+
+-LENGTH---------------------+
+-LEVEL----------------------+
+-NAME-----------------------+
+-NULLABLE-------------------+
+-OCTET_LENGTH---------------+
+-PARAMETER_MODE-------------+
+-PARAMETER_ORDINAL_POSITION-+
+-PARAMETER_SPECIFIC_CATALOG-+
+-PARAMETER_SPECIFIC_NAME----+
+-PARAMETER_SPECIFIC_SCHEMA--+
+-PRECISION------------------+
+-RETURNED_CARDINALITY-------+
+-RETURNED_LENGTH -----------+
+-RETURNED_OCTET_LENGTH------+
+-SCALE----------------------+
+-TYPE-----------------------+
+-UNNAMED--------------------+
+-USER_DEFINED_TYPE_CATALOG -+
+-USER_DEFINED_TYPE_CODE ----+
+-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 is returned from 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 is returned from 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.
- get-header-info
- Returns information about the prepared SQL statement and SQL descriptor.
- VALUE
- Identifies the item number for which the specified information
is retrieved. If the value is greater than the value of COUNT (from
the header information), then no result is returned. 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 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 integer-variable must
be in the range of 1 to the maximum number of items in the SQL descriptor.
- get-item-info
- Returns information about a specific item in the SQL descriptor.
get-header-info
- 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 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.
- COUNT
- A
count of the number of items in the descriptor.
DB2_CURSOR_HOLDABILITY
The
hold status of the cursor. The possible values are:- 0
- The descriptor is not describing a cursor or the cursor was not
declared WITH HOLD.
- 1
- The cursor was declared WITH HOLD.

DB2_CURSOR_RETURNABILITY
The
return status of the cursor's result set. The possible values are:- 0
- The descriptor is not describing a cursor or the cursor's result
set is not returnable.
- 1
- The cursor's result set is returnable to the caller of the procedure.
- 2
- The cursor's result set is returnable to the client.

DB2_CURSOR_SCROLLABILITY
The
scroll status of the cursor. The possible values are:- 0
- The descriptor is not describing a cursor or the cursor was not
declared with SCROLL.
- 1
- The cursor was declared with SCROLL.

DB2_CURSOR_SENSITIVITY
The
sensitivity of the cursor. The possible values are:- 0
- The descriptor is not describing a cursor.
- 1
- The cursor is SENSITIVE DYNAMIC.
- 3
- The cursor is INSENSITIVE.
- 4
- The cursor is ASENSITIVE.

DB2_CURSOR_UPDATABILITY
Specifies
whether the cursor can be used in an UPDATE statement with WHERE CURRENT
OF cursor-name. The possible values are:- 0
- The descriptor is not describing a cursor or the cursor cannot
be used in an UPDATE statement with WHERE CURRENT OF cursor-name.
- 1
- The cursor can be used in an UPDATE statement with WHERE CURRENT
OF cursor-name.

- DB2_MAX_ITEMS
- Represents
the value specified as the allocated maximum number of item descriptors
on the ALLOCATE DESCRIPTOR statement. If the WITH MAX clause was not
specified, the value is the default number of maximum items for the
ALLOCATE DESCRIPTOR statement.
DB2_RESULT_SETS_COUNT
The
number of result sets returned by the procedure. The value will be
0 if this descriptor is not describing a procedure.
- DYNAMIC_FUNCTION
- The
type of the prepared SQL statement as a character string. For information
on statement type, see Table 2.
- DYNAMIC_FUNCTION_CODE
- The
statement code representing the type of the prepared SQL statement.
For information on statement codes, see Table 2.
- KEY_TYPE
- The
type of key included in the select list. The possible values are:
- 0
- The descriptor is not describing the columns of a query or there
are no key columns referenced in the query, or there is no unique
key.
- 1
- The select list includes all the columns of the primary key of
the base table referenced by the query.
- 2
- The table referenced by the query does not have a primary key
but the select list includes a set of columns that are defined as
the preferred candidate key. If there is more than one such preferred
candidate key included in the select list, the left-most preferred
candidate key is used.
get-item-info
- 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 getting 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.
CARDINALITY
The
cardinality of the array data type. If this descriptor is the result
of a DESCRIBE, this is the maximum cardinality of the array data type.
The cardinality is 0 for all other data types.
- DATA
- The
value for the data described by the item descriptor. If the value
of INDICATOR is negative, then the value of DATA is undefined and
the INDICATOR get-item-info must also be
specified in the same statement.
- DATETIME_INTERVAL_CODE
- Codes
that define the specific datetime data type.
- 0
- Descriptor item does not have TYPE value of 9.
- 1
- DATE
- 2
- TIME
- 3
- TIMESTAMP
- DB2_BASE_CATALOG_NAME
- The
server name of the base table for the column represented by the item
descriptor.
- DB2_BASE_COLUMN_NAME
- The
name of the column as defined in the base table referenced in the
described query, possibly indirectly through a view. If a column name
cannot be defined or is not applicable, this item will contain the
empty string. The name is returned as case sensitive and without delimiters.
- DB2_BASE_SCHEMA_NAME
- The
schema name of the base table for the column represented by the item
descriptor. If a schema name cannot be defined or is not applicable,
this item will contain the empty string. The name is returned as case
sensitive and without delimiters.
- DB2_BASE_TABLE_NAME
- The
table name of the underlying base table for the column represented
by the item descriptor. If a table name cannot be defined or is not
applicable, this item will contain the empty string. The name is returned
as case sensitive and without delimiters.
- DB2_CCSID
The
CCSID of character, graphic, or XML data. Value is zero for all types
that are not based on character or graphic string or XML types. Value
is 65535 for binary types or character types with the FOR BIT DATA
attribute.
- DB2_COLUMN_CATALOG_NAME
- The
server name of the referenced table or view for the column represented
by the item descriptor. If a column catalog name cannot be defined
or is not applicable, this item will contain the empty string.
- DB2_COLUMN_GENERATED
- Indicates
whether a column is generated. Possible values are:
- 0
- Not generated
- 1
- GENERATED ALWAYS
- 2
- GENERATED BY DEFAULT
- DB2_COLUMN_GENERATION_TYPE
- Indicates how the column is generated.
Possible values are:
- 0
- Not generated
- 1
- IDENTITY column
- 2
- ROWID column
- 4
- Row change timestamp column
- DB2_COLUMN_HIDDEN
- Indicates
whether the column represented by the item descriptor is hidden. Possible
values are:
- 0
- Not hidden
- 1
- Implicitly hidden
- 3
- Implicitly hidden for optimistic locking
- DB2_COLUMN_NAME
- The
name of the column as defined in the table or view referenced in the
described query. If a column name cannot be defined or is not applicable,
this item will contain the empty string. The name is returned as case
sensitive and without delimiters.
- DB2_COLUMN_ROW_CHANGE
- Indicates
whether the column represented by the item descriptor was added as
a result of using the WITH ROW CHANGE COLUMNS prepare attribute. Possible
values are:
- -1
- ROW CHANGE TOKEN (distinct)
- -2
- ROW CHANGE TOKEN (not distinct)
- -3
- RID (only valid from a remote relational database)
- -4
- RID_BIT (only valid from a remote relational database)
- DB2_COLUMN_SCHEMA_NAME
- The
schema name of the referenced table or view for the column represented
by the item descriptor. If a column schema name cannot be defined
or is not applicable, this item will contain the empty string. The
name is returned as case sensitive and without delimiters.
- DB2_COLUMN_TABLE_NAME
- The
table or view name of the referenced table or view for the column
represented by the item descriptor. If a column table name cannot
be defined or is not applicable, this item will contain the empty
string. The name is returned as case sensitive and without delimiters.
- DB2_COLUMN_UPDATABILITY
- Indicates
whether the column represented by the item descriptor is updatable.
Possible values are:
- 0
- Not updatable
- 1
- Updatable
- DB2_CORRELATION_NAME
- The
empty string is always returned.
DB2_CURSOR_NAME
The
name of the cursor in the procedure for this result set. This is only
set when the descriptor is describing a procedure.
- DB2_LABEL
- The
label defined for the column. If there is no label for the column,
this item will contain the empty string.
- DB2_PARAMETER_NAME
- The
name of the parameter for the stored procedure. Only returned for
a CALL statement. The name is returned as case sensitive and without
delimiters.
DB2_RESULT_SET_LOCATOR
The
result set locator for this result set. This is only set when the
descriptor is describing a procedure.
DB2_RESULT_SET_ROWS
The
estimated number of rows in the result set. It is set to the value
-1 if the number is unknown. This is only set when the descriptor
is describing a procedure.
- DB2_SYSTEM_COLUMN_NAME
- The
system name of the column. If a system name cannot be defined or is
not applicable, this item will contain blanks.
- INDICATOR
- The
value for the indicator. A non-negative value is used when the value
returned in this descriptor item is given in the DATA field. When
extended indicator variables are not enabled, a negative value is
used when the value returned in this descriptor item is the null value.
When extended indicators are enabled:
- -1, -2, -3, -4, or -6 indicates the value returned in this descriptor
is the null value.
- -5 indicates the value returned in this descriptor item is DEFAULT.
- -7 indicates the value returned in this descriptor item is UNASSIGNED.
- KEY_MEMBER
- An
indication of whether this column is part of a key.
- 0
- This column is not part of a key.
- 1
- This column is part of a unique key.
- 2
- This column by itself is a unique key.
- LENGTH
Returns
the maximum length of the data. If the data type is a character or
graphic string, an 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.
For a description of data type codes and lengths, see Table 2.
- LEVEL
The
level of the item descriptor. The value is 0.
- NAME
- The
name associated with the select list column described by the item
descriptor. The name is returned as case sensitive and without delimiters.
- NULLABLE
- Indicates
whether the column or parameter marker is nullable.
- 0
- The select list column or parameter marker cannot have a null
value.
- 1
- The select list column or parameter marker can have a null value.
- OCTET_LENGTH
- Returns
the maximum length of the data in bytes for all types. For a description
of data type codes and lengths, see Table 2.
- PARAMETER_MODE
- The
mode of the parameter marker in a CALL statement.
- 0
- The descriptor is not associated with a CALL statement.
- 1
- Input only parameter.
- 2
- Input and output parameter.
- 4
- Output only parameter.
- PARAMETER_ORDINAL_POSITION
- The ordinal position of the parameter
marker in a CALL statement. The value is 0 if the descriptor is not
associated with a CALL statement.
- PARAMETER_SPECIFIC_CATALOG
- The server name of the procedure
containing the parameter marker.
- PARAMETER_SPECIFIC_NAME
- The
specific name of the procedure containing the parameter marker. The
name is returned as case sensitive and without delimiters.
- PARAMETER_SPECIFIC_SCHEMA
- The schema name of the procedure
containing the parameter marker. The name is returned as case sensitive
and without delimiters.
- PRECISION
- Returns
the precision for the data:
- SMALLINT
- 5
- INTEGER
- 10
- BIGINT
- 19
- NUMERIC and DECIMAL
- Defined precision
- REAL
- 24
- DOUBLE
- 53
- DECFLOAT(7)
- 7
- DECFLOAT(16)
- 16
- DECFLOAT(34)
- 34
- TIME
- 0
- TIMESTAMP
- 6
- Other data types
- 0
RETURNED_CARDINALITY
The current cardinality for an array data type returned by FETCH
or CALL. The value is 0 when the data type of the item is not an array.
- RETURNED_LENGTH
The returned length in characters
for character string, graphic string, and XML data types. The returned
length in bytes for binary string data types.
- RETURNED_OCTET_LENGTH
- The
returned length in bytes for all string data types.
- SCALE
- Returns
the defined scale if the data type is DECIMAL or NUMERIC. The scale
is 0 for all other data types.
- TYPE
- Returns
a data type code representing the data type of the item. For a description
of the data type codes and lengths, see Table 2.
- UNNAMED
- A
value of 1 indicates that the NAME value is generated by the database
manager. Otherwise, the value is zero and NAME is the derived name
of the column in the select list.
- USER_DEFINED_TYPE_CATALOG
- The server name of the user-defined
type. If the type is not a user-defined data type, this item contains
the empty string.
- USER_DEFINED_TYPE_CODE
- Indicates
whether the type of the descriptor item is a user-defined type.
- 0
- The descriptor item is not a user-defined type.
- 1
- The descriptor item is a user-defined type.
- USER_DEFINED_TYPE_NAME
- The
name of the user-defined data type. If the type is not a user-defined
data type, this item contains the empty string. The name is returned
as case sensitive and without delimiters.
- USER_DEFINED_TYPE_SCHEMA
- The schema name of the user-defined
data type. If the type is not a user-defined data type, this item
contains the empty string. The name is returned as case sensitive
and without delimiters.
Notes
Data types for items: The following table shows
the SQL data type for each descriptor item. When a descriptor item
is assigned to a variable, the variable must be compatible with the
data type of the descriptor item.
Table 1. Data Types for GET DESCRIPTOR Items| Item Name |
Data Type |
| Header
Information |
| COUNT |
INTEGER |
DB2_CURSOR_HOLDABILITY |
INTEGER |
DB2_CURSOR_RETURNABILITY |
INTEGER |
DB2_CURSOR_SCROLLABILITY |
INTEGER |
DB2_CURSOR_SENSITIVITY |
INTEGER |
DB2_CURSOR_UPDATABILITY |
INTEGER |
| DB2_MAX_ITEMS |
INTEGER |
DB2_RESULT_SETS_COUNT |
INTEGER |
| DYNAMIC_FUNCTION |
VARCHAR(128) |
| DYNAMIC_FUNCTION_CODE |
INTEGER |
| KEY_TYPE |
INTEGER |
| Item
Information |
CARDINALITY |
BIGINT |
| DATA |
Matches the data type specified by TYPE |
| DATETIME_INTERVAL_CODE |
INTEGER |
| DB2_BASE_CATALOG_NAME |
VARCHAR(128) |
| DB2_BASE_COLUMN_NAME |
VARCHAR(128) |
| DB2_BASE_SCHEMA_NAME |
VARCHAR(128) |
| DB2_BASE_TABLE_NAME |
VARCHAR(128) |
| DB2_CCSID |
INTEGER |
| DB2_COLUMN_CATALOG_NAME |
VARCHAR(128) |
| DB2_COLUMN_GENERATED |
INTEGER |
| DB2_COLUMN_GENERATION_TYPE |
INTEGER |
| DB2_COLUMN_HIDDEN |
INTEGER |
| DB2_COLUMN_NAME |
VARCHAR(128) |
| DB2_COLUMN_ROW_CHANGE |
INTEGER |
| DB2_COLUMN_SCHEMA_NAME |
VARCHAR(128) |
| DB2_COLUMN_TABLE_NAME |
VARCHAR(128) |
| DB2_COLUMN_UPDATABILITY |
INTEGER |
| DB2_CORRELATION_NAME |
VARCHAR(128) |
DB2_CURSOR_NAME |
VARCHAR(128) |
| DB2_LABEL |
VARCHAR(60) |
| DB2_PARAMETER_NAME |
VARCHAR(128) |
DB2_RESULT_SET_LOCATOR |
Result Set Locator |
DB2_RESULT_SET_ROWS |
BIGINT |
| DB2_SYSTEM_COLUMN_NAME |
CHAR(10) |
| INDICATOR |
INTEGER |
| KEY_MEMBER |
INTEGER |
| LENGTH |
INTEGER |
| LEVEL |
INTEGER |
| NAME |
VARCHAR(128) |
| NULLABLE |
INTEGER |
| OCTET_LENGTH |
INTEGER |
| PARAMETER_MODE |
INTEGER |
| PARAMETER_ORDINAL_POSITION |
INTEGER |
| PARAMETER_SPECIFIC_CATALOG |
VARCHAR(128) |
| PARAMETER_SPECIFIC_NAME |
VARCHAR(128) |
| PARAMETER_SPECIFIC_SCHEMA |
VARCHAR(128) |
| PRECISION |
INTEGER |
| RETURNED_CARDINALITY |
INTEGER |
| RETURNED_LENGTH |
INTEGER |
| RETURNED_OCTET_LENGTH |
INTEGER |
| SCALE |
INTEGER |
| TYPE |
INTEGER |
| UNNAMED |
INTEGER |
| USER_DEFINED_TYPE_CATALOG |
VARCHAR(128) |
| USER_DEFINED_TYPE_NAME |
VARCHAR(128) |
| USER_DEFINED_TYPE_SCHEMA |
VARCHAR(128) |
| USER_DEFINED_TYPE_CODE |
VARCHAR(128) |
SQL data type codes and lengths: The following
table represents the possible values for TYPE, LENGTH, OCTET_LENGTH,
and DATETIME_INTERVAL_CODE descriptor items.
The values in the following table are assigned by the
ISO and ANSI SQL Standard and may change as the standard evolves.
Include sqlscds in the include source files in library QSYSINC
should be used when referencing these values.
Table 2. SQL Data Type Codes and Lengths| Data Type |
Data Type Code |
Length |
Octet Length |
| SMALLINT |
5 |
2 |
2 |
| INTEGER |
4 |
4 |
4 |
| BIGINT |
25 |
8 |
8 |
| DECIMAL |
3 |
(precision/2)+1 |
(precision/2)+1 |
| NUMERIC(n) |
2 |
n |
n |
| REAL |
7 |
4 |
4 |
| FLOAT |
6 |
8 |
8 |
| DOUBLE PRECISION |
8 |
8 |
8 |
| DECFLOAT(7) |
-360 |
4 |
4 |
| DECFLOAT(16) |
-360 |
8 |
8 |
| DECFLOAT(34) |
-360 |
16 |
16 |
| CHARACTER(n) |
1 |
n |
n |
| VARCHAR(n) |
12 |
<=n |
n |
| CLOB(n) |
40 |
<=n |
n |
| GRAPHIC(n) |
–95 |
n |
2*n |
| VARGRAPHIC(n) |
–96 |
<=n |
2*n |
| DBCLOB(n) |
-350 |
<=n |
2*n |
| BINARY(n) |
–2 |
n |
n |
| VARBINARY(n) |
–3 |
<=n |
n |
| BLOB(n) |
30 |
n |
n |
| DATE (DATETIME_INTERVAL_CODE = 1) |
9 |
Length depends on date format |
Based on CCSID |
| TIME (DATETIME_INTERVAL_CODE = 2) |
9 |
Length depends on time format |
Based on CCSID |
| TIMESTAMP (DATETIME_INTERVAL_CODE = 3) |
9 |
26 |
26 or 52 (based on CCSID) |
| DATALINK(n) |
70 |
<=n |
n |
| ROWID |
–904 |
40 |
40 |
XML |
137 |
0 |
0 |
| C nul terminated CHARACTER(n) |
1 |
<=n |
n |
| C nul terminated GRAPHIC(n) |
–400 |
<=n |
2*n |
| BLOB File Reference Variable |
–916 |
267 |
267 |
| CLOB File Reference Variable |
–920 |
267 |
267 |
| DBCLOB File Reference Variable |
–924 |
267 |
267 |
Result Set Locator |
-972 |
8 |
8 |
Array |
50 |
N/A |
N/A |
Example
Example 1: Retrieve from
the descriptor 'NEWDA' the number of descriptor items.
EXEC SQL GET DESCRIPTOR 'NEWDA'
:numitems = COUNT;
Example 2: Retrieve from the first item descriptor
of descriptor 'NEWDA' the data type and the octet length.
GET DESCRIPTOR 'NEWDA'
VALUE 1 :dtype = TYPE,
:olength = OCTET_LENGTH;