JDBC: problems using FETCH FIRST and FOR UPDATE clause at the same time.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Acupuncture - 02 Jan 2006 16:59 GMT

Hi,
I am developing a JDBC application and I encountered this problem (DB2
for ISeries).
I want to do a select for update and also use the fetch first rows clause.
This is my sql statement:

SELECT UsrNbr, UsrPwd, UsrPwdChgD, UsrEmail, UsrChgUsrI, UsrChgDte FROM
USERS WHERE UsrNbr = ? FETCH FIRST 1 ROWS ONLY FOR UPDATE

This runs fine (the cursor gets this name 'P00022'), but when I execute the
update where current of:

UPDATE USERS SET UsrPwd=?, UsrPwdChgD=?, UsrEmail=?, UsrChgUsrI=?,
UsrChgDte=? WHERE CURRENT OF P00022

It throws the following error:
'Cursor P00022 for file GUSBRO1 read-only.'

If I take off the 'FETCH FIRST 1 ROWS' from the SELECT CLAUSE the update
works fine.

Is there a way to use both the FETCH FIRST 1 ROWS ONLY and the FOR UPDATE
clauses and still do the update ?

My DB version:
DB2 UDB for AS/400 version 05.01.0000 V5R1m0
My JDBC driver:
AS/400 Toolbox for Java JDBC Driver version 5.0

Regards,
Gustavo

Reply to this Message

Bernd Hohmann - 02 Jan 2006 17:15 GMT

> SELECT UsrNbr, UsrPwd, UsrPwdChgD, UsrEmail, UsrChgUsrI, UsrChgDte FROM
> USERS WHERE UsrNbr = ? FETCH FIRST 1 ROWS ONLY FOR UPDATE

[...]

> It throws the following error:
> 'Cursor P00022 for file GUSBRO1 read-only.'

From the iSeries UDB reference:

"Specification of the fetch-first-clause in a select-statement makes the
result table read-only. A read-only result table must not be referred to
in an UPDATE or DELETE statement. The fetch-first-clause cannot appear
in a statement containing an UPDATE clause."

I found an answer but I don't know a solution for you :-(

Bernd

Signature

"Ja, alles meine Herren" sprach Fürst Lichnowsky. "Ooch det roochen?"
"Ja, auch das Rauchen." "Ooch im Tiergarten?" "Ja, auch im Tiergarten
darf geraucht werden, meine Herren." Und so endeten die Barrikadenkämpfe
des 18. März in Berlin

Reply to this Message

Serge Rielau - 03 Jan 2006 00:04 GMT

>> SELECT UsrNbr, UsrPwd, UsrPwdChgD, UsrEmail, UsrChgUsrI, UsrChgDte
>> FROM USERS WHERE UsrNbr = ? FETCH FIRST 1 ROWS ONLY FOR UPDATE
[quoted text clipped - 12 lines]
>
> I found an answer but I don't know a solution for you :-(

The only thought I have on this is to use a searched update instead of
a cursor update.
Or you leave away the FETCH FIRST 1 ROW and simply close the cursor
after the single update.

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

Reply to this Message

Eugene F - 03 Jan 2006 00:40 GMT

Serge is right, you could try something like this (update a view) in
one single statement:

UPDATE (
SELECT UsrNbr, UsrPwd, UsrPwdChgD, UsrEmail, UsrChgUsrI, UsrChgDte
FROM
USERS WHERE UsrNbr = ? FETCH FIRST 1 ROWS ONLY
) x
SET x.UsrPwd=?, x.UsrPwdChgD=?, x.UsrEmail=?, x.UsrChgUsrI=?,
x.UsrChgDte=?

-Eugene

Reply to this Message

Serge Rielau - 03 Jan 2006 01:30 GMT

> Serge is right, you could try something like this (update a view) in
> one single statement:
[quoted text clipped - 8 lines]
>
> -Eugene

Uh.. that's not what I meant... I'm quite certian that this does not
work in DB2 iSeries.
Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

Reply to this Message

Eugene F - 02 Jan 2006 20:40 GMT

Instead of FETCH FIRST in the cursor, try using ROW_NUMBER() OVER()
like

SELECT UsrNbr, UsrPwd, UsrPwdChgD, UsrEmail, UsrChgUsrI, UsrChgDte
FROM (
SELECT UsrNbr, UsrPwd, UsrPwdChgD, UsrEmail, UsrChgUsrI, UsrChgDte,
ROW_NUMBER() OVER() rn
FROM USERS WHERE UsrNbr = ?
) x WHERE rn = 1 FOR UPDATE

I am not sure though if iSerires supports that.

Regards,
-Eugene

Reply to this Message

doug.partch@db2helpdesk.com - 06 Jan 2006 16:55 GMT

No for the "for update statement". It won't work because the "fetch
first" turns it into a read only statement.

One question I have is, what is the primary key to the table and can
the where statement be quailified with this key. If so you will always
only get back one row which you can use the "for update statement on".

Reply to this Message

Eugene F - 06 Jan 2006 21:44 GMT

This one is working fine on V8.2.3 LUW:

db2 => create table t(c1 int, c2 varchar(10))
DB20000I The SQL command completed successfully.
db2 =>
db2 => insert into t values(1, '111'), (2, '222'), (4, '444'), (3,
'333')
DB20000I The SQL command completed successfully.
db2 =>
DB20000I The SQL command completed successfully.
db2 => select * from t

C1 C2
----------- ----------
1 111
2 222
4 444
3 333

4 record(s) selected.

db2 => update (select c2 from t where c1 > 2 fetch first 1 row only) x
set x.c2 = '000'
DB20000I The SQL command completed successfully.
db2 =>
db2 => select * from t

C1 C2
----------- ----------
1 111
2 222
4 000 <---- fetch first 1 row only
3 333

4 record(s) selected.

So it allowed to do the searched update with fetch first rows. Don't
see why it shouldn't in a proc.

-Eugene

Reply to this Message

Eugene F - 06 Jan 2006 22:05 GMT

Oh... sorry... it, of course, may not work on iSeries, wich I can't
test but maybe someone could run that tiny test on iSeries and let us
know :-))

Regards,
-Eugene

Reply to this Message

Serge Rielau - 08 Jan 2006 00:11 GMT

> Oh... sorry... it, of course, may not work on iSeries, wich I can't
> test but maybe someone could run that tiny test on iSeries and let us
> know :-))
>
> Regards,
> -Eugene

I am quite confident thsi test will not work on iSeries.
In DB2 for LUW was introduced only in DB2 V8.1.4.

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

Reply to this Message