Quick question:

Is COMMITCOUNT a valid option within a stored procedure? I have a procedure that will be updating hundreds of thousands of rows and want to commit every 1000 rows.

What would that syntax look like?

Thanks!!

No, it is not a valid option is a stored procedure. The only way I can think to do what you want is to keep a counter and when it reaches you limit (1000) then commit.

DECLARE counter integer;
...

SET COUNTER = 0;
WHILE ()
INSERT ROW
SET COUNTER = COUNTER + 1;
IF COUNTER = 1000
THEN
COMMIT WORK;
SET COUNTER = 0;
END IF;
END WHILE;
...

HTH

Andy

Here's what I came up with. One question: does it need to be COMMIT WORK or will just COMMIT suffice?

DECLARE RECORDCOUNT INTEGER;
DECLARE decArchiveRetrievalID DECIMAL (13,0);
DECLARE AT_END int DEFAULT 0;
DECLARE NOT_FOUND CONDITION FOR SQLSTATE '02000';
DECLARE UPDATE_CURSOR CURSOR WITH HOLD FOR
SELECT ARCHIVERETRIEVALID FROM AIM.AIMRETRIEVEDITEM
WHERE AIM.AIMRetrievedItem.ArchiveRetrievalID IN (SELECT
AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID
FROM
AIM.ARCHIVERETRIEVAL,
AIM.AIMRETRIEVEDITEM
WHERE
AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID = AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID
AND AIM.AIMRETRIEVEDITEM.IMAGEFRONT IS NOT NULL
AND AIM.ARCHIVERETRIEVAL.EXPIRATIONDATE < DATE(CURRENT TIMESTAMP));

DECLARE CONTINUE HANDLER FOR NOT_FOUND
SET AT_END = 1;

OPEN UPDATE_CURSOR;
FETCH_LOOP:
LOOP
FETCH UPDATE_CURSOR INTO decArchiveRetrievalID;
IF AT_END = 1 THEN
LEAVE FETCH_LOOP;
END IF;
UPDATE AIM.AIMRetrievedItem
SET ImageFront=NULL,
ImageFrontSize=NULL,
ImageFrontType=NULL,
ImageBack=NULL,
ImageBackSize=NULL,
ImageBackType=NULL
WHERE AIM.AIMRetrievedItem.ArchiveRetrievalID = decArchiveRetrievalID;
SET RECORDCOUNT = RECORDCOUNT+1;
IF RECORDCOUNT=1000 THEN
COMMIT;
END IF;
END LOOP FETCH_LOOP;
CLOSE UPDATE_CURSOR;

Thanks for the input....

COMMIT and COMMIT WORK are the same. The keywork is implied if it is omitted.

Andy

Thanks!

Last question:

The other dba here keeps talking about having to commit outside the procedure then returning to the caller?!? He said something about not being able to do an explicit commit inside of a stored procedure...

As far as I know, what I came up with should work just fine - nothing ele should be needed, right?

We have several SPs that have the COMMIT statement to commit the work they do. We are running 8.1.2 on Linux. the other OSs may be different, I would not know.

If it is not allowed then the create procedure will fail.

Andy

I'm trying to prove that the commits are working, so I put this logic in there:

SET intLOOPS = 0;

OPEN UPDATE_CURSOR;
FETCH_LOOP:
LOOP
FETCH UPDATE_CURSOR INTO decArchiveRetrievalID;
IF AT_END = 1 THEN
LEAVE FETCH_LOOP;
END IF;
UPDATE AIM.AIMRetrievedItem
SET ImageFront=NULL,
ImageFrontSize=NULL,
ImageFrontType=NULL,
ImageBack=NULL,
ImageBackSize=NULL,
ImageBackType=NULL
WHERE AIM.AIMRetrievedItem.ArchiveRetrievalID = decArchiveRetrievalID;
SET RECORDCOUNT = RECORDCOUNT+1;
IF RECORDCOUNT=10 THEN
COMMIT;
SET intLOOPS = intLOOPS+1;
SET RECORDCOUNT=0;
END IF;
END LOOP FETCH_LOOP;
CLOSE UPDATE_CURSOR;

intLOOPS is defined as an output parameter. When I run the procedure, the rows do get updated, but intLOOPS is always zero. Is this somehow jumping out of the loop and picking up the original value of intLOOPS?

Try it this way:

DECLARE intLOOPStmp integer;

SET intLOOPStmp = 0;

OPEN UPDATE_CURSOR;
FETCH_LOOP:
LOOP
FETCH UPDATE_CURSOR INTO decArchiveRetrievalID;
IF AT_END = 1 THEN
LEAVE FETCH_LOOP;
END IF;
UPDATE AIM.AIMRetrievedItem
SET ImageFront=NULL,
ImageFrontSize=NULL,
ImageFrontType=NULL,
ImageBack=NULL,
ImageBackSize=NULL,
ImageBackType=NULL
WHERE AIM.AIMRetrievedItem.ArchiveRetrievalID = decArchiveRetrievalID;
SET RECORDCOUNT = RECORDCOUNT+1;
IF RECORDCOUNT=10 THEN
COMMIT;
SET intLOOPStmp = intLOOPStmp+1;
SET RECORDCOUNT=0;
END IF;
END LOOP FETCH_LOOP;
CLOSE UPDATE_CURSOR;

SET intLOOPS = intLOOPStmp;;

HTH

Andy