Cursor remains open across commit

Cursor problems — duplicates, skipped rows, or cursors misbehaving across commits — usually come from cursor definition or commit scope.

What it means

• A cursor defines how rows are fetched; its behavior depends on declaration options and commit handling.
• WITH HOLD keeps the cursor open across commits; without it, COMMIT closes the cursor.

Common causes

• Cursor declared without WITH HOLD but code commits inside the fetch loop.
• ORDER BY missing, so row order is not guaranteed.
• Isolation level allowing other jobs to change rows mid-fetch.

How to diagnose

• Check the DECLARE CURSOR for WITH HOLD.
• Verify COMMIT placement relative to the fetch loop.
• Test the SELECT standalone in SPUFI for duplicates.

How to fix it

• Add WITH HOLD if the cursor must survive commits.
• Add ORDER BY for deterministic order.
• Commit outside the fetch loop where possible.

Example: Cursor that survives COMMIT

WITH HOLD is the fix for most "cursor closed" surprises.

Interview tip

• Q: How would you troubleshoot a Cursor remains open across commit in production?
• A: I would check the cursor declaration for WITH HOLD, review commit placement in the fetch loop, and test the SELECT standalone.

Prevention checklist

• Declare intent (WITH HOLD or not) explicitly.
• Keep commits out of fetch loops.