[Version 5.0.1 and later]DB2 tuning tips for z/OS

Performance tuning for DB2 is usually critical to the overall performance of a WebSphere for z/OS application. DB2 is often the preferred datastore for a session or EJB. There are many books that cover DB2 tuning; we can't possibly provide as thorough a treatment of DB2 here as we would like. Listed here are some basic guidelines for DB2 tuning as well as some guidelines for tuning DB2 for WebSphere. For more complete information on DB2 tuning refer to the DB2 Universal Database for OS/390 and z/OS Administration Guide Document Number SC26-9931-03. The DB2 books can be accessed at the following Internet location:http://www.ibm.com/servers/eserver/zseries/zos/.

You have the choice of Java Database Connectivity (JDBC) or, with WebSphere Application Server Version 5.0.1, Structured Query Language in Java (SQLJ) support with both CMP and CMP beans when you are using the DB2 Universal JDBC driver provider with DB2 as your backend database. JDBC makes use of dynamic SQL whereas SQLJ generally is static and uses pre-prepared plans. You can also use SQLJ support with BMP beans when you are using the DB2 for z/OS Local JDBC provider (RRS) with DB2 for z/OS as your backend database. DB2 for z/OS users who wish to use SQLJ support with CMP beans must use the DB2 Universal JDBC driver provider. SQLJ requires an extra step to create and bind the plan whereas JDBC does not. SQLJ, as a general rule, is faster than JDBC. From a performance point of view, SQLJ it is generally a better performer than JDBC on z/OS DB2. Other features of SQLJ are that it's security model and it's repeatability are similar to static SQL which is often preferred by z/OS DB2 DBAs. SQLJ does require a couple of extra steps that are features of newer versions of WSAD (reference). Refer to Developing data access applications.

General DB2 tuning tips:

This discussion relates only to DB2 for z/OS JDBC Driver which is referred to as the DB2 for z/OS Legacy JDBC Driver.

DB2 for WebSphere tuning tips:

Example: This example identifies zparm values discussed in this article.



//DB2INSTE   JOB MSGCLASS=H,CLASS=A,NOTIFY=IBMUSER                  
/*JOBPARM SYSAFF=*                                                  
//******************************************************************
//* JOB NAME = DSNTIJUZ                                             
//*                                                                 
//* DESCRIPTIVE NAME = INSTALLATION JOB STREAM                      
//*                                                                 
//*    LICENSED MATERIALS - PROPERTY OF IBM                         
//*    5675-DB2                                                     
//*    (C) COPYRIGHT 1982, 2000 IBM CORP.  ALL RIGHTS RESERVED.     
//*                                                                 
//*    STATUS = VERSION 7                                           
//*                                                                 
//* FUNCTION = DSNZPARM AND DSNHDECP UPDATES                        
//*                                                                 
//* PSEUDOCODE =                                                    
//*   DSNTIZA  STEP  ASSEMBLE DSN6.... MACROS, CREATE DSNZPARM      
//*   DSNTIZL  STEP  LINK EDIT DSNZPARM                             
//*   DSNTLOG  STEP  UPDATE PASSWORDS                               
//*   DSNTIZP  STEP  ASSEMBLE DSNHDECP DATA-ONLY LOAD MODULE        
//*   DSNTIZQ  STEP  LINK EDIT DSNHDECP LOAD MODULE                 
//*   DSNTIMQ  STEP  SMP/E PROCESSING FOR DSNHDECP                  
//*                                                                 
//* NOTES = STEP DSNTIMQ MUST BE CUSTOMIZED FOR SMP.  SEE THE NOTES 
//*         NOTES PRECEDING STEP DSNTIMQ BEFORE RUNNING THIS JOB.   
//*                                                                 
//*  LOGLOAD=16000000,                                              
//*********************************************************************/
//*                                                                     
//DSNTIZA EXEC PGM=ASMA90,PARM='OBJECT,NODECK'                          
//STEPLIB DD DSN=ASM.SASMMOD1,DISP=SHR                                  
//SYSLIB   DD  DISP=SHR,                                                
//         DSN=DB2710.SDSNMACS                                          
//         DD  DISP=SHR,                                                
//         DSN=SYS1.MACLIB                                              
//SYSLIN   DD  DSN=&LOADSET(DSNTILMP),DISP=(NEW,PASS),                 
//             UNIT=SYSALLDA,                                           
//             SPACE=(800,(50,50,2)),DCB=(BLKSIZE=800)                  
//SYSPRINT DD  SYSOUT=*                                                 
//SYSUDUMP DD  SYSOUT=*                                                 
//SYSUT1   DD  UNIT=SYSALLDA,SPACE=(800,(50,50),,,ROUND)                
//SYSUT2   DD  UNIT=SYSALLDA,SPACE=(800,(50,50),,,ROUND)                
//SYSUT3   DD  UNIT=SYSALLDA,SPACE=(800,(50,50),,,ROUND)                
//SYSIN    DD  *                                                        
    DSN6ENV    MVS=XA                                                   
    DSN6SPRM   RESTART,																	X
               .
               .
               .
               
AUTH=YES,
                                               X
               AUTHCACH=1024,                                          X
               BINDNV=BINDADD,                                         X
               BMPTOUT=4,                                              X
               
CACHEDYN=YES,
                                           X
               .
               .
               .
               
MAXKEEPD=16000,
                                         X
               .
               .
               .
   DSN6ARVP    ALCUNIT=CYL,                                            X
               .
               .
               .
   DSN6LOGP    DEALLCT=(0),                                            X
               .
               .
               .
   DSN6SYSP    AUDITST=NO,                                             X
               BACKODUR=5,                                             X
              
CHKFREQ=16000000,
                                       X
               CONDBAT=400,                                            X
               CTHREAD=1200,                                           X
               DBPROTCL=PRIVATE,                                       X
               DLDFREQ=5,                                              X
               DSSTIME=5,                                              X
               EXTRAREQ=100,                                           X
               EXTRASRV=100,                                           X
               EXTSEC=NO,                                              X
               
IDBACK=1800,
                                             X
               .
               .
               .
             //*                                                                     






Searchable topic ID:   rprf_tunezdb2
Last updated: Jun 21, 2007 9:56:50 PM CDT    WebSphere Application Server for z/OS, Version 5.0.2
http://publib.boulder.ibm.com/infocenter/wasinfo/index.jsp?topic=/com.ibm.websphere.zseries.doc/info/zseries/ae/rprf_tunezdb2.html

Library | Support | Terms of Use | Feedback