Host variables are REXX language variables that are referenced within SQL statements. They allow an application to pass input data to DB2 and receive output data from DB2. REXX applications do not need to declare host variables, except for LOB locators and LOB file reference variables. Host variable data types and sizes are determined at run time when the variables are referenced. Apply the following rules when naming and using host variables.
Any properly named REXX variable can be used as a host variable. A variable name can be up to 64 characters long. Do not end the name with a period. A host variable name can consist of alphabetic characters, numerics, and the characters @, _, !, ., ?, and $.
The REXX interpreter examines every string without quotation marks in a procedure. If the string represents a variable in the current REXX variable pool, REXX replaces the string with the current value. The following is an example of how you can reference a host variable in REXX:
CALL SQLEXEC 'FETCH C1 INTO :cm'
SAY 'Commission = ' cm
To ensure that a character string is not converted to a numeric data type, enclose the string with single quotation marks as in the following example:
VAR = '100'
REXX sets the variable VAR to the 3-byte character string 100. If single quotation marks are to be included as part of the string, follow this example:
VAR = "'100'"
When inserting numeric data into a CHARACTER field, the REXX interpreter treats numeric data as integer data, thus you must concatenate numeric strings explicitly and surround them with single quotation marks.
An indicator variable data type in REXX is a number without a decimal point. Following is an example of an indicator variable in REXX using the INDICATOR keyword.
CALL SQLEXEC 'FETCH C1 INTO :cm INDICATOR :cmind'
IF ( cmind < 0 )
SAY 'Commission is NULL'
In the above example, cmind is examined for a negative value. If it is not negative, the application can use the returned value of cm. If it is negative, the fetched value is NULL and cm should not be used. The database manager does not change the value of the host variable in this case.
SQLEXEC, SQLDBS and SQLDB2 set predefined REXX variables as a result of certain operations. These variables are:
| Note: | The values -8 through -18 are returned only by the GET ERROR MESSAGE API. |
When you fetch a LOB column into a REXX host variable, it will be stored as a simple (that is, uncounted) string. This is handled in the same manner as all character-based SQL types (such as CHAR, VARCHAR, GRAPHIC, LONG, and so on). On input, if the size of the contents of your host variable is larger than 32K, or if it meets other criteria set out below, it will be assigned the appropriate LOB type.
In REXX SQL, LOB types are determined from the string content of your host
variable as follows:
| Host variable string content | Resulting LOB type |
|---|---|
|
:hv1='ordinary quoted string longer than 32K ...' | CLOB |
|
:hv2="'string with embedded delimiting quotation marks ", "longer than 32K...'" | CLOB |
|
:hv3="G'DBCS string with embedded delimiting single ", "quotation marks, beginning with G, longer than 32K...'" | DBCLOB |
|
:hv4="BIN'string with embedded delimiting single ", "quotation marks, beginning with BIN, any length...'" | BLOB |
Syntax for LOB Locator Host Variables in REXX shows the syntax for declaring LOB locator host variables in REXX.
Syntax for LOB Locator Host Variables in REXX
.-,-------------------.
V |
>>-DECLARE-----:--variable-name---+---LANGUAGE TYPE--+-BLOB---+---LOCATOR-->
+-CLOB---+
'-DBCLOB-'
>--------------------------------------------------------------><
You must declare LOB locator host variables in your application. When REXX/SQL encounters these declarations, it treats the declared host variables as locators for the remainder of the program. Locator values are stored in REXX variables in an internal format.
Example:
CALL SQLEXEC 'DECLARE :hv1, :hv2 LANGUAGE TYPE CLOB LOCATOR'
Data represented by LOB locators returned from the engine can be freed in REXX/SQL using the FREE LOCATOR statement which has the following format:
Syntax for FREE LOCATOR Statement
.-,-------------------.
V |
>>-FREE--LOCATOR-----:--variable-name---+----------------------><
Example:
CALL SQLEXEC 'FREE LOCATOR :hv1, :hv2'
You must declare LOB file reference host variables in your application. When REXX/SQL encounters these declarations, it treats the declared host variables as LOB file references for the remainder of the program.
Syntax for LOB File Reference Variables in REXX shows the syntax for declaring LOB file reference host variables in REXX.
REXX File Reference Declarations
.-,-------------------.
V |
>>-DECLARE-----:--variable-name---+---LANGUAGE TYPE--+-BLOB---+---FILE-->
+-CLOB---+
'-DBCLOB-'
>--------------------------------------------------------------><
Example:
CALL SQLEXEC 'DECLARE :hv3, :hv4 LANGUAGE TYPE CLOB FILE'
File reference variables in REXX contain three fields. For the above example they are:
For FILE_OPTIONS, the application sets the following keywords:
| Note: | A file reference host variable is a compound variable in REXX, thus you must set values for the NAME, NAME_LENGTH and FILE_OPTIONS fields in addition to declaring them. |
On OS/2 it may be necessary to explicitly clear REXX SQL LOB locator and file reference host variable declarations as they remain in effect after your application program ends. This is because the application process does not exit until the session in which it is run is closed. If REXX SQL LOB declarations are not cleared, they may interfere with other applications that are running in the same session after a LOB application has been executed.
The syntax to clear the declaration is:
CALL SQLEXEC "CLEAR SQL VARIABLE DECLARATIONS"
You should code this statement at the end of LOB applications. Note that you can code it anywhere as a precautionary measure to clear declarations which might have been left by previous applications (for example, at the beginning of a REXX SQL application).