Inserting null values into columns by using indicator variables or arrays

If you need to insert null values into a column, using an indicator variable or array is an easy way to do so. An indicator variable or array is associated with a particular host variable or host-variable array.

Procedure

To insert null values into columns by using indicator variables or arrays:

  1. Define an indicator variable or array for a particular host variable or array.
  2. Assign a negative value to the indicator variable or array.
  3. Issue the appropriate INSERT, UPDATE, or MERGE statement with the host variable or array and its indicator variable or array.

    When Db2 processes INSERT, UPDATE, and MERGE statements, it checks the indicator variable if one exists. If the indicator variable is negative, the column value is null. If the indicator variable is greater than -1, the associated host variable contains a value for the column.

Examples

Example of setting a column value to null by using an indicator variable
Suppose your program reads an employee ID and a new phone number and must update the employee table with the new number. The new number could be missing if the old number is incorrect, but a new number is not yet available. If the new value for column PHONENO might be null, you can use an indicator variable, as shown in the following UPDATE statement. 
EXEC SQL
  UPDATE DSN8D10.EMP
    SET PHONENO = :NEWPHONE:PHONEIND
    WHERE EMPNO = :EMPID
END-EXEC.
When NEWPHONE contains a non-null value, set the indicator variable PHONEIND to zero by preceding the UPDATE statement with the following line: 
MOVE 0 TO PHONEIND.
When NEWPHONE contains a null value, set PHONEIND to a negative value by preceding the UPDATE statement with the following line: 
MOVE -1 TO PHONEIND.
Example of setting a column value to null by using an indicator variable array
Assume that host-variable arrays hva1 and hva2 have been populated with values that are to be inserted into the ACTNO and ACTKWD columns. Assume the ACTDESC column allows nulls. To set the ACTDESC column to null, assign -1 to the elements in its indicator array, ind3, as shown in the following example: 
/* Initialize each indicator array */
for (i=0; i<10; i++) {
  ind1[i] = 0;
  ind2[i] = 0;
  ind3[i] = -1;
}

EXEC SQL
  INSERT INTO DSN8D10.ACT
    (ACTNO, ACTKWD, ACTDESC)
     VALUES (:hva1:ind1, :hva2:ind2, :hva3:ind3)
     FOR 10 ROWS;
Db2 ignores the values in the hva3 array and assigns the values in the ARTDESC column to null for the 10 rows that are inserted.