Laravel Iseries Db2 Laravel Package

Getting Started

The package is a Laravel database driver for IBM i (iSeries/AS400) built on pdo_ibm/ibm_db2, so day-to-day work looks like normal Laravel until the SQL hits DB2 for i.

Prerequisites (host, not the IBM i):

• PHP with pdo_ibm (or ibm_db2) enabled — php -m | grep -i ibm.
• IBM i Access ODBC driver (or Db2 Connect) installed and reachable on port 446.
• IBM i 7.3+ recommended (7.2 lacks native BOOLEAN; the driver will map accordingly).

Minimal setup:

1. composer require the driver package and, if it ships one, publish its config.
2. Add a connection to config/database.php:

'ibmi' => [
    'driver'   => 'ibmi',
    'host'     => env('DB_HOST'),
    'port'     => env('DB_PORT', 446),
    'database' => env('DB_DATABASE'),   // library / *LOCAL
    'username' => env('DB_USERNAME'),
    'password' => env('DB_PASSWORD'),
    'schema'   => env('DB_SCHEMA'),     // library used for unqualified names
    'prefix'   => '',
],

3. Set DB_CONNECTION=ibmi in .env, then php artisan config:clear.

Where to look first: the schema builder and query grammar, not the Eloquent layer. Almost every "why doesn't this work" question is answered by the SQL the grammar generates. Dump it with ->toSql() before you touch the model.

First use case: a scratch library (never production) and a smoke test:

DB::connection('ibmi')->select('select 1 as ok from sysibm.sysdummy1');

Then point an existing app's migrations at that library. As of 1.2.4, php artisan migrate:fresh is the natural first workflow — before this release it aborted with "This database driver does not support dropping all tables", which forced manual drop table scripts or a fresh library per run.

Implementation Patterns

Models — declare the connection explicitly and be honest about keys.

class Order extends Model
{
    protected $connection = 'ibmi';
    protected $table = 'orders';          // matches the created identifier exactly
    protected $primaryKey = 'order_id';
    public $incrementing = false;         // identity/sequence columns often report as such
    protected $keyType = 'string';
    protected $casts = [
        'created_at' => 'datetime',
        'amount'     => 'decimal:2',
        'active'     => 'boolean',
    ];
}

Keep the iSeries connection isolated: set it per model and per query builder call (->connection('ibmi')) rather than making it the app default if you run a second database. Cross-connection joins aren't a thing — join in PHP or use a DB2 view.

Migrations. Split concerns: use the schema builder for portable DDL and DB::statement() for anything DB2-specific (identity definitions, constraints, RCDFMT, tablespaces, COMPRESS).

Schema::connection('ibmi')->create('orders', function (Blueprint $t) {
    $t->string('order_id', 20)->primary();
    $t->string('customer_no', 10)->index();
    $t->decimal('amount', 11, 2);
    $t->timestamp('created_at')->nullable();
});

Repeatable environments. With 1.2.4 you can standardise on migrate:fresh --seed for dev/test libraries, which is a real productivity gain over hand-written drop scripts. Wrap it so it can't run against the wrong target:

if (app()->environment('local', 'testing') && config('database.connections.ibmi.schema') === 'DEVOBJ') {
    Artisan::call('migrate:fresh', ['--seed' => true, '--force' => true]);
}

Query building. Use the builder for CRUD, raw SQL for anything analytical. DB2 for i's optimiser rewards CTEs and FETCH FIRST n ROWS ONLY; write those as DB::select() with bindings and hydrate manually or via Model::fromQuery().

Long-running processes. Queue workers and Octane hold connections open; DB2 for i connections die with the job/host server side. Reconnect defensively and avoid PDO::ATTR_PERSISTENT unless you have measured a need.

Testing. You can't mirror DB2 for i semantics with SQLite. Put DB access behind repositories for unit tests and run a small integration suite against a dedicated CI library — that suite is where migrate:fresh, the schema builder, and your raw SQL actually get exercised.

Gotchas and Tips

dropAllTables() is library-scoped and destructive. 1.2.4 drops every table and view in the current library. On IBM i, libraries are frequently shared between applications and third-party packages, so a misconfigured schema/database turns migrate:fresh into an outage. Confirm the resolved library before running it, and consider granting the app user rights to its own library only.

Objects not found is nearly always identifier case or quoting. Laravel quotes identifiers; unquoted DB2 names fold to uppercase. "orders" and ORDERS are different objects, and SQL naming vs system naming changes what LIB.TABLE means. When you hit SQL0204, inspect the catalog before editing code:

select table_schema, table_name, table_type
from qsys2.systables
where table_schema = 'MYLIB';

Views and dependencies. dropAllViews() handles the views in the library, but ordering matters where views are layered or a table has dependent logical files/constraints. If a drop fails, drop the dependants first rather than retrying blindly.

Booleans, dates and decimals. Pre-7.2 has no BOOLEAN; the driver maps it, but raw SQL you write must match the underlying column type. Don't use whereDate() against character-formatted date columns — cast and compare ranges instead. Always cast decimal explicitly; DB2 returns numerics in ways PHP will happily turn into floats.

Identity columns and lastInsertId. Verify insertGetId behaviour on your version. If it's unreliable, use a sequence (NEXTVAL FOR) or select ... from final table (insert ...) and set $incrementing = false so Eloquent stops guessing.

Migration features that simply don't exist. ->after(), column ->change() semantics, enum, json, fulltext, and spatial types have no DB2 for i equivalent. Expect errors at migration time, not runtime — keep them out of shared migrations or guard them by driver.

truncate and foreign keys. Referential constraints block truncation. Drop constraints, truncate, recreate, or use delete inside a transaction.

Debugging. Register DB::listen() to log slow or failing statements, and read the IBM i job log for the real SQLCODE/SQLSTATE (lock timeouts as SQL0911, authority as SQL0551) — the PDO message is usually a truncated version. Set PDO::ATTR_ERRMODE => ERRMODE_EXCEPTION so failures surface as exceptions rather than silent false.

Extension points. Most DB2-specific needs are met by subclassing the connection/grammar (custom functions, FETCH FIRST, CURRENT TIMESTAMP handling) and registering it via DB::extend('ibmi', ...) in a service provider — cleaner than sprinkling raw SQL through models. Connection-level SET OPTION statements (naming convention, commit control, date format) belong in the connection's post-connect hook, not in application code.

Upgrading to 1.2.4. Backward compatible — no API changes, only new dropAllTables()/dropAllViews() support. After composer update, run php artisan config:clear (cached config will hide the new connection options) and prove out migrate:fresh once against a disposable library before letting it near anything shared.