DB2 8 - Collect statistics

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Quasar - 18 Jan 2008 10:21 GMT

Hi everybody!

I''ve created a script that enable the statistics for a specific DB:

#!/bin/sh
if [ $# -eq 1 ] ; then
echo Enabling monitoring switches for ${1}
db2 connect to ${1}
db2 update monitor switches using TABLE on UOW on TIMESTAMP on
STATEMENT on BUFFERPOOL on LOCK on SORT on
db2 reset monitor all
db2 get monitor switches
else
echo "Usage: enableMon <DBNAME>"
fi

This seems to work fine.

I have created another script that *should* reset the statistics to
get "delta" information between different runs of the test script.

#!/bin/sh
if [ $# -eq 1 ] ; then
echo Resetting db2 statistics for ${1}
db2 connect to ${1}
db2 reset monitor all
db2 flush package cache dynamic
db2 terminate
else
echo "Usage: resetStats <DBNAME>"
fi

The problem seems to be that the statistics are not resetted: in fact
when I run a "db2 get snapshot for tables on <DBNAME> the statistics
are identical to the previous snapshot.
Am I doing something wrong? Or are there other commands to reset the
statistics?

Thanks in advance for you help!

Quasar

Reply to this Message

Ian - 19 Jan 2008 01:35 GMT

> Hi everybody!
>
[quoted text clipped - 33 lines]
> Am I doing something wrong? Or are there other commands to reset the
> statistics?

Yes. You have to realize that the snapshot values that you see are
local to your particular instance attachment, as are the monitor
switch settings.

So, running the enableMon will create a new attachment (because it
runs in a separate ksh process than your command line shell),
enable the monitor switches and then exit. The monitor switches
for your current instance attachment are still set to the defaults.

Likewise, the 'resetStats' script does exactly the same thing --
creates a new attachment, resets the stats for that attachment,
and then exits.

The easiest way to do this is to set up shell aliases (put these
in your .profile).

alias monon='db2 update monitor switches using <...>'
alias resetmon='db2 reset monitor all ; db2 flush package cache dynamic'
alias snap='db2 get snapshot for application agentid $1'
# snap 102

Using the aliases lets the commands run within your existing shell
(and therefore the existing instance attachment).

Good luck,

Reply to this Message

Quasar - 23 Jan 2008 13:28 GMT

Thanks a lot Ian. Your explanation was really helpful and now the
stats are correct!

Thank you!

> Yes. You have to realize that the snapshot values that you see are
> local to your particular instance attachment, as are the monitor
[quoted text clipped - 21 lines]
>
> Good luck,

Reply to this Message