473,320 Members | 1,947 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,320 software developers and data experts.

Snapshot monitor, SQL statement question

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.
Nov 12 '05 #1
2 3036
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...prefetcher/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.

Nov 12 '05 #2
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********@yahoo.com (hype) wrote in message news:<71*************************@posting.google.c om>...
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.

Nov 12 '05 #3

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

Similar topics

8
by: Jean-Marc Blaise | last post by:
Dear all, It seems there is a problem with some snapshot table functions, if you try to divide by some element that is unset or equal to 0. This generates a trap file with the db2fmp.exe...
14
by: Ina Schmitz | last post by:
Hello, I would like to get the logical and physical reads for every sql statement executed. Thatfore, I used the command "db2 get snapshot for dynamic sql on <mydatabase>". There, I could see...
3
by: Tejas | last post by:
Hi, I have gone through the snapshot API. I need to use it for finding location of database files, tablespace containers etc on a remote machine. These parameters are present in the response....
3
by: sethwai | last post by:
Hi, I find the "snapshot for dynamic sql" a great tool for identifying the most frequently executed dynamic sql statements, most cpu used, etc. It is a great help for system tuning. My...
1
by: shenanwei | last post by:
I have db2 v8.2.5 on AIX V5.3 with all the switches on Buffer pool (DFT_MON_BUFPOOL) = ON Lock (DFT_MON_LOCK) = ON Sort ...
4
by: steingold | last post by:
Hi all I'm using DB2 UDB v8.2 on linux. After minimizing the code in order to isolate the problem, I have a simple jdbc client that executes the following SQL statement in loop : SELECT * FROM...
0
by: mike | last post by:
Problem: the snapshot output for dynamic SQL shows some SELECT statements executed many times but all three CPU counters (total/system/user) in the dynamic-sql-snapshot are zero, but most other...
1
by: PaulR | last post by:
Hi all, (DB2 LUW 8.2) I'm just trying to write a little widget to capture lock wait details (when they occur). i.e when a lock-wait occurs, simply record the 2 statements involved in the...
2
by: Yaron Goldstein | last post by:
Hi all. I'm writing a performance analysis utility that uses the snapshot monitor C API to get statistics in the connection level. It seems that the way I use the snapshot monitor is very...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.