GET DESCRIPTOR
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
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
- 5
- ROW BEGIN column
- 6
- ROW END column
- 7
- TRANSACTION START ID column
- 8
- Generated expression 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
- Defined precision (0-12)
- 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.
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.
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 | 19-32, depending on precision | 19-32 or 38-64, depending on precision (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;