Host Variables

Host variables are the bridge between COBOL and DB2. They carry input values from the program into SQL statements, and they receive result values back from DB2 into the program.

What are host variables

• A host variable is an ordinary COBOL data item declared in WORKING-STORAGE (or LINKAGE SECTION) and referenced inside SQL with a leading colon, for example :WS-CUSTNO.
• Input host variables appear in the WHERE clause or VALUES clause: WHERE CUSTNO = :WS-CUSTNO.
• Output host variables appear in the INTO clause: SELECT FNAME, LNAME INTO :WS-FNAME, :WS-LNAME.
• A group item can be used as a host structure: SELECT * INTO :CUSTOMER-ROW fetches a whole row into a group whose subordinate items match the columns.

Rules for declaring host variables

• The COBOL picture must be compatible with the DB2 column type. Common mappings: CHAR to PIC X(n), VARCHAR to PIC X(n), SMALLINT to PIC S9(4) COMP, INTEGER to PIC S9(9) COMP, DECIMAL(p,s) to PIC S9(p-s)V9(s) COMP-3, DATE to PIC X(10).
• Declare host variables before the first EXEC SQL that uses them, normally at the top of WORKING-STORAGE.
• Do not use SQL reserved words as host variable names.
• A host variable used in SQL must be an elementary item or a group used as a host structure; redefined items need care.
• Always prefix with a colon inside SQL. Without the colon, DB2 treats the name as a column name.

Null indicator variables

• If a column can contain nulls, pair its host variable with an indicator variable: INTO :WS-SALARY :WS-SAL-IND.
• The indicator must be declared as PIC S9(4) COMP (a halfword integer).
• On FETCH or SELECT INTO, DB2 sets the indicator to -1 when the column value is null, 0 when it is not null.
• On INSERT or UPDATE, set the indicator to -1 to store a null, or 0 to store the host variable's value.
• Skipping the indicator when nulls are possible gives SQLCODE -305 (null indicator needed).

Example of host variable declarations and a SELECT with a null indicator:-