DB2 tuning tips for z/OS

DB2 tuning tips for z/OS

Performance tuning for DB2 is usually critical to the overall performance of a WAS application. DB2 is often the preferred datastore for Enterprise JavaBeans (EJBs). Listed here are some basic guidelines for DB2 tuning as well as some guidelines for tuning DB2 for WebSphere Application Server. 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/.
Benefits of Structured Query Language in Java (SQLJ)
If we use the DB2 Universal JDBC driver provider, we can implement SQLJ as the query language for both BMP and CMP beans. SQLJ incurs less transaction overhead than the default query language for JDBC transactions, which is dynamic SQL. SQLJ is static and uses pre-prepared plans. Thus SQLJ generally improves application performance. For DB2 for z/OS database administrators, SQLJ is often easy to adopt because the security model and the statement repeatability features are similar to those of static SQL. SQLJ does require extra steps that are features of newer versions of WebSphere Studio Application Developer and Rational Application Developer.
Refer to the topic Developing data access applications for more information.
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.

First, ensure that the DB2 logs are large enough, are allocated on the fastest volumes we have, and make sure they have optimal CI sizes.

Next, ensure that we have tuned the bufferpools so that the most often-read data is in memory as much as possible. Use ESTOR and hyperpools.

You many want to consider pre-formatting tables that are going to be heavily used. This avoids formatting at runtime.

DB2 for WebSphere tuning tips:
Ensuring DB2 Tracing Under the DB2 for z/OS Universal Driver is Turned Off.
If the db2.jcc.propertiesFile jvm property has been defined to specify a DB2 jcc properties file to the WAS for z/OS, ensure that the following trace statements in the file are commented out if they are specified:
# jcc.override.traceFile=<file name>
# jcc.override.traceFile=<file name>
If any of the DB2 Universal JDBC Driver datasources the applications are using are defined with a nonzero traceLevel custom property, use the WAS for z/OS Administrative console to set the traceLevel to zero.
Be sure to define indexes on all the object primary keys. Failure to do so will result in costly tablespace scans.

Ensure that, once the tables are sufficiently populated, you do a re-org to compact the tables. Running RUNSTATS will ensure that the DB2 catalog statistics about table and column sizes and accesses are most current so that the best access patterns are chosen by the optimizer.

You will have to define more connections called threads in DB2. WAS uses a lot of threads. Sometimes this is the source of throughput bottlenecks since the server will wait at the create thread until one is available.
Make sure you are current with JDBC maintenance. Many performance improvements have been made to JDBC. To determine the JDBC maintenance level, enter the following from the shell:
java com.ibm.db2.jcc.DB2Jcc  -version
If this returns a class not found, either you are at a level of the driver that is older and doesn't support this command or we have not issued the command properly.
Best practice: Enable dynamic statement caching in DB2. To do this, modify the ZPARMS to say CACHEDYN(YES) MAXKEEPD(16K). Depending on the application, this can make a very significant improvement in DB2 performance. Specifically, it can help JDBC and LDAP query.bprac

Increase DB2 checkpoint interval settings to a large value. To do this, modify the ZPARMS to include CHKFREQ=xxxxx, where xxxxx is set at a high value when doing benchmarks. On production systems there are other valid reasons to keep checkpoint frequencies lower, however.
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
               .
               .
               .
             //*                                                                     
Subtopics

JDBC tuning tips for use with DB2
WAS uses JDBC prepared statement caching as a performance enhancing feature. If you are using this feature together with DB2 for z/OS, be aware of the potential impact on the number of DB2 JDBC cursor objects available.

Related tasks

Configure a JDBC provider using the administrative console Reference topic