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


        .-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. End of change

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. End of change

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 Start of change

or a global variable. End of change

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. End of change

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. End of change

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. End of change

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. End of change

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. End of change

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. End of change

LEVEL

The level of the item descriptor. The value is 0. End of change

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. End of change

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. End of change

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

Start of change 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. End of change

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;