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:
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):
The null indicator variable is listed in the FETCH as an additional host variable immediately after the regular host variable as follows:
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:
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.