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:
# jcc.override.traceFile=<file name> db2.jcc.t2zosTraceFile=<file name>
java com.ibm.db2.jcc.DB2Jcc -versionIf 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.
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 . . . //*