DB2: Indicator Variables with Host Variables

Indicator variables are small integers that you can use to:

• Indicate whether the values of associated host variables are null
• Verify that the value of a retrieved character string has not been truncated
• Insert null values from host variables into columns.

Retrieving Data into Host Variables: If the value for the column you retrieve is null, DB2 puts a negative value in the indicator variable. If it is null because of a numeric or character conversion error, or an arithmetic expression error, DB2 sets the indicator variable to -2.

If you do not use an indicator variable and DB2 retrieves a null value, an error results.

When DB2 retrieves the value of a column, you can test the indicator variable. If the indicator variable's value is less than zero, the column value is null. When the column value is null, the value of the host variable does not change from its previous value.

You can also use an indicator variable to verify that a retrieved character string value is not truncated. If the indicator variable contains a positive integer, the integer is the original length of the string.

You can specify an indicator variable, preceded by a colon, immediately after the host variable. Optionally, you can use the word INDICATOR between the host variable and its indicator variable. Thus, the following two examples are equivalent:

EXEC SQL                     EXEC SQL

     SELECT PHONENO               SELECT PHONENO

     INTO :CBLPHONE:INDNULL       INTO :CBLPHONE INDICATOR :INDNULL

     FROM DSN8510.EMP             FROM DSN8510.EMP

     WHERE EMPNO = :EMPID         WHERE EMPNO = :EMPID

END-EXEC.                    END-EXEC.

You can then test INDNULL for a negative value. If it is negative, the corresponding value of PHONENO is null, and you can disregard the contents of CBLPHONE.

When you use a cursor to fetch a column value, you can use the same technique to determine whether the column value is null.

Inserting Null Values into Columns Using Host Variables: You can use an indicator variable to insert a null value from a host variable into a column. When DB2 processes INSERT and UPDATE statements, it checks the indicator variable (if it 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.

For example, 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 it is possible that the new value for column PHONENO might be null, you can code:

EXEC SQL

     UPDATE DSN8510.EMP

     SET PHONENO = :NEWPHONE:PHONEIND

   WHERE EMPNO = :EMPID

END-EXEC.

When NEWPHONE contains other than a null value, set PHONEIND to zero by preceding the statement with:

MOVE 0 TO PHONEIND.

When NEWPHONE contains a null value, set PHONEIND to a negative value by preceding the statement 

with:

MOVE -1 TO PHONEIND.