Host Variables

Host variables are COBOL variables used to store data exchanged between a COBOL program and the DB2 database. They are used to send input data to SQL statements (like INSERT or UPDATE) and receive output data from SELECT queries into the COBOL program.

Host variables must be declared in the WORKING-STORAGE SECTION of the COBOL program, and they should have data types compatible with DB2 columns.

Why Use Host Variables?

Input Variables: Used to send data to DB2 (e.g., in INSERT or UPDATE operations).
Output Variables: Used to receive data from DB2 (e.g., in SELECT queries).
Prefix in SQL Statements: Host variables are prefixed with a colon (:) when referenced inside SQL statements.

COBOL Equivalent Declarations for DB2 Data Types -

When using DB2 with COBOL, it is essential to match COBOL data types with DB2 data types. Below are some common DB2 data types and their equivalent COBOL declarations:

DB2 Data Type	COBOL Equivalent Declaration	Example Usage
INTEGER (INT)	PIC S9(9) COMP. or PIC 9(5).	For Employee ID.
SMALLINT	PIC S9(4) COMP.	For Age.
BIGINT	PIC S9(18) COMP-3.	Large Counters.
DECIMAL(p, s)	PIC 9(p-s)V9(s) COMP-3.	For Salary.
CHAR(n)	PIC X(n).	For fixed-length text.
VARCHAR(n)	PIC X(n).	For variable-length names.
DATE	PIC X(10).	For storing dates (YYYY-MM-DD).
TIME	PIC X(8).	For storing time (HH:MM).
TIMESTAMP	PIC X(26).	For timestamp values.

How Host Variables Are Used in COBOL + DB2 Programs -

Declare Host Variables: Ensure that the COBOL variables declared in WORKING-STORAGE SECTION or include DCLGEN (explained in next chapter) that contains the matching COBOL variable declarations for the DB2 column data types.
Assign Values to Input Host Variables: Before SQL statements like INSERT or UPDATE, assign appropriate values to host variables.
Retrieve Data Using Output Host Variables: Use SELECT queries to get data from DB2 and store it in host variables.
Use Colon (:) Prefix in SQL Statements: Reference host variables with :WS-VAR-NAME inside SQL.

Usage -

EXEC SQL 
	INCLUDE dclgen-of-table
END-EXEC.

Note! The EXEC SQL and END-EXEC keywords are used to embed SQL statements within COBOL code. All SQL statements like SELECT, INSERT, UPDATE, DELETE, COMMIT, SQLCA and table DCLGENs are enclosed between these keywords.

Examples -

Scenario1 - INSERT Statement Using Host Variables.

...
EXEC SQL
    INSERT INTO Employees (Emp_ID, Emp_Name, Salary)
    VALUES (:WS-EMP-ID, :WS-EMP-NAME, :WS-SALARY)
END-EXEC.
...

The host variables (WS-EMP-ID, WS-EMP-NAME, and WS-SALARY) are used to insert the input data to the Employees table.

More Examples