473,385 Members | 1,693 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,385 software developers and data experts.

DB2 8 - Collect statistics

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
Jan 18 '08 #1
2 2655
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,
Jan 19 '08 #2
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,
Jan 23 '08 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

17
by: Felix | last post by:
Dear Sql Server experts: First off, I am no sql server expert :) A few months ago I put a database into a production environment. Recently, It was brought to my attention that a particular...
5
by: Jesper Jensen | last post by:
Hello group. I have an issue, which has bothered me for a while now: I'm wondering why the column statistics, which SQL Server wants me to create, if I turn off auto-created statistics, are so...
3
by: Metal Dave | last post by:
Hello, A script we run against the database as part of the upgrade of our product is failing with the following message: ALTER TABLE ALTER COLUMN EncodedID failed because STATISTICS hind_61_3...
3
by: Fan Ruo Xin | last post by:
I ran the runstats and try to collect sub-element statistics, but for some reason, the value of that two fields in syscat.columns is always -1. Does anyone have any idea?
0
by: Guru | last post by:
Hi All i used this statement to create a temporary table index. Create index session.indname on session.tablename collect statistics. it throws an error saying that SQL0526N The...
5
by: Mrinal Kamboj | last post by:
Hi , Any pointers when it's absolute necessary to use it . Does it has a blocking effect on the code , as GC per se is undeterministic . what if GC.collect is followed in next line by...
1
by: Chris Weston | last post by:
Hi. I have automatic statistic update turned on for all my databases. Is this an overhead I can do without? Could I update them overnight when the database is hardly in use? Thanks --...
5
by: Allan Ebdrup | last post by:
Hi We have a large class library of different classes, now we are going to implement a lot of usage statistics that we need, stuff like how many times a user has logged in, how many times a...
0
ADezii
by: ADezii | last post by:
In last week's Tip, I showed you how to use the ISAMStats Method of the DBEngine (DAO) to return vital statistics concerning Query executions such as: Disk Reads and Writes, Cache Reads and Writes,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.