Snapshot monitor, SQL statement question

[hype]

Hi,
1) If I need to monitor a resource eg, bufferpools at the database
level across all users, how can I do this ?
If snapshot monitoring needs to be done wouldn't enabling the switch
at the DBM level cause performance problems ?

2)How does one find the SQL statement that a particular user session
is executing?

TIA,
Hype.

[Sean McKeough]

Inline...

hype wrote:

Hi,
1) If I need to monitor a resource eg, bufferpools at the database
level across all users, how can I do this ? Bufferpools are reported per database 'get snapshot for bufferpool on
'db'" Attach to the instance, and use global snapshots if you're EEE. If snapshot monitoring needs to be done wouldn't enabling the switch
at the DBM level cause performance problems ? For the bufferpool switch you're looking at a very small cost in terms
of runtime. The issue is more with additional shared memory consumption
for the counters (counting is done per edu...prefetche r/agent etc).
2)How does one find the SQL statement that a particular user session
is executing?

If you want all statements, then a statement evmon with filtering on
that application. If you want the current statement, then snapshot for
applications (you can specify a single app if you want). For dynamic sql
you'll get the statement text. If you're static, you have to pull the
text from the catalogs using the package/sectoin/creator info.

[Amit]

1. There is no way to turn the monitor switches on for a single
database. You will need to turn the Bufferpools switch on for the
monitoring to begin. If you are using DB2 v8 you can try and use the
Health Indicators which are more 'lightweight' but do not provide as
much information. If you are only interested in cumulative metrics
for bufferpools instead of instantaneous results(i.e. the values of
various counters after the last application disconnects) - you can use
an event monitor instead of a snapshot monitor.
2. The best way to trace all sql statements for a user session is to
use an event monitor on the server. Look at the CREATE EVENT MONITOR
sql statement for further details.

hy********@yaho o.com (hype) wrote in message news:<71******* *************** ***@posting.goo gle.com>...

Hi,
1) If I need to monitor a resource eg, bufferpools at the database
level across all users, how can I do this ?
If snapshot monitoring needs to be done wouldn't enabling the switch
at the DBM level cause performance problems ?

2)How does one find the SQL statement that a particular user session
is executing?

TIA,
Hype.