Thursday, January 18, 2007
Rview Topic: DB2: Commits, Cursors and Rollbacks
Back in 1974 I had some practical experience with the concept of a Database rollback when working in St Paul MN for Univac on a Bell Labs benchmark with a Univac 1110, where the database was DMS-1100, comparable to IDMS in the IBM world. Whole run-units would roll back and get caught in a catch-22 deadly embrace, degrading performance and causing required norms for the benchmark to be missed for several weeks. Finally, an analyst noticed that in one of the transactions, the problem could be fixed by reversing the order of two statements.
Often, with a DB2 cursor, the best coding technique will be to use somewhat frequent COMMIT commands with the WITH HOLD option (on the DECLARE CURSOR), so that the cursor does not close and have to be re-opened and re-positioned. With the option coded, a FETCH command will retrieve the next row in sequence according to the primary key for the table.
If there are no COMMIT commands, no updates get written until the program ends normally. Performance can degrade and contention may result. DB2 will rollback the updates upon an abend, or when a ROLLBACK is coded.