473,241 Members | 1,454 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,241 software developers and data experts.

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.

Dec 21 '05 #1
2 8065
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
Dec 22 '05 #2
"Serge Rielau" (news:40*************@individual.net...) wrote:
Which version of DB2 are you on?
Sorry, I've missed this from my 1st post.
We use DB2 EE for Windows 8.1.6 ( 8.1 + FP6a_WR21346_ESE.exe )
In DB2 V8.2 (aka V8.1 FP7) you can use the SET_ROUTINE_OPTS() procedure
Indeed, there is no such procedure in my version of DB2.
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...
Yes !!! This is exactly what we need. We just need to upgrade.
Thank you very much.
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"
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 ?
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.


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.
Dec 22 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Leaf | last post by:
Greetings, I've been reading with interest the threads here on deadlocking, as I'm finding my formerly happy app in a production environment suddenly deadlocking left and right. It started...
12
by: John Sidney-Woollett | last post by:
I have to convert an java web application currently using an Oracle DB back end to one using a Postgres backend. In Oracle much of the application logic is abstracted away from the java...
3
by: Mike P | last post by:
I am using transactions on my website and the Isolation Level is ReadCommitted. Since the website has a lot of traffic this may be causing it to lock up every now and again. Can somebody tell...
15
by: Zeng | last post by:
Hi, The bigger my C# web-application gets, the more places I need to put in the tedious retrying block of code to make sure operations that can run into database deadlocks are re-run (retried)...
3
by: Florian G. Pflug | last post by:
Hi I'd like to know if there is a way to specify different transaction isolation levels for different tables in the db. The reason i'm asking this (rather bizarre sounding, i know ;-) ) question...
6
by: Crash | last post by:
C# VS 2003 ..Net Framework V1.1 SP1 SQL Server 2000 SP3 Enterprise Library June 2005 I'm working with some code {not of my creation} that performs the following sequence of actions: - Open...
3
by: joshsackett | last post by:
I am redesigning an application that distributes heldesk tickets to our 50 engineers automatically. When the engineer logs into their window a stored procedure executes that searches through all...
2
by: Christian Stooker | last post by:
Part one: ====== Hi ! I want to use SQLite database like the FireBird database: with big isolation level. What's that meaning ? I have an application that periodically check some input...
1
by: Mark | last post by:
Hello, I'm using the following code implementing transactions: Using trans1 As New Transactions.TransactionScope 'Data manipulations here! End using How do I change the transaction...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.