Transaction isolation level for stored procedures.

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.

re: Transaction isolation level for stored procedures.

kanda wrote:[color=blue]
> 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.
>[/color]
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

re: Transaction isolation level for stored procedures.

"Serge Rielau" (news:40ua1bF1b2bdeU1@individual.net...) wrote:
[color=blue]
> Which version of DB2 are you on?[/color]

Sorry, I've missed this from my 1st post.
We use DB2 EE for Windows 8.1.6 ( 8.1 + FP6a_WR21346_ESE.exe )
[color=blue]
> In DB2 V8.2 (aka V8.1 FP7) you can use the SET_ROUTINE_OPTS() procedure[/color]

Indeed, there is no such procedure in my version of DB2.
[color=blue]
> 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...[/color]

Yes !!! This is exactly what we need. We just need to upgrade.
Thank you very much.
[color=blue]
> 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"[/color]

Sure, If this is raw dynamic statement. It does *not* work in
the 2 significant cases:

1) If I use "WITH <level>" in a stored procedure, then specified
<level> is ignored, and an isolation level of procedure is applied.

2) Consider example:

| BEGIN ATOMIC
| IF exists( select 1 from ... WITH RR)
| .......

This example cause "syntax error" message from DB2.
Is this case the bug or the lack of functionality ?
[color=blue]
> 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.[/color]
[color=blue]
> 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.[/color]

I agree, it could be not desirable to let invoker change the isolation level.

Could you be a bit more specific and tell me, what is "teh" stored
procedure ?
--
Konstantin Andreev.