NULL In DB2 and COBOL

SQL, unlike COBOL, supports variables that can contain null (values – the use of word “value” along with NULL is not usually encouraged as NULL itself means the absence of value and it in itself can not be termed as “value”, well). Actually, a null “value” means that no entry has been made and usually implies that the value is either unknown or undefined, at the moment. For example, a null value in a joining date column does not mean that the date is undetermined, it means that the date is not known or has not been set yet.

For a column in a DB2 table for which null is allowed, special handling is required in a COBOL program. The program should define a null indicator variable for each column for which nulls are allowed. This null indicator variable should be defined as a binary halfword – PIC S9(4) COMP (why? – well, at the moment treat it as a rule) – and should be listed in the FETCH as an additional host variable immediately after the regular host variable. If the value of this indicator variable is less than zero then the attribute within the table was null.

To answer your question – let’s begin with a CREATE table called MYTABLE which contains three fields: COL_A (which is NOT NULL), COL_B (nulls allowed) and COL_C (nulls allowed). We then use INSERT statements to populate the table:

CREATE TABLE MYTABLE
( COL_A CHAR(2) NOT NULL,
COL_B CHAR(2) ,
COL_C CHAR(2) ) ;

INSERT INTO MYTABLE VALUES ('A', 'B', 'C');
INSERT INTO MYTABLE VALUES ('D', 'E', NULL);
INSERT INTO MYTABLE VALUES ('F', NULL, G');
INSERT INTO MYTABLE VALUES ('H', NULL, NULL);
INSERT INTO MYTABLE VALUES ('I', 'J', 'K');

Now below, the indicator variables are coded within the WORKING-STORAGE SECTION as follows (no such variable is coded for COL_A as it was defined as NOT NULL):

05 COL_B-NULL-INDICATOR PIC S9(4) COMP.
05 COL_C-NULL-INDICATOR PIC S9(4) COMP.

You can see that the null indicator variable is listed in the FETCH as an additional host variable immediately after the regular host variable as follows:

EXEC SQL

FETCH MYTABLE-CURSOR INTO
:MY-COL_A ,
:MY-COL_B :COL_B-NULL-INDICATOR,
:MY-COL_C :COL_C-NULL-INDICATOR

END-EXEC.

If the value retrieved from the table is null, the null indicator variable will contain a value less than zero, so we check the null indicator as follows:

IF COL_B-NULL-INDICATOR < 0
MOVE ALL '*' TO DL-COL_B
ELSE
MOVE MY-COL_B TO DL-COL_B
END-IF