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.
- 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.
- 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.
>>-variable-name--+-DC-+--+---+--+-H--+----+--------------+---->< '-DS-' '-1-' | '-L2-' | +-F--+----+--------------+ | '-L4-' | +-FD--+----+-------------+ | '-L8-' | | (1) | +-P--+----+--'value----'-+ | '-Ln-' | +-E--+----+--------------+ | '-L4-' | +-EH--+----+-------------+ | '-L4-' | +-EB--+----+-------------+ | '-L4-' | +-ED--+----+-------------+ | '-L4-' | +-D--+----+--------------+ | '-L8-' | +-DH--+----+-------------+ | '-L8-' | +-DB--+----+-------------+ | '-L8-' | +-DD--+----+-------------+ | '-L8-' | '-LD--+-----+------------' '-L16-'
- 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.
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.
>>-variable-name--+-DC-+--+---+--C--+--------+----------------->< '-DS-' '-1-' | (1) | '-Ln-----'
- 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.
>>-variable-name--+-DC-+--+---+--H--+----+--,--+---+--CLn------>< '-DS-' '-1-' '-L2-' '-1-'
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.
>>-variable-name--+-DC-+--G--+-------------+------------------->< '-DS-' +-Ln----------+ +-'<value>'---+ '-Ln'<value>'-'
The following diagram shows the syntax for declaring varying-length graphic strings.
>>-variable-name--+-DS-+--H--+----+--+-----+--,--GLn--+-----------+->< '-DC-' '-L2-' '-'m'-' '-'<value>'-'
Binary host variables
The following diagram shows the syntax for declaring binary host variables.
(1) >>-variable-name--DS--X--Ln------------------------------------><
- 1 ≤ n ≤ 255
Varbinary host variables
The following diagram shows the syntax for declaring varbinary host variables.
(1) >>-variable-name--DS--H--L2--,--X--Ln--------------------------><
- 1 ≤ n ≤ 32704
Result set locators
The following diagram shows the syntax for declaring result set locators.
(1) >>-variable-name----SQL TYPE IS RESULT_SET_LOCATOR VARYING-------><
- 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.
>>-variable-name--SQL TYPE IS--TABLE LIKE--table-name--AS LOCATOR-><
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.
>>-variable-name--SQL TYPE IS--+-+-+-BINARY LARGE OBJECT-+----+--length-+---+-+->< | | '-BLOB----------------' | +-K-+ | | +-+-CHARACTER LARGE OBJECT-+-+ +-M-+ | | | +-CHAR LARGE OBJECT------+ | '-G-' | | | '-CLOB-------------------' | | | '-DBCLOB---------------------' | +-+-BLOB_LOCATOR---+---------------------------+ | +-CLOB_LOCATOR---+ | | '-DBCLOB_LOCATOR-' | '-+-BLOB_FILE---+------------------------------' +-CLOB_FILE---+ '-DBCLOB_FILE-'
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.
(1) >>-variable-name--SQL TYPE IS XML AS--+-+-+-BINARY LARGE OBJECT-+----+--length-+---+-----+->< | | '-BLOB----------------' | +-K-+ | | +-+-CHARACTER LARGE OBJECT-+-+ +-M-+ | | | +-CHAR LARGE OBJECT------+ | '-G-' | | | '-CLOB-------------------' | | | '-DBCLOB---------------------' | '-+-BLOB_FILE---+----------------------------------' +-CLOB_FILE---+ '-DBCLOB_FILE-'
- 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.
>>-variable-name--SQL TYPE IS--ROWID---------------------------><