By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,854 Members | 849 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,854 IT Pros & Developers. It's quick & easy.

capturing optimization level used for certain sql

P: n/a
hello,

where can i see what optimization level a certain sql has used ?
is there any snapshot, event monitor, query patroller information
which can be used to see that ?

regards,
db2admin
Oct 30 '08 #1
Share this Question
Share on Google+
7 Replies


P: n/a
you can use
db2pd -db DBNAME -dynamic -full

In the section "Dynamic SQL Environments:" is the second last col the
optimization level.

Use anchorID and StmtUID to join the informations of the (three)
sections.
On Oct 30, 3:42*pm, db2admin <jag...@gmail.comwrote:
hello,

where can i see what optimization level a certain sql has used ?
is there any snapshot, event monitor, query patroller information
which can be used to see that ?

regards,
db2admin
Oct 31 '08 #2

P: n/a
On Oct 31, 10:11*am, "stefan.albert" <stefan.alb...@spb.dewrote:
you can use
db2pd -db DBNAME -dynamic -full

In the section "Dynamic SQL Environments:" is the second last col the
optimization level.

Use anchorID and StmtUID to join the informations of the (three)
sections.

On Oct 30, 3:42*pm, db2admin <jag...@gmail.comwrote:
hello,
where can i see what optimization level a certain sql has used ?
is there any snapshot, event monitor, query patroller information
which can be used to see that ?
regards,
db2admin
thankyou
i am wondering if all sqls get recorded in the output of this db2pd
command. i asked my developer to run some application which use some
SQLs using JDBC and after i ran this db2pd command. i was not able to
see any of the SQL used by the application my developer ran.
am i missing something ?
Oct 31 '08 #3

P: n/a
db2pd is an evolved snapshot command. It gives you what is there at
that time.
If the statement is gone and executed db2pd may not report on it if
it is not in the cache anymore.

If you need to catch only some statements you can read into the DB2
Info. Center about event monitors.
You can set one for stements and apply either applid or userid for
filtering. Once the event monitor is started it will catch what you
want and you can then analyze the output for your info.

Regards, Pierre.
On Oct 31, 2:00*pm, db2admin <jag...@gmail.comwrote:
On Oct 31, 10:11*am, "stefan.albert" <stefan.alb...@spb.dewrote:
you can use
db2pd -db DBNAME -dynamic -full
In the section "Dynamic SQL Environments:" is the second last col the
optimization level.
Use anchorID and StmtUID to join the informations of the (three)
sections.
On Oct 30, 3:42*pm, db2admin <jag...@gmail.comwrote:
hello,
where can i see what optimization level a certain sql has used ?
is there any snapshot, event monitor, query patroller information
which can be used to see that ?
regards,
db2admin

thankyou
i am wondering if all sqls get recorded in the output of this db2pd
command. i asked my developer to run some application which use some
SQLs using JDBC and after i ran this db2pd command. i was not able to
see any of the SQL used by the application my developer ran.
am i missing something ?
Nov 2 '08 #4

P: n/a
On Nov 2, 5:51*pm, Pierre StJ <p.stjacq...@videotron.cawrote:
db2pd is an evolved snapshot command. It gives you what is there at
that time.
If the statement is gone and executed db2pd may not report on it *if
it is not in the cache anymore.

If you need to catch only some statements you can read into the DB2
Info. Center about event monitors.
You can set one for stements and apply either applid or userid for
filtering. Once the event monitor is started it will catch what you
want and you can then analyze the output for your info.

Regards, Pierre.
On Oct 31, 2:00*pm, db2admin <jag...@gmail.comwrote:
On Oct 31, 10:11*am, "stefan.albert" <stefan.alb...@spb.dewrote:
you can use
db2pd -db DBNAME -dynamic -full
In the section "Dynamic SQL Environments:" is the second last col the
optimization level.
Use anchorID and StmtUID to join the informations of the (three)
sections.
On Oct 30, 3:42*pm, db2admin <jag...@gmail.comwrote:
hello,
where can i see what optimization level a certain sql has used ?
is there any snapshot, event monitor, query patroller information
which can be used to see that ?
regards,
db2admin
thankyou
i am wondering if all sqls get recorded in the output of this db2pd
command. i asked my developer to run some application which use some
SQLs using JDBC and after i ran this db2pd command. i was not able to
see any of the SQL used by the application my developer ran.
am i missing something ?
thankyou Pierre,

I thought about even monitor on statement but did not try yet because
i was in doubt if this will capture optimization level information
I guess i will try and find out
Nov 4 '08 #5

P: n/a
This information is not in the data captured with event monitor for
statement.
Also the snapshot does not give this information.
db2pd seems to be the only way...
>
I thought about even monitor on statement but did not try yet because
i was in doubt if this will capture optimization level information
I guess i will try and find out- Hide quoted text -
Nov 5 '08 #6

P: n/a
On 5 Nov, 13:18, "stefan.albert" <stefan.alb...@spb.dewrote:
This information is not in the data captured with event monitor for
statement.
Also the snapshot does not give this information.
db2pd seems to be the only way...
I thought about even monitor on statement but did not try yet because
i was in doubt if this will capture optimization level information
I guess i will try and find out- Hide quoted text -
Which DB2 release are you using?
Another way to capture and store optimization level used by queries is
the db2 audit feature; I can ensure you that on db2 v9.5 this
information is reported.
To know how to set db2 v9.5 audit , see my post on another forum:

http://www-128.ibm.com/developerwork...8701&#14158701

Regards
Salvatore Vacca
Nov 5 '08 #7

P: n/a
On 5 Nov, 13:18, "stefan.albert" <stefan.alb...@spb.dewrote:
This information is not in the data captured with event monitor for
statement.
Also the snapshot does not give this information.
db2pd seems to be the only way...
I thought about even monitor on statement but did not try yet because
i was in doubt if this will capture optimization level information
I guess i will try and find out- Hide quoted text -

Which DB2 release are you using?
Another way to capture and store optimization level used by queries is
the db2 audit feature; I can ensure you that on db2 v9.5 this
information is reported.
To know how to set db2 v9.5 audit , see my post on another forum:

http://www-128.ibm.com/developerwork...8701&#14158701

Regards
Salvatore Vacca
Nov 5 '08 #8

This discussion thread is closed

Replies have been disabled for this discussion.