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. 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 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.
DB2 for WebSphere tuning tips:
# jcc.override.traceFile=<file name> # jcc.override.traceFile=<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.
//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 . . . //*