Host variables are COBOL language variables that are referenced within SQL statements. They allow an application to pass input data to the database manager and receive output data from the database manager. After the application is precompiled, host variables are used by the compiler as any other COBOL variable. Obey the rules described below when naming, declaring, and using host variables.
The SQL precompiler identifies host variables by their declared name. The following rules apply:
SQL interprets a hyphen enclosed by spaces as a subtraction operator. Use hyphens without spaces in host variable names.
An SQL declare section must be used to identify host variable declarations. This alerts the precompiler to any host variables that can be referenced in subsequent SQL statements.
The COBOL precompiler only recognizes a subset of valid COBOL declarations.
Syntax for Numeric Host Variables in COBOL shows the syntax for numeric host variables.
Syntax for Numeric Host Variables in COBOL
.-IS-.
>>-+-01-+--variable-name---+-PICTURE-+--+----+--picture-string-->
'-77-' '-PIC-----'
>-----+------------------------------------------+-------------->
| (1) |
'-+---------------+---+-COMP-3----------+--'
| .-IS-. | +-COMPUTATIONAL-3-+
'-USAGE-+----+--' +-COMP-5----------+
'-COMPUTATIONAL-5-'
>----+----------------------+---.------------------------------><
| .-IS-. |
'-VALUE-+----+--value--'
Notes:
Floating Point
>>-+-01-+--variable-name----+---------------+------------------->
'-77-' | .-IS-. |
'-USAGE-+----+--'
(1)
>-----+--+-COMPUTATIONAL-1-+--------+--------------------------->
| '-COMP-1----------' |
| (2) |
'--+-COMPUTATIONAL-2-+--------'
'-COMP-2----------'
>-----+----------------------+--.------------------------------><
| .-IS-. |
'-VALUE-+----+--value--'
Notes:
Numeric Host Variable Considerations:
Syntax for Character Host Variables in COBOL: Fixed Length shows the syntax for character host variables.
Syntax for Character Host Variables in COBOL: Fixed Length
.-IS-.
>>-+-01-+--variable-name---+-PICTURE-+--+----+--picture-string-->
'-77-' '-PIC-----'
>-----+----------------------+--.------------------------------><
| .-IS-. |
'-VALUE-+----+--value--'
Variable Length
>>-01--variable-name-.-----------------------------------------><
.-IS-.
>>-49--identifier-1---+-PICTURE-+--+----+--S9(4)---------------->
'-PIC-----'
>-----+------------------------------------------+-------------->
'-+---------------+---+-COMP-5----------+--'
| .-IS-. | '-COMPUTATIONAL-5-'
'-USAGE-+----+--'
>-----+----------------------+--.------------------------------><
| .-IS-. |
'-VALUE-+----+--value--'
.-IS-.
>>-49--identifier-2---+-PICTURE-+--+----+--picture-string------->
'-PIC-----'
>-----+----------------------+--.------------------------------><
| .-IS-. |
'-VALUE-+----+--value--'
Character Host Variable Consideration:
EXEC SQL CONNECT TO :dbname USER :userid USING :p-word
END-EXEC.
However, because blanks can be significant in passwords, the p-word host variable should be declared as a VARCHAR data item, so that your application can explicitly indicate the significant password length for the CONNECT statement as follows:
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 dbname PIC X(8).
01 userid PIC X(8).
01 p-word.
49 L PIC S9(4) COMP-5.
49 D PIC X(18).
EXEC SQL END DECLARE SECTION END-EXEC.
PROCEDURE DIVISION.
MOVE "sample" TO dbname.
MOVE "userid" TO userid.
MOVE "password" TO D OF p-word.
MOVE 8 TO L of p-word.
EXEC SQL CONNECT TO :dbname USER :userid USING :p-word
END-EXEC.
Syntax for Graphic Host Variables in COBOL: Fixed Length shows the syntax for graphic host variables.
Syntax for Graphic Host Variables in COBOL: Fixed Length
>>-+-01-+--variable-name---+-PICTURE-+-------------------------->
'-77-' '-PIC-----'
.-IS-. .-IS-.
>----+----+--picture-string-USAGE---+----+--DISPLAY-1----------->
>-----+----------------------+--.------------------------------><
| .-IS-. |
'-VALUE-+----+--value--'
Variable Length
>>-01--variable-name-.-----------------------------------------><
.-IS-.
>>-49--identifier-1---+-PICTURE-+--+----+--S9(4)---------------->
'-PIC-----'
>-----+------------------------------------------+-------------->
'-+---------------+---+-COMP-5----------+--'
| .-IS-. | '-COMPUTATIONAL-5-'
'-USAGE-+----+--'
>-----+----------------------+--.------------------------------><
| .-IS-. |
'-VALUE-+----+--value--'
>>-49--identifier-2---+-PICTURE-+------------------------------->
'-PIC-----'
.-IS-. .-IS-.
>----+----+--picture-string-USAGE---+----+--DISPLAY-1----------->
>-----+----------------------+--.------------------------------><
| .-IS-. |
'-VALUE-+----+--value--'
Graphic Host Variable Considerations:
Indicator variables should be declared as a PIC S9(4) COMP-5 data type.
Syntax for LOB Host Variables in COBOL shows the syntax for declaring large object (LOB) host variables in COBOL.
Syntax for LOB Host Variables in COBOL
>>-01--variable-name----+--------------+--SQL TYPE IS----------->
'-USAGE-+----+-'
'-IS-'
>-----+-BLOB---+--(--length--+---+---)--.----------------------><
+-CLOB---+ +-K-+
'-DBCLOB-' +-M-+
'-G-'
LOB Host Variable Considerations:
BLOB Example:
Declaring:
01 MY-BLOB USAGE IS SQL TYPE IS BLOB(2M).
Results in the generation of the following structure:
01 MY-BLOB.
49 MY-BLOB-LENGTH PIC S9(9) COMP-5.
49 MY-BLOB-DATA PIC X(2097152).
CLOB Example:
Declaring:
01 MY-CLOB USAGE IS SQL TYPE IS CLOB(125M).
Results in the generation of the following structure:
01 MY-CLOB.
49 MY-CLOB-LENGTH PIC S9(9) COMP-5.
49 MY-CLOB-DATA PIC X(131072000).
DBCLOB Example:
Declaring:
01 MY-DBCLOB USAGE IS SQL TYPE IS DBCLOB(30000).
Results in the generation of the following structure:
01 MY-DBCLOB.
49 MY-DBCLOB-LENGTH PIC S9(9) COMP-5.
49 MY-DBCLOB-DATA PIC G(30000) DISPLAY-1.
Syntax for LOB Locator Host Variables in COBOL shows the syntax for declaring large object (LOB) locator host variables in COBOL.
Syntax for LOB Locator Host Variables in COBOL
>>-01--variable-name----+--------------+--SQL TYPE IS----------->
'-USAGE-+----+-'
'-IS-'
>-----+-BLOB-LOCATOR---+--.------------------------------------><
+-CLOB-LOCATOR---+
'-DBCLOB-LOCATOR-'
LOB Locator Host Variable Considerations:
BLOB Locator Example (other LOB locator types are similar):
Declaring:
01 MY-LOCATOR USAGE SQL TYPE IS BLOB-LOCATOR.
Results in the generation of the following declaration:
01 MY-LOCATOR PIC S9(9) COMP-5.
Syntax for File Reference Host Variables in COBOL shows the syntax for declaring file reference host variables in COBOL.
Syntax for File Reference Host Variables in COBOL
>>-01--variable-name----+--------------+--SQL TYPE IS----------->
'-USAGE-+----+-'
'-IS-'
>-----+-BLOB-FILE---+--.---------------------------------------><
+-CLOB-FILE---+
'-DBCLOB-FILE-'
BLOB File Reference Example (other LOB types are similar):
Declaring:
01 MY-FILE USAGE IS SQL TYPE IS BLOB-FILE.
Results in the generation of the following declaration:
01 MY-FILE.
49 MY-FILE-NAME-LENGTH PIC S9(9) COMP-5.
49 MY-FILE-DATA-LENGTH PIC S9(9) COMP-5.
49 MY-FILE-FILE-OPTIONS PIC S9(9) COMP-5.
49 MY-FILE-NAME PIC X(255).
The COBOL precompiler supports declarations of group data items in the host variable declare section. Among other things, this provides a shorthand for referring to a set of elementary data items in an SQL statement. For example, the following group data item can be used to access some of the columns in the STAFF table of the SAMPLE database:
01 staff-record.
05 staff-id pic s9(4) comp-5.
05 staff-name.
49 l pic s9(4) comp-5.
49 d pic x(9).
05 staff-info.
10 staff-dept pic s9(4) comp-5.
10 staff-job pic x(5).
Group data items in the declare section can have any of the valid host variable types described above as subordinate data items. This includes all numeric and character types, as well as all large object types. You can nest group data items up to 10 levels. Note that you must declare VARCHAR character types with the subordinate items at level 49, as in the above example. If they are not at level 49, the VARCHAR is treated as a group data item with two subordinates, and is subject to the rules of declaring and using group data items. In the example above, staff-info is a group data item, whereas staff-name is a VARCHAR. The same principle applies to LONG VARCHAR, VARGRAPHIC and LONG VARGRAPHIC. You may declare group data items at any level between 02 and 49.
You can use group data items and their subordinates in four ways:
Method 1.
The entire group may be referenced as a single host variable in an SQL statement:
EXEC SQL SELECT id, name, dept, job
INTO :staff-record
FROM staff WHERE id = 10 END-EXEC.
The precompiler converts the reference to staff-record into a list, separated by commas, of all the subordinate items declared within staff-record. Each elementary item is qualified with the group names of all levels to prevent naming conflicts with other items. This is equivalent to the following method.
Method 2.
The second way of using group data items:
EXEC SQL SELECT id, name, dept, job
INTO
:staff-record.staff-id,
:staff-record.staff-name,
:staff-record.staff-info.staff-dept,
:staff-record.staff-info.staff-job
FROM staff WHERE id = 10 END-EXEC.
| Note: | The reference to staff-id is qualified with its group name using the prefix staff-record., and not staff-id of staff-record as in pure COBOL. |
Assuming there are no other host variables with the same names as the subordinates of staff-record, the above statement can also be coded as in method 3, eliminating the explicit group qualification.
Method 3.
Here, subordinate items are referenced in a typical COBOL fashion, without being qualified to their particular group item:
EXEC SQL SELECT id, name, dept, job
INTO
:staff-id,
:staff-name,
:staff-dept,
:staff-job
FROM staff WHERE id = 10 END-EXEC.
As in pure COBOL, this method is acceptable to the precompiler as long as a given subordinate item can be uniquely identified. If, for example, staff-job occurs in more than one group, the precompiler issues an error indicating an ambiguous reference:
SQL0088N Host variable "staff-job" is ambiguous.
Method 4.
To resolve the ambiguous reference, you can use partial qualification of the subordinate item, for example:
EXEC SQL SELECT id, name, dept, job
INTO
:staff-id,
:staff-name,
:staff-info.staff-dept,
:staff-info.staff-job
FROM staff WHERE id = 10 END-EXEC.
Because a reference to a group item alone, as in method 1, is equivalent to a comma-separated list of its subordinates, there are instances where this type of reference leads to an error. For example:
EXEC SQL CONNECT TO :staff-record END-EXEC.
Here, the CONNECT statement expects a single character-based host variable. By giving the staff-record group data item instead, the host variable results in the following precompile-time error:
SQL0087N Host variable "staff-record" is a structure used where
structure references are not permitted.
Other uses of group items which cause an SQL0087N to occur include PREPARE, EXECUTE IMMEDIATE, CALL, indicator variables, and SQLDA references. Groups with only one subordinate are permitted in such situations, as are references to individual subordinates, as in method 2, 3 and 4 above.
The COBOL precompiler supports the declaration of tables of indicator variables, which are convenient to use with group data items. They are declared as follows:
01 <indicator-table-name>.
05 <indicator-name> pic s9(4) comp-5
occurs <table-size> times.
For example:
01 staff-indicator-table.
05 staff-indicator pic s9(4) comp-5
occurs 7 times.
This indicator table can be used effectively with the first format of group item reference above:
EXEC SQL SELECT id, name, dept, job
INTO :staff-record :staff-indicator
FROM staff WHERE id = 10 END-EXEC.
Here, the precompiler detects that staff-indicator was declared as an indicator table, and expands it into individual indicator references when it processes the SQL statement. staff-indicator(1) is associated with staff-id of staff-record, staff-indicator(2) is associated with staff-name of staff-record, and so on.
| Note: | If there are k more indicator entries in the indicator table than there are subordinates in the data item (for example, if staff-indicator has 10 entries, making k=6), the k extra entries at the end of the indicator table are ignored. Likewise, if there are k fewer indicator entries than subordinates, the last k subordinates in the group item do not have indicators associated with them. Note that you can refer to individual elements in an indicator table in an SQL statement. |
You can use the REDEFINES clause when declaring host variables. If you declare a member of a group data item with the REDEFINES clause and that group data item is referred to as a whole in an SQL statement, any subordinate items containing the REDEFINES clause are not expanded. For example:
01 foo.
10 a pic s9(4) comp-5.
10 a1 redefines a pic x(2).
10 b pic x(10).
Referring to foo in an SQL statement as follows:
... INTO :foo ...
The above statement is equivalent to:
... INTO :foo.a, :foo.b ...
That is, the subordinate item a1, declared with the REDEFINES clause is not automatically expanded out in such situations. If a1 is unambiguous, you can explicitly refer to a subordinate with a REDEFINES clause in an SQL statement, as follows:
... INTO :foo.a1 ...
or
... INTO :a1 ...
The DB2 COBOL precompiler supports the use of BINARY, COMP, and COMP-4 data types wherever integer host variables and indicators are permitted, as long as the target COBOL compiler views (or can be made to view) the BINARY, COMP, or COMP-4 data types as equivalent to the COMP-5 data type. In this book, such host variables and indicators are shown with type COMP-5. Target compilers supported by DB2 that treat COMP, COMP-4, BINARY COMP and COMP-5 as equivalent are: