New Registry Variables in 8.2.2 (FP9)

[Mark A]

Here is the description of a new feature in 8.2.2 (FP9) from the InfoCenter:

"DB2_SKIPINSERTED registry variable

You can use the DB2_SKIPINSERTED registry variable to skip uncommitted
inserted rows for Cursor Stability (CS) and Read Stability (RS) isolation
levels.

The registry variables DB2_SKIPDELETED and DB2_EVALUNCOMMITTED are used to
skip uncommitted deletions and uncommitted updates. Otherwise, CS and RS
isolation levels require the processing of committed data only.

If you decide that you can skip any row that is locked because it is an
uncommitted inserted row, you can now turn the DB2_SKIPINSERTED registry
variable on to allow you to skip those rows. Having this registry variable
on produces greater concurrency and would therefore be the preferred choice
for most applications.

There are cases where skipping uncommitted inserts may not be preferred. For
example:

- When two applications use a table to pass data between them.
- When an application does not use UPDATE statements, but instead deletes 9
the old data and then inserts the new data."
[end quote]

Questions:

1. The description of the enhancement seems to suggest that DB2 would lock
wait on uncommitted inserted rows prior to introduction of DB2_SKIPINSERTED
registry variable (prior to FP9). It also seems to suggest that by default
in FP9, DB2 will lock wait on the row unless the registry variable is set to
ON. Is this correct?

2. The text seems to suggest that setting DB2_EVALUNCOMMITTED = ON will
"skip" uncommitted updates with CS or RS isolation level. Is that correct?
It sounds backwards from the name of the registry value (EVALUNCOMMITTED).

3. It is not clear, but I assume the default for all the new registry
variables is OFF?

[Serge Rielau]

Mark A wrote:

Here is the description of a new feature in 8.2.2 (FP9) from the InfoCenter:

"DB2_SKIPINSERTED registry variable

You can use the DB2_SKIPINSERTED registry variable to skip uncommitted
inserted rows for Cursor Stability (CS) and Read Stability (RS) isolation
levels.

The registry variables DB2_SKIPDELETED and DB2_EVALUNCOMMITTED are used to
skip uncommitted deletions and uncommitted updates. Otherwise, CS and RS
isolation levels require the processing of committed data only.

If you decide that you can skip any row that is locked because it is an
uncommitted inserted row, you can now turn the DB2_SKIPINSERTED registry
variable on to allow you to skip those rows. Having this registry variable
on produces greater concurrency and would therefore be the preferred choice
for most applications.

There are cases where skipping uncommitted inserts may not be preferred. For
example:

- When two applications use a table to pass data between them.
- When an application does not use UPDATE statements, but instead deletes 9
the old data and then inserts the new data."
[end quote]

Questions:

1. The description of the enhancement seems to suggest that DB2 would lock
wait on uncommitted inserted rows prior to introduction of DB2_SKIPINSERTED
registry variable (prior to FP9). It also seems to suggest that by default
in FP9, DB2 will lock wait on the row unless the registry variable is set to
ON. Is this correct? Yes that is correct. The idea is that the default befaviour does not
change when you apply a fixpack. The default behaviour may or may not
change when you migrate to a new version.
2. The text seems to suggest that setting DB2_EVALUNCOMMITTED = ON will
"skip" uncommitted updates with CS or RS isolation level. Is that correct?
It sounds backwards from the name of the registry value (EVALUNCOMMITTED). Actually it will do exactly what it claims says.
Example:

Session 1: UPDATE T SET x = 5 WHERE pk = 5
Session 2: SELECT * FROM T WHERE x < 0
Session 2 will evaluate the row with pk = 5. Since it will fail the
predicate x < 0 the scan will move on (gambling that Session 1 will
commit). If the predicate were x > 0 then the scan would wait.
3. It is not clear, but I assume the default for all the new registry
variables is OFF?

Yes.
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Mark A]

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:3e************@individual.net...

Questions:

1. The description of the enhancement seems to suggest that DB2 would
lock wait on uncommitted inserted rows prior to introduction of
DB2_SKIPINSERTED registry variable (prior to FP9). It also seems to
suggest that by default in FP9, DB2 will lock wait on the row unless the
registry variable is set to ON. Is this correct?

Yes that is correct. The idea is that the default befaviour does not
change when you apply a fixpack. The default behaviour may or may not
change when you migrate to a new version.

On the first question, in FP8 and prior (BEFORE 8.2.2), would application 2
wait on locks of newly inserted rows by application 1? I thought application
2 would skip rows inserted by application 1 unless they were committed, but
it sounds like that unless the DB2_SKIPINSERTED registry is set on, it will
lock wait on them.
2. The text seems to suggest that setting DB2_EVALUNCOMMITTED = ON will
"skip" uncommitted updates with CS or RS isolation level. Is that
correct? It sounds backwards from the name of the registry value
(EVALUNCOMMITTED).

Actually it will do exactly what it claims says.
Example:

Session 1: UPDATE T SET x = 5 WHERE pk = 5
Session 2: SELECT * FROM T WHERE x < 0
Session 2 will evaluate the row with pk = 5. Since it will fail the
predicate x < 0 the scan will move on (gambling that Session 1 will
commit). If the predicate were x > 0 then the scan would wait.

Then why does the doc say: "The registry variables DB2_SKIPDELETED and
DB2_EVALUNCOMMITTED are used to skip uncommitted deletions and uncommitted
updates."

In my understanding of English, it means that DB2_EVALUNCOMMITTED will
"skip" uncommitted updates.

[Serge Rielau]

Mark A wrote:

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:3e************@individual.net...

Questions:

1. The description of the enhancement seems to suggest that DB2 would
lock wait on uncommitted inserted rows prior to introduction of
DB2_SKIPINSERTED registry variable (prior to FP9). It also seems to
suggest that by default in FP9, DB2 will lock wait on the row unless the
registry variable is set to ON. Is this correct?

Yes that is correct. The idea is that the default befaviour does not
change when you apply a fixpack. The default behaviour may or may not
change when you migrate to a new version.

On the first question, in FP8 and prior (BEFORE 8.2.2), would application 2
wait on locks of newly inserted rows by application 1? I thought application
2 would skip rows inserted by application 1 unless they were committed, but
it sounds like that unless the DB2_SKIPINSERTED registry is set on, it will
lock wait on them.

2. The text seems to suggest that setting DB2_EVALUNCOMMITTED = ON will
"skip" uncommitted updates with CS or RS isolation level. Is that
correct? It sounds backwards from the name of the registry value
(EVALUNCOMMITTED).

Actually it will do exactly what it claims says.
Example:

Session 1: UPDATE T SET x = 5 WHERE pk = 5
Session 2: SELECT * FROM T WHERE x < 0
Session 2 will evaluate the row with pk = 5. Since it will fail the
predicate x < 0 the scan will move on (gambling that Session 1 will
commit). If the predicate were x > 0 then the scan would wait.

Then why does the doc say: "The registry variables DB2_SKIPDELETED and
DB2_EVALUNCOMMITTED are used to skip uncommitted deletions and uncommitted
updates."

In my understanding of English, it means that DB2_EVALUNCOMMITTED will
"skip" uncommitted updates.

The beauty of "it" - my bad. I meant it's doing exactly what the
registers _name_ says. Let me clarify the text:
"The registry variables DB2_SKIPDELETED and DB2_EVALUNCOMMITTED are used
to skip uncommitted deletions and uncommitted updates
_that_do_no_qualify_the_predicate_."
Predicate being the predicate that was pushed e.g. into the table scan.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Mark A]

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:3e************@individual.net...

The beauty of "it" - my bad. I meant it's doing exactly what the registers
_name_ says. Let me clarify the text:
"The registry variables DB2_SKIPDELETED and DB2_EVALUNCOMMITTED are used
to skip uncommitted deletions and uncommitted updates
_that_do_no_qualify_the_predicate_."
Predicate being the predicate that was pushed e.g. into the table scan.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

OK, so setting registry value DB2_EVALUNCOMMITTED = ON will resolve the
problem discussed a few days ago, where there was contention between two
applications, each updating different rows on a table (but each not
committing right away). Because the table was small, a tablespace scan was
always used, and there was a lock wait even though the rows locked by the
first application update did not qualify the predicate of the second
application. The solution was to force the index to be used by setting the
table to volatile and avoiding the index scan Correct?

Actually, the DB2_EVALUNCOMMITTED and DB2_SKIPDELETED have been around at
least since fixpak 7 (documented in the 8.2 Manuals) and maybe before (but
not documented in 8.1 manuals). I recall talking to IBM support about
DB2_EVALUNCOMMITTED six months ago, but could not find it documented, so my
guess is it was introduced in FP4..

Only DB2_SKIPINSERTED is new for 8.2.2, despite the misleading discussion in
the What's New Section of InfoCenter for 8.2.2.

[Fan Ruo Xin]

Serge,
I am waiting for this for a long time. Also with "set lock timeout in the
current session", instead of using db parameter, and "Re-Opt" feature which
come with Version8.1.
But so far, the DB2 applications I supposrt still can not be benefited from
these new features. The reason is there is no corresponding Java APIs. I do
believe, most of the database servers most DBAs support communicate with
applications, application servers or web application servers which are java
applications.
I do suggest IBM could make it part of the sql statement, like statement
isolation level idea (select ... from ... WITH UR/CS/RS/RR), besides
registry variables or db properties. Do you know which channel I could use
to let IBM know this request???
Thanks,
FRX

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:3e************@individual.net...

Mark A wrote:

Here is the description of a new feature in 8.2.2 (FP9) from the InfoCenter:
"DB2_SKIPINSERTED registry variable

You can use the DB2_SKIPINSERTED registry variable to skip uncommitted
inserted rows for Cursor Stability (CS) and Read Stability (RS) isolation levels.

The registry variables DB2_SKIPDELETED and DB2_EVALUNCOMMITTED are used to skip uncommitted deletions and uncommitted updates. Otherwise, CS and RS
isolation levels require the processing of committed data only.

If you decide that you can skip any row that is locked because it is an
uncommitted inserted row, you can now turn the DB2_SKIPINSERTED registry
variable on to allow you to skip those rows. Having this registry variable on produces greater concurrency and would therefore be the preferred choice for most applications.

There are cases where skipping uncommitted inserts may not be preferred. For example:

- When two applications use a table to pass data between them.
- When an application does not use UPDATE statements, but instead deletes 9 the old data and then inserts the new data."
[end quote]

Questions:

1. The description of the enhancement seems to suggest that DB2 would lock wait on uncommitted inserted rows prior to introduction of DB2_SKIPINSERTED registry variable (prior to FP9). It also seems to suggest that by default in FP9, DB2 will lock wait on the row unless the registry variable is set to ON. Is this correct? Yes that is correct. The idea is that the default befaviour does not
change when you apply a fixpack. The default behaviour may or may not
change when you migrate to a new version.

2. The text seems to suggest that setting DB2_EVALUNCOMMITTED = ON will
"skip" uncommitted updates with CS or RS isolation level. Is that correct? It sounds backwards from the name of the registry value

(EVALUNCOMMITTED). Actually it will do exactly what it claims says.
Example:

Session 1: UPDATE T SET x = 5 WHERE pk = 5
Session 2: SELECT * FROM T WHERE x < 0
Session 2 will evaluate the row with pk = 5. Since it will fail the
predicate x < 0 the scan will move on (gambling that Session 1 will
commit). If the predicate were x > 0 then the scan would wait.

3. It is not clear, but I assume the default for all the new registry
variables is OFF?

Yes.
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Mark A]

"Fan Ruo Xin" <fa*****@sbcglobal.net> wrote in message
news:vN*****************@newssvr19.news.prodigy.co m...

Serge,
I am waiting for this for a long time. Also with "set lock timeout in the
current session", instead of using db parameter, and "Re-Opt" feature
which
come with Version8.1.
But so far, the DB2 applications I supposrt still can not be benefited
from
these new features. The reason is there is no corresponding Java APIs. I
do
believe, most of the database servers most DBAs support communicate with
applications, application servers or web application servers which are
java
applications.
I do suggest IBM could make it part of the sql statement, like statement
isolation level idea (select ... from ... WITH UR/CS/RS/RR), besides
registry variables or db properties. Do you know which channel I could use
to let IBM know this request???
Thanks,
FRX

I don't think there is much to not set the registry values for everyone. It
would be nice to control at the SQL statement level, but the situations
where it is not wanted seem to be few and far between.

[Mark A]

"Mark A" <no****@nowhere.com> wrote in message
news:na********************@comcast.com...

I don't think there is much to not set the registry values for everyone.
It would be nice to control at the SQL statement level, but the situations
where it is not wanted seem to be few and far between.

First sentence should say:

I don't think there is much reason to not set the registry values for
everyone

[Serge Rielau]

Fan Ruo Xin wrote:

Serge,
I am waiting for this for a long time. Also with "set lock timeout in the
current session", instead of using db parameter, and "Re-Opt" feature which
come with Version8.1.
But so far, the DB2 applications I supposrt still can not be benefited from
these new features. The reason is there is no corresponding Java APIs. I do
believe, most of the database servers most DBAs support communicate with
applications, application servers or web application servers which are java
applications. SET CURRENT LOCK TIMEOUT is in V8.2
http://publib.boulder.ibm.com/infoce...n/r0011874.htm I do suggest IBM could make it part of the sql statement, like statement
isolation level idea (select ... from ... WITH UR/CS/RS/RR), besides
registry variables or db properties. Do you know which channel I could use
to let IBM know this request???

Do you have an IBMer covering your customer account?
I read much and remember soem of what's discussed in this group but it
leaves me with "annecdotal" requests.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab