SQLAlchemy Dialect for IBM Data Servers (ibm-db-sa)

Common errors

• [IBM][CLI Driver] SQL10007N The database alias or database name "" was not found. SQLSTATE=08001

  cause The specified database name in the connection string is incorrect, or the database server is not configured to recognize it.
  fix Verify the `DB2_DATABASE` (or equivalent) parameter in your connection string matches the actual database name on your Db2 server.

• SQLSTATE=08001 SQLCODE=-30081

  cause A connection to the database server could not be established. This typically indicates an incorrect host, port, or that the database server is not running or accessible from the client.
  fix Check the `DB2_HOST` and `DB2_PORT` values in your connection string. Ensure the Db2 server is running and network access is permitted from your client machine (e.g., firewall rules).

• ImportError: DLL load failed while importing ibm_db: The specified module could not be found.

  cause On Windows, this error for `ibm_db` usually means the IBM Data Server Client libraries are not installed or are not discoverable in the system's PATH. Similar errors on Linux/macOS (`libdb2.so` not found) point to missing shared libraries or `LD_LIBRARY_PATH` issues.
  fix Install the appropriate IBM Data Server Driver Package for your operating system. Ensure its `bin` (or equivalent) directory is added to your system's PATH environment variable (Windows) or `LD_LIBRARY_PATH` (Linux/macOS).

• sqlalchemy.exc.DBAPIError: (ibm_db_sa.base.DB2SAException) ibm_db_sa://<username>:***@<host>:<port>/<database> (ibm_db_sa://<username>:***@<host>:<port>/<database>)

  cause This generic error from `ibm_db_sa` often wraps an underlying `ibm_db` error (like `SQLCODE=-20000`, `SQLSTATE=08001`). It indicates a failure during the DBAPI connection or initial setup, commonly due to incorrect credentials.
  fix Carefully re-check your username and password in the connection string. Ensure they are correct for the specified database and user. Also, verify that the user has necessary permissions to connect to the database.

Warnings

• breaking Older versions of ibm_db_sa might have compatibility issues with SQLAlchemy 2.0's idiom for statement execution without `text()`. Always wrap literal SQL strings with `text()` for forward compatibility.
  Fix: Ensure all literal SQL strings are wrapped with `sqlalchemy.text()` (e.g., `connection.execute(text('SELECT 1'))`) for SQLAlchemy 1.4+ / 2.0 compatibility.
• gotcha `ibm_db_sa` relies on the `ibm_db` driver, which often requires pre-installed IBM Data Server Client or Driver Package (e.g., CLI driver) on the system. Without these, `ibm_db` might fail to install or connect.
  Fix: Before installing `ibm_db` or `ibm_db_sa`, ensure you have the appropriate IBM Data Server Client or Driver Package installed and configured for your operating system. Refer to the `ibm_db` documentation for specific installation instructions.
• gotcha Connecting to Informix databases requires specific connection string parameters, most notably `INFORMIXSERVER` if not implicitly configured.
  Fix: When connecting to Informix, append `?INFORMIXSERVER=<servername>` to your connection string. Example: `ibm_db_sa://user:pass@host:port/database?INFORMIXSERVER=myserver`.
• gotcha SSL/TLS connections to Db2 require specific configuration within the connection string parameters or potentially environment variables/client configuration files, which are often overlooked.
  Fix: For SSL connections, add `SECURITY=SSL;SSLCLIENTKEYSTOREDB=/path/to/keystore.kdb;SSLCLIENTKEYSTASH=/path/to/keystore.sth` or similar parameters to your connection string's query part (e.g., `?security=SSL;...`). Consult IBM Db2 documentation for full SSL setup.

Install

• pip install ibm_db_sa Install ibm-db-sa

Imports

• ibm_db_sa dialect

  from sqlalchemy import create_engine
  engine = create_engine('ibm_db_sa://...')

  ibm_db_sa is a SQLAlchemy dialect, not a module with direct symbols to import. Its functionality is accessed via the SQLAlchemy `create_engine` function using the 'ibm_db_sa' scheme in the connection string.

Quickstart

This quickstart demonstrates how to establish a connection to an IBM Db2 or Informix database using `ibm-db-sa` with SQLAlchemy. It retrieves connection parameters from environment variables for security and flexibility. The example executes a simple query to fetch the current timestamp, printing the result or any connection error.

import os
from sqlalchemy import create_engine, text

# Get credentials from environment variables
# Ensure these are set in your environment or replace with actual values
DB2_USER = os.environ.get('DB2_USER', 'db2inst1')
DB2_PASSWORD = os.environ.get('DB2_PASSWORD', 'password')
DB2_HOST = os.environ.get('DB2_HOST', 'localhost')
DB2_PORT = os.environ.get('DB2_PORT', '50000')
DB2_DATABASE = os.environ.get('DB2_DATABASE', 'SAMPLE')
INFORMIX_SERVER = os.environ.get('INFORMIX_SERVER', '') # Optional for Informix

# Construct the connection string
connection_string = f"ibm_db_sa://{DB2_USER}:{DB2_PASSWORD}@{DB2_HOST}:{DB2_PORT}/{DB2_DATABASE}"
if INFORMIX_SERVER:
    connection_string += f"?INFORMIXSERVER={INFORMIX_SERVER}"

print(f"Attempting to connect to: {connection_string.split('@')[-1].split('?')[0]}")

try:
    engine = create_engine(connection_string)
    with engine.connect() as connection:
        # Example query for Db2. For Informix, adjust query (e.g., SELECT FIRST 1 current FROM systables)
        result = connection.execute(text("SELECT CURRENT_TIMESTAMP AS current_time FROM SYSIBM.SYSDUMMY1"))
        for row in result:
            print(f"Successfully connected! Current Db2 timestamp: {row.current_time}")
except Exception as e:
    print(f"Error connecting or executing query: {e}")
    print("Please ensure your Db2/Informix server is running, credentials are correct, and necessary client drivers are installed.")