DB2 Tuning Tips
The DB2 runstats command updates statistics that profile the physical characteristics of a database table,
along with its associated indexes. Dynamic SQL statements generated by your app code will automatically
use these updated statistics, but static SQL statements built inside a database require the DB2 rbind command
be run as well. Execution of runstats/rbind should be done on a regular basis as part of regular database maintenance.
As your databases grow and change over time, recalculating table statistics is critical to improving
database performance and should be done regularly (manually using scripts below or using a cron job).
Along with runstats/rbind, the DB2 reorgchk command should also be run to check to see if a
reorganization of certain tables and/or indexes may be warranted. Belowe we list the commands that, first,
dynamically determine a database tables (given a schema name) that runstats can run on, and second,
a Windows batch file that actually runs the runstats/rbind commands - they are easily ported to UNIX platforms since
the DB2 commands are the same:
__ 1. runstats/rbind commands
db2 connect to mydb user mydb_usr using usr_password
db2 -x select tabname from syscat.tables where type='T' and tabschema='MYSCHEMA' | \\
sed -e "s/ *.$//" | sed -e "s/^/@db2 runstats on table MYSCHEMA./" | \\
sed -e "s/$/ with distribution and detailed indexes all/" > .\perform_stats.bat
echo @db2 connect reset >> .\perform_stats.bat
echo @db2rbind mydb -l .\log.txt all -u mydb_usr -p usr_password >> .\perform_stats.bat
Inside the perform_stats.bat, there should be lines similar to the following:
...
@db2 runstats on table MYSCHEMA.UDTSTWORKLISTS with distribution and detailed indexes all
@db2 runstats on table MYSCHEMA.UDTSTXDOOBJECTS with distribution and detailed indexes all
@db2 runstats on table MYSCHEMA.UDTSTXDOOBJFACTORY with distribution and detailed indexes all
@db2 runstats on table MYSCHEMA.UDTUT00200001 with distribution and detailed indexes all
@db2 runstats on table MYSCHEMA.UDTUT00201001 with distribution and detailed indexes all
...
Each of the above lines performs runstat on a table. By running the my_runstats.bat program,
performance statistics on all mydb tables will be profiled. The db2rbind command at the end
revalidates all packages in the database.
__ 2. reorgchk commands
db2 connect to mydb user mydb_usr using usr_password
db2 reorgchk current statistics on table all
Re-organizes tables and indexes, if needed, by DB2.
|
Here we list the common tuning parameters and their corresponding values used in large DB2
setups (for Enterprise data volume and traffic). If you want to learn the basics of DB2 application
tuning, this article named "Measure, Improve, Repeat" by Scott Hayes on the DB2 Magazine website
can help you get started.
These parameters and values enhanced performance (response times) and gained scalability (in throughput) in our lab tests, but
they may not give your application the boost you seek. Table space usage and other system tunings (e.g., OS, network, disks, etc.)
can make huge differences in overall performance too. Please see other parts of this web site for those tunings. But we do recommend
start with those values and tune as you go, and remember, always "measure, improve, and repeat."
#!/bin/sh
# *******************************************************
# DB2 ENVIRONMENT VARIABLES
# *******************************************************
db2set DB2_MMAP_READ=NO
db2set DB2_MMAP_WRITE=NO
db2set DB2_FMP_COMM_HEAPSZ=
db2set DB2_EVALUNCOMMITTED=YES
db2set DB2_SKIPINSERTED=YES
db2set DB2_SKIPDELETED=YES
db2set DB2_HASH_JOIN=NO
# *******************************************************
# DB2 DATABASE MANAGER SETTINGS; NOTE: MONITORING
# IS OFF TO REDUCE CPU; YOUR APPLICATION MAY REQUIRE
# MONITORS TO BE ON, SO CHANGE ACCORDINGLY
# *******************************************************
db2 update dbm cfg using NUMDB 4
db2 update dbm cfg using DFT_MON_BUFPOOL OFF
db2 update dbm cfg using DFT_MON_LOCK OFF
db2 update dbm cfg using DFT_MON_SORT OFF
db2 update dbm cfg using DFT_MON_STMT OFF
db2 update dbm cfg using DFT_MON_TABLE OFF
db2 update dbm cfg using DFT_MON_UOW OFF
db2 update dbm cfg using DFT_MON_TIMESTAMP OFF
db2 update dbm cfg using MON_HEAP_SZ 10000
db2 update dbm cfg using UDF_MEM_SZ 256
db2 update dbm cfg using JAVA_HEAP_SZ 512
db2 update dbm cfg using SHEAPTHRES 10000
db2 update dbm cfg using DIR_CACHE YES
db2 update dbm cfg using ASLHEAPSZ 15
db2 update dbm cfg using RQRIOBLK 65535
db2 update dbm cfg using QUERY_HEAP_SZ 16384
db2 update dbm cfg using DRDA_HEAP_SZ 128
# *******************************************************
# DB2 DATABASE MANAGER SETTINGS FOR CONNECTIONS AND AGENTS
# SHOWN HERE SUPPORTS 1200 CONCURRENT CONNECTIONS!
# *******************************************************
db2 update dbm cfg using FENCED_POOL 200
db2 update dbm cfg using NUM_INITAGENTS 50
db2 update dbm cfg using MAXAGENTS 200
db2 update dbm cfg using MAX_COORDAGENTS 200
db2 update dbm cfg using NUM_INITFENCED 50
db2 update dbm cfg using NUM_POOLAGENTS 200
db2 update dbm cfg using MAX_CONNECTIONS 200
# *******************************************************
# UPDATE YOUR DATABASE SETTINGS
# *******************************************************
db2 update db cfg for yourdb using DFT_QUERYOPT 2
db2 update db cfg for yourdb using DBHEAP 1200
db2 update db cfg for yourdb using CATALOGCACHE_SZ 64
db2 update db cfg for yourdb using LOGBUFSZ 128
db2 update db cfg for yourdb using UTIL_HEAP_SZ 5000
db2 update db cfg for yourdb using LOCKLIST 1000
db2 update db cfg for yourdb using APP_CTL_HEAP_SZ 1000
db2 update db cfg for yourdb using APPGROUP_MEM_SZ 60000
db2 update db cfg for yourdb using SORTHEAP 256
db2 update db cfg for yourdb using STMTHEAP 4096
db2 update db cfg for yourdb using APPLHEAPSZ 4096
db2 update db cfg for yourdb using PCKCACHESZ 5000
db2 update db cfg for yourdb using STAT_HEAP_SZ 4384
db2 update db cfg for yourdb using MAXLOCKS 25
db2 update db cfg for yourdb using LOCKTIMEOUT 60
db2 update db cfg for yourdb using CHNGPGS_THRESH 60
db2 update db cfg for yourdb using NUM_IOCLEANERS 4
db2 update db cfg for yourdb using NUM_IOSERVERS 6
db2 update db cfg for yourdb using MAXAPPLS 1200
db2 update db cfg for yourdb using AVG_APPLS 1
db2 update db cfg for yourdb using MAXFILOP 64
db2 update db cfg for yourdb using LOGFILSIZ 1000
db2 update db cfg for yourdb using LOGPRIMARY 10
db2 update db cfg for yourdb using LOGSECOND 20
|
For details on each tuning parameter listed above, see IBM publib site for Database tuning overview.
|
