I have a very basic SQL statement that has in it a subselect. For some reason, this query is performing kinda slowly, and another developer thinks the sub-select needs a FOR FETCH ONLY clause.

The problem is that I have put in the clause but it doesn't seem to work anywhere. Here is the original without the clause:

CREATE PROCEDURE AIM.UPDATEDELIVERYSTATUS (IN decAIMRETRIEVALID DECIMAL(13,0),
IN intENVMODULUS INTEGER,
IN dateARCHIVERETRIEVALDATE DATE,
IN decSTATUSID DECIMAL(13,0),
IN dateDELIVERYDATE TIMESTAMP)
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
SPECIFIC AIM.UPDATEDELSTATUS
LANGUAGE SQL
RESULT SETS 0
P1: BEGIN
DECLARE strArchiveDate VARCHAR(31);
--SET strArchiveDate = CAST(dateARCHIVERETRIEVALDATE AS VARCHAR(10)) || '-00.00.00.000000';
UPDATE AIM.DELIVERY
SET STATUSID = decSTATUSID,
DELIVERYDATE = dateDELIVERYDATE
WHERE ARCHIVERETRIEVALID IN
(SELECT ARCHIVERETRIEVALID FROM AIM.ARCHIVERETRIEVAL
WHERE
DATE(ARCHIVERETRIEVALRECEIPT) BETWEEN dateArchiveRetrievalDate and DATE(ARCHIVERETRIEVALRECEIPT) + 1 DAY
AND
MOD(INTEGER(AIMRETRIEVALID), intENVMODULUS) = decAIMRETRIEVALID);
END P1

You would normally place the FOR FETCH after the complete SELECT, but I get an error if I put it inside or outside of the subselect.

Does anyone have any suggestions?

A FOR FETCH ONLY would usually just affect the type of locking and/or the blocking (defines whether it is an ambigious cursor if used for an answer set returned to a remote requester). If should not affect performance of a stored procedure running on the server that does not return rows to a client.

Check the access path with an explain.

Apparently, we are getting a fairly high level of lock escalations in the database being caused by this stored procedure.

So, which problem are you trying to solve: lock escalation or update speed?

Little from column a and a little from column b. The dba here isn't too keen on adding an index to the table - it already has five indexes. He's talking about breaking up the query into separate UOW's, but i mentioned regardless of how you break it up, you are still doing a WHERE on ARCHIVERETRIEVALRECEIPT and if there's no index, it will still do a table scan...

..........

I think your DBA is right - adding indexes won't help a bit because you're not using column values in your WHERE clause but the results of functions.

This change may help a little:

Hi ansonee,

For fetch only in subselect work only with db2 v8.1 and higher. What version do you use? Do you use v7 or lower? If so, for fetch only is not supported in earlyer versions.

Hope this helps,
Grofaty

Actually, "for fetch only" is a clause in the SELECT statement, not in a subselect. At least that's what the documentation says: