Using SQL inside RPGLE programs

Set the SQL naming, commitment, date, and error-handling choices deliberately before writing statements.

• SQL precompiler options such as COMMIT, NAMING, CLOSQLCSR, and DATFMT affect how embedded SQL is interpreted and how resources behave. Confirm the option names and release defaults in the IBM reference for your build command.
• Use a qualified naming strategy when a program must not depend on a changing library list. Keep compile options with the source and build record.
• Choose *NONE or a commitment-control level from the transaction requirement; do not enable a mode without understanding journaling and rollback expectations.

**FREE
ctl-opt option(*srcstmt : *nodebugio);

// Build-time SQL options belong in the compile command or source member.
// Keep naming, commitment, and cursor behavior documented with the program.

Use a singleton SELECT when the predicate is intended to return zero or one row.

• Declare RPG variables with types that match the SQL columns and prefix them with a colon in embedded SQL.
• Handle SQLSTATE 02000 before using the target variables. A no-row result must not leave the previous customer in the output fields.
• If several rows are valid, use a cursor or multi-row fetch rather than hiding the cardinality error.

exec sql
  select NAME, STATUS
    into :customerName, :customerStatus
    from MYLIB.CUSTOMER
   where CUSTOMER_ID = :customerId;

if SQLSTATE = '02000';
  // not found branch
elseif SQLSTATE <> '00000';
  // diagnostic branch
endif;

Declare, open, fetch, test end-of-data, and close in a predictable lifecycle.

• The cursor query should have a stable ORDER BY when the program depends on processing order.
• After each FETCH, check SQLSTATE or the SQL communication area before processing host variables.
• Close the cursor in normal and error cleanup paths. Keep the loop bounded or observable for batch workloads.

exec sql
  declare cOrders cursor for
    select ORDER_ID, TOTAL
      from MYLIB.ORDERS
     where CUSTOMER_ID = :customerId
     order by ORDER_ID;
exec sql open cOrders;

// Pseudocode skeleton:
// dow SQLSTATE <> '02000';
//   exec sql fetch cOrders into :orderId, :total;
//   if SQLSTATE = '00000'; process the row; endif;
// enddo;

exec sql close cOrders;

Use parameterized values and make affected-row expectations explicit.

• Name target columns in INSERT statements and bind values rather than concatenating text.
• After UPDATE or DELETE, check the expected row count and distinguish zero rows from a successful change.
• For a duplicate or constraint error, preserve the SQLSTATE and message text so the caller can choose a business response.

exec sql
  insert into MYLIB.ORDER_LINE (ORDER_ID, SKU, QUANTITY)
  values (:orderId, :sku, :quantity);

if SQLSTATE = '00000';
  // continue
elseif SQLSTATE = '23505';
  // duplicate business key
else;
  // preserve diagnostic
endif;

Keep NULL, zero, blank, and not-found as separate states.

• A null-capable result needs a null indicator or an equivalent nullable host structure.
• Check the indicator before formatting or calculating with the host variable.
• If the application needs a default, make that conversion explicit in SQL with COALESCE or in RPG after the null state is known.

exec sql
  select SHIPPED_DATE
    into :shipDate :shipDateNull
    from MYLIB.ORDERS
   where ORDER_ID = :orderId;

if shipDateNull < 0;
  // not scheduled
else;
  // use shipDate
endif;

Group related changes and leave the database in a known state after failure.

• Make the transaction boundary visible: begin or use the configured commitment scope, perform related statements, then commit once the checks pass.
• On a handled failure, roll back the unit and return the original diagnostic. Do not commit a marker separately from the business update when replay is possible.
• Test the failure between statements and after a process interruption, not only the happy path.

exec sql
  insert into MYLIB.ORDER_HEADER (...) values (...);
exec sql
  insert into MYLIB.ORDER_DETAIL (...) values (...);

if SQLSTATE = '00000';
  exec sql commit;
else;
  exec sql rollback;
endif;

Treat a routine call as an interface with typed inputs, outputs, and diagnostics.

• Document parameter order, data types, nullability, and expected SQLSTATE values at the RPG boundary.
• Use host variables for IN, OUT, and INOUT values and validate outputs before using them.
• Keep authorization and transaction behavior explicit; a routine can participate in the caller's transaction.

exec sql
  call MYLIB.CALCULATE_TOTAL(:orderId, :total :totalNull);

if SQLSTATE <> '00000';
  // preserve the routine diagnostic
endif;

Choose whether the database or the RPG program should own the work.

• A set-based UPDATE or aggregate can avoid moving every row through RPG, but it must have a reviewed predicate and measured plan.
• Use an RPG loop when each row needs procedural validation, external calls, or a detailed recovery path.
• When mixing the two, define the transaction, ordering, restart marker, and idempotency behavior.

exec sql
  update MYLIB.ORDER_LINE
     set EXTENDED = QUANTITY * UNIT_PRICE
   where ORDER_ID = :orderId;

// Prefer one measured set operation when row-level procedural work is not required.

Use a singleton SELECT for a business key that should return zero or one row, then branch before using host variables.

• The colon prefixes RPG host variables. The SQL statement stays parameterized; do not build the key into SQL text.
• SQLSTATE 02000 is the normal not-found branch. It is not a successful row read.
• If duplicate rows are possible, repair the data rule or use a cursor; do not hide an unexpected many-row result.

**free
dcl-s customerId packed(9:0);
dcl-s customerName varchar(80);
dcl-s customerStatus char(1);

exec sql
  select NAME, STATUS
    into :customerName, :customerStatus
    from MYLIB.CUSTOMER
   where CUSTOMER_ID = :customerId;

select;
when SQLSTATE = '00000';
  // Use customerName and customerStatus.
when SQLSTATE = '02000';
  // Same business branch as CHAIN with %FOUND = *off.
other;
  // Preserve SQLCODE and SQLSTATE for the caller.
endsl;

Ask the database whether a row exists when the program does not need the record fields.

• This is the embedded SQL equivalent of a lightweight SETLL/%EQUAL-style existence decision, not a replacement for a later validated update.
• Use FETCH FIRST 1 ROW ONLY so the statement expresses that one answer is sufficient.
• A later UPDATE must still include its business condition because another job can change the row after this check.

**free
dcl-s rowExists int(5) inz(0);

exec sql
  select 1
    into :rowExists
    from MYLIB.CUSTOMER
   where CUSTOMER_ID = :customerId
   fetch first 1 row only;

if SQLSTATE = '00000';
  // The key exists.
elseif SQLSTATE = '02000';
  // The key does not exist.
else;
  // An error happened; do not treat it as not-found.
endif;

Use a cursor when a customer can own many order rows and the RPG procedure must process each one.

• The WHERE predicate represents READE's equal-key boundary; ORDER BY makes the processing order explicit.
• FETCH writes into host variables. Process them only after SQLSTATE 00000, and exit on 02000.
• Always close the cursor, including on an error path, so the program does not retain cursor resources longer than intended.

**free
dcl-s orderId packed(9:0);
dcl-s total packed(11:2);

exec sql
  declare orderCursor cursor for
    select ORDER_ID, TOTAL
      from MYLIB.ORDERS
     where CUSTOMER_ID = :customerId
     order by ORDER_ID;

exec sql open orderCursor;
dou SQLSTATE = '02000';
  exec sql fetch next from orderCursor into :orderId, :total;
  if SQLSTATE = '00000';
    // Process this order row.
  elseif SQLSTATE <> '02000';
    leave;  // Route diagnostics after cleanup.
  endif;
enddo;
exec sql close orderCursor;

Use a stable order and keyset boundary when the program needs the next small group of rows.

• A predicate such as ORDER_ID > :lastOrderId describes the boundary explicitly, unlike relying on an earlier cursor position in another call.
• FETCH FIRST limits the result set. The program can move the boundary only after it has successfully processed each row.
• For changing data, keyset paging is often easier to reason about than a large OFFSET, but choose after measuring the real workload.

**free
dcl-s lastOrderId packed(9:0);
dcl-s orderId packed(9:0);

exec sql
  declare nextOrders cursor for
    select ORDER_ID
      from MYLIB.ORDERS
     where CUSTOMER_ID = :customerId
       and ORDER_ID > :lastOrderId
     order by ORDER_ID
     fetch first 25 rows only;

exec sql open nextOrders;
// FETCH, test SQLSTATE, process, then set lastOrderId = orderId.
exec sql close nextOrders;

Let Db2 match related rows when the procedure needs columns from multiple files or tables.

• A LEFT JOIN keeps an order even when its optional shipment row is absent. Use a null indicator for nullable right-side columns.
• Name the columns and qualify them with aliases so a later schema change or join does not make the result ambiguous.
• If the business request needs many result rows, move this SELECT into a cursor rather than assuming one order is returned.

**free
dcl-s orderTotal packed(11:2);
dcl-s shippedDate date;
dcl-s shippedDateNull int(5);

exec sql
  select o.TOTAL, s.SHIPPED_DATE
    into :orderTotal, :shippedDate :shippedDateNull
    from MYLIB.ORDERS o
    left join MYLIB.SHIPMENT s
      on s.ORDER_ID = o.ORDER_ID
   where o.ORDER_ID = :orderId;

if SQLSTATE = '00000' and shippedDateNull < 0;
  // The order exists but has not shipped.
endif;

Make the change condition visible in the SQL statement so the row is not changed merely because it was read earlier.

• The version or status predicate is an optimistic-concurrency check. A zero-row update is a business outcome that needs a deliberate branch.
• Use one transaction for related header, detail, and status changes. Do not commit a partial business action.
• For DELETE, add the required status or ownership condition rather than deleting solely by key when the rule depends on current state.

**free
exec sql
  update MYLIB.ORDERS
     set STATUS = 'COMPLETE',
         VERSION = VERSION + 1
   where ORDER_ID = :orderId
     and VERSION = :expectedVersion
     and STATUS = 'READY';

if SQLSTATE = '00000';
  // Confirm the affected-row expectation, then continue.
elseif SQLSTATE = '02000';
  // No qualifying row: stale version or changed status.
else;
  // Preserve diagnostics and roll back if this is one unit of work.
endif;

A common table expression makes a multi-step query readable without creating a permanent table or view.

• WITH creates a result name for the one SQL statement. It is useful when an aggregate, filter, or join needs to be understood before the final SELECT.
• A CTE is not a promise that Db2 materializes a temporary table; the optimizer chooses how to execute the statement.
• In RPGLE, a singleton final SELECT still needs the zero-row and error branches shown in the earlier examples.

**free
dcl-s customerTotal packed(13:2);

exec sql
  with CustomerTotals as (
    select CUSTOMER_ID, sum(AMOUNT) as TOTAL_AMOUNT
      from MYLIB.ORDERS
     group by CUSTOMER_ID
  )
  select TOTAL_AMOUNT
    into :customerTotal
    from CustomerTotals
   where CUSTOMER_ID = :customerId
     and TOTAL_AMOUNT > 10000;

if SQLSTATE = '02000';
  // No qualifying customer total.
endif;