How to loop through AS400 table with cursor

Question

I have a AS400 table that contains multiple rows per person. Each row contains data concerning a transaction by the person including points assigned to each transaction. I read through all rows for one person ordered by the points. The first row with the highest points, I leave alone. On subsequent rows the point values are cut in half and I do an update. I am currently using a cursor to open the table and doing a Loop to read through the rows. I am getting an -508 error stating :

An UPDATE or DELETE statement with a WHERE CURRENT OF CL was attempted, but the cursor is not positioned on a row or is positioned on a row, but the row is not locked because a COMMIT HOLD or ROLLBACK HOLD statement released the lock on the row. A FETCH statement must be issued to position the cursor on a row and lock the row. Recovery . . . : Issue a FETCH statement to position the cursor on a row and lock the row; then, try the request again.

Part of my code is below:

DECLARE V_LNAME CHAR ( 30 ) ; 
DECLARE V_LNAMEHOLD CHAR ( 30 ) ; 
DECLARE V_FNAME CHAR ( 15 ) ; 
DECLARE V_FNAMEHOLD CHAR ( 15 ) ; 
DECLARE V_DOB DATE ; 
DECLARE V_DOBHOLD DATE ; 
DECLARE V_TRANSNMBR CHAR ( 9 ) ; 
DECLARE V_TRANSNMBRHOLD CHAR ( 9 ) ; 
DECLARE V_POINTS NUMERIC ( 5 ) ; 
DECLARE V_POINTSHOLD NUMERIC ( 5 ) ; 
DECLARE V_POINTSEQ NUMERIC ( 5 ) ; 
DECLARE FIRSTRECORD CHAR ( 1 ) ; 
DECLARE CL CURSOR FOR 
SELECT LNAME , FNAME , DOB , TRANSNCNMBR , TOPOINTS 
FROM DB_TRANSDATA 
ORDER BY LNAME ASC , FNAME ASC , DOB ASC , TOPOINTS DESC ; 
DECLARE CLHLD CURSOR FOR 
SELECT LNAME , FNAME , DOB , TRANSNCNMBR , TOPOINTS 
FROM DB_TRANSDATA 
ORDER BY LNAME ASC , FNAME ASC , DOB ASC , TOPOINTS DESC ; 
OPEN CLHLD ; 
FETCH CLHLD INTO V_LNAMEHOLD , V_FNAMEHOLD , V_DOBHOLD , V_TRANSNMBRHOLD ; 
close clhld;
OPEN CL ; 
SET FIRSTRECORD = 'Y' ; 
LOOP 
FETCH CL INTO V_LNAME , V_FNAME , V_DOB , V_TRANSNMBR , V_POINTS , V_POINTSEQ ; 
IF TRIM ( CHAR ( V_LNAME ) ) = TRIM ( CHAR ( V_LNAMEHOLD ) ) AND TRIM ( CHAR ( V_FNAME ) ) = TRIM ( CHAR ( V_FNAMEHOLD ) ) AND V_DOB = V_DOBHOLD AND V_TRANSNMBR = V_TRANSNMBRHOLD AND FIRSTRECORD = 'N' THEN 
SET V_POINTSEQ = V_POINTS * .5 ; 
UPDATE DB_TRANSDATA 
SET POINTSEQ = V_POINTSEQ 
WHERE CURRENT OF CL ; 
SET V_LNAMEHOLD = V_LNAME ; 
SET V_FNAMEHOLD = V_FNAME ; 
SET V_DOBHOLD = V_DOB ; 
SET V_TRANSNMBRHOLD = V_TRANSNMBR ; 
ELSE 
UPDATE DB_TRANSDATA 
SET POINTSEQ = V_POINTS 
WHERE CURRENT OF CL ; 
SET V_LNAMEHOLD = V_LNAME ; 
SET V_FNAMEHOLD = V_FNAME ; 
SET V_DOBHOLD = V_DOB ; 
SET V_TRANSNMBRHOLD = V_TRANSNMBR ; 
SET FIRSTRECORD = 'N' ; 
END IF ; 
END LOOP ; 
CLOSE CL;
END ; 

• Chris Aldrich: you should mark JamesA's answer as the answer then. :)

Answer 1

You are not checking for an end of cursor condition.

DECLARE END_CL INT DEFAULT 0;
DECLARE CL CURSOR FOR 
SELECT LNAME , FNAME , DOB , TRANSNCNMBR , TOPOINTS 
FROM DB_TRANSDATA 
ORDER BY LNAME ASC , FNAME ASC , DOB ASC , TOPOINTS DESC ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET END_CL = 1;
FETCH CL INTO V_LNAME , V_FNAME , V_DOB , V_TRANSNMBR , V_POINTS , V_POINTSEQ ;
WHILE END_CL = 0 DO
 . . .
 FETCH CL INTO V_LNAME , V_FNAME , V_DOB , V_TRANSNMBR , V_POINTS , V_POINTSEQ ; 
END WHILE;

For more information:

IBM i 7.1 Information Center> Database> Programming> SQL programming> Routines> Stored procedures

IBM Redbooks> Power Systems> Stored Procedures, Triggers, and User-Defined Functions on DB2 Universal DatabaseTM for iSeries