Current DB solution does not implement valid transactions for DB2 as DBrollback is not related to DBbegin. This causes the problem when single rollback terminates all cursors, prepared statements, rows, temp files, etc. as no SAVEPOINT is set with DBbegin.
IBM DB2 documentation states:
ROLLBACK without the TO SAVEPOINT clause also causes the following actions to occur:
All locks that are implicitly acquired during the unit of recovery are released.
All cursors are closed, all prepared statements are destroyed, and any cursors that are associated with the prepared statements are invalidated.
All rows and all logical work files of every created temporary table of the application process are deleted. (All the rows of a declared temporary table are not implicitly deleted. As with base tables, any changes that are made to a declared temporary table during the unit of recovery are undone to restore the table to its state at the last commit point.)
All LOB locators, including those that are held, are freed.
Consider the following example:
Expected result is All records are processed by DBfetch, no data is altered by DBexecute (as rollback is called), but current result is DBrollback breaks while loop execution as DBfetch returns NULL after first DBrollback call