Using Indicator Variables to Speed Processing

If any of your output parameters require a lot of storage, do not pass the entire storage areas to your stored procedure, but declare an indicator variable for every large output parameter in your SQL statement CALL. Indicator variables are used in the calling program to pass only a two-byte area to the stored procedure and to receive the entire area from the stored procedure.

Note: If you are using the SIMPLE WITH NULLS linkage convention, you must declare indicator variables for all of your parameters, so you do not need to declare another indicator variable for the large output parameters.

Assign a negative value to each indicator value associated with a large output variable. Then include the indicator variables in the CALL statement. This technique can be used whether the stored procedure linkage is SIMPLE or SIMPLE WITH NULLS.

For example, suppose that a stored procedure, STPROC2 defined with the SIMPLE linkage convention takes one integer input parameter and one character output parameter of length 5000. It is wasteful to pass the 5000 byte storage area to the stored procedure. Instead, a COBOL program containing these statements passes only two bytes to the stored procedure for the output variable and receives all 5000 bytes from the stored procedure:

INNUM  PIC S9(9) COMP
OUTCHAR PIC X(5000)
IND  PIC S9(4) COMP
.
.
.
MOVE -1                      TO IND
EXEC SQL CALL STPROC2(:INNUM, :OUTCHAR :IND)  END-EXEC