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

[Paul Reddin]

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.

[Paul Reddin]

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