Sunday, September 21, 2014

LUW - How to capture performance details using DB2TOP for a given database? - Batch or Reply mode

db2top batch/reply mode - Capturing performance details for a database or DB2 problem determination using db2top utility:

db2top is one of the powerful tool with multiple options.  Here, I am
focused to enable and capture performance data on a database for a
given period of time.  This would be useful in scerios like, if you have
periodic issues at a given time frame of the day and want to see what
causes the issue, this technique might be useful or your application or
testing team wants to conduct a test and they are looking for performance
parameter, then this might be useful.

Here are quick things before starting the monitoring:
1)  The monitoring will capture large amount of data.  Check your file
system and ensure you have enough space (use commands like "df -h").
2)  You need to know how long you are going to capture the data and at
what intervals.  Both these parameters will have impact on the size of the output file.

Issue the following command:
db2top -d {database_name} -f {output_file_name} -C -m x -i xx
The above command captures a snapshot every 'x' seconds for 'xx' minutes

Once the details are gathered on the output file (output_file_name), you
can choose what need to be analyzed and gather those details in a
semi-colon seperated values.  This can be taken back to PC and imported
to Excel sheet for further analysis and graphs.

db2top -d {database_name} -f {output_file_name} -b d > dbout
db2top -d {database_name} -f {output_file_name} -b b > bpout
db2top -d {database_name} -f {output_file_name} -b T > tbout
db2top -d {database_name} -f {output_file_name} -b D > sqlout
db2top -d {database_name} -f {output_file_name} -b s > stmtout
db2top -d {database_name} -f {output_file_name} -b U > lockout
db2top -d {database_name} -f {output_file_name} -b u > utilout
db2top -d {database_name} -f {output_file_name} -b m > memout
You can add "-A" to the end of the above commands (before '>') 
to get automatic analysis.  Like:
   db2top -d sample -f db2snap-sample-AIX64.bin -b D -A > sqlout
The above command will give auto analysis for SQLs.

Good Luck!