Host variables in assembler

In assembler programs, you can specify numeric, character, graphic, binary, LOB, XML, and ROWID host variables. You can also specify result set, table, and LOB locators and LOB and XML file reference variables.

Restrictions:

Only some of the valid assembler declarations are valid host variable declarations. If the declaration for a host variable is not valid, any SQL statement that references the variable might result in the message UNDECLARED HOST VARIABLE.
The locator data types are assembler language data types and SQL data types. You cannot use locators as column types.

Recommendations:

Be careful of overflow. For example, suppose that you retrieve an INTEGER column value into a DS H host variable, and the column value is larger than 32767. You get an overflow warning or an error, depending on whether you provide an indicator variable.
Be careful of truncation. For example, if you retrieve an 80-character CHAR column value into a host variable that is declared as DS CL70, the rightmost ten characters of the retrieved string are truncated. If you retrieve a floating-point or decimal column value into a host variable declared as DS F, any fractional part of the value is removed.

Numeric host variables

The following diagram shows the syntax for declaring numeric host variables.

Notes:

¹ value is a numeric value that specifies the scale of the packed decimal variable. If value does not include a decimal point, the scale is 0.

For floating-point data types (E, EH, EB, D, DH, and DB), use the FLOAT SQL processing option to specify whether the host variable is in IEEE binary floating-point or z/Architecture® hexadecimal floating-point format. If you specify FLOAT(S390), you need to define your floating-point host variables as E, EH, D, or DH. If you specify FLOAT(IEEE), you need to define your floating-point host variables as EB or DB. Db2 does not check if the host variable declarations or format of the host variable contents match the format that you specified with the FLOAT SQL processing option. Therefore, you need to ensure that your floating-point host variable types and contents match the format that you specified with the FLOAT SQL processing option. Db2 converts all floating-point input data to z/Architecture hexadecimal floating-point format before storing it.

Restriction: The FLOAT SQL processing options do not apply to the decimal floating-point host variable types ED, DD, or LD.

For the decimal floating-point host variable types ED, DD, and LD, you can specify the following special values: MIN, MAX, NAN, SNAN, and INFINITY.

Character host variables

You can specify the following forms of character host variables:

Fixed-length strings
Varying-length strings
CLOBs

The following diagrams show the syntax for forms other than CLOBs.

The following diagram shows the syntax for declaring fixed-length character strings.

Notes:

¹ If you declare a character string host variable without a length (for example, DC C 'ABCD') Db2 interprets the length as 1. To get the correct length, specify a length attribute (for example, DC CL 4 'ABCD').

The following diagram shows the syntax for declaring varying-length character strings.

Graphic host variables

You can specify the following forms of graphic host variables:

Fixed-length strings
Varying-length strings
DBCLOBs

The following diagrams show the syntax for forms other than DBCLOBs. In the syntax diagrams, value denotes one or more DBCS characters, and the symbols < and > represent the shift-out and shift-in characters.

The following diagram shows the syntax for declaring fixed-length graphic strings.

The following diagram shows the syntax for declaring varying-length graphic strings.

Binary host variables

The following diagram shows the syntax for declaring binary host variables.

Notes:

¹ 1 ≤ n ≤ 255

Varbinary host variables

The following diagram shows the syntax for declaring varbinary host variables.

Notes:

¹ 1 ≤ n ≤ 32704

Result set locators

The following diagram shows the syntax for declaring result set locators.

Notes:

¹ To be compatible with previous releases, result set locator host variables may be declared as fullword integers (FL4), but the method shown is the preferred syntax.

Table Locators

The following diagram shows the syntax for declaring of table locators.

LOB variables, locators, and file reference variables

The following diagram shows the syntax for declaring BLOB, CLOB, and DBCLOB host variables, locators, and file reference variables.

XML data host and file reference variables

The following diagram shows the syntax for declaring BLOB, CLOB, and DBCLOB host variables and file reference variables for XML data types.

Notes:

¹ If you specify the length of the LOB in terms of KB, MB, or GB, do not leave spaces between the length and K, M, or G.

ROWIDs

The following diagram shows the syntax for declaring ROWID host variables.