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

V8.2 - How to stop SPs always being explained?

P: n/a
Hi,

Having moved our database to V8.2, it now does the following...

Everytime a particular Stored Procedure is CALL'd, the explain tables
are populated ? Clearly, we would like to stop this behaviour - it is
generating GBs of data and hitting performance significantly.

Previously, we used the registry variable DB2_SQLROUTINE_PREPOPTS
which controlled whether at build time explains were populated. Is
this still
relevant with 8.2 now not using a Compiler etc ..... ?

Is there another mechanism/switch in 8.2 to stop this ?

NB. The SP makes extensive use of GTT's.

Thanks.

Paul.
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
OK,

After reading the manual a little more closely.

EXPLSNAP ALL -

"An Explain Snapshot for each eligible static SQL statement will be
placed in the Explain tables at prep/bind time. Explain snapshot
information for each eligible incremental bind SQL statement will be
placed in the Explain tables at run time. In addition, explain
snapshot information will be gathered for eligible dynamic SQL
statements at run time, even if the CURRENT EXPLAIN SNAPSHOT register
is set to NO."

I Guess GTTs = incremental bind = explain on each call, as I was
seeing.

Maybe one to watch out for.

Paul
Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.