Indicator variables, arrays, and structures

You can use indicator variables to perform the following actions:

• Determine whether the value of an associated output host variable is null or indicate that the value of an input host variable is null
• Determine the original length of a character string that was truncated when it was assigned to a host variable
• Determine that a character value could not be converted when it was assigned to a host variable
• Determine the seconds portion of a time value that was truncated when it was assigned to a host variable
• Indicate that the target column of the host variable is to be set to its defined DEFAULT value, or that the host variable's value is UNASSIGNED and its target column is to be treated as if it had not appeared in the statement.

You can use indicator variable arrays and indicator structures to perform these same actions for individual items in host data arrays and structures.

If you provide an indicator variable for the variable X, when Db2 retrieves a null value for X, it puts a negative value in the indicator variable and does not update X. Your program should check the indicator variable before using X. If the indicator variable is negative, you know that X is null and any value that you find in X is irrelevant. When your program uses variable X to assign a null value to a column, the program should set the indicator variable to a negative number. Db2 then assigns a null value to the column and ignores any value in X.

An indicator variable array contains a series of small integers to help you determine the associated information for the corresponding item in a host data array. When you retrieve data into a host-variable array, you can check the values in the associated indicator array to determine how to handle each data item. If a value in the associated indicator array is negative, you can disregard the contents of the corresponding element in the host-variable array. Values in indicator arrays have the following meanings:

On output to the application, the normal indicator variable can contain the following values:

0

A 0 (zero), or positive value of the indicator variable specifies that the first host-identifier provides the value of this host variable reference.

-1

A -1 value indicates that the value that was selected was the null value.

-2

A -2 value of the indicator variable indicates that a numeric conversion error (such as a divide by 0 or overflow) has occurred. Or indicates a null result because of character string conversion warnings.

-3

A -3 value of the indicator variable indicates that no value was returned. A -3 value of the indicator variable can also indicate a null result because the cursor's current row is on a hole that was detected during a multiple row FETCH.

positive integer

If the indicator variable contains a positive integer, the retrieved value is truncated, and the integer is the original length of the string.

positive integer

The seconds portion of a time if the time is truncated on assignment to a host variable.

On input to Db2, normal indicator variables or extended indicator variables can contain the following values:

0, or positive integer

Specifies a non-null value. A 0 (zero), or positive value of the indicator variable specifies that the first host-identifier provides the value of this host variable reference.

-1, -2, -3, -4, -6

Specifies a null value.

-5

• If extended indicator variables are not enabled, a -5 value specifies the NULL value.
• If extended indicator variables are enabled, a -5 value specifies the DEFAULT value. A -5 value specifies that the target column for this host variable is to be set to its DEFAULT value.

-7

• If extended indicator variables are not enabled, a -7 value specifies the NULL value.
• If extended indicator variables are enabled, a -7 value specifies the UNASSIGNED value. A -7 value specifies that the target column for this host variable is to be treated as if it had not been specified in the statement.

An indicator structure is an array of halfword integer variables that supports a specified host structure. If the column values that your program retrieves into a host structure can be null, you can attach an indicator structure name to the host structure name. This name enables Db2 to notify your program about each null value it returns to a host variable in the host structure.