IBM

4.3 Setting up WLM for DB2 stored procedures

This section describes the WLM Application Environment panels and some sample JCL procedures for running some of the sample stored procedures used in our case studies. We also discuss WLM setup for some of the DB2-supplied stored procedures used by the Data Studio or Developers Workbench.

Each WLM Application Environment needs to be defined to WLM using the WLM panels. Once into WLM, enter 9 for Application Environment. Create a new definition or copy an existing WLM definition. We made the Application Environment Name and the Procedure Name that run in this environment the same, because the JCL in this procedure is unique to DB9A. When the procedure name is the same as the WLM Application Environment name, it is easy to monitor the active WLM definitions, and know which one to refresh, quiesce or resume by using the same name that's displayed on the SDSF Display Active panel. Example 4-1 shows a sample Application Environment definition panel.

When you want to be able to easily change the NUMTCB value, we recommend that you leave this parameter off the WLM Application Environment definition, and specify it on the procedure that executes in the WLM Application Environment. If the NUMTCB parameter is specified on both the WLM Application Environment and the procedure that executes in the WLM Application Environment, the value in the WLM Application Environment overrides the value in the procedure.

Example 4-1 WLM Application Environment definition for general DB2 stored procedures

Application-Environment  Notes  Options  Help                                
 --------------------------------------------------------------------------     
                     Modify an Application Environment                          
 Command ===> ____________________________________________________________      
                                                                                
 Application Environment Name . : DB9AWLM                                       
 Description  . . . . . . . . . . DB9A General DB2 SPs                          
 Subsystem Type . . . . . . . . . DB2                                           
 Procedure Name . . . . . . . . . DB9AWLM                                       
 Start Parameters . . . . . . . . DB2SSN=DB9A,APPLENV=DB9AWLM         
                                  ________________________________________      
                                  ___________________________________           
                                                                                
 Starting of server address spaces for a subsystem instance:                    
 1   1.  Managed by WLM                                                         
     2.  Limited to a single address space per system                           
     3.  Limited to a single address space per sysplex                          

Example 4-2 shows the procedure definition we used for executing many of our DB2 system stored procedures including DSNTJSPP. This WLM environment contains all APF authorized STEPLIB data sets, so that we can run the DB2 system WLM_REFRESH stored procedure here as well, which requires all APF-authorized STEPLIB data sets. We specify the NUMTCB value on the procedure, and not the WLM Application Environment definition, due to ease of maintenance. Changes to JCL procs can be made available by refreshing WLM Application Environment, while changes to WLM Application Environment definitions need re-installing z/OS service policy at an LPAR or sysplex level. If the LE runtime SCEERUN library is not included in your system LINKLIST, you need to uncomment the STEPLIB DD for SCEERUN.

Example 4-2 Our procedure for executing many DB2-supplied stored procedures

//*************************************************************         
//*    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=XXXXXXXX,DB2SSN=DB9A,NUMTCB=40           
//IEFPROC EXEC PGM=DSNX9WLM,REGION=&RGN,TIME=NOLIMIT,                   
//        PARM='&DB2SSN,&NUMTCB,&APPLENV'                               
//STEPLIB  DD  DISP=SHR,DSN=CEE.SCEERUN                                 
//         DD  DISP=SHR,DSN=DB9A9.SDSNEXIT                              
//         DD  DISP=SHR,DSN=DB9A9.SDSNLOAD 

Example 4-3 is a sample procedure for executing DSNTPSMP and DSNTBIND stored procedures that are used by the Development Center. The JCL needed for DSNTPSMP is included in <hlq>.SDSNSAMP(DSN8WLMP). Since both DSNTPSMP and DSNTBIND are REXX stored procedures, we set NUMTCB equal to 1.

Example 4-3 Our procedure for executing DSNTPSMP and DSNTBIND

//DB9AWLMR PROC DB2SSN=DB9A,NUMTCB=1,APPLENV=DB9AWLMR                   
//*                                                                     
//NUMTCB@1 SET NUMTCB=                         <== NULL NUMTCB SYMBOL   
//*                                                                     
//DSNTPSMP EXEC PGM=DSNX9WLM,TIME=1440,                                 
//             PARM='&DB2SSN,1,&APPLENV',      <== USE 1, NOT NUMTCB    
//             REGION=0M,DYNAMNBR=5            <== ALLOW FOR DYN ALLOCS 
//* INCLUDE SDSNEXIT TO USE SECONDARY AUTHIDS (DSN3@ATH DSN3@SGN EXITS) 
//STEPLIB  DD  DISP=SHR,DSN=DB9A9.SDSNEXIT                              
//         DD  DISP=SHR,DSN=DB9A9.SDSNLOAD                              
//         DD  DISP=SHR,DSN=CBC.SCCNCMP      <== C COMPILER             
//         DD  DISP=SHR,DSN=CEE.SCEERUN      <== LE RUNTIME             
//SYSEXEC  DD  DISP=SHR,                       <== LOCATION OF DSNTPSMP 
//             DSN=DB9A9.SDSNCLST                                       
//SYSTSPRT DD  SYSOUT=*                                                 
//CEEDUMP  DD  SYSOUT=*                                                 
//SYSPRINT DD  SYSOUT=*                                                 
//SYSABEND DD  DUMMY                                                    
//DSNTRACE DD  SYSOUT=* 
//**** DATA SETS REQUIRED BY THE SQL PROCEDURES PROCESSOR               
//SQLDBRM  DD  DISP=SHR,                       <== DBRM LIBRARY         
//             DSN=DB9AU.DBRMLIB.DATA                                   
//SQLCSRC  DD  DISP=SHR,                       <== GENERATED C SOURCE   
//             DSN=DB9AU.SRCLIB.DATA                                    
//SQLLMOD  DD  DISP=SHR,                       <== APPLICATION LOADLIB  
//             DSN=DB9AU.RUNLIB.LOAD                                    
//SQLLIBC  DD  DISP=SHR,                       <== C HEADER FILES       
//             DSN=CEE.SCEEH.H                                        
//         DD  DISP=SHR,                                                
//             DSN=CEE.SCEEH.SYS.H                                    
//         DD  DISP=SHR,                       <== DEBUG HEADER FILE    
//             DSN=DB9A9.SDSNC.H                                        
//SQLLIBL  DD  DISP=SHR,                       <== LINKEDIT INCLUDES    
//             DSN=CEE.SCEELKED                                       
//         DD  DISP=SHR,                                                
//             DSN=DB9A9.SDSNLOAD                                       
//SYSMSGS  DD  DISP=SHR,                       <== PRELINKER MSG FILE   
//             DSN=CEE.SCEEMSGP(EDCPMSGE)                             
//*                                                                     
//**** DSNTPSMP CONFIGURATION FILE - CFGTPSMP (OPTIONAL)                
//*             A SITE PROVIDED SEQUENTIAL DATASET OR MEMBER, USED TO   
//*             DEFINE CUSTOMIZED OPERATION OF DSNTPSMP IN THIS APPLENV.
//*CFGTPSMP DD  DISP=SHR,DSN=                                           
//*                                                                     
//**** WORKFILES REQUIRED BY THE SQL PROCEDURES PROCESSOR               
//SQLSRC   DD  UNIT=SYSALLDA,SPACE=(23440,(20,20)), 
//             DCB=(RECFM=FB,LRECL=80,BLKSIZE=23440)                
//SQLPRINT DD  UNIT=SYSALLDA,SPACE=(23476,(20,20)),                 
//             DCB=(RECFM=VB,LRECL=137,BLKSIZE=23476)               
//SQLTERM  DD  UNIT=SYSALLDA,SPACE=(23476,(20,20)),                 
//             DCB=(RECFM=VB,LRECL=137,BLKSIZE=23476)               
//SQLOUT   DD  UNIT=SYSALLDA,SPACE=(23476,(20,20)),                 
//             DCB=(RECFM=VB,LRECL=137,BLKSIZE=23476)               
//SQLCPRT  DD  UNIT=SYSALLDA,SPACE=(23476,(20,20)),                 
//             DCB=(RECFM=VB,LRECL=137,BLKSIZE=23476)               
//SQLUT1   DD  UNIT=SYSALLDA,SPACE=(23440,(20,20)),                 
//             DCB=(RECFM=FB,LRECL=80,BLKSIZE=23440)                
//SQLUT2   DD  UNIT=SYSALLDA,SPACE=(23440,(20,20)),                 
//             DCB=(RECFM=FB,LRECL=80,BLKSIZE=23440)                
//SQLCIN   DD  UNIT=SYSALLDA,SPACE=(32000,(20,20))                  
//SQLLIN   DD  UNIT=SYSALLDA,SPACE=(3200,(30,30)),                  
//             DCB=(RECFM=FB,LRECL=80,BLKSIZE=3200)                 
//SQLDUMMY DD  DUMMY                                                
//SYSMOD   DD  UNIT=SYSALLDA,SPACE=(23440,(20,20)),   <= PRELINKER  
//             DCB=(RECFM=FB,LRECL=80,BLKSIZE=23440) 

Example 4-4 is a sample procedure for executing the user external SQL, COBOL, or C/C++ stored procedures. This user procedure for external SQL stored procedures needs one unauthorized data set included in STEPLIB.

Example 4-4 Sample user procedure for SQL, COBOL, C/C++ stored procedures

//*************************************************************         
//*    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=XXXXXXXX,DB2SSN=DB9A,NUMTCB=40           
//IEFPROC EXEC PGM=DSNX9WLM,REGION=&RGN,TIME=NOLIMIT,                   
//        PARM='&DB2SSN,&NUMTCB,&APPLENV'                               
//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 

Example 4-5 is a sample procedure for executing user Java stored procedures. Only the WLM Application Environment that executes Java stored procedures should include a //JAVAENV DD. The presence of this DD causes a JVM to be loaded, one for each NUMTCB. We set the NUMTCB to 1 for our test environment, so the refresh to the WLM environment went quickly while we were developing our code and making changes. We set NUMTCB to 5 in our production user procedure for Java stored procedures.

This user procedure for Java stored procedures needs one unauthorized data set included in STEPLIB.

Example 4-5 Sample procedure for user Java stored procedures

//*************************************************************    
//*    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=WLMENVJ,DB2SSN=DB9A,NUMTCB=5        
//IEFPROC EXEC PGM=DSNX9WLM,REGION=&RGN,TIME=NOLIMIT,              
//        PARM='&DB2SSN,&NUMTCB,&APPLENV'                          
//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                         
//         DD  DISP=SHR,DSN=DB9A9.SDSNLOD2                         
//JAVAENV DD DISP=SHR,DSN=DB9AU.JSPENV                             
//JSPDEBUG DD SYSOUT=* 


Redbooks
ibm.com/redbooks
Copyright IBM Corp. 2011. All rights reserved.