DB2 commands to analyze performances issue

Today a list of commands to run (before, during and after the workload) to check performances issue of a db2 database. On the db2 instance connect to the database and run the commands:

• Start up the system

Db2 and the application using the database needs up and running. 

1) db2support -install

2) db2 "select * from table(mon_get_package_cache_stmt(null,null,null,null))" > pkg1.out

3) db2 "get snapshot for all on <db> "> snap1.out

4) db2pd -d <db> -dynamic > dyn1.out

• Run the workload

While the workload is running at peak load run (ie run multiple times):

5) tprof -skel -x sleep 10, then copy sleep.prof output to tprof1.out

6) db2 "select * from table(mon_get_package_cache_stmt(null,null,null,null))" > pkg2.out

7) db2 "get snapshot for all on <db> "> snap2.out

8)  db2pd -d <db> -dynamic > dyn2.out

Additionally:
- add a capture of edu timings to the data gathering script 
db2pd -edus > pdedus.<timestamp>

- when running at the high load interval, run db2trc (just need this once):

db2trc on -perfcount -t;sleep 30;db2trc dmp perftrc.dmp;db2trc perffmt perftrc.dmp perftrc.perffmt 

• Workload ends

Finally collects these output:

9) db2 "select * from table(mon_get_package_cache_stmt(null,null,null,null))" > pkg.end.out

10) db2 "get snapshot for all on <db> "> snap.end.out

11) db2pd -d <db> -dynamic > dyn.end.out

12) db2pd -d <db> -apm dump dynamic zpas (outputs a message with an output file name - copy that file to be analyzed)

13) Save a copy of the db2diag.log

Now you have all ouput files that can be analyzed.