i400

i400

i400 is a pure Go database/sql driver for Db2 for IBM i. It talks to the IBM i database host server directly (no ODBC, no CGO) and follows the protocol and behaviour of JTOpen, IBM's open-source JDBC driver for IBM i.

Highlights:

• Full database/sql support: connection pooling, transactions, savepoints, prepared statements, named parameters, sql.Out, Rows.NextResultSet, context cancellation, column metadata.
• All 175 CCSIDs that JTOpen ships conversion tables for, including EBCDIC mixed SBCS/DBCS and bidi code pages, verified byte for byte against JTOpen.
• SQL errors with the server's message text ([SQL0204] ... not found.).
• JTOpen features such as extended dynamic SQL packages with package cache, block insert/update (ExecBatch), client info, generated keys, query timeout mechanisms, password levels 0 to 4, and TLS.
• Tested against a live IBM i 7.5 system (PUB400), see Verification.

Installation

go get github.com/bashdi/i400

The driver registers itself as i400. Go 1.24 or later is required.

Usage

package main

import (
	"context"
	"database/sql"
	"errors"
	"log"

	"github.com/bashdi/i400"
)

func main() {
	db, err := sql.Open("i400", "as400://USER:PASSWORD@ibmi.example.com/MYLIB")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	ctx := context.Background()
	rows, err := db.QueryContext(ctx, "SELECT ID, NAME FROM CUSTOMER WHERE CITY = ?", "Berlin")
	if err != nil {
		var sqlErr *i400.SQLError
		if errors.As(err, &sqlErr) {
			log.Fatalf("%s: %s", sqlErr.MessageID, sqlErr.MessageText)
		}
		log.Fatal(err)
	}
	defer rows.Close()

	for rows.Next() {
		var id int64
		var name string
		if err := rows.Scan(&id, &name); err != nil {
			log.Fatal(err)
		}
		log.Println(id, name)
	}
	if err := rows.Err(); err != nil {
		log.Fatal(err)
	}
}

Use as400s:// for a TLS connection. To build a *sql.DB from a connector, pass the connector returned by (&i400.Driver{}).OpenConnector(dsn) to sql.OpenDB.

DSN format

SCHEME://USER:PASSWORD@HOST[:PORT][/LIBRARY][?OPTION=VALUE&...]

Required:

• SCHEME: as400 for an unencrypted connection or as400s for TLS.
• HOST: Hostname or IP address of the IBM i system.

Optional:

• USER:PASSWORD: Credentials. They are optional during DSN parsing, but the server must authenticate the connection.
• PORT: Database host server port. Defaults to 8471 for as400 and 9471 for as400s. The port and portNumber options are also accepted.
• /LIBRARY: Default schema (library). If omitted, no schema is configured.
• ?OPTION=VALUE: Driver options, described below.

User names, passwords and library names must be URL-encoded when they contain characters such as @, : or spaces. ParseDSN parses and validates a DSN without connecting.

DSN options

All options are optional. Boolean values are true or false (parsed by strconv.ParseBool, so 1/0 also work). Invalid values are rejected with ErrInvalidDSN.

Options shown with a JTOpen spelling in the tables (for example query optimize goal) are matched ignoring case, spaces, underscores and dashes, so queryOptimizeGoal, query optimize goal and query_optimize_goal are equivalent. Options shown with a single key only (tls, secure, naming, dateFormat, dateSeparator, timeFormat, timeSeparator, decimalSeparator, commitmentControl, commitControl, libraries, statementCacheSize, scrollableCursors, holdCursors, bidiImplicitReordering, bidiNumericOrdering, connectTimeout, timeout, port, portNumber) accept only that key.

Connection and socket

Session and formats

Performance, cursors and LOBs

Generated keys

Extended dynamic packages

Client info

These values are sent to the server on connect when set (V6R1 and later). They can be changed at runtime with Conn.SetClientInfo.

Example:

as400://USER:PASSWORD@ibmi.example.com/MYLIB?naming=sql&libraries=APPDATA,APPLIB&connectTimeout=10s&statementCacheSize=20&errors=full

Driver extensions

Some JTOpen features have no database/sql API. They are methods on *i400.Conn and *i400.Stmt, reached through sql.Conn.Raw:

conn, err := db.Conn(ctx)
if err != nil {
	return err
}
defer conn.Close()

err = conn.Raw(func(driverConn any) error {
	c := driverConn.(*i400.Conn)
	// use c.ExecBatch, c.SetClientInfo, c.Warnings, ...
	return nil
})

The types i400.Timestamp and i400.NullTimestamp scan and bind TIMESTAMP values with picosecond precision (TIMESTAMP(12)).

Batch execution

ExecBatch prepares the statement once, runs it for every row and returns the total rows affected:

err = conn.Raw(func(driverConn any) error {
	c := driverConn.(*i400.Conn)
	n, err := c.ExecBatch(ctx, "INSERT INTO MYLIB.ORDERS VALUES(?, ?, ?)", [][]driver.Value{
		{int64(1), "widget", 3.5},
		{int64(2), "gadget", nil},
		{int64(3), "gizmo", 7.25},
	})
	log.Println("rows:", n)
	return err
})

Without useBlockUpdate=true, each row is one EXECUTE on the prepared statement. With useBlockUpdate=true, eligible rows are sent in blocks, as JTOpen does with "use block update":

• Eligible are INSERT ... VALUES(?, ...) with only parameter markers in the VALUES clause, and UPDATE or DELETE. As in JTOpen, UPDATE and DELETE are blocked only on V7R1 and later; on older or unknown servers they run row by row. Statements with LOB parameters are never blocked.
• A block holds at most 32000 rows and about 1 MiB of parameter data. Consecutive rows with the same encoded shape share a block; a single larger row is sent alone.
• The result is the sum of the block update counts. If a block fails, the error is returned together with the count of the blocks that succeeded.

Row values bind to the placeholders in the order they appear in the query, including named placeholders. Values are normalized like normal parameters. sql.Out is rejected.

Client info at runtime

Conn.SetClientInfo(ctx, name, value) sets one client info value on the server:

err := conn.Raw(func(driverConn any) error {
	return driverConn.(*i400.Conn).SetClientInfo(ctx, "ClientUser", "ALICE")
})

Names are case-sensitive, as in JTOpen: ApplicationName, ClientUser, ClientAccounting, ClientHostname, ClientProgramID. An unknown name returns ErrUnknownClientInfo. Conn.ClientInfo(name) returns the current value, or "" for an unknown name. Below V6R1 the value is kept on the client only, as in JTOpen. ResetSession (pool reuse) restores the DSN values, so a runtime change does not leak to the next user of the connection.

Query timeouts

database/sql has no query-timeout method; use a context deadline (context.WithTimeout) with QueryContext or ExecContext. When the deadline expires, the driver cancels the running statement with the host server's native cancel request (sent over a second connection, as JTOpen does) and returns context.DeadlineExceeded. The connection stays usable: the driver reads the server's reply to the interrupted statement (usually SQL0952) and closes its cursor. If the server does not answer within 30 seconds after the cancel, or native cancel is not available, the connection is invalidated instead. A deadline never returns driver.ErrBadConn, so database/sql does not retry the statement on another connection.

With queryTimeoutMechanism=qqrytimlmt the driver also sends the remaining time (whole seconds, at least 1) to the server before each open or execute, as JTOpen does. This is the IBM i query time limit (QQRYTIMLMT): the server compares the optimizer's estimated run time with the limit before running the query and rejects it with SQL0666 if it is over. It does not stop a query that is already running.

Type mapping

Result-set columns (server to Go)

LOB locator columns are resolved automatically and decoded like the LOB types above. For stored procedure result sets on V6R1 and later, DATE and TIME use the date and time format reported with the result set, as in JTOpen.

Parameters (Go to server)

LOB parameters are sent inline when the server describes them without a locator, and through the server-provided LOB locator otherwise.

Length rules (as in JTOpen):

• TIMESTAMP values are truncated to the column length, so TIMESTAMP(6) keeps six fraction digits.
• Fixed-length BINARY values longer than the column are truncated and shorter values are zero-padded.
• CHAR, VARCHAR, VARBINARY and VARGRAPHIC values that do not fit the column are rejected with an error, as are values longer than 65535 bytes for the 2-byte length prefix.

Character conversion

• All CCSIDs that JTOpen ships conversion tables for: 168 table-driven CCSIDs (single-byte EBCDIC, ASCII and Windows code pages, bidi code pages such as 420, 424, 862, 864, 1255, 1256, EBCDIC DBCS graphic, and EBCDIC mixed SBCS/DBCS with shift-out/shift-in such as 930, 933, 935, 937, 939, 1364, 1371, 1377, 1379, 1388, 1399, 5026, 5035, 5473, 13676) plus 7 Unicode CCSIDs: 1200 and 1201 (UTF-16BE), 1202 (UTF-16LE), 1208 (UTF-8), 13488, 17584 and 21680 (UTF-16BE).
• The tables are extracted from JTOpen (internal/ccsid) and checked against vectors generated by JTOpen.
• As in JTOpen, characters that cannot be represented in a CCSID are replaced by its substitution character.
• CCSIDs 0 and 65535 are treated as 37 when text is required; character columns with CCSID 65535 are returned as []byte. CCSIDs without a JTOpen table return ErrUnsupported.
• Bidi CCSIDs get JTOpen's bidi layout transformation (Arabic shaping, reordering), controlled by bidiStringType, bidiImplicitReordering and bidiNumericOrdering. As in JTOpen's JDBC driver, it applies to character column values and parameters, SQL statement text and column names.

Errors

SQLError.Error() is formatted like JTOpen's messages, for example [SQL0204] MYTABLE in MYLIB type *FILE not found. (SQLSTATE 42704, SQLCODE -204). To get the message text, the driver sends one follow-up request after an SQL error, as JTOpen does; if that request fails, the error contains only SQLSTATE and SQLCODE.

errors.Is matches SQLSTATE classes with the sentinels ErrSQLNoData (02), ErrSQLDataException (22), ErrSQLConstraintViolation (23), ErrSQLInvalidCursor (24/25) and ErrSQLSyntaxError (42). Further sentinels: ErrAuthentication, ErrConnection, ErrConnectionClosed, ErrInvalidDSN, ErrUnsupported.

SQL warnings (positive SQLCODE) do not fail the call; they are available from Conn.Warnings().

Capability matrix

Verification

• Unit tests run without a server (go test -short ./...). Conversion tables, DECFLOAT encoding, password substitutes, SQL package names and bidi transformations are checked against vectors generated by running JTOpen itself.
• The integration suite passes against PUB400 (IBM i 7.5, password level 3, CCSID 273). It covers close-on-EOF, extended dynamic packages and the package cache, block insert/update, returnGeneratedKeys, both query timeout mechanisms, SQL error message text, CCSID round trips including mixed DBCS, BOOLEAN, DECFLOAT, TIMESTAMP, date and time formats, client info, CALL with several result sets and the reuse of statement ids.
• Not verified on a live system: password level 4 (checked against JTOpen vectors only), savepoint rollback (needs a journaled library; the PUB400 test library is not journaled) and bidi CCSID data stored on a server (checked against JTOpen vectors only).

Known gaps and differences from JTOpen

• The standard database/sql.Rows API is forward-only; scrolling needs the ScrollableRows extension.
• With the default timestampPrecision=nanoseconds, TIMESTAMP(12) fractions are truncated to nanoseconds. Use timestampPrecision=string with i400.Timestamp to keep them.
• The package cache is filled when the connection first uses the package. Statements added to the package later in the session are not added to the cache.
• Bidi: each transformation uses its own copy of the bidi flags, so numeral shaping does not leak between transformations and conversion is safe for concurrent use (JTOpen shares one flag set). When directional marks are removed, the remaining length is padded with spaces where JTOpen leaves stale characters. Base table, column and schema names are not transformed because the driver does not expose them.
• bidiStringType defaults to 5 as in JTOpen. Compared with earlier releases of this driver, which converted bidi CCSIDs character by character, text from bidi CCSID columns can be ordered differently. Set bidiStringType=-1 to use each column's CCSID string type.
• queryTimeoutMechanism defaults to cancel; JTOpen defaults to qqrytimlmt.
• Client info: the non-empty DSN values are also sent on connect, which JTOpen only does through setClientInfo. Values are encoded as CCSID 37, and values longer than 255 bytes are an error.

Unsupported

• Isolation levels LevelWriteCommitted, LevelSnapshot and LevelLinearizable.
• CCSIDs for which JTOpen has no conversion table (JTOpen's fallback to Java charsets is not replicated).
• sql.Out parameters in ExecBatch.

Development

Running the tests

go test -short ./...   # unit tests, no server needed
go test ./...          # also runs the integration tests when a system is configured

The integration tests (integration_*_test.go, database_sql_crud_test.go) run against a live IBM i system. They are skipped when no system is configured and with -short.

The simplest setup is a .env file in the repository root. It is listed in .gitignore. Copy .env_template to .env and fill it in:

I400_ADRESSE=pub400.com
I400_USER=MYUSER
I400_PASSWORT=secret
I400_BIBLIOTHEK=MYUSER1

The tests build the DSN as400://I400_USER:I400_PASSWORT@I400_ADRESSE and create their objects in the library I400_BIBLIOTHEK. Each test drops what it creates. Variables already set in the environment take precedence over .env.

Instead of .env, the environment variables I400_TEST_DSN (a complete DSN) and I400_TEST_LIBRARY can be set:

$env:I400_TEST_DSN = "as400://USER:PASSWORD@ibmi.example.com/?naming=sql"
$env:I400_TEST_LIBRARY = "ITTEST"
go test -count=1 -v -run 'Integration|DatabaseSQLCRUD' ./...

export I400_TEST_DSN='as400://USER:PASSWORD@ibmi.example.com/?naming=sql'
export I400_TEST_LIBRARY=ITTEST
go test -count=1 -v -run 'Integration|DatabaseSQLCRUD' ./...

Tests that create objects are skipped when no library is configured. The savepoint tests need a library with journaled tables and are skipped otherwise.

Regenerating tables from JTOpen

The CCSID tables (internal/ccsid/tables) and the bidi tables (internal/bidi) are generated from a JTOpen source checkout and are not edited by hand. The generators are Java programs in internal/ccsid/gen and internal/bidi/gen; the commands are documented at the top of internal/ccsid/ccsid.go and in the header comment of each generator in internal/bidi/gen. The same programs write the JTOpen-generated test vectors in the testdata directories. A JDK and a JTOpen checkout are needed only for regenerating, not for building or testing.