June 2004 Technical Tip – COBOL with Embedded SQL: Handling Null Fields

June 2004 Technical Tip – COBOL with Embedded SQL: Handling Null Fields

If a column within a DB2 table allows nulls, special handling is required in a COBOL program which uses embedded SQL to read that table. 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 – and 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. Let’s look at a simple example to illustrate.

We begin with the SQL to create a simple table called MYTABLE which contains three fields: FLDA (which is NOT NULL), FLDB (nulls allowed) and FLDC (nulls allowed). We then use INSERT statements to populate the table:

CREATE TABLE MYTABLE
  ( FLDA  CHAR(2)  NOT NULL,
    FLDB  CHAR(2)          ,
    FLDC  CHAR(2)            ) ;

INSERT INTO MYTABLE VALUES ('A1', 'B1', 'C1');
INSERT INTO MYTABLE VALUES ('A2', 'B2', NULL);
INSERT INTO MYTABLE VALUES ('A3', NULL, 'C3');
INSERT INTO MYTABLE VALUES ('A4', NULL, NULL);
INSERT INTO MYTABLE VALUES ('A5', 'B5', 'C5');

SELECT * FROM MYTABLE;

Download SQL code here.

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

05  FLDB-NULL-INDICATOR    PIC S9(4)   COMP.
05  FLDC-NULL-INDICATOR    PIC S9(4)   COMP.

Download complete COBOL code here.

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-FLDA , :MY-FLDB :FLDB-NULL-INDICATOR, :MY-FLDC :FLDC-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 FLDB-NULL-INDICATOR < 0
   MOVE ALL '*' TO DL-FLDB
ELSE
   MOVE MY-FLDB TO DL-FLDB
END-IF

The complete COBOL code can be found at http://www.caliberdt.com/tips/NullPgm.txt. The DCLGEN output can be found at http://www.caliberdt.com/tips/DclMyTbl.txt. The output from the COBOL program can be found at http://www.caliberdt.com/tips/NullPgmOutput.txt.

If you or your staff are in need of training in COBOL or DB2 or both, we hope you’ll consider Caliber Data Training when you are looking for a training provider.