EF Core Migrations on DB2 for i

Migrations work with the standard EF Core tooling (dotnet ef migrations add, dotnet ef database update). See the Microsoft documentation. This page covers what is specific to IBM i: journaled schema creation, the history table, idempotent SQL PL scripts, the limits specific to DB2 for i, and how IDENTITY values come back.

The examples reuse the Order entity and the OrderContext context from Entity Framework Core.

Applying migrations

MigrateAsync creates the target schema (the library) if it doesn't exist, then applies any pending migrations:

using Microsoft.EntityFrameworkCore;

await using var db = new OrderContext();
await db.Database.MigrateAsync();   // Migrate() is available synchronously

Schema creation: automatic journaling

When the target schema doesn't exist, the provider creates it through CREATE SCHEMA. On IBM i, a schema created by CREATE SCHEMA automatically journals the tables created in it, unlike a library created by CRTLIB. Commitment control requires journaled tables, so EF Core transactions work immediately on tables coming out of migrations, with no manual STRJRNPF needed.

The __EFMIGRATIONSHISTORY history table

As on any database, EF Core records applied migrations in a history table. True to the provider's identifier convention, it's emitted in uppercase in the target schema as __EFMIGRATIONSHISTORY, the name you'll see in ACS. Don't modify it by hand.

Idempotent SQL PL scripts

To deploy by script (DBA, CI/CD) rather than through Migrate(), generate an idempotent script with the standard command:

dotnet ef migrations script --idempotent --output migrate.sql

The provider wraps each migration in a compound SQL PL block that checks __EFMIGRATIONSHISTORY. The script can be replayed safely: each migration applies exactly once, whatever the state of the target database.

Explicit limits

Column rename: not supported

DB2 for i doesn't support renaming a column. Rather than silently applying a destructive workaround, the provider fails the migration with an actionable message. The way around it: add the new column, copy the data, drop the old one.

using Microsoft.EntityFrameworkCore.Migrations;

public partial class RenameCustomerColumn : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AddColumn(
            name: "CUSTOMERNAME",
            table: "ORDERS",
            type: "VARCHAR(60)",
            nullable: false,
            defaultValue: "");
        migrationBuilder.Sql("UPDATE ORDERS SET CUSTOMERNAME = CUSTOMER");
        migrationBuilder.DropColumn(
            name: "CUSTOMER",
            table: "ORDERS");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AddColumn(
            name: "CUSTOMER",
            table: "ORDERS",
            type: "VARCHAR(60)",
            nullable: false,
            defaultValue: "");
        migrationBuilder.Sql("UPDATE ORDERS SET CUSTOMER = CUSTOMERNAME");
        migrationBuilder.DropColumn(
            name: "CUSTOMERNAME",
            table: "ORDERS");
    }
}

The same restriction applies to changing the IDENTITY or ROW CHANGE TIMESTAMP nature of an existing column. The migration then fails with an explicit message.

One statement per round trip (MaxBatchSize = 1)

SaveChangesAsync sends one statement per server round trip. For bulk changes, prefer ExecuteUpdateAsync / ExecuteDeleteAsync, which run as a single SQL statement server side:

using System;
using System.Linq;
using Microsoft.EntityFrameworkCore;

await using var db = new OrderContext();

// a single server-side DELETE statement
await db.Orders
    .Where(o => o.PlacedOn < DateTime.Today.AddYears(-2))
    .ExecuteDeleteAsync();

// a single server-side UPDATE statement
await db.Orders
    .Where(o => o.Customer == "ACME")
    .ExecuteUpdateAsync(s => s.SetProperty(o => o.Customer, "ACME SAS"));

IDENTITY: the value comes back through FINAL TABLE

By default, an integer primary key becomes an IDENTITY column. On insert, the provider retrieves the generated value in the same round trip, through SELECT ... FROM FINAL TABLE (INSERT ...):

using System;
using Microsoft.EntityFrameworkCore;

await using var db = new OrderContext();
var order = new Order { Customer = "ACME", Amount = 1249.90m, PlacedOn = DateTime.Now };
db.Orders.Add(order);
await db.SaveChangesAsync();

Console.WriteLine(order.Id);   // IDENTITY value, returned through SELECT ... FROM FINAL TABLE

To remove the identity round trip on insert, see HiLo keys in Mapping and DB2 for i types.

What's next?

• Entity Framework Core : versions, UseNTi, provider options
• Mapping and DB2 for i types : Unicode, zoned and packed, HiLo, ROW CHANGE TIMESTAMP
• Blazor Server CRUD with EF Core : a complete example with Entity Framework Core on DB2 for i
• IBM i Transactions : commitment control, isolation levels, savepoints