IBM i (AS/400) Transactions in C# (.NET): Commit, Rollback and Savepoints with NTi

Introduction

A transaction guarantees that a set of database operations executes atomically: either all operations are committed (Commit), or none of them are (Rollback).

A classic example is a wire transfer: debiting one account and crediting another are two separate operations. If the debit succeeds but the credit fails, the data ends up corrupted.

On IBM i, this mechanism relies on DB2 for i commitment control. When a transaction starts, IBM i automatically initializes the commitment control environment. This is what ensures a transaction is either fully committed or fully rolled back, including in the event of an abnormal program termination.

Commitment control only applies to journaled tables. Two situations:

• Schema created in SQL with CREATE SCHEMA: nothing to do. CREATE SCHEMA automatically creates a QSQJRN journal inside the schema, and every table created in it is journaled automatically.
• Library created in CL with CRTLIB: no automatic journaling. You must create a journal receiver (CRTJRNRCV), a journal (CRTJRN), then start journaling on each table involved (STRJRNPF).

This tutorial uses the CREATE SCHEMA path, the simplest one, and shows the CRTLIB variant for existing libraries.

Step 1 - Prepare the IBM i environment

Scenario: a wire transfer between two accounts. The ACCOUNTS table holds two account holders, Alice (1000) and Bob (500). The tests consist of debiting one and crediting the other within a single transaction.

Run this complete script in ACS:

-- Create the schema: CREATE SCHEMA journals automatically
-- (QSQJRN journal created inside the schema, tables journaled automatically)
CREATE SCHEMA BANKTEST;

SET CURRENT SCHEMA = BANKTEST;

-- Create the table
CREATE TABLE accounts (
    account_id  INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
    owner       VARCHAR(50) NOT NULL,
    balance     DECIMAL(11,2) NOT NULL DEFAULT 0,
    CONSTRAINT pk_accounts PRIMARY KEY (account_id)
);

-- Initial data
INSERT INTO accounts (owner, balance) VALUES ('Alice', 1000.00);
INSERT INTO accounts (owner, balance) VALUES ('Bob',    500.00);

No CRTJRNRCV, CRTJRN or STRJRNPF here: journaling is already in place thanks to CREATE SCHEMA.

💡 CRTLIB variant: for a library created in CL (or an existing non-journaled library), journaling is up to you, and STRJRNPF must be run on every table involved in transactions:

-- CL library: no automatic journaling
CL: CRTLIB LIB(BANKTEST);
CL: CRTJRNRCV JRNRCV(BANKTEST/BANKRCV);
CL: CRTJRN JRN(BANKTEST/BANKJRN) JRNRCV(BANKTEST/BANKRCV);

-- ... create the table as above, then:
CL: STRJRNPF FILE(BANKTEST/ACCOUNTS) JRN(BANKTEST/BANKJRN);

To verify the journal is present (named QSQJRN with the CREATE SCHEMA path):

WRKOBJ OBJ(BANKTEST/*ALL) OBJTYPE(*JRN)

Step 2 - Create the .NET project

Create a Console App project and add the NTi package:

dotnet new console -n NtiTransacDemo
cd NtiTransacDemo
dotnet add package Aumerial.Data.Nti

Step 3 - Open the connection

Declare an NTiConnection instance and open it. With NTi, async is the normal path (real async end to end, from OpenAsync to DisposeAsync). The synchronous path Open() remains available.

using System.Data;
using Aumerial.Data.Nti;

await using var conn = new NTiConnection(
    "server=MY_SYSTEM;user=MY_USER;password=MY_PASSWORD;schema=BANKTEST");
await conn.OpenAsync();
Console.WriteLine("Connection open");

Step 4 - Isolation levels

BeginTransaction and BeginTransactionAsync accept the four standard ADO.NET isolation levels (see IsolationLevel) and map them to the DB2 for i levels:

BeginTransaction() and BeginTransactionAsync() without an argument start in ReadCommitted.

💡 Terminology differs between the two worlds: DB2's "Repeatable Read" level corresponds to the ANSI Serializable level.

Step 5 - Test 1: Commit

Wire transfer of 200 from Alice to Bob. Both UPDATE statements are executed within the same transaction and committed together with a CommitAsync.

await using var transaction = (NTiTransaction)await conn.BeginTransactionAsync(IsolationLevel.ReadCommitted);
try
{
    await using var debit = conn.CreateCommand();
    debit.Transaction = transaction;
    debit.CommandText = "UPDATE BANKTEST.ACCOUNTS SET BALANCE = BALANCE - 200 WHERE OWNER = 'Alice'";
    await debit.ExecuteNonQueryAsync();

    await using var credit = conn.CreateCommand();
    credit.Transaction = transaction;
    credit.CommandText = "UPDATE BANKTEST.ACCOUNTS SET BALANCE = BALANCE + 200 WHERE OWNER = 'Bob'";
    await credit.ExecuteNonQueryAsync();

    await transaction.CommitAsync();
    Console.WriteLine("Commit OK");
}
catch (Exception ex)
{
    await transaction.RollbackAsync();
    Console.WriteLine($"Rollback: {ex.Message}");
}

For the synchronous path: conn.BeginTransaction(IsolationLevel.ReadCommitted), transaction.Commit(), transaction.Rollback().

Once Commit is applied, the changes are permanently written to the database and can no longer be rolled back. Without Commit, a Rollback or an abnormal program termination cancels all changes and restores the balances to their initial state.

Check in ACS:

SELECT OWNER, BALANCE FROM BANKTEST.ACCOUNTS;
-- Expected result: Alice 800.00 / Bob 700.00

Step 6 - Test 2: Rollback on error

Attempting to debit 9999 from Bob's account: this example simulates a transfer rejected due to insufficient funds. The balance check throws an exception, RollbackAsync is triggered in the catch, and no changes are applied to the database.

await using var transaction2 = (NTiTransaction)await conn.BeginTransactionAsync();
try
{
    await using var debit = conn.CreateCommand();
    debit.Transaction = transaction2;
    debit.CommandText = "UPDATE BANKTEST.ACCOUNTS SET BALANCE = BALANCE - 9999 WHERE OWNER = 'Bob'";
    await debit.ExecuteNonQueryAsync();

    // Check the balance after the debit: negative = transfer rejected
    await using var check = conn.CreateCommand();
    check.Transaction = transaction2;
    check.CommandText = "SELECT BALANCE FROM BANKTEST.ACCOUNTS WHERE OWNER = 'Bob'";
    var balance = Convert.ToDecimal(await check.ExecuteScalarAsync());

    if (balance < 0)
    {
        throw new InvalidOperationException("Insufficient funds");
    }

    await transaction2.CommitAsync();
}
catch (Exception ex)
{
    await transaction2.RollbackAsync();
    Console.WriteLine($"Rollback: {ex.Message}");
}

Check in ACS:

SELECT OWNER, BALANCE FROM BANKTEST.ACCOUNTS;
-- Expected result: Alice 800.00 / Bob 700.00 (unchanged)

Step 7 - Savepoints: partial rollback

A savepoint marks an intermediate point inside the transaction: Rollback(name) cancels only what follows the savepoint, without losing the beginning of the transaction, which can then be committed normally. NTiTransaction exposes Save(name), Rollback(name) and Release(name), along with their asynchronous variants SaveAsync, RollbackAsync(name) and ReleaseAsync (standard DbTransaction contract).

Example: the transfer is committed even if granting a bonus fails.

await using var transaction3 = (NTiTransaction)await conn.BeginTransactionAsync();

// Transfer: the firm part of the transaction
await using var transfer = conn.CreateCommand();
transfer.Transaction = transaction3;
transfer.CommandText = "UPDATE BANKTEST.ACCOUNTS SET BALANCE = BALANCE - 100 WHERE OWNER = 'Alice'";
await transfer.ExecuteNonQueryAsync();

await using var credit3 = conn.CreateCommand();
credit3.Transaction = transaction3;
credit3.CommandText = "UPDATE BANKTEST.ACCOUNTS SET BALANCE = BALANCE + 100 WHERE OWNER = 'Bob'";
await credit3.ExecuteNonQueryAsync();

// Savepoint before the optional step
await transaction3.SaveAsync("BEFORE_BONUS");

try
{
    await using var bonus = conn.CreateCommand();
    bonus.Transaction = transaction3;
    // Nonexistent table: the bonus step fails
    bonus.CommandText = "UPDATE BANKTEST.BONUS SET AMOUNT = AMOUNT + 50 WHERE OWNER = 'Bob'";
    await bonus.ExecuteNonQueryAsync();
}
catch (NTiSqlException ex)
{
    // Cancels ONLY what follows the savepoint: the transfer is preserved
    await transaction3.RollbackAsync("BEFORE_BONUS");
    Console.WriteLine($"Bonus step cancelled: {ex.Message}");
}

// Commits the transfer (and the bonus if it succeeded)
await transaction3.CommitAsync();

Release(name) / ReleaseAsync(name) frees a savepoint that is no longer needed, without cancelling anything.

Cancellation: a broken connection by contract

Cancelling a token (CancellationToken) during an NTi operation breaks the connection, by contract: the in-flight protocol frame is lost, and the operation throws an OperationCanceledException carrying the caller's token. The uncommitted transaction is rolled back on the server side, and the connection must be reopened (with pooling enabled, OpenAsync immediately provides a healthy session).

Never treat a cancellation as a recoverable error on the same connection: it is an emergency exit, not a nominal flow.

What's next?

• Run a CL command : run a CL command and handle errors
• Call a program : call an RPG program with input/output parameters
• Stored procedure : call a SQL stored procedure with Dapper and DataReader