Configuration, tuning, and performance scripts

db2 update db cfg for mydb using logfilsiz 65535
db2 update db cfg for mydb using logprimary 40
db2 update db cfg for mydb using newlogpath /db2log
db2 connect to mydb
db2 -v alter bufferpool ibmdefaultbp size 675000 automatic
db2 connect reset
db2stop;db2start
db2 connect to mydb
db2 connect reset

# DB2 Auto Configurator Example
#
# Using this command can help you tune DB2 automatically for your environment
#
# 1. Manually adjust your log file locations
# 2. Specify the values that is needed for DB2 Auto Configurator 
#
# 
# Example:
# 
# db2 autoconfigure using mem_percent 80 workload_type simple num_stmts 60 
tpm 2000 is_populated yes 
#              num_local_apps 0 num_remote_apps 65 isolation rs bp_resizeable 
yes apply db and dbm
# 
# 	mem_percent - the percentage of system memory you would like to use for 
bufferpool memory. 
# 	
# 	Workload_type - for our purpose, WebSphere generally uses simple SQLs.
# 	
# 	num_stmts - number of statements involved in the workload
# 	
# 	tpm - estminate the transaction per minute that you want DB2 to handle
# 	
# 	is_populated - is the database populated with data?
# 	
# 	num_local_apps - number of applications connecting locally. 
If you setup is a two tier config with WebSphere on a separate machine, 
then enter '0'
# 	
# 	num_remote_app - number of applications that will connect to DB2 remotely. 
This number should be the summation of all the connection pool sizes from 
WebSphere. For our workload, it is the size of Connection Pool size of the DB. 
If it is a WebSphere cluster, multiply it by the number of WebSphere nodes.
# 	
# 	isolation - specify the highlest isolation level your Application demands.  
# 	
# 	bp_resizable - bufferpool resizable or not. Generally is 'yes'
# 	
# 	apply db and dbm - apply the settings on your behalf.
# 	
# 	I have a dedicated DB2 machine that is in a 2-tier setup separated from the 
WebSphere machine. 
# 	- use 80% of the memory as my buffer pool. 
#	- workload has about 60 statements, 
#	- simple workload, 
#       - tpm is dictated by workload rate, so my guess it will hit 2000 max 
for my work on this machine. 
#	- Data is already loaded. I have 65 connections coming in from Websphere, 
#	- most of the beans require isolation level rs.

db2 connect to mydb
db2 autoconfigure using mem_percent 80 workload_type simple num_stmts 60 
tpm 2000 is_populated yes num_local_apps 0 num_remote_apps 100 isolation 
rs bp_resizeable yes apply db and dbm
#Run the following db2 terminate or else the changes won't take.
db2 terminate
db2 connect to mydb
db2 reorgchk update statistics on table all
db2 terminate

#invocation:   ./startgatherDB2 testname 
db2 -v connect to mydb
db2 -v update monitor switches using bufferpool on
db2 -v get snapshot for database on MYDB >snapA$1.out   
db2 -v get snapshot for tablespaces on MYDB >tablesnapA$1.out
db2 -v get snapshot for all applications >appsnapA$1.out   
db2 -v get snapshot for all bufferpools >buffersnapA$1.out
date >>snapA$1.out
############## Beginning of steady state – sleep through the whole thing!
sleep 600  
db2 -v get snapshot for database on MYDB >snapC$1.out
date >>snapC$1.out
db2 -v get snapshot for all applications >appsnapC$1.out
#############
db2 -v reset monitor all