IBM i CLI (`ibmi`)

Command-line interface for querying, exploring, and managing IBM i systems via Db2 for i.

ibmi <command> [options]

Install

# Published package (recommended)
npm i -g @ibm/ibmi-cli

# Or from a local checkout
npm run build && npm link -w @ibm/ibmi-cli

Quick Start

# Option A: Configure via .ibmi/config.yaml
ibmi system add dev --host myhost.com --user MYUSER --password '${DB2i_PASS}'

# Option B: Use existing DB2i_* env vars from .env (zero config)
# The CLI falls back to DB2i_HOST, DB2i_USER, DB2i_PASS automatically

# Run a query
ibmi sql "SELECT * FROM SAMPLE.EMPLOYEE FETCH FIRST 5 ROWS ONLY"

# Run a YAML-defined tool
ibmi tool system_status --tools ../tools/work-management.yaml

Global Options

These apply to every command:

Flag	Description
`--system <name>`	Target a specific configured system
`--format <type>`	`table`, `json`, `csv`, or `markdown`
`--raw`	Shorthand for `--format json`
`--stream`	NDJSON output (one JSON object per line)
`--output <path>`	Write output to a file instead of stdout
`--watch <seconds>`	Re-run command at interval (Ctrl+C to stop)
`--tools <path>`	Path to YAML tool file(s), comma-separated
`--no-color`	Disable colored output

Format priority: --raw → --format → config format → auto-detect (TTY=table, piped=json). Set a default in your config to always get the same format regardless of TTY:

format: table   # always render tables, even when piped

Commands

`ibmi sql [statement]` — Execute SQL

ibmi sql "SELECT * FROM SAMPLE.EMPLOYEE FETCH FIRST 10 ROWS ONLY"
ibmi sql --file query.sql
cat query.sql | ibmi sql
ibmi sql "SELECT * FROM SAMPLE.EMPLOYEE" --dry-run      # preview only
ibmi sql "SELECT * FROM SAMPLE.EMPLOYEE" --format csv --output results.csv
ibmi sql "SELECT JOB_NAME FROM TABLE(QSYS2.ACTIVE_JOB_INFO())" --watch 5

Option	Description	Default
`--file <path>`	Read SQL from a file	—
`--limit <n>`	Max rows returned	system `maxRows` (5000)
`--read-only` / `--no-read-only`	Enforce or disable read-only mode	`--read-only`
`--dry-run`	Print SQL without executing (no connection needed)	—

SQL source priority: positional argument > --file > piped stdin.

Multi-system execution

Run the same query against multiple systems in parallel by passing comma-separated names to --system. Each name must be defined in your config file (~/.ibmi/config.yaml or project .ibmi/config.yaml).

ibmi sql "SELECT * FROM SAMPLE.EMPLOYEE FETCH FIRST 5 ROWS ONLY" --system dev,prod
ibmi sql "SELECT JOB_NAME FROM TABLE(QSYS2.ACTIVE_JOB_INFO())" --system dev,prod --format json
ibmi sql "SELECT * FROM QSYS2.SYSTEM_STATUS_INFO" --system dev,prod --stream

Each row includes a SYSTEM column. JSON output includes a systems array with per-system row counts, timing, and errors. If one system is unreachable, its results appear as an error row without affecting the others. --watch is not supported with multiple systems.

`ibmi describe <objects>` — Generate DDL

Generate DDL (CREATE statements) for one or more SQL objects via QSYS2.GENERATE_SQL.

ibmi describe "SAMPLE.EMPLOYEE"
ibmi describe "SAMPLE.EMPLOYEE,SAMPLE.DEPARTMENT"
ibmi describe "QSYS2.SYSCOLUMNS" --type VIEW
ibmi describe "SYSCOLUMNS" --type VIEW          # defaults library to QSYS2

Option	Description	Default
`--type <type>`	Object type (`TABLE`, `VIEW`, `INDEX`, `PROCEDURE`, `FUNCTION`, etc.)	`TABLE`

Objects are specified as LIBRARY.OBJECT or just OBJECT (defaults to QSYS2). When describing multiple objects, each is queried independently — a failure on one produces an ERROR: row without crashing.

`ibmi tool <name>` — Run a YAML Tool

ibmi tool system_status --tools ../tools/work-management.yaml
ibmi tool get_table_data --schema SAMPLE --table EMPLOYEE --tools ../tools/custom.yaml
ibmi tool list_jobs --dry-run --tools ../tools/work-management.yaml

Each YAML tool's parameters become CLI flags automatically (snake_case → --kebab-case).

Option	Description
`--dry-run`	Show resolved SQL and parameters without executing

Requires --tools to specify the YAML file(s).

`ibmi tools` — List Available Tools

ibmi tools --tools ../tools/                          # list all tools
ibmi tools --toolset work_management --tools ../tools/ # filter by toolset
ibmi tools show system_status --tools ../tools/        # show tool details

`ibmi toolsets` — List Toolsets

ibmi toolsets --tools ../tools/

`ibmi system` — Manage Connections

ibmi system list                          # list all configured systems
ibmi system show dev                      # show system details
ibmi system add dev --host h --user u     # add (prompts for missing fields)
ibmi system remove dev                    # remove a system
ibmi system default dev                   # set default system
ibmi system test dev                      # test connectivity (live)
ibmi system test --all                    # test all systems
ibmi system config-path                   # show config file locations

`ibmi schemas` — List Schemas

ibmi schemas
ibmi schemas --filter "MY%"
ibmi schemas --system-schemas             # include Q* and SYS* schemas

`ibmi tables <schema>` — List Tables

ibmi tables SAMPLE
ibmi tables QSYS2 --filter "SYS%"

`ibmi columns <schema> <table>` — Column Metadata

ibmi columns SAMPLE EMPLOYEE

`ibmi related <library> <object>` — Related Objects

ibmi related SAMPLE EMPLOYEE
ibmi related SAMPLE EMPLOYEE --type INDEX

`ibmi validate "<sql>"` — Validate SQL

ibmi validate "SELECT * FROM SAMPLE.EMPLOYEE"

`ibmi completion [shell]` — Shell Completions

# Auto-detect shell
eval "$(ibmi completion)"

# Explicit shell
eval "$(ibmi completion bash)"    # add to ~/.bashrc
eval "$(ibmi completion zsh)"     # add to ~/.zshrc
ibmi completion fish | source     # add to Fish config

Configuration

Config Files

The CLI loads config from two locations (project overrides user):

Scope	Path
Project	Nearest `.ibmi/config.yaml` walking up from `cwd`
User	`~/.ibmi/config.yaml`

Example .ibmi/config.yaml:

default: dev
format: table    # optional: table, json, csv, markdown
systems:
  dev:
    host: ${DB2i_HOST}
    port: 8076
    user: ${DB2i_USER}
    password: ${DB2i_PASS}
    readOnly: false
    confirm: false
    timeout: 60
    maxRows: 5000
    ignoreUnauthorized: true
  prod:
    host: prod400.example.com
    port: 8076
    user: ${PROD_USER}
    password: ${PROD_PASS}
    readOnly: true
    confirm: true

${VAR} references are expanded from environment variables at load time.

System Resolution Order

When a command needs a system connection, the CLI resolves it in this order:

--system <name> flag
IBMI_SYSTEM environment variable
default: in config file
Only system configured (implicit default)
Legacy DB2i_HOST / DB2i_USER / DB2i_PASS environment variables

Password Resolution

Config password field (with ${ENV_VAR} expansion)
Interactive prompt (TTY only, hidden input)
Error if non-interactive and no password available

Output Formats

Table (default for TTY)

┌────────────────┬───────┐
│ SCHEMA_NAME    │ TYPE  │
├────────────────┼───────┤
│ MYLIB          │ USER  │
└────────────────┴───────┘
[dev] myhost.com · 1 row · 0.25s

JSON (default for pipes)

{
  "ok": true,
  "system": "dev",
  "host": "myhost.com",
  "command": "list_schemas",
  "data": [{"SCHEMA_NAME": "MYLIB", "TYPE": "USER"}],
  "meta": {"rows": 1, "hasMore": false, "elapsed_ms": 250}
}

NDJSON (`--stream`)

{"SCHEMA_NAME":"MYLIB","TYPE":"USER"}
{"SCHEMA_NAME":"QSYS2","TYPE":"SYSTEM"}

CSV (`--format csv`)

SCHEMA_NAME,TYPE
MYLIB,USER

JSON Error

{
  "ok": false,
  "error": {"code": "SQL_ERROR", "message": "..."}
}

Exit Codes

Code	Name	When
`0`	SUCCESS	Command completed
`1`	GENERAL	Connection failure, unexpected error
`2`	USAGE	Invalid arguments or missing options
`3`	QUERY	SQL execution error
`4`	SECURITY	Read-only violation, forbidden operation
`5`	AUTH	Authentication failure

Agent Integration

The CLI is designed for programmatic use by AI agents and shell scripts.

Piped JSON by Default

When stdout is not a TTY, the CLI automatically outputs JSON. Agents can parse structured results without specifying --format json.

result=$(ibmi sql "SELECT COUNT(*) AS CNT FROM SAMPLE.EMPLOYEE")
count=$(echo "$result" | jq '.data[0].CNT')

NDJSON for Streaming

Use --stream to get one JSON object per row — ideal for incremental processing:

ibmi sql "SELECT * FROM SAMPLE.EMPLOYEE" --stream | while IFS= read -r row; do
  echo "$row" | jq '.EMPNO'
done

Exit Code Routing

Agents can branch on exit codes for error handling:

ibmi sql "SELECT 1 FROM SYSIBM.SYSDUMMY1" --system prod
case $? in
  0) echo "OK" ;;
  1) echo "Connection failed" ;;
  3) echo "SQL error" ;;
  5) echo "Auth failed" ;;
esac

Dry-Run for Planning

Agents can use --dry-run to preview SQL without executing:

ibmi sql "SELECT * FROM SAMPLE.EMPLOYEE" --dry-run
ibmi tool my_report --schema SAMPLE --dry-run --tools ./tools.yaml

File Output for Pipelines

ibmi sql "SELECT * FROM SAMPLE.EMPLOYEE" --format csv --output /tmp/employees.csv

Watch for Monitoring

ibmi sql "SELECT * FROM TABLE(QSYS2.ACTIVE_JOB_INFO()) WHERE JOB_STATUS = 'RUN'" --watch 10

Architecture

packages/cli/src/
├── index.ts                   # Program setup, global options, command registration
├── commands/
│   ├── sql.ts                 # ibmi sql — direct SQL execution (+ multi-system)
│   ├── describe.ts            # ibmi describe — DDL generation
│   ├── tool.ts                # ibmi tool — YAML tool execution
│   ├── tools-list.ts          # ibmi tools / toolsets — tool discovery
│   ├── system.ts              # ibmi system — connection management
│   ├── schemas.ts             # ibmi schemas
│   ├── tables.ts              # ibmi tables
│   ├── columns.ts             # ibmi columns
│   ├── related.ts             # ibmi related
│   ├── validate.ts            # ibmi validate
│   └── completion.ts          # ibmi completion — shell completions
├── config/
│   ├── types.ts               # TypeScript interfaces
│   ├── schema.ts              # Zod validation schemas
│   ├── loader.ts              # Config file discovery, parsing, merging
│   ├── resolver.ts            # System resolution chain
│   └── credentials.ts         # Password expansion and interactive prompts
├── formatters/
│   └── output.ts              # Table, JSON, CSV, NDJSON, file output, errors
└── utils/
    ├── command-helpers.ts      # withConnection wrapper, watch mode, format helpers
    ├── connection.ts           # DB2i_* env bridge to IBMiConnectionPool
    ├── multi-connection.ts     # Multi-system parallel execution via SourceManager
    ├── exit-codes.ts           # Semantic exit codes and error classification
    ├── yaml-loader.ts          # YAML tool file loading and merging
    └── yaml-to-commander.ts    # YAML parameters → Commander options

Key Pattern: `withConnection`

Most data commands use the withConnection(cmd, toolName, action) wrapper which handles the full lifecycle:

Resolve target system
Connect (set env vars for the connection pool)
Execute the action callback
Render output (table/json/csv/ndjson)
Cleanup (close pool)
Set exit code on error

This keeps each command focused on its query logic.

FilesExpand file tree

README.md

Latest commit

History

README.md

File metadata and controls

IBM i CLI (ibmi)