DB2 Version 10.1 for Linux, UNIX, and Windows

Indicator variable arrays

You can use indicator arrays for FETCH INTO statements that are non-dynamic, when you set the precompiler option COMPATIBILITY_MODE to ORA.

An indicator variable array is a short data type variable that is associated with a specific host variable array or a structure array. Each indicator variable element in the indicator variable array can contain 0 or -1 value that indicates whether an associated host variable or structure contains a null value. If an indicator variable value is less than zero, it identifies the corresponding array value as NULL.

In FETCH INTO statements, you can use indicator variable arrays to determine whether any elements of array variables are null.

You can use the keyword INDICATOR to identify an indicator variable, as shown in the example.

In the following example, the indicator variable array that is called bonus_ind is declared. The bonus_ind indicator variable array can have up to 100 elements, the same cardinality as the bonus array variable. When the data is being fetched, if the value of bonus is NULL, the value in bonus_ind is negative.

EXEC SQL BEGIN DECLARE SECTION;
    char   empno[100][8];
    char   lastname[100][15];
    short  edlevel[100];
    double bonus[100];
    short  bonus_ind[100];
EXEC SQL END DECLARE SECTION;

EXEC SQL DECLARE empcr CURSOR FOR
	SELECT empno, lastname, edlevel, bonus
		FROM employee
		WHERE workdept = 'D21';

EXEC SQL OPEN empcr;

EXEC SQL WHENEVER NOT FOUND GOTO end_fetch;

while (1) {
	EXEC SQL FETCH empcr INTO :empno :lastname :edlevel, 
							  :bonus INDICATOR :bonus_ind
	...                                           
	...
}
end_fetch:
EXEC SQL CLOSE empcr;

Instead of being identified by the INDICATOR keyword, an indicator variable can immediately follow its corresponding host variable, as shown in the following example:

EXEC SQL FETCH empcr INTO :empno :lastname :edlevel, :bonus:bonus_ind

If the cardinality of indicator variable array does not match the cardinality of the corresponding host variable array, an error is returned.

In the following example, the indicator structure array MyStructInd is declared.

// declaring indicator structure array of size 3 
EXEC SQL BEGIN DECLARE SECTION;
   ...

   struct MyStructInd
   {
       short c1_ind;
       short c2_ind;
   } MyStructVarInd[3];
EXEC SQL END DECLARE SECTION;

...

// using structure array host variables & indicators structure type
// array while executing FETCH statement
// ‘MyStructVar’ is structure array for host variables
// ‘MyStructVarInd’ is structure array for indicators
EXEC SQL FETCH cur INTO :MyStructVar :MyStructVarInd;

Applications can check the sqlca.sqlerrd[2] field to get the number of rows that are successfully fetched.

Important: The following conditions must be met when the indicator structure array is used.

The cardinality of the indicator structure array must be equal to or greater than the cardinality of the structure array.
All members in the indicator structure array must use the short data type.
The number of members in the indicator structure array must match the number of members in the corresponding structure array.