Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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  ; 
share|improve this question
    
you should mark JamesA's answer as the answer then. :) –  Chris Aldrich Sep 16 '13 at 20:49

1 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 Database™ for iSeries

share|improve this answer
    
JamesA - This worked! Thanks –  Lele Jul 18 '13 at 17:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.