# How does DCLGEN utility accommodate NULL host variable for VARCHAR(n) data type?

DB2DatabaseBig Data Analytics

#### Oracle DB Online Training

41 Lectures 8.5 hours

#### Oracle DB 12c Online Training

33 Lectures 7 hours

#### Cosmos DB

30 Lectures 1 hours

The DB2 column can store NULL value if it is explicitly not defined with the ‘NOT NULL’ option. However, COBOL does not have any NULL concept. In order to handle these NULL values, COBOL programs use spaces for character columns and zeros for integer columns having NULL value.

However, the main challenge is how to detect that particular column is having NULL value and how to move spaces/zeros into corresponding host variables. In order to overcome this, the DCLGEN utility generates a NULL indicator for each DB2 column which can hold a null value. The NULL indicator is a 2 byte field and takes the value as -1 when the corresponding column has the NULL value in it. The configuration of NULL indicator in COBOL is PIC S9(4) COMP.

For example, we have a DB2 column ORDER_DESCRIPTION with data type VARCHAR(50) and it can hold NULL value. The corresponding host variable and NULL indicator for this column generated by DCLGEN will be as below−

01 ORDER-DESCRIPTION
49 ORDER-DESCRIPTION-N PIC S9(4) COMP
49 ORDER-DESCRIPTION-DATA PIC X(50).

We can check if the ORDER_DECRIPTION column has NULL value in a COBOL-DB2 program like below.

A010-CHECK-ORDER.
EXEC SQL
SELECT ORDER_DESCRIPTION INTO :ORDER-DESCRIPTION-DATA :ORDERDESCRIPTION-N
FROM ORDERS
WHERE ORDER_ID = :ORDER-ID
END-EXEC
IF ORDER-DESCRIPTION-N = -1
MOVE SPACES TO ORDER-DESCRIPTION-DATA
END-IF

In this example, when the value of the NULL indicator is -1, it indicates that the ORDER_DESCRIPTION column has the NULL value and we have moved spaces in the host variable ORDER-DESCRIPTION-DATA in this case.

Updated on 14-Sep-2020 14:39:55