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 redocumented here (see the DbConnection reference).

This page covers the surface specific to NTi: configuration, CL commands, program and service program calls, MFA, diagnostics, and pooling.

Async is the normal path. Every method also has a synchronous counterpart.

Configuration properties

Each typed property maps to a connection string keyword. Defaults, synonyms, and detailed semantics are in Connection string.

Configuration is locked in as soon as the connection is open. Everything is set before OpenAsync (or Open). Only the current schema can be changed on the fly, with ChangeDatabase/ChangeDatabaseAsync.

By default (persist security info set to 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 job (QZDASOINIT), while CL commands and program calls run in the command 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, go through CALL QSYS2.QCMDEXC('...'), at the cost of the SQL round trip.

A few constraints apply to CallServiceProgram:

• The export name is case sensitive, matched byte for byte in the procedureNameCcsid CCSID (37 by default).
• Each parameter passes by reference by default, or by value depending on its ServiceProgramParameterFormat. By value, it must be a BINARY(4) of exactly 4 bytes.
• 7 parameters maximum.
• The method returns the receptacle carrying the return value, 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, since a server space pointer has no meaning on the client. A procedure that returns text fills a caller supplied buffer instead.

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() is still available synchronously

        // 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());         // system serial number

        // Exported procedure of a service program (QZRUCLSP)
        var buffer = new NTiProgramParameter(new byte[64], ParameterDirection.InputOutput);
        var length = new NTiProgramParameter(64, ParameterDirection.Input)
        {
            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 remaining");

        // Purging the pool
        NTiConnection.ClearPool(connection);   // the pool matching these options
        NTiConnection.ClearAllPools();         // every pool in the process
    }
}

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 the session's sockets) and can possibly run concurrently while the pool grows. Returning null continues the signon without a factor. The server makes the final call.

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 signon of every physical session, with
        // the caller's token on OpenAsync. A static factor
        // (connection.AdditionalFactor = "123456") or the synchronous callback
        // (AdditionalFactorProvider) take precedence if defined.
        connection.AdditionalFactorAsyncProvider = async (ctx, ct) =>
        {
            await Task.Yield();                  // vault, HSM, or prompt: cancellable through 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"
            : "Signed on without an additional factor");
    }
}

Diagnostics and state

Pool

Two purge statics, mirroring SqlConnection: ClearPool(connection) clears the pool matching the passed connection's options, ClearAllPools() clears every pool in the process (see the end of the first example).

The pool is off by default (v4 compatibility). Client identity (ApplicationName, ClientAccounting, ClientUserIdentifier, ClientProgramIdentifier) is part of the pool key, so two distinct identities never share a pool.