get snapshot for dynamic sql

[Ina Schmitz]

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 the logical and physical reads from
bufferpool for every statement monitored. Am I right?
But now, I would like to read these results out of a table so that I could
process them automatically. In which table(s) are the results of snapshot
for dynamic sql stored? The values, I am interested in are statement text,
logical and physical reads, cpu time and total execution time.

Would be really great if you had any ideas for me!

Cheers,
Ina

[Jan M. Nelken]

Ina Schmitz wrote:

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 the logical and physical reads from
bufferpool for every statement monitored. Am I right?
But now, I would like to read these results out of a table so that I could
process them automatically. In which table(s) are the results of snapshot
for dynamic sql stored? The values, I am interested in are statement text,
logical and physical reads, cpu time and total execution time.

Would be really great if you had any ideas for me!

Cheers,
Ina

The best idea would be to search online documentation intalled with DB2
or available online at:

http://publib.boulder.ibm.com/infoce...help/index.jsp

Please tell us what search arguments you used if you cannot find answer
you are looking for.
Jan M. Nelken

[Ina Schmitz]

[...]

Thatfore, I used the command "db2 get snapshot for dynamic sql on
<mydatabase>". There, I could see the logical and physical reads from
bufferpool for every statement monitored. Am I right?
But now, I would like to read these results out of a table so that I
could process them automatically. In which table(s) are the results of
snapshot for dynamic sql stored? The values, I am interested in are
statement text, logical and physical reads, cpu time and total execution
time.

Please tell us what search arguments you used if you cannot find answer
you are looking for.

This was already the first thing I did - looking in the online help.
I used the search arguments: "snapshot dynamic sql table"
I just found out that the SNAPSHOT_DYN_SQL doesn't give me what I
need.Perhaps the pool_index_p_reads element could help me, but I didn't
found out how to access it in a sql statement.
Would be great if you could help me in finding better search arguments...

Cheers,
Ina

[Jan M. Nelken]

Ina Schmitz wrote:

Would be great if you could help me in finding better search arguments...

Ina,

Your original question was:

"...In which table(s) are the results of snapshot for dynamic sql stored?
The values, I am interested in are statement text, logical and physical
reads, cpu time and total execution time..."

I would start with searching online documentation available at:

http://publib.boulder.ibm.com/infoce...help/index.jsp

with SNAPSHOT BUFFER POOL TABLE FUNCTION words. You will find -
approximately within first 10 hits - a table function which closely
answers some of your requirements.

You may also try SNAPSHOT DYNAMIC SQL TABLE FUNCTION in order to find
out another candidate.

From your own words - you were looking for table function providing
snapshot for dynamic sql.

So why not search for TABLE FUNCTION providing SNAPSHOT for DYNAMIC SQL?
Jan M. Nelken

[RdR]

Hi,

We are currently doing some designs for Sarbanes-Oxley compliant auditing
applications that needs to capture SQL statements, I know it is not exactly
the same but similar, hopefully these ideas will prompt you to get more
ideas, these are two ways we have tried so far:
1) Use Data Propagator to capture from the logs, inserts, updates, and
deletes but modify the apply job to turn everything to inserts instead of
the normal updates which mimicks the original statement. The drawback of
this solution is that we wil not detect selects from the database. One big
advantage on this is we can get the row information (what changed before and
after images) plus other journal information like who made the change, when
the change was made and what type of change it was (UP/UB, DL, PT).
2) To write an application that will stage the results of the db2 get
snapshot command and have a reader (an ETL tool) convert them to inserts to
a table. Once the above is done, have something like Crystal Reports to read
them and format it to a report.

Hope this helps.

RdR

"Ina Schmitz" <we*@inalein.net> wrote in message
news:d5*************@news.t-online.com...

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 the logical and physical reads from
bufferpool for every statement monitored. Am I right?
But now, I would like to read these results out of a table so that I could
process them automatically. In which table(s) are the results of snapshot
for dynamic sql stored? The values, I am interested in are statement text,
logical and physical reads, cpu time and total execution time.

Would be really great if you had any ideas for me!

Cheers,
Ina

[Serge Rielau]

RdR wrote:

Hi,

We are currently doing some designs for Sarbanes-Oxley compliant auditing
applications that needs to capture SQL statements, I know it is not exactly
the same but similar, hopefully these ideas will prompt you to get more
ideas, these are two ways we have tried so far:
1) Use Data Propagator to capture from the logs, inserts, updates, and
deletes but modify the apply job to turn everything to inserts instead of
the normal updates which mimicks the original statement. The drawback of
this solution is that we wil not detect selects from the database. One big
advantage on this is we can get the row information (what changed before and
after images) plus other journal information like who made the change, when
the change was made and what type of change it was (UP/UB, DL, PT).
2) To write an application that will stage the results of the db2 get
snapshot command and have a reader (an ETL tool) convert them to inserts to
a table. Once the above is done, have something like Crystal Reports to read
them and format it to a report.

3) Query patroller (?)

Just a thought
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Ina Schmitz]

Hello,

thanks for your answer. But unfortunaly, I think that my problem has not
become clear. I try to explain...

"...In which table(s) are the results of snapshot for dynamic sql stored?
The values, I am interested in are statement text, logical and physical
reads, cpu time and total execution time..." [...] From your own words - you were looking for table function providing
snapshot for dynamic sql.

So why not search for TABLE FUNCTION providing SNAPSHOT for DYNAMIC SQL?

Yes, I executed:
"select * from table(snapshot_dyn_sql('mydatabase',-1)) as myDYNSQL"
Here I got the statement text, the cpu time etc.
BUT: I also need the logical and physical reads for my statement.
With "select * from table(snapshot_bp('mydatabase',-1)) as myBP"
I get logical and physical reads.

Do you have any idea HOW TO get the logical and physical reads of a certain
sql statement? Because in the output of "select * from
table(snapshot_bp('mydatabase',-1)) as myBP" the statement text does not
occur. Is there any join attribut with which I could link these two tables?

Hope that my problem came clearer and you could help me there?

Thanks in advance,
cheers
Ina

[Sean McKeough]

Keep in mind that the dynamic sql snapshot is an aggrigate of ALL
invocations of a particular statement on the system. Also, it's possible
for statements to be bumped out of the cache if you're running near the
limit of your package cache size (you may not see every sql statement
ever run on the system).

If you want to do analysis for a period of time that ensure you capture
every sql statement, use an event monitor. There are write to table
event monitors that will allow you to have the results written directly
to tables (per node on EEE).

Note that these event monitors are more expensive to run than collection
via snapshot (particularly the statement event monitors, which is what
you want)...so you'd want to have these gather your stats for a period
of time, and then turn off the monitors...

Ina Schmitz wrote:

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 the logical and physical reads from
bufferpool for every statement monitored. Am I right?
But now, I would like to read these results out of a table so that I could
process them automatically. In which table(s) are the results of snapshot
for dynamic sql stored? The values, I am interested in are statement text,
logical and physical reads, cpu time and total execution time.

Would be really great if you had any ideas for me!

Cheers,
Ina

[Ina Schmitz]

Note that these event monitors are more expensive to run than collection
via snapshot (particularly the statement event monitors, which is what you
want)...so you'd want to have these gather your stats for a period of
time, and then turn off the monitors...

Hm... I studied the statement event monitors once again. But I still didn't
find out how to get the LOGICAL AND PHYSICAL READS for a statement with
these event monitors. I don't know where to look for them anymore. Do you
have an idea?

Cheers,
Ina

[Serge Rielau]

Ina Schmitz wrote:

Note that these event monitors are more expensive to run than collection
via snapshot (particularly the statement event monitors, which is what you
want)...so you'd want to have these gather your stats for a period of
time, and then turn off the monitors...

Hm... I studied the statement event monitors once again. But I still didn't
find out how to get the LOGICAL AND PHYSICAL READS for a statement with
these event monitors. I don't know where to look for them anymore. Do you
have an idea?

Cheers,
Ina

Ina are you looking at this table?
http://publib.boulder.ibm.com/infoce...n/r0007595.htm
There is an element ROWS_READ and there are in V8.2 a boatfull of
bufferpool counters disecting whether you got a hit or not for indexes,
data, ...

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[florian.boldt]

Hello Ina,

you can't find logical and physical reads at dynamic statement level,
just the number of rows read from the table, no matter if they were
comming from disk or cache. (my knowledge is prior to 8.2.2, so this
may have changed). Further more there is no relationship between
dynamic sql from the package cache and the application which fired this
sql. The cache is just doing it's job to store prepared and already
parsed statements and plans etc., so there is no need to know which
application triggered the SQL.

If you are looking for logical and physical reads for a statement with
snapshots you need to look at application level:

db2 get snapshot for application agentid xxx
....
Buffer pool data logical reads =
Buffer pool data physical reads =
.....

The disadvantage with snapshots at application level is that you see
the dynamic statement only if it is running while you take the
snapshot. However most of the numbers are cumulated, so if it is in
your hand to start the SQL statement manually you could do a snapshot
for the application before and after the statement is executed and
calculate the delta afterwards.

I will check if event monitors or db2pd could help and let you know,
but I don't think so

cheers
Florian

[Sean McKeough]

We do have pool_data_l_reads and pool_data_p_reads at the statement level...

All the documentation is in the system monitor guide and
reference...there's an appending of 'logical data groupings' that'll
show you what info you get for a statement event.

Ina Schmitz wrote:

Note that these event monitors are more expensive to run than collection
via snapshot (particularly the statement event monitors, which is what you
want)...so you'd want to have these gather your stats for a period of
time, and then turn off the monitors...

Hm... I studied the statement event monitors once again. But I still didn't
find out how to get the LOGICAL AND PHYSICAL READS for a statement with
these event monitors. I don't know where to look for them anymore. Do you
have an idea?

Cheers,
Ina

[Ian]

Serge Rielau wrote:

RdR wrote:

Hi,

We are currently doing some designs for Sarbanes-Oxley compliant auditing
applications that needs to capture SQL statements, I know it is not
exactly
the same but similar, hopefully these ideas will prompt you to get more
ideas, these are two ways we have tried so far:
1) Use Data Propagator to capture from the logs, inserts, updates, and
deletes but modify the apply job to turn everything to inserts instead of
the normal updates which mimicks the original statement. The drawback of
this solution is that we wil not detect selects from the database. One
big advantage on this is we can get the row information (what changed
before and after images) plus other journal information like who made
the change, when the change was made and what type of change it was
(UP/UB, DL, PT).
2) To write an application that will stage the results of the db2 get
snapshot command and have a reader (an ETL tool) convert them to
inserts to a table. Once the above is done, have something like Crystal
Reports to read them and format it to a report.

3) Query patroller (?)

4) db2audit (?)

[Ina Schmitz]

Hello Florian,

thanks a lot for your help.

db2 get snapshot for application agentid xxx

I was developping a similar solution - being confused now, which logical
read value is the correct one...

For getting the physical and logical reads for a sql statement, I sent my
sql statement to the database via JDBC and then the following statement:
SELECT pool_data_l_reads, pool_data_p_reads from
table(snapshot_bp('mydatabase',-1)) as test;"
With this, I hope to get the sum of all logical and physical reads after the
execution of my sql statement.
Now, I execute the sql statement again, and again I execute the statement to
select the pool_data_l_reads and ..._r_reads.
Now I build the difference between these values and have the logical and
physical reads for the last execution of my sql statement.
Am I right till now?

Not having thought that this could also cause a problem, I now tried to get
the total execution time for the statement ("db2 get snapshot for dynamic
sql on mydatabase" unfortunately doesn't help me, because I do a series of
executions of the same sql statement and have to do some calculations on the
result).
Thatfore, I created the following event monitor:
"create event monitor test_monitor

for statements

write to table

STMT (table db2admin.test_monitor)

manualstart"
I then started this monitor, executed my queries and then executed "select *
from test_monitor".
BUT: Now I get 3 rows for each of my sql statements. In two of these three
rows, system_cpu_time and user_cpu_time are 0. So for calculating the
total_cpu_time, I just need the third row generated. But for getting the
total execution time of the statement, I have the following problem: the
start_time and stop_time differ also in these rows with system_ and
user_cpu_time=0. So I have do calculate the sum of all these three
differences to get the total execution time?
Am I right with this idea? Or is there a more simple idea to get the results
I need?

Would be great if you could help me there!

Cheers,
Ina

[Pierre Saint-Jacques]

See below in the note.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
<fl***********@itgain.de> a écrit dans le message de news:
11*********************@o13g2000cwo.googlegroups.c om...

Hello Ina,

you can't find logical and physical reads at dynamic statement level,
just the number of rows read from the table, no matter if they were
comming from disk or cache. (my knowledge is prior to 8.2.2, so this
may have changed). Further more there is no relationship between
dynamic sql from the package cache and the application which fired this
sql. The cache is just doing it's job to store prepared and already
parsed statements and plans etc., so there is no need to know which
application triggered the SQL.

If you are looking for logical and physical reads for a statement with
snapshots you need to look at application level:

db2 get snapshot for application agentid xxx
...
Buffer pool data logical reads =
Buffer pool data physical reads =
....

The disadvantage with snapshots at application level is that you see
the dynamic statement only if it is running while you take the
snapshot. However most of the numbers are cumulated, so if it is in
your hand to start the SQL statement manually you could do a snapshot
for the application before and after the statement is executed and
calculate the delta afterwards. ####### It would be easier to issue a:
RESET MONITOR ALL FOR DATABASE database-alias
This brings the counters to zero.
Then start the app and then get snapshot after end of app.

If you absolutely neeed to see the stmts. then run this on your test system
without anybody else on the box. Sizr the package cache large enough to
hold the stmts.
and get your snapshots at the end.é I still also think you will not be able
to correlate the values to each stements.
HTH, Pierre.


I will check if event monitors or db2pd could help and let you know,
but I don't think so

cheers
Florian