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

Click to skip syntax diagram 
Read syntax diagramSkip visual syntax diagram        .-SQL-.              .-LOCAL--.
>>-GET--+-----+--DESCRIPTOR--+--------+--SQL-descriptor-name---->
                             '-GLOBAL-'
 
     .-,---------------.
     V                 |
>--+---get-header-info-+----------------------------+----------><
   |                              .-,-------------. |
   |                              V               | |
   '-VALUE--+-integer----------+----get-item-info-+-'
            '-integer-variable-'
 

Read syntax diagramSkip visual syntax diagramget-header-info:
 
|--variable-1-- = --+-COUNT-----------------+-------------------|
                    +-DYNAMIC_FUNCTION------+
                    +-DYNAMIC_FUNCTION_CODE-+
                    +-KEY_TYPE--------------+
                    '-DB2_MAX_ITEMS---------'
 
get-item-info:
 
|--variable-2-- = --+-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_NAME------------+
                    +-DB2_COLUMN_SCHEMA_NAME-----+
                    +-DB2_COLUMN_TABLE_NAME------+
                    +-DB2_COLUMN_UPDATABILITY----+
                    +-DB2_CORRELATION_NAME-------+
                    +-DB2_LABEL------------------+
                    +-DB2_PARAMETER_NAME---------+
                    +-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_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. 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. The data type of the variable must be compatible with the descriptor information item as specified in Table 56. 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.
DYNAMIC_FUNCTION
The type of the prepared SQL statement as a character string. For information on statement type, see Table 59.
DYNAMIC_FUNCTION_CODE
The statement code representing the type of the prepared SQL statement. For information on statement codes, see Table 59.
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.
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.

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. The data type of the variable must be compatible with the descriptor information item as specified in Table 56. 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 56. 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.

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 or graphic data. Value is zero for all types that are not based on character or graphic string 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
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_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_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_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.
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
INDICATOR
The value for the indicator. A negative value is used when the value returned in this descriptor item is the null value. A non-negative value is used when the value returned in this descriptor item is given in the DATA field.
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 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 57.
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 57.
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
4
INTEGER
9
BIGINT
19
NUMERIC and DECIMAL
Defined precision
DOUBLE
53
REAL
24
TIME
0
TIMESTAMP
6
Other data types
0
RETURNED_LENGTH
The returned length in characters for character string and graphic string 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 57.
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 diagnostic item.

Table 56. Data Types for GET DESCRIPTOR Items
Item Name Data Type
Header Information
COUNT INTEGER
DYNAMIC_FUNCTION VARCHAR(128)
DYNAMIC_FUNCTION_CODE INTEGER
KEY_TYPE INTEGER
DB2_MAX_ITEMS INTEGER
Item Information
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_NAME VARCHAR(128)
DB2_COLUMN_SCHEMA_NAME VARCHAR(128)
DB2_COLUMN_TABLE_NAME VARCHAR(128)
DB2_COLUMN_UPDATABILITY INTEGER
DB2_CORRELATION_NAME VARCHAR(128)
DB2_LABEL VARCHAR(60)
DB2_PARAMETER_NAME VARCHAR(128)
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_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 57. SQL Data Type Codes and Lengths
Data Type Data Type Code Length Octet Length
BIGINT 25 8 8
BLOB(n) 30 n n
CHARACTER(n) 1 n n
VARCHAR(n) 12 <=n n
CLOB(n) 40 <=n n
DATALINK(n) 70 <=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)
DBCLOB(n) -350 <=n 2*n
BINARY(n) –2 n n
VARBINARY(n) –3 <=n n
DECIMAL 3 (precision/2)+1 (precision/2)+1
DOUBLE PRECISION 8 8 8
FLOAT 6 8 8
GRAPHIC(n) –95 n 2*n
INTEGER 4 4 4
NUMERIC(n) 2 n n
ZONED DECIMAL(n) 2 n n
REAL 7 4 4
ROWID –904 40 40
SMALLINT 5 2 2
VARGRAPHIC(n) –96 <=n 2*n
C nul terminated GRAPHIC(n) –400® <=n 2*n
C nul terminated CHARACTER(n) 1 <=n n
BLOB File Reference Variable –916 267 267
CLOB File Reference Variable –920 267 267
DBCLOB File Reference Variable –924 267 267

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;

[ Top of Page | Previous Page | Next Page | Contents | Index ]