DB2 tuning tips for z/OS

Performance tuning for DB2® is usually critical to the overall performance of a WebSphere® Application Server 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. DB2 information can be accessed at the following Internet location:https://www.ibm.com/servers/eserver/zseries/zos/.

Benefits of Structured Query Language in Java™ (SQLJ)

If you use the DB2 Universal JDBC driver provider, you 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 your DB2 logs are large enough, are allocated on the fastest volumes you have, and make sure they have optimal CI sizes.
Next, ensure that you have tuned your 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 WebSphere Application Server 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 your applications are using are defined with a nonzero traceLevel custom property, use the WebSphere Application Server for z/OS Administrative console to set the traceLevel to zero.
Be sure to define indexes on all your object primary keys. Failure to do so will result in costly tablespace scans.
Ensure that, once your 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. WebSphere Application Server 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 you have not issued the command properly.
Enable dynamic statement caching in DB2. To do this, modify your 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.
Increase DB2 checkpoint interval settings to a large value. To do this, modify your ZPARMS to include CHKFREQ=xxxxx, where xxxxx is set at a larger value when doing benchmarks. On production systems there are other valid reasons to keep checkpoint frequencies at a minimum level, 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
               .
               .
               .
             //*