I am trying to update a record with a sub select and retrieve a "REQUESTID" from the sub select to allow this value to be used later in my stored procedure. For some reason I can not get this value. Does anyone have a solution? I am thinking I can maybe do a select (with fetch first 1 rows only) with an isolation level (not sure which one) to get a REQUESTID, then update it, then do the select again. But I would rather get the value from the sub select. Here is my proc:


CREATE PROCEDURE MYDBA.PROCEDURE2 ( )
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN

DECLARE SOMEREQUEST INT DEFAULT 0;

-- Declare cursor for returning select
DECLARE cursor1 CURSOR WITH RETURN FOR
SELECT REQUESTID, REQUESTFILE, REQUESTSTATUS
FROM MYTABLE
WHERE REQUESTID = SOMEREQUEST
FOR READ ONLY;

-- Find a single record with REQUESTSTATUS = 'N and update it.
UPDATE MYTABLE
SET REQUESTSTATUS = 'P'
WHERE (REQUESTID in
(SELECT RequestID as SOMEREQUEST FROM MYTABLE WHERE (REQUESTSTATUS = 'N')
FETCH FIRST 1 ROWS ONLY));

-- Cursor left open for client application
OPEN cursor1;

END P1

The FETCH FIRST clause cannot be used like that.

Why not do it this way:

Declare a cursor for the subselect. Open it. FETCH the first row of the result set. Update the row. Close the cursor. COMMIT. Then open your original cursor.

Andy

The problem I have is that I will have several machines running this SQL at the same time and I want a machine to have exclusive lock on the record so that no other process can read or update it. It is my understanding that if I use an update command I will get this. Basically I want to read a single record to update (locked), update the record, then do some more procesing with the record.

That being the case, then try this:

Read the row (add the FOR UPDATE clause)
Update the row
Do your processing
Commit

The lock will be held until the commit, preventing others into the process.

Andy

Tried it but for some reason it does not compile...

This works fine:
SELECT RequestID FROM IRREQUESTSTATUS WHERE REQUESTSTATUS = 'N' FETCH FIRST 1 ROWS ONLY FOR UPDATE;

But this will not compile:
DECLARE vRequestTID VARCHAR(32) DEFAULT '0';

SELECT RequestID INTO vRequestTID FROM IRREQUESTSTATUS WHERE REQUESTSTATUS = 'N' FOR UPDATE FETCH FIRST 1 ROWS ONLY FOR UPDATE;

The error: SETIRACTIVESTATUS_SLH2: 19: [IBM][CLI Driver][DB2/SUN] SQL0104N An unexpected token "FOR UPDATE" was found following "REQUESTSTATUS = 'N'". Expected tokens may include: "<space>". LINE NUMBER=19. SQLSTATE=42601


But when I take out the FOR UPDATE it compiles:
DECLARE vRequestTID VARCHAR(32) DEFAULT '0';

SELECT RequestID INTO vRequestTID FROM IRREQUESTSTATUS WHERE REQUESTSTATUS = 'N' FOR UPDATE FETCH FIRST 1 ROWS ONLY;

Any thoughts on what I might be doign wrong?

The FOR UPDATE clause is not valid for SELECT INTO statement.

Use a cursor instead;

DECLARE CURSOR with FOR UPDATE (no FETCH FIRST)
OPEN the cursor
FETCH (this will get the first row)
UPDATE
Do your processing
COMMIT

Andy