What it means
A host variable can hold a value but cannot record that the value is null. For that, the program pairs the host variable with an indicator variable. When a null comes back for a host variable that has no indicator variable, Db2 has nowhere to put the null, so it refuses the statement and retrieves no data. The message gives the position of the host variable in the output list.
The usual trap is an aggregate function. MAX or SUM over rows that may not exist can return null, so a host variable that looks safe can still receive one. Give every host variable that can receive a null an indicator variable, including those fed by aggregate results.
Typical causes
- A nullable column fetched into a host variable with no indicator variable.
- An aggregate such as MAX or SUM over rows that may not exist, fetched without an indicator.
- A table that became nullable, or gained a nullable column, while the program kept its old declarations.
- An INTO list that lost its indicator variables during an edit.
How to fix it
- Check the table definition for each column in the statement and note which ones are nullable.
- Add an indicator variable to every host variable that can receive a null, including host variables for aggregate results.
- Test each indicator variable after the FETCH or SELECT INTO before the value is used.
- After a table change, update the program's host variable declarations, then recompile.
Example
Illustrative only; names and values are invented.
EXEC SQL SELECT MAX(ORDER_DT) INTO :WS-LAST-DT FROM MYSCHEMA.ORDERS END-EXEC
SQLCODE = -305, null value for output host variable 1, no indicator variable given