I've been doing a lot of cursor posting - my apologies. But can't seem ot find a whole bunch on it (tried v7 and v8 references).

I put together a stored procedure taking what I know of DB2 and SQL Server and cursors.

Was hoping someone could take a look and let me know how far off the mark I may be. I'd appreciate any hints or help...thanks!!!
CREATE PROCEDURE AIM.PurgeImagesFromCache ( )
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
RESULT SETS 1
LANGUAGE SQL
P1: BEGIN
DECLARE intRetrievalID INTEGER;

DECLARE PURGEITEMS_CURSOR CURSOR FOR
(SELECT
AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID
FROM
AIM.ARCHIVERETRIEVAL,
AIM.AIMRETRIEVEDITEM
WHERE
AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID = AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID
AND AIM.ARCHIVERETRIEVAL.EXPIRATIONDATE < DATE(CURRENT TIMESTAMP));

OPEN PURGEITEMS_CURSOR;

FETCH FROM PURGEITEMS_CURSOR;

WHILE SQLSTATE='0000' DO


UPDATE AIM.AIMRetrievedItem
SET ImageFront=NULL,
ImageFrontSize=NULL,
ImageFrontType=NULL,
ImageBack=NULL,
ImageBackSize=NULL,
ImageBackType=NULL
WHERE AIM.AIMRetrievedItem.ArchiveRetrievalID = intRetrievalID;

FETCH NEXCT FROM PURGEITEMS_CURSOR;
END;
CLOSE PURGEITEMS_CURSOR;
DEALLOCATE PURGEITEMS_CURSOR;
END P1

Again, thanks in advance.

I think this can be put in an UPDATE Statement instead of using a cursor

See if this thread throws light

http://www.tek-tips.com/viewthread.c...PID=178&page=1

Cheers
Sathyaram

The linkj just takes me to the home page....

mmm ... tek-tips behaves that way!!

on the home page, search for 'ibm db2' (find forum) ..

Click on the resuling link

On page 2(if you are visiting today), you will find the thread 'Updating Multiple fields with Table Joins'

HTH

Sathyaram

I was able to eventually get to the page, but - call me stupid - the solutions discussed there sisn't ring any bells for me. The examples on the site are updating multiple rows of a table with values from another table. I'm looking to do a straight update...

Again, any hints would be great.

UPDATE AIM.AIMRetrievedItem
SET ImageFront=NULL,
ImageFrontSize=NULL,
ImageFrontType=NULL,
ImageBack=NULL,
ImageBackSize=NULL,
ImageBackType=NULL
WHERE AIM.AIMRetrievedItem.ArchiveRetrievalID = (SELECT
AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID
FROM
AIM.ARCHIVERETRIEVAL,
AIM.AIMRETRIEVEDITEM
WHERE
AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID = AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID
AND AIM.ARCHIVERETRIEVAL.EXPIRATIONDATE < DATE(CURRENT TIMESTAMP));

First what i looking is that you open a cursor ,fetching rows
and wants to update the fetched row.

why you need to put result set 1.you are not using that cursor in another procedure to get the result set.

you want to update a fetched row then you can use

update <>

....
.... where current of <cursor name>

use the variable to hold values during the fetch part and use the same variables to update.

I think there is no need to deallocate the cursor. it works with associate and allocate way.