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

Keep customers with no orders using LEFT JOIN

Begin a join by stating what one result row represents. If the report needs one row per customer, joining customer, order and payment detail directly can multiply rows and inflate totals. For example, two orders joined to three independent payment rows can produce six combinations. DISTINCT does not repair a total that was calculated over the wrong combinations; aggregate each detail set to the required key first.

This smaller example counts open orders and keeps customers with none. CUSTOMER_ID is unique in CUSTOMERS and ORDER_ID is non-null in ORDERS. Put the open-status condition in ON so it restricts which orders match without removing the customer. COUNT(o.ORDER_ID) counts actual matching orders; COUNT(*) would count the preserved outer-join row even when no order matched.

With customer 42 having two open orders and customer 43 having none, expect counts 2 and 0. Moving o.STATUS into WHERE would remove customer 43. Test that missing-child case explicitly; successful-looking reports often hide it.

Db2 for i SQL: one result row per customer

SELECT c.CUSTOMER_ID, COUNT(o.ORDER_ID) AS OPEN_ORDERS
FROM TESTDATA.CUSTOMERS AS c
LEFT JOIN TESTDATA.ORDERS AS o
  ON o.CUSTOMER_ID = c.CUSTOMER_ID
 AND o.STATUS = 'OPEN'
GROUP BY c.CUSTOMER_ID
ORDER BY c.CUSTOMER_ID;

Make NULL and zero mean different things

NULL represents a missing or unknown value; zero is a known numeric value. If three adjustment rows contain 10, 0 and NULL, COUNT(*) returns 3 while COUNT(AMOUNT) returns 2. Replacing every NULL with zero changes the question, especially for averages: the missing observation should not automatically become a measured zero.

The self-contained query below gives you a small dataset without creating a table. Expect ROWS_SEEN = 3, KNOWN_AMOUNTS = 2 and TOTAL = 10. Use IS NULL to find the missing amount. Do not write AMOUNT = NULL as a missing-value test; use the SQL null predicate.

When a report intentionally displays zero for an empty total, COALESCE can express that output rule. Explain why the replacement is appropriate instead of using it to hide incomplete data. In embedded SQL, a nullable result also needs an appropriate indicator variable unless the expression intentionally supplies a non-null replacement; the host variable alone cannot represent both states.

Read-only SQL fixture: compare row count with known-value count

WITH Adjustments(AMOUNT) AS (
  VALUES DECIMAL(10, 9, 2),
         DECIMAL(0, 9, 2),
         CAST(NULL AS DECIMAL(9, 2))
)
SELECT COUNT(*) AS ROWS_SEEN,
       COUNT(AMOUNT) AS KNOWN_AMOUNTS,
       SUM(AMOUNT) AS TOTAL
FROM Adjustments;

Use a CTE to show the steps in a business query

A common table expression, introduced by WITH, names an intermediate query result within a statement. CustomerTotals below first groups orders by customer. The outer query then filters those totals. For customer 42 with orders worth 6000 and 5000, the sum is 11000, so that customer appears. Customer 43 with a total of exactly 10000 does not pass the greater-than condition.

This example uses one CTE reference. It is a way to organise SQL, not a CREATE TABLE statement or a persistent object you can query later. A view is a different choice when several statements should reuse a named query definition.

Execution details matter when performance is involved. IBM documents that IBM i 7.5 changed the default handling of shared CTEs referenced multiple times to capture their results for consistency. Avoid a blanket rule that every CTE is always merged or always captured on every release. Inspect the actual plan and release/PTF behavior before tuning a slow report.

Db2 for i SQL: group first, then filter the customer totals

WITH CustomerTotals AS (
  SELECT CUSTOMER_ID, SUM(AMOUNT) AS TOTAL_AMOUNT
  FROM TESTDATA.ORDERS
  GROUP BY CUSTOMER_ID
)
SELECT CUSTOMER_ID, TOTAL_AMOUNT
FROM CustomerTotals
WHERE TOTAL_AMOUNT > 10000
ORDER BY TOTAL_AMOUNT DESC, CUSTOMER_ID;

Use a guarded UPDATE and check the affected-row count

Consider a reservation request for three units of item A100. Reading quantity 5 and later writing quantity 2 can lose another job’s change if both jobs read the old value. A conditional UPDATE expresses the change and minimum-stock rule in one statement. This does not replace transaction design, but it avoids a separate unprotected read-modify-write calculation.

The SQLRPGLE fragments assume matching declared host variables and a unique ITEM_ID. A successful update of one row means the reservation applied. Zero affected rows means no row satisfied the predicate; it could be a missing item or insufficient quantity, so choose the user-facing explanation deliberately. More than one affected row indicates the unique-key assumption is wrong. Reject zero or negative requested quantities before executing the statement.

Capture the original SQL status, retrieve diagnostics immediately, and handle errors before relying on the count. If the reservation also creates an order line, put both changes in the same intended transaction and commit only after both succeed. These fragments omit that caller-owned transaction and error-reporting code.

Embedded SQLRPGLE fragments: host declarations and transaction handling required

     C/EXEC SQL
     C+ UPDATE TESTDATA.STOCK
     C+ SET AVAILABLE = AVAILABLE - :qty
     C+ WHERE ITEM_ID = :itemId AND AVAILABLE >= :qty
     C/END-EXEC
     C                   EVAL      savedState = SQLSTATE
     C/EXEC SQL
     C+ GET DIAGNOSTICS :changed = ROW_COUNT
     C/END-EXEC

exec sql
  update TESTDATA.STOCK
     set AVAILABLE = AVAILABLE - :qty
   where ITEM_ID = :itemId and AVAILABLE >= :qty;
savedState = SQLSTATE;
exec sql get diagnostics :changed = ROW_COUNT;
// Check savedState and diagnostics status before using changed.
// Commit or roll back the complete business transaction in the caller.