SQL Reference

Field Descriptions

An SQLDA consists of four variables followed by an arbitrary number of occurrences of a sequence of variables collectively named SQLVAR. In OPEN, FETCH, EXECUTE, and CALL each occurrence of SQLVAR describes a host variable. In DESCRIBE and PREPARE, each occurrence of SQLVAR describes a column of a result table. There are two types of SQLVAR entries:

Base SQLVARs: These entries are always present. They contain the base information about the column or host variable such as data type code, length attribute, column name, host variable address, and indicator variable address.
Secondary SQLVARs: These entries are only present if the number of SQLVAR entries is doubled as per the rules outlined above. For distinct types they contain the distinct type name. For reference types, they contain that target type of the reference. For LOBs, they contain the length attribute of the host variable and a pointer to the buffer that contains the actual length. ⁹⁹ If locators or file reference variables are used to represent LOBs, these entries are not necessary.

In SQLDAs that contain both types of entries, the base SQLVARs are in a block before the block of secondary SQLVARs. In each, the number of entries is equal to value in SQLD (even though many of the secondary SQLVAR entries may be unused).

The circumstances under which the SQLVAR entries are set by DESCRIBE is detailed in Effect of DESCRIBE on the SQLDA.

Fields in the SQLDA Header

Table 33. Fields in the SQLDA Header

C Name SQL Data Type Usage in DESCRIBE and PREPARE (set by the database manager except for SQLN) Usage in FETCH, OPEN, EXECUTE, and CALL (set by the application prior to executing the statement)
sqldaid CHAR(8) The seventh byte of this field is a flag byte named SQLDOUBLED. The database manager sets SQLDOUBLED to the character '2' if two SQLVAR entries have been created for each column; otherwise it is set to a blank (X'20' in ASCII, X'40' in EBCDIC). See Effect of DESCRIBE on the SQLDA for details on when SQLDOUBLED is set. The seventh byte of this field is used when the number of SQLVARs is doubled. It is named SQLDOUBLED. If any of the host variables being described is a BLOB, CLOB, or DBCLOB, the seventh byte must be set to the character '2'; otherwise it can be set to any character but we advise the use of a blank.
When used with the CALL statement and one or more SQLVARs define of data field as FOR BIT DATA, the sixth byte must be set to the '+' character; otherwise it can be set to any character but we advise the use of blank.
sqldabc INTEGER Length of the SQLDA, equal to SQLN*44+16. Length of the SQLDA, >= to SQLN*44+16.
sqln SMALLINT Unchanged by the database manager. Must be set to a value greater than or equal to zero before the DESCRIBE statement is executed. Indicates the total number of occurrences of SQLVAR. Total number of occurrences of SQLVAR provided in the SQLDA. SQLN must be set to a value greater than or equal to zero.
sqld SMALLINT Set by the database manager to the number of columns in the result table (or to zero if the statement being described is not a select-statement). The number of host variables described by occurrences of SQLVAR.

C Name	SQL Data Type	Usage in DESCRIBE and PREPARE (set by the database manager except for SQLN)	Usage in FETCH, OPEN, EXECUTE, and CALL (set by the application prior to executing the statement)
sqldaid	CHAR(8)	The seventh byte of this field is a flag byte named SQLDOUBLED. The database manager sets SQLDOUBLED to the character '2' if two SQLVAR entries have been created for each column; otherwise it is set to a blank (X'20' in ASCII, X'40' in EBCDIC). See Effect of DESCRIBE on the SQLDA for details on when SQLDOUBLED is set.	The seventh byte of this field is used when the number of SQLVARs is doubled. It is named SQLDOUBLED. If any of the host variables being described is a BLOB, CLOB, or DBCLOB, the seventh byte must be set to the character '2'; otherwise it can be set to any character but we advise the use of a blank. When used with the CALL statement and one or more SQLVARs define of data field as FOR BIT DATA, the sixth byte must be set to the '+' character; otherwise it can be set to any character but we advise the use of blank.
sqldabc	INTEGER	Length of the SQLDA, equal to SQLN*44+16.	Length of the SQLDA, >= to SQLN*44+16.
sqln	SMALLINT	Unchanged by the database manager. Must be set to a value greater than or equal to zero before the DESCRIBE statement is executed. Indicates the total number of occurrences of SQLVAR.	Total number of occurrences of SQLVAR provided in the SQLDA. SQLN must be set to a value greater than or equal to zero.
sqld	SMALLINT	Set by the database manager to the number of columns in the result table (or to zero if the statement being described is not a select-statement).	The number of host variables described by occurrences of SQLVAR.

Fields in an Occurrence of a Base SQLVAR

Table 34. Fields in a Base SQLVAR

Name Data Type Usage in DESCRIBE and PREPARE Usage in FETCH, OPEN, EXECUTE, and CALL
sqltype SMALLINT Indicates the data type of the column and whether it can contain nulls. Table 36 lists the allowable values and their meanings.
Note that for a distinct or reference type, the data type of the base type is placed into this field. There is no indication in the Base SQLVAR that it is part of the description of a distinct type.
Same for host variable. Host variables for datetime values must be character string variables. For FETCH, a datetime type code means a fixed-length character string. If sqltype is an even number value, the sqlind field is ignored.
sqllen SMALLINT The length attribute of the column. For datetime columns, the length of the string representation of the values. See Table 36.
Note that the value is set to 0 for large object strings (even for those whose length attribute is small enough to fit into a two byte integer).
The length attribute of the host variable. See Table 36.
Note that the value is ignored by the database manager for CLOB, DBCLOB, and BLOB columns. The len.sqllonglen field in the Secondary SQLVAR is used instead.
sqldata pointer For character-string SQLVARs, sqldata contains 0 if the column is defined with the FOR BIT DATA attribute. If the column does not have the FOR BIT DATA attribute, the value depends on the encoding of the data. For single-byte SBCS encoded data, sqldata contains the SBCS code page. For mixed DBCS encoded data, sqldata contains the SBCS code page associated with the composite DBCS code page. For Japanese or Traditional-Chinese EUC encoded data, sqldata contains the composite EUC code page.
For all other column types, sqldata is undefined.
Contains the address of the host variable (where the fetched data will be stored).
sqlind pointer For character-string SQLVARs, sqlind contains 0 except for mixed DBCS encoded data when sqlind contains the DBCS code page associated with the composite DBCS code page.
For all other column types, sqlind is undefined.
Contains the address of an associated indicator variable, if there is one; otherwise, not used. If sqltype is an even number value, the sqlind field is ignored.
sqlname VARCHAR (30) Contains the unqualified name of the column.
For columns that have a system generated name (the result column was not directly derived from a single column and did not specify a name using the AS clause), the thirtieth byte is set to X'FF'. For column names specified by the AS clause, this byte is X'00'.
When used with the CALL statement to access a DRDA application server, sqlname can be set to indicate a FOR BIT DATA string as follows:

the length of sqlname is 8
the first four bytes of sqlname are X'00000000'
the remaining four bytes of sqlname are reserved (and currently ignored).

In addition, the sqltype must indicate a CHAR, VARCHAR or LONG VARCHAR and the sixth byte of the sqldaid field is set to the '+' character.
This technique can also be used with OPEN and EXECUTE when using DB2 Connect to access the server.

Name	Data Type	Usage in DESCRIBE and PREPARE	Usage in FETCH, OPEN, EXECUTE, and CALL
sqltype	SMALLINT	Indicates the data type of the column and whether it can contain nulls. Table 36 lists the allowable values and their meanings. Note that for a distinct or reference type, the data type of the base type is placed into this field. There is no indication in the Base SQLVAR that it is part of the description of a distinct type.	Same for host variable. Host variables for datetime values must be character string variables. For FETCH, a datetime type code means a fixed-length character string. If sqltype is an even number value, the sqlind field is ignored.
sqllen	SMALLINT	The length attribute of the column. For datetime columns, the length of the string representation of the values. See Table 36. Note that the value is set to 0 for large object strings (even for those whose length attribute is small enough to fit into a two byte integer).	The length attribute of the host variable. See Table 36. Note that the value is ignored by the database manager for CLOB, DBCLOB, and BLOB columns. The len.sqllonglen field in the Secondary SQLVAR is used instead.
sqldata	pointer	For character-string SQLVARs, sqldata contains 0 if the column is defined with the FOR BIT DATA attribute. If the column does not have the FOR BIT DATA attribute, the value depends on the encoding of the data. For single-byte SBCS encoded data, sqldata contains the SBCS code page. For mixed DBCS encoded data, sqldata contains the SBCS code page associated with the composite DBCS code page. For Japanese or Traditional-Chinese EUC encoded data, sqldata contains the composite EUC code page. For all other column types, sqldata is undefined.	Contains the address of the host variable (where the fetched data will be stored).
sqlind	pointer	For character-string SQLVARs, sqlind contains 0 except for mixed DBCS encoded data when sqlind contains the DBCS code page associated with the composite DBCS code page. For all other column types, sqlind is undefined.	Contains the address of an associated indicator variable, if there is one; otherwise, not used. If sqltype is an even number value, the sqlind field is ignored.
sqlname	VARCHAR (30)	Contains the unqualified name of the column. For columns that have a system generated name (the result column was not directly derived from a single column and did not specify a name using the AS clause), the thirtieth byte is set to X'FF'. For column names specified by the AS clause, this byte is X'00'.	When used with the CALL statement to access a DRDA application server, sqlname can be set to indicate a FOR BIT DATA string as follows: the length of sqlname is 8 the first four bytes of sqlname are X'00000000' the remaining four bytes of sqlname are reserved (and currently ignored). In addition, the sqltype must indicate a CHAR, VARCHAR or LONG VARCHAR and the sixth byte of the sqldaid field is set to the '+' character. This technique can also be used with OPEN and EXECUTE when using DB2 Connect to access the server.

Fields in an Occurrence of a Secondary SQLVAR

Table 35. Fields in a Secondary SQLVAR

Name Data Type Usage in DESCRIBE and PREPARE Usage in FETCH, OPEN, EXECUTE, and CALL
len.sqllonglen INTEGER The length attribute of a BLOB, CLOB, or DBCLOB column. The length attribute of a BLOB, CLOB, or DBCLOB host variable. The database manager ignores the SQLLEN field in the Base SQLVAR for the data types. The length attribute stores the number of bytes for a BLOB or CLOB, and the number of characters for a DBCLOB.
reserve2 CHAR(3) Not used. Not used.
sqlflag4 CHAR(1) The value is X'01' if the SQLVAR represents a reference type with a target type named in sqldatatype_name. Otherwise, the value is X'00'. Set to X'01' if the SQLVAR represents a reference type with a target type named in sqldatatype_name. Otherwise, the value is X'00'.
sqldatalen pointer Not used. Used for BLOB, CLOB, and DBCLOB host variables only.
If this field is NULL, then the actual length (in characters) should be stored in the 4 bytes immediately before the start of the data and SQLDATA should point to the first byte of the field length.
If this field is not NULL, it contains a pointer to a 4 byte long buffer that contains the actual length in bytes (even for DBCLOB) of the data in the buffer pointed to from the SQLDATA field in the matching Base SQLVAR.
Note that, whether or not this field is used, the len.sqllonglen field must be set.
sqldatatype_name VARCHAR(27) For a distinct type column, the database manager sets this to the fully qualified distinct type name.¹ For a reference type, the database manager sets this to the fully qualified type name of the target type of the reference. Not used.
reserved CHAR(3) Not used. Not used.

Note:

The first 8 bytes contain the schema name of the type (extended to the right with spaces, if necessary). Byte 9 contains a dot (.). Bytes 10 to 27 contain the low order portion of the type name which is not extended to the right with spaces.

Note that, although the prime purpose of this field is for the name of user-defined types, the field is also set for IBM predefined data types. In this case, the schema name is SYSIBM and the low order portion of the name is the name stored in TYPENAME column of the DATATYPES catalog view. For example:
type name length sqldatatype_name --------- ------ ---------------- A.B 10 A .B INTEGER 16 SYSIBM .INTEGER "Frank's".SMINT 13 Frank's .SMINT MY."type " 15 MY .type

Footnotes:

⁹⁹: The distinct type and LOB information does not overlap, so distinct types can be based on LOBs without forcing the number of SQLVAR entries on a DESCRIBE to be tripled.

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

[ DB2 List of Books | Search the DB2 Books ]