Host Variables

Host variables are used to hold data that is accessed by both COBOL and SQL commands. You define a host variable as a data structure that represents the column names and data types of the tables that are accessed by the COBOL program.

Host variables are usually defined in copybooks. Copybooks can be quickly created from any SQL Option or DB2 table using the SQL Option Declaration Generator utility.

There are two ways to use host variables:

• To store information returned by the database engine as the result of an SQL statement.
• To store information sent to the database engine and/or dynamic information that controls what is returned by a SELECT statement.

All host variables appearing inside an EXEC SQL ... END-EXEC block should be preceded by a colon, as shown for the host variable DEPT-NUM in the following example:

 EXEC SQL 
     DECLARE EMPCURS CURSOR FOR 
     SELECT LNAME, FNAME, PAYRATE, HOURS 
       FROM EMPLOYEE 
       WHERE DEPT = :DEPT-NUM 
 END-EXEC

DB2 V5 does not require all host variables to be preceded by a colon, but DB2 V6 does. If you plan to upgrade in the future, you should set your message level to WARNING in the build setting for every SQL programs in the project.

The use of host variables allows you to design more flexible queries, and to interactively define the criteria for data selection. In the preceding example, the host variable specifies the department for which data will be selected.

For information about null values in host variables, see the section Using Indicator Variables .