Indicator variables in applications that use SQL

An indicator variable is a halfword integer variable used to communicate additional information about its associated host variable.

If the value for the result column is null, SQL puts a -1 in the indicator variable.
If you do not use an indicator variable and the result column is a null value, a negative SQLCODE is returned.
If the value for the result column causes a data mapping error, SQL sets the indicator variable to -2.

You can also use an indicator variable to verify that a retrieved string value has not been truncated. If truncation occurs, the indicator variable contains a positive integer that specifies the original length of the string. If the string represents a large object (LOB), and the original length of the string is greater than 32 767, the value that is stored in the indicator variable is 32 767, because no larger value can be stored in a halfword integer.

Always test the indicator variable first. If the value of the indicator variable is less than zero, you know the value of the result column should not be used. When the database manager returns a null value, the host variable might or might not be set to the default value for the result column's data type (0 for numeric, blanks for fixed length character, etc).

You specify an indicator variable (preceded by a colon) immediately after the host variable. For example:

EXEC SQL
   SELECT COUNT(*), AVG(SALARY)
   INTO :PLICNT, :PLISAL:INDNULL
   FROM CORPDATA.EMPLOYEE
   WHERE EDLEVEL < 18
END-EXEC.

You can then test INDNULL in your program to see if it contains a negative value. If it does, you know SQL returned a null value (if its value is -1) or a data mapping error (if its value is -2). If the indicator value is not negative, the value returned in PLISAL can be used.