SEO

hi, we are using db2 udb v8.1 for win 64bit with fix pack 3, type 4
driver.

The program below is trying to create a scrollable resultset, then
when the cursor is moving, the current reading row supposed to be
locked and not allow other session to update, but can read. so i
create index on the table on column id, which is unique. The problem
is when i try to lock the cursor and then there is another statement
execute and if there is no record found, then i will close this
cursor, but at the same time, it release the record lock which is
locked by another cursor. These are two different resultsets, why one
resultset close will cause this problem? i don't want to have the lock
release. please help.

Class.forName(jdbcDriver);
conn = DriverManager.getConnection(catalogUrl, username,
password);

statement = conn.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);

sqlStr = "select id, imno08, imcd90, arno15 from NJIPD.IMLMLNB1
WHERE imno08='3M' ORDER BY imno08 ASC, imcd90 ASC"; // This is to
create a scrollable resultset

resultSet = statement.executeQuery(sqlStr);

resultSet.next();


if (openForUpdate && !readOnly)
{
stm =
conn.createStatement(
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_UPDATABLE);

sqlStr = "select id, imno08, imcd90, arno15,from NJIPD.IMLMLNB1
WHERE id=42"; // this is try to create a row lock

tmpCursor = stm.executeQuery(sqlStr);

tmpCursor.next(); // I do get a row U lock

sqlStr = "select id, imno08, apno01, prno02 from NJIPD.IMPWSBS
WHERE imno08='3M' ORDER BY imno08 ASC, apno01 ASC, prno02 ASC"; //
This is another table and there is no resultset returned

Statement stmt = conn.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);

cursor = stmt.executeQuery(sqlStr); // The row U lock still there
if (!cursor.isBeforeFirst())
cursor.close(); // The row U lock is released, why?

This is designed by this way.
If you don't want to release the cusor. You can upgrade the isolation level
to RS, like what you did before.

"xixi" <dai_xi@yahoo.com> wrote in message
news:c0f33a17.0402091631.405b2ebf@posting.google.c om...
> hi, we are using db2 udb v8.1 for win 64bit with fix pack 3, type 4
> driver.
>
> The program below is trying to create a scrollable resultset, then
> when the cursor is moving, the current reading row supposed to be
> locked and not allow other session to update, but can read. so i
> create index on the table on column id, which is unique. The problem
> is when i try to lock the cursor and then there is another statement
> execute and if there is no record found, then i will close this
> cursor, but at the same time, it release the record lock which is
> locked by another cursor. These are two different resultsets, why one
> resultset close will cause this problem? i don't want to have the lock
> release. please help.
>
> Class.forName(jdbcDriver);
> conn = DriverManager.getConnection(catalogUrl, username,
> password);
>
> statement = conn.createStatement(
> ResultSet.TYPE_SCROLL_INSENSITIVE,
> ResultSet.CONCUR_READ_ONLY);
>
> sqlStr = "select id, imno08, imcd90, arno15 from NJIPD.IMLMLNB1
> WHERE imno08='3M' ORDER BY imno08 ASC, imcd90 ASC"; // This is to
> create a scrollable resultset
>
> resultSet = statement.executeQuery(sqlStr);
>
> resultSet.next();
>
>
> if (openForUpdate && !readOnly)
> {
> stm =
> conn.createStatement(
> ResultSet.TYPE_FORWARD_ONLY,
> ResultSet.CONCUR_UPDATABLE);
>
> sqlStr = "select id, imno08, imcd90, arno15,from NJIPD.IMLMLNB1
> WHERE id=42"; // this is try to create a row lock
>
> tmpCursor = stm.executeQuery(sqlStr);
>
> tmpCursor.next(); // I do get a row U lock
>
> sqlStr = "select id, imno08, apno01, prno02 from NJIPD.IMPWSBS
> WHERE imno08='3M' ORDER BY imno08 ASC, apno01 ASC, prno02 ASC"; //
> This is another table and there is no resultset returned
>
> Statement stmt = conn.createStatement(
> ResultSet.TYPE_SCROLL_INSENSITIVE,
> ResultSet.CONCUR_READ_ONLY);
>
> cursor = stmt.executeQuery(sqlStr); // The row U lock still there
> if (!cursor.isBeforeFirst())
> cursor.close(); // The row U lock is released, why?

This is designed as this way ... -

"xixi" <dai_xi@yahoo.com> wrote in message
news:c0f33a17.0402091631.405b2ebf@posting.google.c om...
> hi, we are using db2 udb v8.1 for win 64bit with fix pack 3, type 4
> driver.
>
> The program below is trying to create a scrollable resultset, then
> when the cursor is moving, the current reading row supposed to be
> locked and not allow other session to update, but can read. so i
> create index on the table on column id, which is unique. The problem
> is when i try to lock the cursor and then there is another statement
> execute and if there is no record found, then i will close thisis
> cursor, but at the same time, it release the record lock which is
> locked by another cursor. These are two different resultsets, why one
> resultset close will cause this problem? i don't want to have the lock
> release. please help.
>
> Class.forName(jdbcDriver);
> conn = DriverManager.getConnection(catalogUrl, username,
> password);
>
> statement = conn.createStatement(
> ResultSet.TYPE_SCROLL_INSENSITIVE,
> ResultSet.CONCUR_READ_ONLY);
>
> sqlStr = "select id, imno08, imcd90, arno15 from NJIPD.IMLMLNB1
> WHERE imno08='3M' ORDER BY imno08 ASC, imcd90 ASC"; // This is to
> create a scrollable resultset
>
> resultSet = statement.executeQuery(sqlStr);
>
> resultSet.next();
>
>
> if (openForUpdate && !readOnly)
> {
> stm =
> conn.createStatement(
> ResultSet.TYPE_FORWARD_ONLY,
> ResultSet.CONCUR_UPDATABLE);
>
> sqlStr = "select id, imno08, imcd90, arno15,from NJIPD.IMLMLNB1
> WHERE id=42"; // this is try to create a row lock
>
> tmpCursor = stm.executeQuery(sqlStr);
>
> tmpCursor.next(); // I do get a row U lock
>
> sqlStr = "select id, imno08, apno01, prno02 from NJIPD.IMPWSBS
> WHERE imno08='3M' ORDER BY imno08 ASC, apno01 ASC, prno02 ASC"; //
> This is another table and there is no resultset returned
>
> Statement stmt = conn.createStatement(
> ResultSet.TYPE_SCROLL_INSENSITIVE,
> ResultSet.CONCUR_READ_ONLY);
>
> cursor = stmt.executeQuery(sqlStr); // The row U lock still there
> if (!cursor.isBeforeFirst())
> cursor.close(); // The row U lock is released, why?

if i do conn.setTransactionIsolation(4);, this will change the
isolation level of all the resultsets under same connection, and i
need to keep the record lock mean while allow other table access, so i
am not sure when is the best time to set back to cs. i feel unsafe
using this way, that is why i use forward only undatable cursor, but
not forsee the problem. is there any suggestion? thanks a lot

hi, i have change the program to this

conn.setTransactionIsolation(4);

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

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


tmpCursor = stm.executeQuery(sqlStr);

tmpCursor.next();
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.isBeforeFirst())
cursor.close();

even i use RS isolation, once one resultset close, it will close the
row U lock opened by another resultset. i don't want that . please
help

"xixi" <dai_xi@yahoo.com> wrote in message
news:c0f33a17.0402101132.385275c8@posting.google.c om...
> if i do conn.setTransactionIsolation(4);, this will change the
> isolation level of all the resultsets under same connection, and i
if i do conn.setTransactionIsolation(4);, this will change the
isolation level of all the resultsets under same connection
====
Yes, exactly. That is why DB2 UDB V7.x introduced statement isolation
level - You can specify the isolation level only for the statement by using
"SELECT ... WITH RS". During the runtime, all the other resultsets of the
same session will use the default isolation level or the isolation level if
you use conn.setTransactionIsolation(n) ...



> need to keep the record lock mean while allow other table access, so i
> am not sure when is the best time to set back to cs. i feel unsafe
> using this way, that is why i use forward only undatable cursor, but
> not forsee the problem. is there any suggestion? thanks a lot

"xixi" <dai_xi@yahoo.com> wrote in message
news:c0f33a17.0402101323.2856b57f@posting.google.c om...
> hi, i have change the program to this
>
> conn.setTransactionIsolation(4);
>
> stm =
> conn.createStatement(
> ResultSet.TYPE_SCROLL_SENSITIVE,
> ResultSet.CONCUR_UPDATABLE);
>
> sqlStr = "select id, imno08, imcd90, arno15 from NJIPD.IMLMLNB1
> WHERE id=42";
>
>
> tmpCursor = stm.executeQuery(sqlStr);
>
> tmpCursor.next();
> 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.isBeforeFirst())
> cursor.close();
>
> even i use RS isolation, once one resultset close, it will close the
> row U lock opened by another resultset. i don't want that . please
> help

That is strange. IIRC, this is because of cursor.isBeforeFirst(). Can you
try to use next(), other than isBeforeFirst()?
ResultSet cursor = stmt.executeQuery(sqlStr);
cursor.next();
cursor.close();

Does this release the Row (U) lock of tmpCursor result set?

if (!cursor.isBeforeFirst())
cursor.close();

in order for cursor.close execute, i need to check whether the
resultset return rows or not, if not, then close the cursor. so that
is why use cursor.isBeforeFirst(), if i do cursor.next() , there will
be error.

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.

"xixi" <dai_xi@yahoo.com> wrote in message
news:c0f33a17.0402110823.2406474e@posting.google.c om...
> if (!cursor.isBeforeFirst())
> cursor.close();
>
> in order for cursor.close execute, i need to check whether the
> resultset return rows or not, if not, then close the cursor. so that
> is why use cursor.isBeforeFirst(), if i do cursor.next() , there will
> be error.

"if(cursor.next())" can us if the result set is empty ...