NULL Indicator Variable

The Null indicator variable are used in an Application Program as a part of exception handling while dealing with the columns which are defined as NULL ( Or say the one which is not defined with NOT NULL).

Let us consider the following scenario:

EXEC SQL
  SELECT EMP_NUMBER,EMP_ADDRESS
  INTO :EMP_NUMBER,:EMP_ADDRESS
  FROM EMP
END-EXEC.

Let's say EMP_ADDRESS column in EMP tables is defined as NULL column and one of the Employee has not updated his address in database so it will be considered as null. Now what should happen if we try to fetch the row for that employee, what the host variable: EMP_ADDRESS suppose to contain after the Select operation. No it will not contain spaces as Null is absence of value and not blanks, spaces or any other character.

In this case you will get the error with -305 as SQLCODE.

To avoid this we need to use null indicator in our program which can be used to process the program flow in case of null.

The Null indicator variable should be defined for the every column which may contain the null value.

It must be defined as 2 Byte binary variable.

01   EMP_ADDRESS_INDICATOR S9(4) COMP

You can then refer this variable in your query as below.

EXEC SQL
  SELECT EMP_NUMBER,EMP_ADDRESS
  INTO :EMP_NUMBER,:EMP_ADDRESS :EMP_ADDRESS_INDICATOR
  FROM EMP
END-EXEC.

Note there is no comma between EMP_ADDRESS and EMP_ADDRESS_INDICATOR.

Now see how this null indicator resolves our problem.

If one of the employees have EMP_ADDRESS as NULL then EMP_ADDRESS_INDICATOR will be automatically updated with Negative value.

Then you can check like

IF EMP_ADDRESS_INDICATOR IS NOT ZERO
THEN < do some processing>

EMP_ADDRESS_INDICATOR will contain zero if the EMP_ADDRESS has some value (NOT NULL)

Null Indicators hold one of the following values

 0: Field value is not null
-1: Field value is null 
-2: Field value is truncated