IBM i (AS/400) Transactions in C# (.NET): Commit and Rollback 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 an isolation level is specified, 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.

For this mechanism to work, each table must be explicitly journaled via STRJRNPF. Without journaling, operations still execute but commitment control does not apply to those tables.

This requires three IBM i objects:

• A journal receiver CRTJRNRCV: the physical file where IBM i records every change.
• A journal CRTJRN: the logical object pointing to that receiver.
• Table journaling STRJRNPF: for each table involved in transactions.

Step 1 - Prepare the IBM i environment

This tutorial shows how to implement transactions in C# (.NET) with NTi, based on a wire transfer scenario 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 library
CL: CRTLIB LIB(BANKTEST) TEXT('Demo transactions NTi');

-- Create the journal receiver
CL: CRTJRNRCV JRNRCV(BANKTEST/BANKRCV) TEXT('Récepteur journal BANKTEST');

-- Create the journal
CL: CRTJRN JRN(BANKTEST/BANKJRN) JRNRCV(BANKTEST/BANKRCV) TEXT('Journal BANKTEST');

-- Create the table
SET CURRENT SCHEMA = BANKTEST;

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);

-- Journal the table (required for Commit/Rollback)
CL: STRJRNPF FILE(BANKTEST/ACCOUNTS) JRN(BANKTEST/BANKJRN);

💡STRJRNPF must be run on every table involved in transactions.

To verify that the journal was created successfully:

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 the connection:

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

using var conn = new NTiConnection("server=serverName;user=userName;password=password;schema=BANKTEST;"); 
conn.Open(); 
Console.WriteLine("Connection OK");

💡 With NTi, BeginTransaction requires IsolationLevel.ReadCommitted. Without this parameter, commitment control is not initialized on the IBM i side and calls to Commit or Rollback have no effect.

Step 4 - 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 Commit.

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

    var cmd2 = conn.CreateCommand();
    cmd2.Transaction = transaction;
    cmd2.CommandText = "UPDATE BANKTEST.ACCOUNTS SET BALANCE = BALANCE + 200 WHERE OWNER = 'Bob'";
    cmd2.ExecuteNonQuery();

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

During the transaction, changes are visible from ACS. Once Commit is applied, they 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 5 - Test 2: Rollback on error

Attempting to debit 9999€ from Bob's account, this example simulates a transfer rejected due to insufficient funds. The exception is thrown manually to reproduce this case, the Rollback is triggered in the catch and no changes are applied to the database.

using var transaction2 = (NTiTransaction)conn.BeginTransaction(IsolationLevel.ReadCommitted);
try
{
    var cmd1 = conn.CreateCommand();
    cmd1.Transaction = transaction2;
    cmd1.CommandText = "UPDATE BANKTEST.ACCOUNTS SET BALANCE = BALANCE - 9999 WHERE OWNER = 'Bob'";
    cmd1.ExecuteNonQuery();

    throw new Exception("Insufficient funds");

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

Check in ACS:

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

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