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

dynamic sql snapshot vs static sql snapshot

P: n/a
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 understanding is it
does not display static sql statements. Is a statement event monitor
the only way to capture the same information for static sql in
packages?

Lew

Sep 11 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
<se*****@yahoo.comwrote in message
news:11**********************@d34g2000cwd.googlegr oups.com...
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 understanding is it
does not display static sql statements. Is a statement event monitor
the only way to capture the same information for static sql in
packages?

Lew
REBIND will re-explain packages that were created with the explsnap bind
option set to YES or ALL (indicated in the EXPLAIN_SNAPSHOT column in the
SYSCAT.PACKAGES catalog table entry for the package) or with the explain
bind option set to YES or ALL (indicated in the EXPLAIN_MODE column in the
SYSCAT.PACKAGES catalog table entry for the package). The Explain tables
used are those of the REBIND requester, not the original binder.

If the package was not bound with explsnap YES or ALL, you can do a BIND
REPLACE.

Then you can use the db2exfmt command to format reports from the explain
tables.
Sep 11 '06 #2

P: n/a
Have you tried using the Activity monitor.
I t has capabilities to show you appls. and stmts with the metrics you
mention. To my knowledge, that report does not differentiate between static
or dynamic sql.
It shows most cpu used and tec.. for all.
HTH, Pierre.
PS: I don't remember if there's an equivalent command line to that tool.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
<se*****@yahoo.coma écrit dans le message de news:
11**********************@d34g2000cwd.googlegroups. com...
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 understanding is it
does not display static sql statements. Is a statement event monitor
the only way to capture the same information for static sql in
packages?

Lew
Sep 11 '06 #3

P: n/a
Hi Pierre,

It looks like the Activity monitor is going against the dynamic sql
cache so I don't believe it will have package statements. I guess I'll
have to set up a statement event monitor which traps both types of sql
statements.

Thanks.

Lew
Pierre Saint-Jacques wrote:
Have you tried using the Activity monitor.
I t has capabilities to show you appls. and stmts with the metrics you
mention. To my knowledge, that report does not differentiate between static
or dynamic sql.
It shows most cpu used and tec.. for all.
HTH, Pierre.
PS: I don't remember if there's an equivalent command line to that tool.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
<se*****@yahoo.coma écrit dans le message de news:
11**********************@d34g2000cwd.googlegroups. com...
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 understanding is it
does not display static sql statements. Is a statement event monitor
the only way to capture the same information for static sql in
packages?

Lew
Sep 12 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.