"xixi" <dai_xi@yahoo.com> wrote in message
news:c0f33a17.0402111432.207322ce@posting.google.c om...
> i have an difficult situation now. because other cursor close will
> close the row lock and release it. so i can't do close cursor. But for
> same table access, if one table has a scrollable cursor and same time
> has row lock, if i don't close the scrollable cursor before i create a
> different one where different where clause but same table, i will have
> problem since i think the first scrollable cursor still remain with IX
> lock, even the second cursor is readonly cursor, i am afraid too many
> IX lock will escalate to X lock. so i really need a better solution
> for close or not close cursor. thanks a lot.


IX is a table lock, it can't be escalated to the table X lock.
Define a big result set will cause concurrency problem. -

hi, i have changed to use statement isolation level with RS and with
cursor.next(), after that, cursor.close() still release the lock,
please help.

stm =
conn.createStatement(
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_UPDATABLE);

sqlStr = "select id, imno08, imcd90 from NJIPD.IMLMLNB1 WHERE
id=42 with rs";


tmpCursor = stm.executeQuery(sqlStr);

tmpCursor.next();
// different file
sqlStr = "select id, imno08, apno01, prno02 from NJIPD.IMPWSBS
WHERE imno08='3M' ORDER BY imno08 ASC, apno01 ASC, prno02 ASC";
Statement stmt = conn.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);

ResultSet cursor = stmt.executeQuery(sqlStr);
if (!cursor.next())
cursor.close(); // record lock created by tmpCursor release

from db2 information center, guidelines for locking, i get this
excerpt

Close cursors to release the locks that they hold.
When you close a cursor with the CLOSE CURSOR statement that includes
the WITH RELEASE clause, the database manager attempts to release all
read locks that have been held for the cursor. Table read locks are
IS, S, and U table locks. Row-read locks are S, NS, and U row locks.
Block-read locks are IS, S, and U block locks.

The WITH RELEASE clause has no effect on cursors that are operating
under the CS or UR isolation levels. When specified for cursors that
are operating under the RS or RR isolation levels, the WITH RELEASE
clause ends some of the guarantees of those isolation levels.
Specifically, a RS cursor may experience the nonrepeatable read
phenomenon, and a RR cursor may experience either the nonrepeatable
read or phantom read phenomenon.

for my case, i have two different cursor, one is holding U lock, the
other one is IS lock, why i close the IS lock cursor will affect U
lock? i don't understand the meaning of the guideline, the close
cursor means any cursor close will release the lock? Please help.
thanks a lot

OK. This means it is a bug.
I suggested you apply the latest fixpak. If you don't want to apply it on
your production system. You can
- Apply it on the testing box, try the program you posted here.
or
- Check the APAR, the bugs which fixed by fixpak4a.

if the latest fixpak couldn't resolve the problem.
You need to report IBM support. The temporary workaround for this case is -
using APP jdbc driver, other than JCC type-4 jdbc driver. I tested app jdbc
driver with DB2 UDB V8.1, you will not meet this situation - close one
cursor caused the lock release of another cursor.
But I met another problem - PERFORMANCE. I don't think IBM will address this
problem, because app jdbc driver is only used for back compatibility.

Another thing is when you use app jdbc driver. During the READ phase, you
will find the NS row lock, other than the U row lock.

"xixi" <dai_xi@yahoo.com> wrote in message
news:c0f33a17.0402121122.18142890@posting.google.c om...
> hi, i have changed to use statement isolation level with RS and with
> cursor.next(), after that, cursor.close() still release the lock,
> please help.
>
> stm =
> conn.createStatement(
> ResultSet.TYPE_FORWARD_ONLY,
> ResultSet.CONCUR_UPDATABLE);
>
> sqlStr = "select id, imno08, imcd90 from NJIPD.IMLMLNB1 WHERE
> id=42 with rs";
>
>
> tmpCursor = stm.executeQuery(sqlStr);
>
> tmpCursor.next();
> // different file
> sqlStr = "select id, imno08, apno01, prno02 from NJIPD.IMPWSBS
> WHERE imno08='3M' ORDER BY imno08 ASC, apno01 ASC, prno02 ASC";
> Statement stmt = conn.createStatement(
> ResultSet.TYPE_SCROLL_INSENSITIVE,
> ResultSet.CONCUR_READ_ONLY);
>
> ResultSet cursor = stmt.executeQuery(sqlStr);
> if (!cursor.next())
> cursor.close(); // record lock created by tmpCursor release

"xixi" <dai_xi@yahoo.com> wrote in message
news:c0f33a17.0402121150.7e578575@posting.google.c om...
> from db2 information center, guidelines for locking, i get this
> excerpt
>
> Close cursors to release the locks that they hold.
> When you close a cursor with the CLOSE CURSOR statement that includes
> the WITH RELEASE clause, the database manager attempts to release all
> read locks that have been held for the cursor. Table read locks are
> IS, S, and U table locks. Row-read locks are S, NS, and U row locks.
> Block-read locks are IS, S, and U block locks.
>
> The WITH RELEASE clause has no effect on cursors that are operating
> under the CS or UR isolation levels. When specified for cursors that
> are operating under the RS or RR isolation levels, the WITH RELEASE
> clause ends some of the guarantees of those isolation levels.
> Specifically, a RS cursor may experience the nonrepeatable read
> phenomenon, and a RR cursor may experience either the nonrepeatable
> read or phantom read phenomenon.
>
> for my case, i have two different cursor, one is holding U lock, the
> other one is IS lock, why i close the IS lock cursor will affect U
> lock?

See my other post ...

>i don't understand the meaning of the guideline, the close
> cursor means any cursor close will release the lock?

No. When you need to use RS or RR isolation levels, you use this because you
want to get away the nonrepeatable read or phantom read, the db server need
to guarantee this. Some locks will be kept until the end of the transaction.


>Please help.
> thanks a lot

hello, i still confuse for what the db2 guideline said. It said, there
are two options for how the cursors are declared, one is with hold,
the other is with release.

so i have cursor1 open a scrollable read only cursor, under CS
isolation, this will create a IS lock, then i have another cursor
cursor2 create a forward only updatable cursor (same CS isolation
level), i got a row U lock, and seems cursor1's IS lock becomes a IX
lock. then i have cursor3 open a scrollable read only cursor (CS
isolation level), i got IS lock, but once i close cursor3, i got
cursor2 released and cursor1 remained as IX lock. so from the DB2 doc,
where can i find out this specification? if i didn't do close cursor3
, but do connection.commit(), i got cursor2 released too. where is the
db2 doc?. we are using jdbc java, i find the samples related cursor
in db2 doc are use cli/odbc. are they the same? in db2cli.ini file,
does it suppose has CURSORHOLD keyword, but i can't find it in the
file. so where does this set up and how to configurate it? please
help. thanks a lot.

"xixi" <dai_xi@yahoo.com> wrote in message
news:c0f33a17.0402131003.4db56ec3@posting.google.c om...
> hello, i still confuse for what the db2 guideline said. It said, there
> are two options for how the cursors are declared, one is with hold,
> the other is with release.
======
You may need to double check with the db2 documentation.
AFAIK, when you define a cursor, you can define a cursor without or with
hold.
When you close a cursor, you can close the cursor (default) or close the
cursor with release.

u
> so i have cursor1 open a scrollable read only cursor, under CS
> isolation, this will create a IS lock, then i have another cursor
> cursor2 create a forward only updatable cursor (same CS isolation
> level), i got a row U lock, and seems cursor1's IS lock becomes a IX
> lock.
========
The IS lock will not be promoted or converted to IX. Does the two cursors
referred the same table?

> then i have cursor3 open a scrollable read only cursor (CS
> isolation level), i got IS lock, but once i close cursor3, i got
> cursor2 released and cursor1 remained as IX lock. so from the DB2 doc,
> where can i find out this specification? if i didn't do close cursor3
> , but do connection.commit(), i got cursor2 released too. where is the
> db2 doc?. we are using jdbc java, i find the samples related cursor
> in db2 doc are use cli/odbc. are they the same? in db2cli.ini file,
> does it suppose has CURSORHOLD keyword, but i can't find it in the
> file. so where does this set up and how to configurate it? please
> help. thanks a lot.

==========
(1). db2cli.ini is not the configuration file for jcc jdbc driver. I can't
find the documentation either (at least in year 2003). The documentation
only mentioned how to capture the trace information when using jcc jdbc
driver.

(2). If you defined a couple of cursors within the same transaction, close
one cursor shouldn't influence the others. You do need to talk to IBM
support.

(3). Either you use JAVA/JDBC or CLI/ODBC, the lock behaviour should be the
same.

Didn't read the whole thread but could it relate to the Early Close
attribute?

PM

hello,

so how do i set the cursor by using with hold or without? and how to
close cursor with release or without? since i don't see use jdbc can
do that.

yes, cursor1 and cursor2 refer to the same table, cursor1 use readonly
table scan, cursor2 is for row lock purpose. so once i open cursor2
and cursor2.next(), i found the IS lock is gone, only have IX lock and
row U lock.

i have tried the fix pack 4, the cursor close problem still exist.

"xixi" <dai_xi@yahoo.com> wrote in message
news:c0f33a17.0402161353.21c7371f@posting.google.c om...
> hello,
>
> so how do i set the cursor by using with hold or without? and how to
> close cursor with release or without? since i don't see use jdbc can
> do that.
===========
For WITH HOLD topic, there are two JDBC APIs:
(1) in your example:
stm =
conn.createStatement(
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_UPDATABLE);
- when you prepare a statement, you can also set HOLDABILITY:
resultSetHoldability - one of the following ResultSet constants:
ResultSet.HOLD_CURSORS_OVER_COMMIT or ResultSet.CLOSE_CURSORS_AT_COMMIT

(2). setHoldability
public void setHoldability(int holdability)
throws SQLExceptionChanges the holdability of ResultSet
objects created using this Connection object to the given holdability.

Parameters:
holdability - a ResultSet holdability constant; one of
ResultSet.HOLD_CURSORS_OVER_COMMIT or
ResultSet.CLOSE_CURSORS_AT_COMMIT

Try it, and please let me know the result. Since our production system is
using Version7.2. I had to downgrade my desktop from Version8.1 to
Version7.2.
Than ks,
=============
>
> yes, cursor1 and cursor2 refer to the same table, cursor1 use readonly
> table scan, cursor2 is for row lock purpose. so once i open cursor2
> and cursor2.next(), i found the IS lock is gone, only have IX lock and
> row U lock.
>
> i have tried the fix pack 4, the cursor close problem still exist.