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]

Re: get snapshot for dynamic sql

Ina Schmitz wrote:[color=blue]
> 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
>
>[/color]

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

http://publib.boulder.ibm.com/infocenter/db2help/index.jsp

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


Jan M. Nelken

[Ina Schmitz]

Re: get snapshot for dynamic sql

[...][color=blue][color=green]
>> 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.[/color][/color]
[color=blue]
> Please tell us what search arguments you used if you cannot find answer
> you are looking for.[/color]
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_SQ L doesn't give me what I
need.Perhaps the pool_index_p_re ads 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]

Re: get snapshot for dynamic sql

Ina Schmitz wrote:
[color=blue]
> Would be great if you could help me in finding better search arguments...[/color]

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/infocenter/db2help/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]

Re: get snapshot for dynamic sql

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" <web@inalein.ne t> wrote in message
news:d5t5h5$orq $04$1@news.t-online.com...[color=blue]
> 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
>
>[/color]

[Serge Rielau]

Re: get snapshot for dynamic sql

RdR wrote:[color=blue]
> 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.[/color]
3) Query patroller (?)

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

[Ina Schmitz]

Re: get snapshot for dynamic sql

Hello,

thanks for your answer. But unfortunaly, I think that my problem has not
become clear. I try to explain...
[color=blue]
> "...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..."[/color]
[...][color=blue]
> 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?[/color]
Yes, I executed:
"select * from table(snapshot_ dyn_sql('mydata base',-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]

Re: get snapshot for dynamic sql

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:[color=blue]
> 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
>
>[/color]

[Ina Schmitz]

Re: get snapshot for dynamic sql

[color=blue]
> 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...[/color]
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]

Re: get snapshot for dynamic sql

Ina Schmitz wrote:[color=blue][color=green]
>>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...[/color]
>
> 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
>
>[/color]
Ina are you looking at this table?

http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/admin/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@itgain.de]

Re: get snapshot for dynamic sql

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]

Re: get snapshot for dynamic sql

We do have pool_data_l_rea ds and pool_data_p_rea ds at the statement level...

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

Ina Schmitz wrote:[color=blue][color=green]
>>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...[/color]
>
> 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
>
>[/color]

[Ian]

Re: get snapshot for dynamic sql

Serge Rielau wrote:[color=blue]
> RdR wrote:
>[color=green]
>> 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.[/color]
>
> 3) Query patroller (?)[/color]

4) db2audit (?)

[Ina Schmitz]

Re: get snapshot for dynamic sql

Hello Florian,

thanks a lot for your help.
[color=blue]
> db2 get snapshot for application agentid xxx[/color]
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_rea ds, pool_data_p_rea ds 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_rea ds 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_m onitor)

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]

Re: get snapshot for dynamic sql

See below in the note.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
<florian.boldt@ itgain.de> a écrit dans le message de news:
1115980283.5803 55.62390@o13g20 00...legro ups.com...[color=blue]
> 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.[/color]
####### 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.

[color=blue]
>
> I will check if event monitors or db2pd could help and let you know,
> but I don't think so
>
> cheers
> Florian
>[/color]