updating a lob

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Menon - 15 Jan 2005 18:13 GMT

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.

Reply to this Message

Mark A - 15 Jan 2005 18:36 GMT

> Hi folks
> First time posting -if this is not the correct forum, please let me
[quoted text clipped - 5 lines]
>
> 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.

Reply to this Message

Menon - 15 Jan 2005 18:52 GMT

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 ...

Reply to this Message

Mark A - 15 Jan 2005 19:16 GMT

> Thanx.
> So this is because db2 does not support "read consistency" as supported
[quoted text clipped - 5 lines]
> 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.

Reply to this Message

Menon - 15 Jan 2005 19:31 GMT

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,

Reply to this Message

Mark A - 15 Jan 2005 19:56 GMT

> Interesting...
> Thanx for the info...
[quoted text clipped - 18 lines]
>
> 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.

Reply to this Message

Menon - 15 Jan 2005 20:01 GMT

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

Reply to this Message

Knut Stolze - 17 Jan 2005 07:57 GMT

> 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.

Signature

Knut Stolze
Information Integration
IBM Germany / University of Jena

Reply to this Message