Collect and analyze db2 snapshots to identify slow queries

To identify slow queries it needs to monitor the DB2 for the time of execution of the test scenario. It can be a little more complicate if you are running a set of scenarios, because you have to identify what is the relationship between scenario and queries.

These are the main steps to think about:

1) Start monitor DB2:

<from db2 command line connect to the database>

db2 "update monitor switches using bufferpool on lock on sort on statement on table on uow on"

db2 -v reset monitor all


2) Execute the stress scenario


3) Take the snapshot (*)
db2 -v get snapshot for all on DBNAME > dbsnapshot.txt


4) Stop monitor DB2:

db2 "update monitor switches using bufferpool off lock off sort off statement off table off uow off"


5) Analyze the snapshots and order by worst queries (in terms of num_executions, total_cpu_util, total_exec_time, avg_busy_time). 

You can use the IBM Performance Analyst to Analyze DB2 Snapshots, more info at this link:  http://goo.gl/crsYQT

(*) Instead of capturing the snapshots above the alternative is also to run a table function like this to get the worst 10 queries:

db2 "SELECT varchar(package_name,10) as package, num_executions, STMT_EXEC_TIME, rows_returned, varchar(stmt_text,100) as sql_text FROM TABLE(MON_GET_PKG_CACHE_STMT ('D',NULL,NULL,-1)) as t1 ORDER BY num_executions DESC fetch first 10 rows only"


6) To make sure you have identified the right query, based on your experience of the product,  try to identify what could be the scenario that produces the slow query (checking into the logs, traces and db2diag.log). Then run the single scenario manually to confirm that is(are) the right query(queries) to analyze.

Start IBM HttpServer

Go under the bin dir of default installation, on AIX:

cd /opt/IBM/HTTPServer/bin


stop:
./apachectl -k stop


start:
./apachectl -k start -f /opt/IBM/HTTPServer/conf/httpd.conf


The WebSphere plugin file (plugin-cfg.xml) allows to link the IBM HttpServer as webserver of Websphere that starts with the command above.
If the http requests are not served by http server on the port 80 and not forwarded to the related the application server ports (9080, 9081.. depending how big is the cluster), check  if the plugin file is correctly  defined with actual ports and hosts of the application servers currently running. The WebSphere plugin file is located for example here:

/opt/IBM/WebSphere/AppServer/profiles/PROFILENAME/config/cells/HOSTCELLNAME/nodes/HOSTNODENAME/servers/webserver1/plugin-cfg.xml

If needed you can regenerate and propagate the plugin from was admin console (Server -> Server Type - Web servers):