Null Indicator Variable (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 indicator can contain three value

0 : If column is not null that is it have some value in it.

-1: If column contains null

-2: If column contains null as a part of data conversion.

Some Tricky Pointes to remember with Null indicator:

1> You have to define the Null indicator with S9(4) COMP ( 2 BYTE Binary) or else you will get the error as " Undefined Host variable " even though you have defined it ( but with some other picture clause)

2>  The value which host variable going to contain depends on the null indicator irrespective of content we move to it.

For ex : Suppose i have to insert some record in table.

If i move some address to the variable EMP_ADDRESS

Move 'address' to EMP_ADDRESS

Then insert this record to EMP table but i move -1 to Null indicator variable EMP_ADDRESS_INDICATOR associated with  EMP_ADDRESS then irrespective of 'address' moved to EMP_ADDRESS null will be moved EMP_ADDRESS in EMP table.

3> You have used Null indicator in your query but didn't check it's value later in your program you may not get -305 sqlcode but your program will behave in unpredicted manner.