Step SQL Optimize

db2expln -d tibsdb -f xx/sql -o xx.log

db2expln - SQL Explain Command
http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/core/r0005736.htm

The Two Biggest DB2 Performance Things
ref

Check index
====
db2 "describe indexes for table tibsadmin.fundtransferhistory"
db2 "SELECT colnames,tbname FROM SYSIBM.SYSINDEXES where upper(tbname) =upper('fundtransferhistory') ";

db2expln -d tibsdb -f 1.sql -o 1.log


drop index ;
CREATE INDEX {index_name} ON {table} ( {column1} ASC, {column2} ASC) ;
CREATE INDEX {index_name} ON {table} ( {column1} ASC) CLUSTER ;
**cluster index is physical index. Only 1 cluster index per table

Setup JMeter for load test
====================
pre requisite-put the db2 drirver.jar to jmeter/lib

1)Test Plan -> Add Thread Group
2)Thread Group->Add->Config Element ->JDBC Connection Configuration
3)Max number of connection = {> Number of thread}
Database url = jdbc:db2://{host}:{port}/{dbname}
Jdbc driver classess = com.ibm.db2.jcc.DB2Driver
username=
password=
**package for "com.ibm.db2.jcc" is ibm jdbc driver type4
**package for "COM.ibm.db2.jdbc" is ibm jdbc driver type2 which use by websphere server
**"DB2Driver" class is normal driver
**"DB2ConnectionPoolDataSource" class is connection pool data source.
****jdbc:db2://10.100.101.30:61099/tibsdb


4)Thread Group->Add->Sampler->JDBC Request
**For insert sql, u can create Non Duplicate ID using this function
CHAR( ${__counter(FALSE,100)} )

example:
INSERT INTO Schema.Mytable VALUES ( CHAR( ${__counter(FALSE,100)} )
, 'AbcValue')

5)ThreadGroup ->Add->Listner
i)Summary report - to view summary statistic
ii)View Results Tree - to check the request and response data
iii) Aggregate Report - Have 90% line











ref 1 : How to easily populate a table with random data