NULL

NULL

What is the Null Indicator?

In DB2, a null value represents missing or unknown data. DB2 allows columns to have null values if the column is defined to accept them. However, when retrieving data through COBOL + DB2 programs, there needs to be a way to predict whether a column has a null value or actual data—this is where the null indicator comes in.

A null indicator is a host variable in the COBOL program that indicates whether a column's value is null or not when fetching data from DB2. If the column value is null, DB2 sets the null indicator to -1 and skips assigning any value to the corresponding host variable.

Why is the Null Indicator Required?

DB2 cannot properly communicate null values to the COBOL program without a null indicator. If a column value is null and no null indicator is provided:

  • SQLCODE -305 will be returned, indicating that a null value was encountered, but no null indicator was declared.

The null indicator ensures the program can safely handle both null and non-null values from DB2.

How to Declare and Use a Null Indicator in COBOL

The null indicator is typically declared in the WORKING-STORAGE SECTION or in the DCLGEN as a 2-byte integer like below -

...
   05 EMP-NAME      PIC X(50). * Host variable for Employee Name
   05 EMP-NAME-NI   PIC S9(4) COMP. * Null indicator for Employee Name
   ...

Declaration of null indicator for a column immediately follows the host variable declaration for the column name.

  • Host Variable (WS-EMP-NAME): Stores the actual data (if not null).
  • Null Indicator (WS-NAME-NULL-IND): Stores the status of the column value. The valid status of the null indicator are -
    • 0: Column has a non-null value (valid data).
    • -1: Column has a null value (missing data).
    • -2: Column has a value that is more in size thn host variable (truncated data).

SQLCODE -305 Error: When to Use Null Indicator

If the COBOL program does not use a null indicator for a column that may contain null values, DB2 will return an SQLCODE -305 error.

  • SQLCODE -305: "A null value was found, but no null indicator variable was provided".

Examples -

Scenario - the program retrieves an employee's name from the Employees table. If the Emp_Name column is null, the null indicator will be set to -1.

...
MOVE 101 TO EMP-ID.

EXEC SQL
    SELECT emp_name
      INTO :EMP-NAME 
	       :EMP-NAME-NI
      FROM employees
     WHERE emp_id = :EMP-ID
END-EXEC.

IF SQLCODE = 0 THEN
...
  • The SELECT statement tries to retrieve the employee name for the given Employee ID.
  • The :EMP-NAME-NI checks whether the Emp_Name column has a null value.
    • If EMP-NAME-NI is -1: The column value is null, and the message 'Employee Name is NULL.' is displayed.
    • If EMP-NAME-NI is 0: The column has a valid value, and the employee name is displayed.
More Examples