I've been struggling on tuning my application. I google on how to tune my back end and make my front end work smoothly with high scalability. My objective for this project was to make my back end run faster and accept multiple connection without risking other connection, high scalability and of course secured. I used Apache Wicket as my MVC, Hibernate as my persistence layer and Spring as my Security Layer. My topic is how to tune my DB2 running on a Linux Ubuntu v 9.1 64 bit.
This is what I did on DB2 Express C v9.7 running on Linux v 9.10 64 bit
I updated the DB2 environment variables
db2set DB2_MMAP_WRITE = NO
db2set DB2_MMAP_READ = NO
db2set DB2_FMP_COMM_HEAPSZ =
db2set DB2_EVALUNCOMMITTED = YES
db2set DB2_SKIPINSERTED = YES
db2set DB2_SKIPDELETED = YES
db2set DB2_HASH_JOIN = NO
I turn off some of the setting for the monitoring to reduce CPU usage. If the DB2 is for testing mode, the monitoring tool can be turn on.
db2 update dbm cfg using NUMDB 4
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_BUFPOOL OFF
db2 update dbm cfg using DFT_MON_TABLE OFF
db2 update dbm cfg using MON_HEAP_SZ 10000
db2 update dbm cfg using DFT_MON_UOW OFF
db2 update dbm cfg using DFT_MON_TIMESTAMP OFF
db2 update dbm cfg using SHEAPTHRES 10000
db2 update dbm cfg using UDF_MEM_SZ 256
db2 update dbm cfg using JAVA_HEAP_SZ 512
db2 update dbm cfg using DIR_CACHE YES
db2 update dbm cfg using ASLHEAPSZ 15
db2 update dbm cfg using DRDA_HEAP_SZ 128
db2 update dbm cfg using RQRIOBLK 65535
db2 update dbm cfg using QUERY_HEAP_SZ 16384
I initialized the connection pooling even there is already a connection pooling established on the application server. The connection pooling on the application should be lesser or equal to the max connections on DB2 to avoid runtime error.
db2 update dbm cfg using FENCED_POOL 100
db2 update dbm cfg using NUM_INITAGENTS 50
db2 update dbm cfg using MAXAGENTS 100
db2 update dbm cfg using MAX_COORDAGENTS 100
db2 update dbm cfg using NUM_INITFENCED 50
db2 update dbm cfg using NUM_POOLAGENTS 100
db2 update dbm cfg using MAX_CONNECTIONS 100
I updated the database memory and heap management to provide high scalability.
db2 update db cfg for DB2T using DFT_QUERYOPT 2
db2 update db cfg for DB2T using DBHEAP 1200
db2 update db cfg for DB2T using CATALOGCACHE_SZ 64
db2 update db cfg for DB2T using LOGBUFSZ 128
db2 update db cfg for DB2T using UTIL_HEAP_SZ 5000
db2 update db cfg for DB2T using LOCKLIST 1000
db2 update db cfg for DB2T using APP_CTL_HEAP_SZ 1000
db2 update db cfg for DB2T using APPGROUP_MEM_SZ 60000
db2 update db cfg for DB2T using SORTHEAP 256
db2 update db cfg for DB2T using STMTHEAP 4096
db2 update db cfg for DB2T using APPLHEAPSZ 4096
db2 update db cfg for DB2T using PCKCACHESZ 5000
db2 update db cfg for DB2T using STAT_HEAP_SZ 4384
db2 update db cfg for DB2T using MAXLOCKS 25
db2 update db cfg for DB2T using LOCKTIMEOUT 60
db2 update db cfg for DB2T using CHNGPGS_THRESH 60
db2 update db cfg for DB2T using NUM_IOCLEANERS 4
db2 update db cfg for DB2T using NUM_IOSERVERS 6
db2 update db cfg for DB2T using MAXAPPLS 1200
db2 update db cfg for DB2T using AVG_APPLS 1
db2 update db cfg for DB2T using MAXFILOP 64
db2 update db cfg for DB2T using LOGFILSIZ 1000
db2 update db cfg for DB2T using LOGPRIMARY 10
db2 update db cfg for DB2T using LOGSECOND 20
Note: DB2T is the name of Database.
Before I did all the configuration / settings shown above, I execute RUNSTAT and REORGCHK after I created the database. This will help the query performance when using RI and indexes. I am not sure if the syntax or command shown above is also supported on DB2 z/OS. One more thing, the DB2 Linux and the Application Server runs on the same machine which I there is a minimal network traffic because I used localhost instead of the IP address.
My reference on DB2 performance tuning can be found on this link: Reference on DB2 Tuning can be found on this link: http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.doc/doc/t0024229.htm
