Improving the performance of stored procedures and user-defined functions

You can improve the performance of stored procedures and user-defined functions by following certain recommendations.

Procedure

To improve the performance of stored procedures and user-defined functions, use any of the following recommendations:

  • Update the ASUTIME column of the SYSIBM.SYSROUTINES catalog table to set processor limits for each stored procedures or function. The limits that you specify enable DB2® to cancel procedures or functions that loop.
  • Limit the number of times that a stored procedure can terminate abnormally by specifying one of the following options:
    • The MAX ABEND COUNT field on installation panel DSNTIPX. The limit that you specify applies to all stored procedures and prevents a problem procedure from overwhelming the system with abend dump processing.
    • The STOP AFTER FAILURES option on the ALTER or CREATE PROCEDURE statement. The limit that you specify overrides the system limit that is specified in the MAX ABEND COUNT field to specify limits for specific stored procedures.
  • Maximize the number of procedures or functions that can run concurrently in a WLM-established stored procedure address space.
  • Group your stored procedures in WLM application environments. For more information, see Defining application environments.
  • Use indicator variables in your programs and pass the indicator variables as parameters. When output parameters occupy a large amount of storage, passing the entire storage areas to your stored procedure can be wasteful. However, you can use indicator variables in the calling program to pass only a two-byte area to the stored procedure and receive the entire area from the stored procedure.
  • Set a high-enough priority for the WLM-managed stored procedures address spaces.
  • Set the performance-related options appropriately in the CREATE PROCEDURE statement. The following table shows the recommended values.
    Table 1. Recommended values for performance-related options in the CREATE procedure statement.
    Option Recommend setting
    PROGRAM TYPE SUB
    STAY RESIDENT YES
    PARAMETER STYLE GENERAL WITH NULLS or SQL
    COMMIT ON RETURN NO for stored procedures that are called locally; YES for stored procedures that are called from distributed client applications in environments where sysplex workload balancing is not used.
  • Do not use the DSNTRACE DD statement in any of your stored procedures address space startup procedures. DSNTRACE is a facility that can be used to capture all trace messages for offline reference and diagnosis. However, DSNTRACE greatly increases the stored procedure initialization overhead. Also, DSNTRACE does not function in a multitasking environment because the CAF does not serialize access to the DSNTRACE trace data set.
  • Specify a large enough value for the CACHERAC subsystem parameter on DSNTIP installation panel. The CACHERAC parameter specifies how much storage to allocate for the caching of routine authorization information for all routines on DB2 the member.
  • Set the CMTSTAT subsystem parameter to INACTIVE This setting causes distributed threads to become inactive at commit when possible. The inactive threads become available for thread reuse, and that reduces the amount of thread storage needed for the workload, by reducing the number of distributed active threads.
  • Convert external stored procedures to native SQL procedures whenever possible. The body of a native SQL procedure is written in SQL, and DB2 does not generate an associated C program for native stored procedures. Native procedures typically perform better and have more functionality that external procedures.
  • Study your workload for external stored procedures and functions carefully. You can use DB2 Performance Expert of DB2 Performance Monitor to monitor stored procedures and user-defined functions.
  • Use partitioned data set extended (PDSE) members for load libraries that contain stored procedures. By using PDSE members, you might eliminate the need to stop and start the stored procedures address space because of growth in load libraries, because the new extent information is dynamically updated. If a load library grows from additions or replacements, the library might have to be extended. If you use partitioned data set (PDS) members, load failures might occur because the new extent information is not available.