DB2 8 - Collect statistics

[Quasar]

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 <DBNAMEthe 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

[Ian]

Quasar wrote:

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 <DBNAMEthe statistics
are identical to the previous snapshot.
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,

[Quasar]

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
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,