ibm_db Node.js Driver

The Pool class allows for efficient management of multiple database connections. You can initialize the pool, open connections asynchronously or synchronously, and close the entire pool.

Key Methods:

• Pool.open(connectionString, [callback]): Returns a Database instance. If no callback is provided, it returns a Promise.
• Pool.openSync(connectionString): Synchronously returns a Database instance.
• Pool.init(N, connStr): Synchronously initializes the pool with N active connections.
• Pool.initAsync(N, connStr, [callback]): Asynchronously initializes the pool with N active connections.
• Pool.setMaxPoolSize(N): Sets the maximum number of connections allowed in the pool.
• Pool.close([callback]): Asynchronously closes all connections in the pool.
• Pool.closeSync(): Synchronously closes all connections in the pool.

const Pool = require("ibm_db").Pool;
const pool = new Pool();
const cn = "DATABASE=dbname;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=dbuser;PWD=xxx";

// Asynchronous usage with async/await
try {
  await pool.initAsync(1, cn);
  let conn = await pool.open(cn);
  let data = await conn.query("select 1 from sysibm.sysdummy1");
  console.log("data = ", data);
} catch(err) {
  console.log(err);
}

The ibm_db.Pool is a built-in connection pooling mechanism. It attempts to keep database connections ready for use when open() is called.

Key Behaviors:

• Reuse: When you call .close() on a Database instance obtained from a pool, the connection is not destroyed; instead, it is immediately added back to the pool of available connections.
• Efficiency: The next time you call Pool.open() with the same connection string, the pool will reuse an existing available connection.
• Concurrency: For applications requiring multiple simultaneous connections, it is highly recommended to use Pool.open() rather than the top-level ibmdb.open() method.
• Queueing: If you reach the maximum number of connections defined by setMaxPoolSize(), subsequent open() or openSync() requests will be queued and will wait until a connection is returned to the pool or a timeout occurs.

You can improve performance for large result sets by using SQLBindCol-based block fetching. By setting SQL_ATTR_ROW_ARRAY_SIZE to a value greater than 1 on a prepared statement, ibm_db retrieves multiple rows per SQLFetch() call, reducing CLI round-trips.

Important Considerations:

• All fetch APIs (fetch, fetchSync, fetchAll, fetchAllSync, fetchN, fetchNSync) automatically honor this attribute.
• LOB Limitation: Block fetch uses pre-allocated buffers. For columns with very large declared sizes (like CLOB/BLOB), the buffer per element is capped at 64KB. If you need to retrieve larger LOB values, use the default single-row fetch (do not set SQL_ATTR_ROW_ARRAY_SIZE).

API Behavior with SQL_ATTR_ROW_ARRAY_SIZE > 1:

• fetchSync(): Returns an array of up to N rows (or arrays if FETCH_ARRAY is used). Returns null when done.
• fetch(): Returns an array of up to N rows via callback or Promise. Returns null when done.
• fetchAllSync() / fetchAll(): Returns a flat array of all rows (block fetch is used internally).
• fetchNSync(count) / fetchN(count): Returns a flat array of count rows.

// Set SQL_ATTR_ROW_ARRAY_SIZE to fetch 10 rows per SQLFetch() call
const stmt = conn.prepareSync("select * from mytable order by id");
stmt.setAttrSync(ibmdb.SQL_ATTR_ROW_ARRAY_SIZE, 10);
const result = stmt.executeSync();

// fetchSync returns an array of up to 10 rows per call
let block;
while ((block = result.fetchSync()) !== null) {
  console.log(`Block of ${block.length} rows:`, block);
}

To send large data in chunks or handle data-at-execution parameters, use the paramData() and putData() workflow. This is necessary when parameters are bound with the SQL_DATA_AT_EXEC indicator.

Typical Workflow:

1. Bind parameters with SQL_DATA_AT_EXEC indicator.
2. Execute the statement (returns with SQL_NEED_DATA state).
3. Call paramData() to get the parameter that needs data.
4. Call putData() one or more times to send the data in chunks.
5. Call paramData() again to proceed to the next parameter or complete execution.
6. Repeat until paramData() returns { needsData: false }.

// Example using paramData with callback
ibmdb.open(cn, function(err, db) {
  db.conn.createStatement(function(err, stmt) {
    // After binding data-at-execution parameters and executing...
    stmt.paramData(function(err, result) {
      if (err) {
        console.log(err);
        return;
      }
      if (result.needsData) {
        console.log("Need data for parameter:", result.paramIndex);
        // Call putData() to send the data...
      } else {
        console.log("All parameters satisfied, statement executed.");
      }
      stmt.closeSync();
      db.closeSync();
    });
  });
});

node-ibm_db is an asynchronous/synchronous interface for Node.js to connect to IBM DB2 and IBM Informix. It supports various platforms including Windows, MacOS (Intel and Silicon), Linux, AIX, and z/OS.

On most platforms (Linuxx64, MacARM64, Mac-x64, and Windows-x64), the package attempts to compile native code first but will automatically fall back to a precompiled binary if a compiler or required dependencies are missing.

Note on clidriver versions:

• ibm_db@3.3.0+: Automatically downloads v12.1.x clidriver. This requires a v12.1 db2connect license.
• Older licenses: If you need to use an older db2connect license, you can force the installation of the v11.5.9 clidriver using the -clidriver flag.
• MacOS Silicon: Supported from ibm_db@3.3.0 onwards using v12.x clidriver.
• MacOS Intel: Uses v11.x clidriver (defaults to v11.5.9).

# To use an older db2connect license with v11.5.9 clidriver:
npm install ibm_db -clidriver="v11.5.9"

For Row-wise Array Inserts, pass an object as the sqlQuery containing a rows property. The rows property is an array of arrays, where each inner array represents a single row of data.

• Basic usage: Pass rows instead of params and ArraySize.
• With metadata: Optionally pass a columns array to provide type metadata for each column (e.g., {DataType: "INTEGER"}).

If you are using the prepare/bind/execute pattern, you should use ibmdb.convertRowsToColumns() to manually convert your row-based data into column-wise ARRAY parameters before binding.

// Row-wise array insert: pass data as rows instead of column-wise arrays.
conn.querySync("create table rowtab (c1 int, c2 double, c3 varchar(10))");

const rowOptions = {
  sql: "insert into rowtab values (?, ?, ?)",
  rows: [
    [1, 10.5, "Row 1"],
    [2, 20.3, "Row 2"],
    [3, 30.7, "Row 3"]
  ]
};
conn.querySync(rowOptions);

// With columns metadata:
const rowOptions2 = {
  sql: "insert into rowtab values (?, ?, ?)",
  columns: [
    {DataType: "INTEGER"},
    {DataType: "DOUBLE"},
    {DataType: "VARCHAR", Length: 10}
  ],
  rows: [
    [4, 40.1, "Row 4"],
    [5, 50.2, "Row 5"]
  ]
};
conn.querySync(rowOptions2);