# What is the usage and purpose of DCLGEN and host variables used in COBOL-DB2 program

DB2DatabaseBig Data Analytics

A COBOL-DB2 program can query/update/insert/delete data from multiple DB2 tables. However, in order to achieve this, we must fulfill two main conditions.

1. Notify the structure of the DB2 table to the COBOL-DB2 program. This includes all the columns and data types of these columns.

2. The respective host variables for each column. The host variables are used in the program logic to move the data from DB2 to program and vice versa. There is one host variable for every table column based on its data type. For example, for a table column with data type CHAR(2), there should be a host variable with equivalent COBOL data type as PIC X(2).

The DCLGEN utility helps us to generate the table structures and host variables automatically. Using this utility, we just need to give the DB2 table name and it will return the table structure and the host variables in a PDS member. We can simply use this PDS member in our COBOL-DB2 program in the working storage section using the INCLUDE statement as below−

EXEC SQL
INCLUDE ORDERD
END-EXEC

The ORDERD is a PDS member which is generated using a DCLGEN utility. This will have the structure of the ORDERS table and host variables for all the columns. For example,the columns ORDER_ID and ORDER_DATE with data type as CHAR(30) and TIMESTAMP will have a host variable as ORDER-ID PIC X(30) and ORDER-DATE PIC X(26) respectively.If we want to use this host variable to move the data from DB2 to program, then we can have−

EXEC SQL
EXEC SQL
SELECT ORDER_DATE
INTO :ORDER-DATE,
FROM ORDERS
WHERE ORDER_ID = :ORDER-ID
END-EXEC
END-EXEC
Published on 14-Sep-2020 14:27:30