What happend to DB2_RR_TO_RS in V8

What happend to DB2_RR_TO_RS in V8

Post by Onno Ceele » Fri, 27 Jun 2003 22:23:48



Hi,

I read somewhere that DB2_RR_TO_RS is a pre-V8 environmental variable. But
what happend with it in DB2 UDB V8.1? Has the default behavior changed, or
are there other methods introduced to prevent extra locking?

Thanks,

Onno Ceelen

 
 
 

What happend to DB2_RR_TO_RS in V8

Post by Larry Menar » Fri, 27 Jun 2003 22:42:01


   I believe the Type 2 indexes in V8 deprecates the DB2_RR_TO_RS.

--
Larry Menard
IBM Workstation Database (DB2) Performance Team
Defender of Geese and of All Things Natural


Quote:> Hi,

> I read somewhere that DB2_RR_TO_RS is a pre-V8 environmental variable. But
> what happend with it in DB2 UDB V8.1? Has the default behavior changed, or
> are there other methods introduced to prevent extra locking?

> Thanks,

> Onno Ceelen


 
 
 

1. DB2_RR_TO_RS

According to the DB2 Administration Guide:

"The DB2_RR_TO_RS profile registry variable can be used to prevent
Repeatable Read (RR) isolation level access to user tables. This
registry value can be set to "YES" using db2set in environments where
RR isolation semantics are not required. Before taking effect, you
must stop and start the database. Following the db2start, this change
affects the entire instance. Once set, if a request to access a user
table using RR is received, the request is modified internally to use
the Read Stability (RS) isolation level instead. No warning is given
when this occurs."

At one point in time we used this setting to successfully eliminate a
deadlock problem in our UDB for AIX application.  We changed that
setting many months ago.  We observed immediate positive effects after
changing this variable.

Since than we have applied multiple fix packs and it now appears that
the setting is not providing the advertised effect.  We are currently
running UDB EEE v7.1.0.75 on AIX.

Here's what I've done to prove the point.

Application 1:
change isolation to RR;
connect to db;
select count(*) from a;

At this point a share (S) lock is obtained by application 1 on table
a.  What is expected for repeatable read.

Application 2:
change isolation to RR;
connect to db;
insert into a ...;

At this point an intent exclusive lock (IX) on table a is requested by
application 2.  This places application a in a lock wait state and the
transaction ultimately times out.  Again, expected behavior for
repeatable read, but not what I'd expect with DB2_RR_TO_RS=YES.

Performing the same exercise with both applications using read
stability or cursor stability does not result in a lock wait and both
statements are executed concurrently.  Expected behavior for the lower
levels of isolation.

I've have tried this test with DB2_RR_TO_RS set to YES, ON and NO
(I've seen the variable documented multiple ways).  Yes, I've
restarted the instance between each test.  Each time the results are
identical.  Unless I've grossly misinterpreted the DB2_RR_TO_RS
variable, a PMR with IBM may be in order.

Any thoughts or insights appreciated.  If anyone would like to comment
on the theory as to why not allowing RR isolation level in an
environment improves the concurrency of applications running RS or CS,
I would appreciate your thoughts.  As I've worked through this
problem, I've become more confused as to the intended impact of this
variable on non-RR applications.

2. 11.0.3 <=> 11.9.2

3. DB2_RR_TO_RS=YES in UDB 7.2fixpak-5

4. CL/I servers for other systems?

5. Migrating from v7.3.4 to v8.0 or v8.1 series

6. 19399-NJ-Middletown-Solaris-C-C++-ORACLE-PL/SQL-Sun Workstation-Software Developer

7. container class with scroll bars wanted. Possible?

8. Progress V8 developer available -- 8 months V8 experience

9. V8.2B versus V8.1C concerning enabling

10. Warning when upgrading v8.0 -> v8.1

11. V8.2A to V8.2B

12. What happend to MSQUERY