Connecting to IBM i DB2 from Linux Using 64-bit IBM i Access ODBC Driver

This comprehensive guide covers multiple methods to connect to IBM i DB2 from Linux, including ODBC, JDBC, Python, Node.js, PHP, and direct SQL session connections.

Prerequisites

64-bit Linux OS (x86_64)
Network access to IBM i system
Valid user credentials
Root/sudo access

Method 1: 64-bit ODBC Connection

Installation & Configuration

# Debian/Ubuntu
sudo apt-get update
sudo apt-get install -y unixodbc unixodbc-dev

# RHEL/CentOS
sudo yum install -y unixODBC unixODBC-devel

# Install 64-bit driver
sudo dpkg -i ibm-iaccess-*_x86_64.deb  # Debian/Ubuntu
sudo rpm -ivh ibm-iaccess-*.x86_64.rpm  # RHEL/CentOS

ODBC Configuration (/etc/odbc.ini)

[IBMI64]
Description = IBM i 64-bit DB2 Connection
Driver = IBM i Access ODBC Driver 64-bit
System = your.ibmi.server
UserID = youruser
Password = yourpassword
Database = *SYSBAS
Naming = 1
CommitMode = 2

Connection Examples

1. Python (pyodbc)

import pyodbc
conn = pyodbc.connect(
    "DRIVER={IBM i Access ODBC Driver 64-bit};"
    "SYSTEM=your.ibmi.server;"
    "UID=youruser;"
    "PWD=yourpassword;"
    "DATABASE=QGPL;"
)
cursor = conn.cursor()
cursor.execute("SELECT * FROM QIWS.QCUSTCDT")
print(cursor.fetchall())

2. Node.js (node-odbc)

const odbc = require('odbc');
const connection = await odbc.connect(
  "DRIVER={IBM i Access ODBC Driver 64-bit};" +
  "SYSTEM=your.ibmi.server;" +
  "UID=youruser;" +
  "PWD=yourpassword;" +
  "DATABASE=QGPL;"
);
const result = await connection.query("SELECT * FROM QIWS.QCUSTCDT");
console.log(result);

3. PHP (unixODBC)

<?php
$conn = odbc_connect("IBMI64", "youruser", "yourpassword");
$sql = "SELECT * FROM QIWS.QCUSTCDT";
$result = odbc_exec($conn, $sql);
while ($row = odbc_fetch_array($result)) {
    print_r($row);
}
odbc_close($conn);
?>

4. Direct SQL Session (Command Line)

# Start interactive SQL session
/opt/ibm/iaccess/bin64/db2cli execsql -connstring "DATABASE=*SYSBAS;HOSTNAME=your.ibmi.server;UID=youruser;PWD=yourpassword"

# Execute single command
/opt/ibm/iaccess/bin64/db2cli execsql -connstring "DATABASE=*SYSBAS;HOSTNAME=your.ibmi.server;UID=youruser;PWD=yourpassword" -sql "SELECT * FROM QIWS.QCUSTCDT"

JDBC Connection (Java)

// Using JTOpen (IBM Toolbox for Java)
String url = "jdbc:as400://your.ibmi.server;libraries=QGPL";
Connection conn = DriverManager.getConnection(url, "youruser", "yourpassword");
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM QIWS.QCUSTCDT");

Troubleshooting

Issue	Solution
Driver not found	Verify path: `/opt/ibm/iaccess/lib64`
Connection timeout	Check firewall and port 8471
Authentication failure	Verify user profile on IBM i

Best Practices

Use TLS encryption for production
Implement connection pooling
Follow principle of least privilege
Consider using SSH tunneling for remote connections

This guide provides multiple approaches to connect to IBM i DB2 from Linux, allowing you to choose the best method for your specific application needs.

ER March 6, 2017 No Comments Database Connections, IBM i, Linux

er400