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 DB2 for i limitations and how IDENTITY values come back.

The examples reuse the Order entity and the OrderContext context from Introduction and configuration.

Applying migrations

MigrateAsync creates the target schema (library) if it does not exist, then applies the pending migrations:

using Microsoft.EntityFrameworkCore;

await using var db = new OrderContext();

await db.Database.MigrateAsync();   // Migrate() for the synchronous path

Schema creation: automatic journaling

When the target schema does not exist, the provider creates it with CREATE SCHEMA. An important point on IBM i: a schema created with CREATE SCHEMA automatically journals the tables created in it (unlike a library created with CRTLIB), and commitment control requires journaled tables. EF Core transactions therefore work immediately on tables created by migrations, with no manual STRJRNPF.

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 is emitted uppercase in the target schema: __EFMIGRATIONSHISTORY, the name you will see in ACS. Do not modify it manually.

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 limitations

Column rename: not supported

DB2 for i does not support renaming a column. Rather than silently applying a destructive workaround, the provider fails the migration with an actionable message. The procedure: 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 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 on the server:

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

await using var db = new OrderContext();

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

// a single UPDATE statement on the server
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 by SELECT ... FROM FINAL TABLE

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

What's next?

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