How many DB2 for z/OS Stored Procedure Application Environments Should I define?

As always, the answer to this is "it depends"! However, here are some basic rules of thumb that you may find useful:

·         Define an application environment specifically for your Java stored procedures. This should have NUMTCB set to a relatively low value (6-8 is good) as Java stored procedures typically require a lot of storage. Java SPs also have their own JCL DD statement requirements, such as entries for JAVAENV and JSPDEBUG.

·         Define another environment specifically for your REXX stored procedures. Only a single REXX SP can run in any given application environment at any one time, so you need to set NUMTCB=1 for these.

·         Define an application environment specifically for stored procedures that need to run under RACF program control (some of the IBM-supplied SPs fall into this category, such as DSNACCUC). These SPs need their own environment: if you try to execute both program-controlled and non program-controlled SPs in the same environment you'll receive message ICH420I and the controlled SPs will fail.

·         Define an application environment to host all of the IBM-supplied stored procedures that don't fall into one of the above categories.

·         Define 1-n environments for your "normal" COBOL, PL/1, C or SQL stored procedures. NUMTCB for these can typically be set fairly high (40-50 isn't uncommon).

·         Finally, you may want to consider having one or more test environments for your normal procedures, but with NUMTCB=1. This forces procedures to be run individually, which can be very useful during debugging when your procedure writes messages to address space log. Failing to do this can result in several procedures interleaving their debug messages, which can get very confusing. A simple ALTER PROCEDURE statement is all that is required to change the application environment, but don't forget to stop/start the procedure afterwards.

If you'd like to find out more about how to set up your DB2 for z/OS environment to make best use of Stored Procedures, take a look at Triton's Stored Procedures Masterclass course which is due to run in August.