Host variables in DB2 COBOL programs

What a host variable is

A host variable is an application variable that passes data between the program and Db2. It is not a Db2 object. It does not live in the catalog. It is a COBOL data item whose name, when prefixed with a colon, the precompiler recognizes inside SQL.

You use host variables to:

• Supply predicate values (WHERE EMPNO = :HV-EMPNO)
• Receive SELECT INTO or FETCH column values
• Provide INSERT/UPDATE column values
• Pass arguments on CALL to a stored procedure
• Hold dynamic SQL strings for PREPARE / EXECUTE IMMEDIATE

Literals in static SQL are baked into the statement text. Host variables keep the statement text stable so one bound package serves every employee number you process. That is better for the dynamic statement cache (static SQL does not need it) and for security: you are not concatenating unchecked text into SQL.

Declaring host variables

IBM rules for COBOL:

• Declare every host variable and host-variable array explicitly in the WORKING-STORAGE SECTION or LINKAGE SECTION
• No implicit typing and no COBOL IMPLICIT for SQL hosts
• With ONEPASS (common), declare the variable before any SQL that uses it
• With TWOPASS, declare it before DECLARE CURSOR if the cursor SQL references it
• The SQL statement must be in scope of the declaration (the same compilation unit)
• With the Db2 precompiler, names must be unique in the program unless you qualify them with a structure name
• OCCURS is for host-variable arrays and indicator arrays—not for a scalar host variable

If STDSQL(YES) is set, wrap declarations in BEGIN/END DECLARE SECTION. Otherwise those statements are optional. Many shops still use them so humans can see which fields are SQL hosts.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
       DATA DIVISION.
       WORKING-STORAGE SECTION.
       EXEC SQL
            INCLUDE SQLCA
       END-EXEC.
       EXEC SQL
            BEGIN DECLARE SECTION
       END-EXEC.
       01  HV-EMPNO              PIC X(6).
       01  HV-LASTNAME           PIC X(15).
       01  HV-SALARY             PIC S9(7)V99 COMP-3.
       EXEC SQL
            END DECLARE SECTION
       END-EXEC.

DCLGEN output is usually INCLUDE'd inside the declare section (or immediately after SQLCA). That INCLUDE expands to a DECLARE TABLE and a level-01 structure whose elementary items are the host variables for each column.

Using host variables in SQL

Inside EXEC SQL, write a colon immediately before the name. You may qualify with a structure: :DCL-EMPLOYEE.EMPNO. Indicator variables, covered on the next page, follow the host: :HV-SALARY:HV-SALARY-IND.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
           MOVE '000010' TO HV-EMPNO.
           EXEC SQL
                SELECT LASTNAME, SALARY
                  INTO :HV-LASTNAME,
                       :HV-SALARY
                  FROM HR.EMPLOYEE
                 WHERE EMPNO = :HV-EMPNO
           END-EXEC.
 
           EXEC SQL
                UPDATE HR.EMPLOYEE
                   SET SALARY = :HV-SALARY
                 WHERE EMPNO = :HV-EMPNO
           END-EXEC.
 
           EXEC SQL
                INSERT INTO HR.EMPLOYEE (EMPNO, LASTNAME, SALARY)
                VALUES (:HV-EMPNO, :HV-LASTNAME, :HV-SALARY)
           END-EXEC.

On FETCH or SELECT INTO, Db2 writes into the host. On INSERT, UPDATE, and predicates, Db2 reads from the host. Always MOVE input values before the SQL statement. A stale WORKING-STORAGE field is a common “wrong row updated” bug.

Host variables are not valid everywhere in SQL. You cannot use a host variable as a table name or column name in static SQL. Those identifiers must be in the source. For names that change at run time you need dynamic SQL.

Data type matching

The host picture must be compatible with the SQL type. Compatible does not always mean identical: SMALLINT can often go into an INTEGER host, and CHAR can promote toward VARCHAR, but you should still match what DCLGEN emits. Wrong length on CHAR/VARCHAR causes padding or truncation. Wrong scale on DECIMAL causes rounding or overflow. Using PIC X for INTEGER stores the wrong bytes.

Shop standards vary between COMP, COMP-4, COMP-5, and BINARY. COMP-5 is the usual recommendation for Db2 binary integers because it uses the native binary representation the compiler and Db2 agree on. Packed decimal for DECIMAL should be COMP-3 with the same number of integer digits and scale digits as the column: DECIMAL(9,2) maps to PIC S9(7)V99 COMP-3, not PIC S9(9)V99.

VARCHAR groups

A VARCHAR host is a group. You set the length field before INSERT/UPDATE. After FETCH, you use the length field to know how much of the data picture is valid. Forgetting to set the length on INSERT is a classic defect (Db2 may see length 0 or garbage).

1
2
3
4
5
6
7
8
9
10
11
       01  HV-COMMENT.
           49 HV-COMMENT-LEN     PIC S9(4) COMP-5.
           49 HV-COMMENT-TEXT    PIC X(254).
 
           MOVE 'On leave' TO HV-COMMENT-TEXT.
           MOVE 8          TO HV-COMMENT-LEN.
           EXEC SQL
                UPDATE HR.EMPLOYEE
                   SET COMM_TXT = :HV-COMMENT
                 WHERE EMPNO = :HV-EMPNO
           END-EXEC.

GRAPHIC/VARGRAPHIC use PIC G and DISPLAY-1 (or NATIONAL, depending on the column and compiler options). BINARY/VARBINARY, LOBs, XML, and ROWID use SQL TYPE IS declarations—the advanced host-variable page covers those.

Host structures

A host structure is a COBOL group whose elementary items map to a list of columns. SELECT INTO :DCL-EMPLOYEE can fill the whole DCLGEN structure in column order. The number and order of columns in the SELECT list must match the structure. Using a structure is convenient; using elementary names is clearer when you select a subset of columns.

Qualifying names (:DCL-EMP.EMPNO) avoids clashes when two DCLGENs both contain EMPNO. The precompiler uniqueness rule is the reason shops prefix DCLGEN fields with NAMES(EMP-) COLSUFFIX(YES).

SELECT list order and INTO lists

The INTO list is positional. The first host receives the first selected expression, the second host the second, and so on. Names do not magically match column names. If you SELECT LASTNAME, SALARY into :HV-SALARY, :HV-LASTNAME you have swapped the values and the types may still be compatible enough to bind—then you display a salary as a name.

Count expressions, not tables. A join that selects five columns needs five hosts (plus indicators). A SELECT * into a DCLGEN structure works only while the table matches that DCLGEN. Adding a column in production without regenerating DCLGEN is a bind or run-time mismatch waiting to happen. Prefer explicit column lists in application SQL even when the host is a structure.

Host variables in predicates

Predicates with hosts are still sargable in the usual cases: equality, range, and many LIKE patterns where the pattern is in the host. The optimizer does not know the value at bind time (unless you REOPT), so it uses statistics and default filter factors. That is why a host variable is not “worse than a literal” by definition, but a highly selective literal can get a different access path than a host.

LIKE :HV-PATTERN is only index-friendly when the value does not start with a wildcard. The program can still put '%SMITH' in the host; Db2 will scan. TRIM, SUBSTR, or scalar functions wrapped around the column (not the host) are what usually kill indexability—not the fact that the other side is a host variable.

Compare compatible types. CHAR(6) EMPNO compared with a PIC X(10) host may pad or truncate according to assignment rules and surprise you with no rows. Keep the search host the same length and type as the column, just as DCLGEN would.

Common host-variable mistakes

• Forgetting the colon inside SQL—the precompiler then thinks you meant a column named HV-EMPNO
• Using the colon in COBOL MOVE/IF—that is invalid COBOL
• Reusing one PIC X(n) for every string column “to save fields,” then truncating LASTNAME into a 6-byte employee-number host
• Declaring the host after the SQL statement under ONEPASS
• Two DCLGENs both emitting EMPNO without a prefix, so the precompiler sees duplicate names
• Passing a group item that is not a recognized VARCHAR/SQL TYPE structure and getting a precompiler error or the wrong bytes

Host variables on CALL

Stored-procedure arguments are host variables too. Match the PARAMETER STYLE and the CREATE PROCEDURE parameter types. IN arguments are read from the host; OUT/INOUT are written back. Nullable parameters need indicators in the CALL list just like INSERT columns. USING DESCRIPTOR :SQLDA is the dynamic form when the procedure signature is not fixed at precompile time.

1
2
3
           EXEC SQL
                CALL HR.RAISE_SALARY (:HV-EMPNO, :HV-RATE)
           END-EXEC.

Nulls, indicators, and initialization

A host variable alone cannot represent SQL NULL. If the column is nullable, you need an indicator variable (next page). FETCH of NULL into a host without an indicator is an error (SQLCODE -305).

Initialize hosts before use. COMP-5 fields should be ZERO, CHAR fields SPACE or a real key, VARCHAR length ZERO until you MOVE text. Garbage in a WHERE host variable looks like a mysterious empty result set.

Explain It Like I'm Five

A host variable is a labeled cup on your COBOL desk. When you ask Db2 for a last name, Db2 pours the name into that cup. When you ask Db2 to find employee 000010, you first put “000010” in the cup and point at it with a colon so Db2 knows which cup to read. The cup's shape must fit the drink: a tiny cup cannot hold a long comment, and a letter cup should not be used for packed money. DCLGEN is the factory that makes cups the same shape as the table's bottles.

Exercises

1. Declare host variables for EMPNO CHAR(6), SALARY DECIMAL(9,2), and HIREDATE DATE.
2. Write SELECT INTO and UPDATE statements that use those hosts, including the colon.
3. Sketch the VARCHAR group for a VARCHAR(100) column and the MOVEs required before INSERT.
4. Explain why PIC S9(9)V99 COMP-3 is the wrong picture for DECIMAL(9,2).
5. Find whether your shop uses COMP-5 or COMP for SMALLINT/INTEGER and why DCLGEN matches that choice.