Db2 for i: SQL, cursors, commitment and isolation

Think in sets first

Db2 for i can filter, join, aggregate, and order rows close to the data. Start with the result the business needs, then write the smallest query that returns it. A loop in RPG is still useful when each row drives a side effect, but it should not replace a simple set operation.

Use explicit column lists, meaningful predicates, and a stable ordering when the result is shown to a user. Check the access plan before calling a query slow.

Readable SQL skeleton

select o.customer_id, sum(o.amount) as open_total
  from appdata.orders as o
 where o.status = :status
 group by o.customer_id
 order by o.customer_id;

Use a cursor when the program must visit rows

A cursor has a lifecycle: declare the result, open it, fetch into host variables, handle SQLSTATE 02000, process the row, and close it. Keep the row work small and decide whether the cursor needs a stable snapshot or can see committed changes while it runs.

Never hide SQL errors behind an end-of-data check. Log SQLSTATE and the message text, then choose a retry, rollback, or operator action.

Embedded SQL control skeleton

exec sql
  declare c_orders cursor for
    select order_id, amount from appdata.orders
     where customer_id = :customerId;
exec sql open c_orders;
// fetch in a loop; stop only on SQLSTATE 02000
exec sql close c_orders;

1. Declare a result with a clear predicate
2. Open the cursor inside the intended transaction
3. Fetch and validate SQLSTATE
4. Process one row without doing unbounded work
5. Close the cursor and commit or roll back

Make commitment and locking a design choice

Commitment control groups changes into a unit that can be committed or rolled back. The boundary should match the business action: one order, one message, or a deliberately sized batch. A transaction that is too large holds locks and journals more work; one that is too small can leave a partial business action.

Isolation controls what a reader can see while other jobs change data. Explain the trade-off in plain language: stronger consistency can mean more waiting, while weaker isolation can expose a moving view. Test the chosen level with two concurrent jobs.

CL transaction outline

STRCMTCTL LCKLVL(*CS) CMTSCOPE(*JOB)
CALL PGM(APPDATA/POSTORDER)
COMMIT
ENDCMTCTL