kanda wrote:
Hello.
I am developing the application (VBA&ODBC, to be exact) which
periodically calls the stored procedures in the IBM DB2. A few of the
procedures require executing with isolation level RR ( ANSI
"SERIALIZABLE" ), not the default; default is CS (ANSI "Read
Committed")). The procedure language is SQL.
According to the documentation, I can adjust procedure *run*-time
isolation level by setting *compile*-time dataserver-wide option
DB2_SQLROUTINE_PREPOPTS="ISOLATION RR" and by restarting server.
But development process is not unitary code writing and compiling. I
have to design/code/compile/test/ the procedures multitude times,
before the code stabilize. The isolation levels of these procedures are
going every which way.
Consequently I have to restart dataserver almost every time I changed a
line of the SQL code, haven't I ? 6-10 restarts per hour... This is
nightmare...
Besides me there are other developers using this dataserver. How should
we "serialize" our compilations ? I imagine the schedule for
compilation of the SQL stored procedures:
- 10:00-11:00 - "ISOLATION RR"
- 11:00-12:00 - "ISOLATION CS"
... Sure, It's incomparable degree of parallelism of work !
I have some practical questions:
1) Is there a way to develop the SQL stored procedures with different
transaction isolation levels, but without continuous dataserver
restarting ?
2) Is there a way to deploy these procedures at the production
dataserver without interrupting the service ?
I have some academic questions:
1) Why the stored procedure does not obey to the transaction isolation
level of the calling unit of work ?
2) Why can't I adjust this level after procedure creation ?
3) Why this level is adjusted dataserver-wide ?
--
Thank you in advance, Konstantin Andreev.
Which version of DB2 are you on?
In DB2 V8.2 (aka V8.1 FP7) you can use the SET_ROUTINE_OPTS() procedure
to change the bind options at the session level:
http://publib.boulder.ibm.com/infoce...n/r0011873.htm
This answers most of the restart questions I presume...
If you want to change the isolation for a specific statement you can use
the isolation level clause of SQL statements such as:
"SELECT * FROM SYSCAT.TABLES WITH UR"
If you want statements inside of a proecdure to obey the isolation level
of the current isolation level you need to use dynamic SQL inside teh
SQL Procedure.
A change of isolation level affects teh semantics of teh stored
procedure, therefore, typically, it is not in the interest of the
definer of the procedure to let the invoker change this behaviour.
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab
