Passing large output parameters to stored procedures by using indicator variables

If any output parameters occupy a large amount of storage, passing the entire storage area to a stored procedure can degrade performance.

About this task

In the calling program, you can specify indicator variables for large output parameters to pass only a 2-byte area to the stored procedure, but receive the entire output data area from the stored procedure. When Db2 processes the CALL statement, it inspects the parameters before moving any data. If an output parameter has a NULL indicator, Db2 determines that it does not need to copy the associated data area to the Db2 address space, which avoids the need for acquisition of extra buffers or cross-memory moves.

You can use the following procedure regardless of whether the linkage convention for the stored procedure is GENERAL, GENERAL WITH NULLS, or SQL.

Procedure

To pass large output parameters to stored procedures by using indicator variables:

  1. Declare an indicator variable for every large output parameter in the stored procedure.
    If you are using the GENERAL WITH NULLS or SQL linkage convention, you must declare indicator variables for all of your parameters. In this case, you do not need to declare another indicator variable.
  2. Assign a negative value to each indicator variable that is associated with a large output variable.
  3. Include the indicator variables in the CALL statement.

Example

For example, suppose that a stored procedure that is defined with the GENERAL linkage convention takes one integer input parameter and one character output parameter of length 6000. You do not want to pass the 6000 byte storage area to the stored procedure. The following example PL/I program passes only 2 bytes to the stored procedure for the output variable and receives all 6000 bytes from the stored procedure: 
DCL INTVAR BIN FIXED(31);        /* This is the input variable    */
DCL BIGVAR(6000);                /* This is the output variable   */
DCL I1 BIN FIXED(15);            /* This is an indicator variable */
⋮
I1 = -1;                         /* Setting I1 to -1 causes only  */
                                 /*  a two byte area representing */
                                 /*  I1 to be passed to the       */
                                 /*  stored procedure, instead of */
                                 /*  the 6000 byte area for BIGVAR*/
EXEC SQL CALL PROCX(:INTVAR, :BIGVAR INDICATOR :I1);