NTiConnection

NTiConnection is the ADO.NET connection of the NTi provider for IBM i (DB2 for i). It derives from DbConnection and fully honors the standard contract (Open/OpenAsync, CreateCommand, BeginTransaction, ChangeDatabase, GetSchema, Close/DisposeAsync...), which is not documented again here: see the DbConnection reference. This page covers the NTi specific surface only: configuration, CL commands, program and service program calls, MFA, diagnostics and pooling. Async is the normal path; every method also exists synchronously.

Configuration properties

Each typed property matches a connection string keyword; defaults, synonyms and detailed semantics live in Connection properties. Configuration is frozen once the connection is open: everything is set before OpenAsync (or Open); only the current schema can be changed at runtime, with ChangeDatabase/ChangeDatabaseAsync. By default ("persist security info" false), ConnectionString is stripped of the password as soon as the connection opens.

CL commands and program calls

Every connection opens two distinct IBM i jobs: SQL runs in the database server job (QZDASOINIT), CL commands and program calls run in the command server job (QZRCSRVS). Each job has its own QTEMP, its own CURLIB and its own overrides: an object created in QTEMP by a CL command is not visible from SQL, and vice versa. To run a CL command INSIDE the SQL job, use CALL QSYS2.QCMDEXC('...'), at the cost of the SQL round trip.

CallServiceProgram: the export name is CASE SENSITIVE (compared byte by byte in the procedureNameCcsid CCSID, default 37); each parameter passes by reference (default) or by value according to its ServiceProgramParameterFormat (by value: a BINARY(4) of exactly 4 bytes); up to 7 parameters. The method returns the return value holder, or null when returnFormat is None: read rc.GetInt() for the integer, and rc.GetInt(4) for the errno with IntegerAndErrno. Pointer return values are not supported (a server space pointer has no meaning on the client): a procedure returning text fills a caller supplied buffer.

using System;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;
using Aumerial.Data.Nti;

class NTiConnectionDemo
{
    static async Task Main()
    {
        await using var connection = new NTiConnection(
            "server=MYIBMI;user=MYUSER;password=MYPASSWORD;pooling=true");
        await connection.OpenAsync();            // Open() remains available for sync code

        // CL command: command job (QZRCSRVS)
        await connection.ExecuteClCommandAsync("OVRDBF FILE(ORDERS) TOFILE(MYLIB/ORDERS)");

        // System API = ordinary program: QWCRSVAL (system values)
        var parms = new List
        {
            new NTiProgramParameter().AsInputOutput(),                   // empty receiver
            new NTiProgramParameter(32, ParameterDirection.Input),       // receiver length
            new NTiProgramParameter(1, ParameterDirection.Input),        // number of values
            new NTiProgramParameter("QSRLNBR", 10, ParameterDirection.Input),
            new NTiProgramParameter("", 8, ParameterDirection.Output),   // error code
        };
        await connection.CallProgramAsync("QSYS", "QWCRSVAL", parms);
        Console.WriteLine(parms[0].GetString(24, 8, 37).Trim());         // serial number

        // Exported procedure of a service program (QZRUCLSP)
        var buffer = new NTiProgramParameter(new byte[64], ParameterDirection.InputOutput);
        var length = new NTiProgramParameter(64)
        {
            ServiceProgramParameterFormat = NTiServiceProgramParameterFormat.ByValue,
        };
        var rc = await connection.CallServiceProgramAsync("QSYS", "QSOSRV1", "gethostname",
            new List { buffer, length },
            NTiServiceProgramReturnFormat.Integer);
        if (rc is not null && rc.GetInt() == 0)
            Console.WriteLine(buffer.GetString().TrimEnd('\0'));         // server host name

        // Two distinct IBM i jobs per connection
        Console.WriteLine($"SQL job     : {connection.DatabaseJob} (CCSID {connection.DatabaseJobCcsid})");
        Console.WriteLine($"Command job : {connection.CommandJob} (CCSID {connection.CommandJobCcsid})");
        Console.WriteLine($"License: {connection.RemainingDays} days left");

        // Pool purge
        NTiConnection.ClearPool(connection);   // the pool matching these options
        NTiConnection.ClearAllPools();         // every pool of the process
    }
}

The MFA family

The context (NTiAuthenticationFactorContext) identifies the host (Host) and the profile (User) being authenticated. The callback is invoked once per physical session (the factor is reused for that session's sockets) and possibly concurrently while the pool grows. Returning null proceeds without a factor: the server decides.

using System;
using System.Threading;
using System.Threading.Tasks;
using Aumerial.Data.Nti;

class MfaDemo
{
    static async Task Main()
    {
        await using var connection = new NTiConnection("server=MYIBMI;user=MYUSER;password=MYPASSWORD");

        // Async variant: called at the signon of each physical session, with the
        // caller's token on OpenAsync. A static factor
        // (connection.AdditionalFactor = "123456") or the synchronous callback
        // (AdditionalFactorProvider) take precedence when set.
        connection.AdditionalFactorAsyncProvider = async (ctx, ct) =>
        {
            await Task.Yield();                  // vault, HSM or prompt: cancelable via ct
            return Environment.GetEnvironmentVariable($"TOTP_{ctx.User}");
        };

        using var cts = new CancellationTokenSource(TimeSpan.FromSeconds(30));
        await connection.OpenAsync(cts.Token);
        Console.WriteLine(connection.SupportsAdditionalFactor
            ? "The server supports MFA"
            : "Signon without an additional factor");
    }
}

Diagnostics and state

Pool

Two purge statics, mirroring SqlConnection: ClearPool(connection) empties the pool matching the given connection's options, ClearAllPools() empties every pool of the process (see the end of the first example). Pooling is off by default (v4 parity); the client identity (ApplicationName, ClientAccounting, ClientUserIdentifier, ClientProgramIdentifier) is part of the pool key: two distinct identities never share a pool.