(ZOS) 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 EJBs. Listed here are some basic guidelines for DB2 tuning as well as some guidelines for tuning DB2 for WAS. 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 your 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 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 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 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. 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 we 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 -versionIf this returns a class not found, either we 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 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 . . . AXKEEPD=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 . . . //*
Configure a JDBC provider