Using Indicator Variables to Speed Processing

If any of your output parameters require a great deal of storage, it is wasteful to pass the entire storage areas to your stored procedure. You can use indicator variables 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. To accomplish this, declare an indicator variable for every large output parameter in your SQL statement CALL.

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);