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.