Tuning routines with storage-consuming parameter lists

Some tuning might be required if you encounter SQLCODE -904 with reason code 00E7900F for calls to routines (stored procedure or user defined function) with storage-consuming parameter lists.

About this task

When Db2 obtains storage for the input or output parameters for a routine, it issues a conditional STORAGE OBTAIN request. If the available contiguous storage is not sufficient, Db2 issues SQLCODE -904 with reason code 00E7900F. This reason code indicates a condition where not enough above-the-line 31-bit storage in subpool 229, key 7 is available for Db2 to obtain the parameter list of a routine.

For example, the SYSTOOLS.JSON2BSON() function has a 16 MB LOB as input and a 16 MB LOB for output. When such functions are called, Db2 gets the actual size of storage needed for the content of the input parameter. For example, if a 2 KB LOB is passed for the 16MB input LOB, Db2 obtains only 2 KB of storage for the input parameter. However, Db2 still obtains the full 16MB LOB for the output parameter. In a highly concurrent and heavily loaded system, the storage used for these LOBs can take a toll on above-the-line storage, so tuning might be required.

Procedure

To tune the use of storage by routines with storage-consuming parameters, use the following approaches:

  1. Examine the WLM stored procedure address space environment and determine if routines with storage-consuming parameter lists are sharing the same WLM application environment with other routines. You might be able to isolate them in their own WLM application environment to allow for maximum storage availability for the parameter lists.
  2. Consider reducing the NUMTCB value for the WLM stored procedure address space. Instead of NUMTCB=40, try NUMTCB=20.
    The net result of this change may be additional WLM stored procedure address spaces active concurrently, so review this impact against the needs of the rest of the system.
  3. Examine how these routines are being used:
    • If repeated calls to the routines are being used, check whether saving and reusing the result of a single call would suffice.
    • If the routine is called as part of a CURSOR block, ensure that the cursor is closed in a timely manner.
  4. Consider retrying the routine call after this condition.
    If you see occasional spikes in processing when these -904s occur, retrying the routine call might have the least impact to your operations, especially if routines are short-running transactions.