Maximizing the number of procedures or functions that run in an address space

You can improve the performance of stored procedures and user-defined functions by maximizing the number of procedures or functions that can run concurrently in a WLM-established stored procedures address space.

About this task

Each task control block that runs in a WLM-established stored procedures address space uses approximately 200 KB below the 16-MB line. Db2 needs this storage for stored procedures and user-defined functions because you can create both main programs and subprograms, and Db2 must create an environment for each.

Procedure

To maximize the number of procedures or functions that can run concurrently in a WLM-established stored procedures address space:

  • Set the region size for the address spaces to REGION=0 to obtain the largest possible amount of storage below the 16-MB line.
  • Limit storage required by application programs below the 16-MB line by using the following methods:
    • Link edit programs above the line with AMODE(31) and RMODE(ANY) attributes
    • Use the RES and DATA(31) compiler options for COBOL programs
  • Limit storage required by Language Environment® by using the following run time options:
    HEAP(,,ANY)
    Allocates program heap storage above the 16-MB line
    STACK(,,ANY,)
    Allocates program stack storage above the 16-MB line
    STORAGE(,,,4K)
    Reduces reserve storage area below the line to 4 KB
    BELOWHEAP(4K,,)
    Reduces the heap storage below the line to 4 KB
    LIBSTACK(4K,,)
    Reduces the library stack below the line to 4 KB
    ALL31(ON)
    Causes all programs contained in the external user-defined function to execute with AMODE(31) and RMODE(ANY)

    The definer can list these options as values of the RUN OPTIONS parameter of CREATE FUNCTION, or the system administrator can establish these options as defaults during Language Environment installation.

    For example, the RUN OPTIONS option parameter could contain:
    H(,,ANY),STAC(,,ANY,),STO(,,,4K),BE(4K,,),LIBS(4K,,),ALL31(ON)
  • Set the NUMTCB parameter for WLM-established stored procedures address spaces to a value greater than 1 to allow more than one TCB run in an address space. Be aware that setting NUMTCB to a value greater than 1 also reduces your level of application program isolation. For example, a bad pointer in one application can overwrite memory that is allocated by another application.

    A stored procedure can invoke only one utility in one address space at any given time because of the resource requirements of utilities. On the WLM Application-Environment panel, set NUMTCB to 1.With NUMTCB=1 or the value of NUMTCB being forced to 1, multiple WLM address spaces are created to run each concurrent utility request that comes from a stored procedure call.