updating a lob

[Menon]

Hi folks
First time posting -if this is not the correct forum, please let me
know.

For updating a LOB in Oracle (using JDBC), you need to lock the row. Is
it true in DB2? How Are LOBs implemented in DB2 - Do you have a locator
and contents separated?

Thanx.

[Mark A]

"Menon" <rm*******@gmail.com> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...

Hi folks
First time posting -if this is not the correct forum, please let me
know.

For updating a LOB in Oracle (using JDBC), you need to lock the row. Is
it true in DB2? How Are LOBs implemented in DB2 - Do you have a locator
and contents separated?

Thanx.

DB2 does not have explicit locking by the application program. DB2 will
automatically lock the row(s) depending on the SQL statement(s) and the
isolation level (for select statements). The one exception to this is the
lock table SQL statement.

The default is to lock by row, but this can be changed to always lock at the
table level when defining (or altering) the table definition.

Lock escalation can automatically occur from row locks to table locks by DB2
depending on a number of other factors, such as whether the locklist memory
is filled up. The size of the locklist for a database can be changed by the
DBA.

The actual data for a lob is stored separately from the rest of the table.

[Menon]

Thanx.
So this is because db2 does not support "read consistency" as supported
by Oracle, correct?

Thus in the case of db2, if you do a select on a lob and you start
updating
I guess at that point db2 would lock the row so that others can not
update it?
Sorry if the questions are basic - am not familiar with the db2 ...

[Mark A]

"Menon" <rm*******@gmail.com> wrote in message
news:11*********************@c13g2000cwb.googlegro ups.com...

Thanx.
So this is because db2 does not support "read consistency" as supported
by Oracle, correct?

Thus in the case of db2, if you do a select on a lob and you start
updating
I guess at that point db2 would lock the row so that others can not
update it?
Sorry if the questions are basic - am not familiar with the db2 ...

The isolation level determines how long read locks are held. DB2 has the
following Isolation levels:

RR - Repeatable Read - Holds the share lock on all rows accessed. Releases
locks at next SQL commit.
RS- Read Stability - Hold share lock on all rows accessed. Releases locks
when SQL select statement finishes.
CS - Cursor Stability - Holds share lock on all rows accessed. Releases
locks when finished accessing a particular row (when DB2 read the next
row)..
UR - Uncommitted Read - No locks held (dirty read).

No other SQL statement can update a row while a share lock is being held,
but other share locks can coexist.

There is no lock contention for SQL statements in the same unit of work
(same application program). A share lock does not block an update by the
same program (only blocks others).

Isolation levels can be specified in numerous different ways, including in
each individual SQL statement. The default is CS.

[Menon]

Interesting...
Thanx for the info...
So when you say "There is no lock contention for SQL statements in the
same unit of work (same application program)." - you mean that in the
same "session"
there is no lock contention. But if you have selected a bunch of rows
in one session and you try to update them from another session
then except in the case of UR , the second session will block, correct?

Also curious to know if developers in DB2 world use the UR isolation
level due
to obvious reasons of data integrity compromise and what not. Any
applications
where this is recommended? I suspect that it may be useful where you
dont
care about exact computation and need to compute fast but approximate
results (e.g. in cases such as weather forecast etc where results need
not
be exact - this is just a random example but hopefully you get the
idea:))

Many thanx,

[Mark A]

"Menon" <rm*******@gmail.com> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...

Interesting...
Thanx for the info...
So when you say "There is no lock contention for SQL statements in the
same unit of work (same application program)." - you mean that in the
same "session"
there is no lock contention. But if you have selected a bunch of rows
in one session and you try to update them from another session
then except in the case of UR , the second session will block, correct?

Also curious to know if developers in DB2 world use the UR isolation
level due
to obvious reasons of data integrity compromise and what not. Any
applications
where this is recommended? I suspect that it may be useful where you
dont
care about exact computation and need to compute fast but approximate
results (e.g. in cases such as weather forecast etc where results need
not
be exact - this is just a random example but hopefully you get the
idea:))

Many thanx,

Same application means the same program running in the same thread. The same
code running as another thread is another application.

UR was not in the original implementation of DB2 because IBM was somewhat
reluctant to allow any access to data that would not have data integrity. I
have used it in some situations where data integrity problems would not be
an issue and concurrency was a high priority.

[Menon]

Care to share an example where UR was used?
Thanx for all the help so far - appreciate it..

[Knut Stolze]

Menon wrote:

Care to share an example where UR was used?
Thanx for all the help so far - appreciate it..

I used it a couple times when I loaded/imported a huge amount of data into a
table, and I wanted to see the progress made so far.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena