Setting up a WLM application environment for stored procedures during migration

All stored procedures and user-defined functions (except native SQL procedures) must run in WLM-managed stored procedure address spaces. You must assign each stored procedure to a WLM application environment, which routes the stored procedure work to the appropriate WLM-managed address space.

About this task

If you use data sharing, see Special considerations for WLM-managed stored procedures in coexistence for additional guidance about setting up WLM environments.

Recommendation: Use partitioned data set extended (PDSE) for load libraries that contain external modules for stored procedures. Using PDSEs might eliminate your need to stop and start the stored procedures address space due to growth of the load libraries. If a load library grows from additions or replacements, the library might need to be extended.

Procedure

To set up a WLM application environment for one or more stored procedures:

Gather requirements for the WLM environment. These requirements include:
- The name of the Db2 subsystem or data sharing group where the stored procedure or user-defined function is to run.
- The maximum number of task control blocks (TCBs) that can run concurrently in an address space that is initiated by the WLM environment. This setting, referred to as the NUMTCB, is the maximum number of procedures or functions that should run concurrently in that WLM-managed address space. The appropriate setting depends on the requirements or limitations of the individual stored procedure or user-defined function.
  - For recommended NUMTCB values for Db2-supplied stored procedures, see Installation information for procedures and functions that are supplied with Db2.
  - For recommended NUMTCB values for your own stored procedures, see Assigning stored procedures and functions to WLM application environments. If a routine allocates a large amount of memory or if the external module for the routine is not re-entrant, the NUMTCB value should be 1. If there is a need for serialization, the NUMTCB value should also be 1.
  - The WLM environment for a REXX stored procedure must have a NUMTCB setting of 1.
- Requirements for Authorized Program Facility (APF).
  - Stored procedures and user-defined functions that execute privileged instructions (for example, WLM_REFRESH) must run in a WLM environment that has an APF-authorized address space.
  - Db2 REXX stored procedures, such as DSNTPSMP, and other routines that use TSO/E and REXX facilities must run in a WLM environment that does not have an APF-authorized address space.
  - Most stored procedures can run in a WLM environment that either has or does not have an APF-authorized address space. However, running a routine in an APF-authorized environment is recommended only when doing so is necessary.
- Special data sets and DD names that are needed by the stored procedure or user-defined function. For example, all Java™ stored procedures implicitly require a Language Environment® run time options data set to be allocated to DD name JAVAENV. Other routines might also have an explicit requirement for a work file to be allocated to a particular DD name.
Create a JCL startup procedure for the WLM application environment.
Use ssnmWLM (where ssnm is the Db2 subsystem ID) as the basis for creating the new startup procedure. ssnmWLM is the default name of the sample JCL procedure for starting WLM-established address spaces. If you specified a name in the WLM PROC NAME field on the DSNTIPX installation panel, the sample procedure has that specified name. The sample procedure is placed in your system PROCLIB by installation job DSNTIJMV. For the DSNACICS stored procedure, DSNTIJMV also provides sample procedure ssnmCICS, where ssnm is the subsystem ID. Tailor your new startup procedure as follows:
- Change the APPLENV value to the name of the WLM application environment that you plan to define.
- Change the startup procedure name to an appropriate procedure name.
  Recommendation: Specify the same name for the application environment name and the procedure name. Using the same name simplifies monitoring the WLM environment definitions.
- Change the value of DB2SSN to your Db2 subsystem name.
- Ensure that the NUMTCB value is appropriate. If the minimum number of address spaces is specified, the NUMTCB value will be the number of task control blocks that are started in an address space.
  Recommendation: If you want to be able to easily change the NUMTCB value, specify the NUMTCB value here, and do not specify this parameter on the application environment definition panel when you define the WLM application environment. Specifying NUMTCB on the environment definition panel associates this parameter with the WLM application environment definition. Any changes to the JCL startup procedure can be made by refreshing the WLM application environment. However, changes to the WLM application environment require that you reinstall the z/OS service policy at an LPAR or sysplex level. If you specify the NUMTCB parameter for both the WLM application environment and the startup procedure, the value in the WLM application environment overrides the value in the startup procedure.
- If the environment is one in which the cost of starting new address spaces is very high, you can use the MNSPAS parameter. The MNSPAS parameter specifies the minimum number of address spaces that are to be started and maintained. The value can be 0 through 50. The default value is 0, which means that no minimum number of address spaces is specified. If the number of address spaces is at the specified minimum, canceling an address space causes WLM to start another one to maintain the minimum. If you refresh a WLM environment, WLM starts a number of new address spaces as needed (or the specified minimum number) and stops the old address spaces.
  If the MNSPAS parameter is specified, the NUMTCB value is the number of task control blocks that are started in an address space. If the MNSPAS parameter is not specified, the NUMTCB value is the maximum number of task control blocks that can be started in an address space.
- Edit the data set names to match your data set names.
- Ensure that the library in which the stored procedure resides is in the STEPLIB concatenation.
- Ensure that the IBM® Language Environment run time library is specified in the STEPLIB concatenation, unless your system includes the Language Environment run time library in the system link list.
- If you want to run the stored procedure in a WLM environment that has an APF-authorized address space, ensure that all the libraries in the STEPLIB concatenation are defined to the Authorized Program Facility.
- Add any special DD statements that are required for the stored procedures or user-defined functions that are to run in the WLM environment.
  - If you plan to use stored procedures that use the ODBA interface to access IMS databases, make the following additional changes:
    - Add the data set name of the IMS data set that contains the ODBA callable interface code to the end of the STEPLIB concatenation. The name of this data set is usually IMS.RESLIB.
    - After the STEPLIB DD statement, add a DFSRESLB DD statement that names the IMS data set that contains the ODBA callable interface code.
  - If your routines are defined as PROGRAM TYPE SUB and use z/OS UNIX System Services syscall statements, you might need to add a NOUNDUB DD statement. By default, the z/OS UNIX System Services process and its resources are cleaned up after every stored procedure and user-defined function invocation. If you need the task in this WLM environment to retain the process across subsequent invocations, add a dummy NOUNDUB DD statement. This option specifies that no cleanup will occur unless the WLM environment is refreshed. To enable this option, add the following statement to the WLM stored procedure address space startup JCL:
```
//NOUNDUB DD DUMMY
```
    Use this option with caution because routines use the z/OS UNIX System Services process that was used by the previous routine that was run under the same task. Also note that any open resources such as file descriptors remain open for the next routine that is run under the same task. Consider using this option on new workloads only if reusing the z/OS UNIX System Services process is the desired behavior.
The following example shows a startup procedure for a WLM application environment for external SQL, COBOL, C, C++, PL/I, and assembler stored procedures. This startup procedure for external SQL stored procedures needs one unauthorized data set included in STEPLIB.
```
//*******************************************************************
//* JCL FOR RUNNING THE WLM-ESTABLISHED STORED PROCEDURES
//* ADDRESS SPACE
//* RGN     -- THE MVS REGION SIZE FOR THE ADDRESS SPACE.
//* DB2SSN  -- THE DB2 SUBSYSTEM NAME.
//* NUMTCB  -- THE NUMBER OF TCBS USED TO PROCESS
//*            END USER REQUESTS.
//* APPLENV -- THE MVS WLM APPLICATION ENVIRONMENT
//*            SUPPORTED BY THIS JCL PROCEDURE.
//*
//********************************************************************
//DB9AWLM PROC RGN=0K,APPLENV=WLMENV,DB2SSN=DB9A,NUMTCB=40,MNSPAS=3
//IEFPROC EXEC PGM=DSNX9WLM,REGION=&RGN,TIME=NOLIMIT, 
//             PARM='&DB2SSN,&NUMTCB,&APPLENV,&MNSPAS' 
//STEPLIB  DD  DISP=SHR,DSN=DB9AU.RUNLIB.LOAD 
//         DD  DISP=SHR,DSN=CEE.SCEERUN 
//         DD  DISP=SHR,DSN=DB9A9.SDSNEXIT 
//         DD  DISP=SHR,DSN=DB9A9.SDSNLOAD
```
Define the application environment to WLM by using one of the following methods.
- Use the WLM administrative application (IWMARIN0) to define a new environment or copy or modify an existing one.
  1. In the first WLM panel, Definition Menu, enter 9 for Application Environments.
    The application environment definition panel is displayed.
  2. On the application environment definition panel, specify the appropriate action code:
    - Create a new WLM application environment.
    - Copy an existing WLM application environment.
    - Modify an existing WLM application environment.
    The appropriate panel for creating, copying or modifying the application environment is displayed.
  3. If you are creating or modifying a WLM environment, specify the appropriate options for the WLM application environment definition, using the same application environment name and procedure name that you specified for the startup procedure.
  4. After you specify the appropriate options, press Enter to create the application environment definition.
- Modify and run sample job DSNTIJRW. As provided, DSNTIJRW creates the core Db2 WLM environments, but you can modify the job to define your own WLM environments. Job DSNTIJMV copies an address space procedure for each of the Db2 core WLM environments into SYS1.PROCLIB.
Install the WLM service definition that contains information about the application environment by opening the WLM Definition Menu panel and selecting the Install function in the Utilities menu bar.
Activate the WLM service policy from the installed service definition by completing one of the following actions:
- Issue the VARY WLM command as follows:
```
VARY WLM,POLICY=policy-name 
```
- On the WLM Definition Menu panel, choose the Activate service policy option from Utilities menu bar.
Set up security for the WLM application environments and for creating stored procedures by completing the following actions:
1. Optional: Specify which WLM-established address spaces can run stored procedures.
  If you do not complete this step, any WLM-established address space can run stored procedures.
2. Grant access to users to create procedures in specific WLM address spaces.
3. Grant access to users to create procedures in specific schemas. Use the GRANT statement with the CREATEIN option for the appropriate schema.
4. Grant access to users to create packages for procedures in specific collections. Use the GRANT statement with the CREATE option for the appropriate collection.
  Requirement: For native SQL procedures, the collection name for the package must be the same as the schema name.
5. Grant access to refresh the WLM environments to the appropriate people.
For more detail, see Managing authorization for stored procedures.
Ensure that the ID that is used to run the JCL startup procedure for the WLM application environment has permission to use RRSAF.
Each time one of the Db2 WLM address spaces is started, it uses RRSAF to attach to Db2.

What to do next

When you create or alter a stored procedure, specify the WLM application environment that Db2 is to use in the WLM_ENVIRONMENT option of the CREATE or ALTER PROCEDURE statement. When you specify environments, be aware that:

Routines that have low memory requirements and that are frequently called should not share an environment with routines that have high memory requirements and are rarely called. Otherwise, storage is wasted.
Stored procedures can be grouped according to the authorization settings for a WLM environment.
WLM environments should not be shared among routines that use the same DD name for different purposes.
WLM environments should not be shared among routines if one routine allocates data sets in the environment and that routine is run only occasionally. If other routines that do not use the data sets use the environment, the data sets are kept artificially allocated, which might interfere with other tasks.