ibm db2 lock (row/table) status/indication

[adri4n]

as wat ive mentioned in the title.. im would like to know whether the a
particular record/table is being locked in my program. some of the
methods which i would like to develop are as below:

Lock(KEY, SQLCODE)
- to create a lock. Returns 0 on success, 1 if a lock already exists
and -1 on failure with SQLCODE. There is no necessity to use IsLock()
for checking if a lock exists or not, just insert the record and check
for

Unlock(KEY, SQLCODE)
- to remove a lock. Returns 0 on success, -1 on error with the
appropriate SQLCODE. Returns 1 if no lock record is found.

IsLock(Key, SQLCODE)
- to check if a lock for the Keyvalue exists or not. Returns 1 if a
lock exists, 0 otherwise and -1 on error with the appropriate SQLCODE.

is there any dbms status return that will return a status code if the
table/row is lock? however i still couldnt manage to dig any
information from the internet which can help me .any help would be
appreciated. thnks in advance

[Knut Stolze]

ad****@gmail.com wrote:

as wat ive mentioned in the title.. im would like to know whether the a
particular record/table is being locked in my program. some of the
methods which i would like to develop are as below:

Lock(KEY, SQLCODE)
- to create a lock. Returns 0 on success, 1 if a lock already exists
and -1 on failure with SQLCODE. There is no necessity to use IsLock()
for checking if a lock exists or not, just insert the record and check
for

Unlock(KEY, SQLCODE)
- to remove a lock. Returns 0 on success, -1 on error with the
appropriate SQLCODE. Returns 1 if no lock record is found.

IsLock(Key, SQLCODE)
- to check if a lock for the Keyvalue exists or not. Returns 1 if a
lock exists, 0 otherwise and -1 on error with the appropriate SQLCODE.

is there any dbms status return that will return a status code if the
table/row is lock? however i still couldnt manage to dig any
information from the internet which can help me .any help would be
appreciated. thnks in advance

I really doubt that there is an external interface to get such information
from the DB2 lock manager because:

(1) DB2 locks tuples/rows and you do not get the internal row-id out. This
has to do with encapsulation (basic OO stuff) and data independence.
(2) DB2 supports hierarchical locks, i.e. it locks databases, tables and
rows. Somehow your interface would have to accommodate for that.
(3) There are about a gazillion different locking modes in DB2. I really
doubt that you want to dig into all of them.
(4) Whoever needs to access the locking mechanisms explicitly should rethink
his/her application. The idea is that you use the DBMS to manage your
data. Handling the concurrent access is the job of the DBMS.
What do you need that for?

What you could do, however, is to implement a bunch of UDFs that employ
shared memory techniques to maintain their own locks. Implementing that is
very straight forward.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Mark A]

<ad****@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

as wat ive mentioned in the title.. im would like to know whether the a
particular record/table is being locked in my program. some of the
methods which i would like to develop are as below:

Lock(KEY, SQLCODE)
- to create a lock. Returns 0 on success, 1 if a lock already exists
and -1 on failure with SQLCODE. There is no necessity to use IsLock()
for checking if a lock exists or not, just insert the record and check
for

Unlock(KEY, SQLCODE)
- to remove a lock. Returns 0 on success, -1 on error with the
appropriate SQLCODE. Returns 1 if no lock record is found.

IsLock(Key, SQLCODE)
- to check if a lock for the Keyvalue exists or not. Returns 1 if a
lock exists, 0 otherwise and -1 on error with the appropriate SQLCODE.

is there any dbms status return that will return a status code if the
table/row is lock? however i still couldnt manage to dig any
information from the internet which can help me .any help would be
appreciated. thnks in advance

DB2 is known as a locking or pessimistic locking database. It locks
automatically based on the SQL statement submitted in a manner that is
appropriate for most all situation without the need for a programmer to
perform locking tasks. For that reason, it has only limited capabilities to
manually lock rows.

In DB2 you cannot directly lock a row, however by executing the appropriate
SQL statement on the row such as select for update, update, or select on the
row, DB2 will automatically take appropriate locks on the row(s). These
locks may be share locks, update locks, exclusive locks, etc. There is an
explicit lock table statement, but not for rows.

To unlock rows, it depends on what SQL statement was used to lock them. The
isolation level determines how long share locks are held. CS holds share
locks the until the row is finished processing for the select, RS hold the
share locks on all rows processed by the select until the SQL statement is
completely finished, and RR holds share locks on all rows processed until a
commit is issued. Isolation level UR holds no locks and ignores any existing
locks for select statements. All other non-share locks (update, exclusive,
etc) are released at commit time.

If you SET CURRENT LOCK TIMEOUT WAIT, then DB2 will return a -911 reason
code 68 immediately for any SQL that is waiting on a lock. This only works
in a different application than is holding the lock, since an application
cannot lock itself out even with multiple SQL statements. But it will be an
indication that the row is locked. This feature is in the 8.2 release.