-501 on FETCH 1

[Member 310024]

I declare a cursor with hold and then open it.
I then do my fetch loop.
For each fetch, I lookup various other tables and under certain conditions, do a delete of one or more of the records I have looked up.
At the end of each loop, I issue a ROLLBACK command, if the program has been requested to be run in 'non update' mode.
But I'm finding that the ROLLBACK is killing my cursor because the very next fetch after the rollback, returns sqlcode -501. How can I stop the ROLLBACK from having this unwanted side affect?

 

[gregsimpson]

Terminate,

not knowing you application I can't perhaps accurately comment. However it seems to me that you know you're running in "non update" mode which effectively tells you to rollback.

Can't you simply put some extra code in so you only update the database if your not in "non update" mode. It seems pointless updating a database with the associated resource this takes, when you know you're going to have to rollback any changes anyway.

I would question the design of this.

Cheers
Greg

 

[MarcLodge]

Terminate,

I agree with Greg's comments. If you know that you are in non update mode, why issue the update in the first place?

That said, you can issue a SAVEPOINT command with the ON ROLLBACK RETIAN CURSORS option, followed by aROLLBACK TO SAVEPOINT command which should leave the cursors open. See

http://www-3.ibm.com/cgi-bin/db2www...t.d2w/report?fn=db2v7s0savepnt.htm#HDRSAVEPNT

for details of the SAVEPOINT command and

http://www-3.ibm.com/cgi-bin/db2www....d2w/report?fn=db2v7s0sqls0652.htm#HDRROLLBAC

for details of the ROLLBACK

Hope this helps,

Marc

 

[gregsimpson]

Marc,

a good bit of info. Never heard of it before. Have a star.

Cheers
Greg

 

[MarcLodge]

Many thanks Greg