capturing optimization level used for certain sql

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

re: capturing optimization level used for certain sql

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:

re: capturing optimization level used for certain sql

On Oct 31, 10:11*am, "stefan.albert" <stefan.alb...@spb.dewrote: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 ?

re: capturing optimization level used for certain sql

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:

re: capturing optimization level used for certain sql

On Nov 2, 5:51*pm, Pierre StJ <p.stjacq...@videotron.cawrote: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

re: capturing optimization level used for certain sql

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...

re: capturing optimization level used for certain sql

On 5 Nov, 13:18, "stefan.albert" <stefan.alb...@spb.dewrote: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

re: capturing optimization level used for certain sql

On 5 Nov, 13:18, "stefan.albert" <stefan.alb...@spb.dewrote:
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