Handling Null Values in COBOL DB2

Handling Null Values in COBOL DB2 program is an important aspect without which program may get into erroneous status. Null is nothing but no value has been populated or Value is missing for that DB2 table column.In other words, few columns gets populated only based on certain criteria, if it meets that criteria then it gets populated otherwise it is left un populated which means it is set to NULL.

Take an example of an employee who gets tickets assigned and his work is to resolve those tickets. Once the ticket is assigned,a row gets created in a Ticket tracking Table and it will be updated once the issue is resolved.
It has columns Ticket_no, Assigned_TS, Resolved_TS, Resolved_BY. At the time of ticket assignment, Resolved_ts will not have any value, so it is left as blank(NULL) and once the ticket is resolved this columns gets updated with a valid value.
Here all 3 columns except Resolved_TS should have values at the time of ticket assignment,so these can be defined as NOT NULL and Resolved_TS will be with no value assigned so this needs to be defined to allow nulls.

Example:

CREATE TABLE TKCT_TRCK 
(TKCT_NO INTEGER NOT NULL,
 ASSG_TS TIMESTMP NOT NULL,
 RSLVD_TS TIMESTMP,
 RSLVD_BY CHAR(10) NOT NULL WITH DEFAULT);

Here all other columns except RSLVD_TS accept NULLs.

How to retrieve the records ?
IN the above example, if I want to select all the tickets that are not yet resolved then I need to select all the rows with NULL RSLVD_TS.
SQL looks like below

SELECT TKCT_NO FROM TKCT_TRCK
   WHERE RSLVD_TS IS NULL.

SO nulls really helps to recognize whether any value has been assigned to any column or not.

But the question is how the NULL can be inserted into that table? Does any special value needs to be populated in to that column before inserting it into table? And how a it is identified while retrieving the data from DB2 table?

NULL Indicator(NI) concept is introduced to identify these values. For all the columns that allows NUlls, a corresponding Indicator value of length Half word integer gets created. It can hold a value of Zero, positive and Negative integers.

0 –> indicates that the columns has data
-1–> Indicates that the column is NULL
-2 –> NULL came due a data conversion error. So this is not actually a NULL indication but it is an error case.
-3, -4 & -6 also indicates that the variable as NULL
Positive integers specifies that a value is present but truncation happened.

Inserting data with NULLs

INSERT INTO TKCT_TRCK 
(TKCT_NO, ASSG_TS, RSLVD_TS,RSLVD_BY)
  VALUES (:WS-TKT,CURRENT TIMESTAMP, :WS-RSLD_TS :WS-RSLVD_NL_IND, :WS-USER-ID);

In that before executing the above SQL, move -1 to WS-RSLVD_NL_IND. So that Db2 stores this column as NULL.

Retrieving data

SELECT * INTO 
(:WS-TKT,CURRENT TIMESTAMP, :WS-RSLD_TS :WS-RSLVD_NL_IND, :WS-USER-ID)
WHERE TKCT_NO='A001';

After retrieving the values, variable WS-RSLVD_NL_IND needs to be validated to check whether it has a NULL or not. If its value is -1 then it is a NULL other wise Not.

So it is best practice to check this NL, after retrieving the data from the table so that based on the result further processing can be performed.