Setting queryopt on a per stored proc basis

[APAPF]

How can I set the QUERYOPT value for only a few stored procedures? I have a
large .sql file that is loaded using db2 -td@ -vf file.sql... however I want
to be able to set the QUERYOPT setting to different valus for different
stored procs in that file.. is this possible?

Thanks

[Eric Herber]

APAPF wrote:

How can I set the QUERYOPT value for only a few stored procedures? I have
a large .sql file that is loaded using db2 -td@ -vf file.sql... however I
want to be able to set the QUERYOPT setting to different valus for
different stored procs in that file.. is this possible?

Thanks

I don't think that you can do that because QUERYOPT for SQL
stored procedures is specified thru the DB2_SQLROUTINE_PREPOPTS
registry variable.

I would suggest that you split your .sql file into several files,
grouping the stored procedures which should have the same QUERYOPT
setting together. Then you would be able to set DB2_SQLROUTINE_PREPOPTS
before calling the individual .sql files thru the db2 CLP.
Restarting the databaseserver (db2stop/db2start) as suggested in the
DB2 manuals should not be necessary. At least in db2 V8.1.3 on Linux
I was able to set DB2_SQLROUTINE_PREPOPTS without restarting the
databaseserver.

Splitting the files should be fairly easy using an awk or perl
script.

Best regards

Eric
--
IT-Consulting Herber
***********************************************
Download the IFMX Database-Monitor for free at:
http://www.herber-consulting.de/BusyBee
***********************************************