DB2 does not always create a results table. This is a common misunderstanding because programmers are taught to code their cursors as if the results have already been materialized.
We had a case in our shop, shortly after migrating to DB2 V8, of recently inserted rows suddenly appearing within a cursor. The programmer was indignant, insisting that since the program hadn't been changed, this must be a DB2 V8 issue. Well, DB2 has never guaranteed that this would not happen in an ambiguous cursor. It was therefore not a bug in DB2 V8, we'd just been lucky for the previous several years.
More recent versions of DB2 allow much more control over this situation than in the past.
We had a case in our shop, shortly after migrating to DB2 V8, of recently inserted rows suddenly appearing within a cursor. The programmer was indignant, insisting that since the program hadn't been changed, this must be a DB2 V8 issue. Well, DB2 has never guaranteed that this would not happen in an ambiguous cursor. It was therefore not a bug in DB2 V8, we'd just been lucky for the previous several years.
More recent versions of DB2 allow much more control over this situation than in the past.
Adding INSENSITIVE SCROLL solved the problem for us. It was an easier alternative than changing the application to ignore recently (within the same unit of work) inserted rows.
Even a very experienced and expert programmer (ahem) can trip over this one. I once changed an online program to insert rows into the same table that was the subject of a cursor. The rows were inserted after the current row, so the FETCH loop just kept running, endlessly chasing EOF without ever getting there. Doh!
Fortunately, the problem was caught during unit testing.
No comments:
Post a Comment