{"id":7298,"library":"ibm-db-sa","title":"SQLAlchemy Dialect for IBM Data Servers (ibm-db-sa)","description":"ibm-db-sa is a SQLAlchemy dialect that enables Python applications to connect to IBM Db2 for LUW, Db2 on Cloud, and Informix data servers. It provides an interface for SQLAlchemy's ORM and core features, leveraging the `ibm_db` DBAPI driver for database communication. The current version is 0.4.4, with releases typically following updates in its core dependencies, `ibm_db` and `SQLAlchemy`.","status":"active","version":"0.4.4","language":"en","source_language":"en","source_url":"https://github.com/ibmdb/python-ibmdb-sa","tags":["database","SQLAlchemy","IBM","Db2","Informix","orm","dbapi"],"install":[{"cmd":"pip install ibm_db_sa","lang":"bash","label":"Install ibm-db-sa"}],"dependencies":[{"reason":"Required DBAPI driver for IBM Data Servers.","package":"ibm_db"},{"reason":"Core ORM and database toolkit framework.","package":"SQLAlchemy"}],"imports":[{"note":"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.","symbol":"ibm_db_sa dialect","correct":"from sqlalchemy import create_engine\nengine = create_engine('ibm_db_sa://...')"}],"quickstart":{"code":"import os\nfrom sqlalchemy import create_engine, text\n\n# Get credentials from environment variables\n# Ensure these are set in your environment or replace with actual values\nDB2_USER = os.environ.get('DB2_USER', 'db2inst1')\nDB2_PASSWORD = os.environ.get('DB2_PASSWORD', 'password')\nDB2_HOST = os.environ.get('DB2_HOST', 'localhost')\nDB2_PORT = os.environ.get('DB2_PORT', '50000')\nDB2_DATABASE = os.environ.get('DB2_DATABASE', 'SAMPLE')\nINFORMIX_SERVER = os.environ.get('INFORMIX_SERVER', '') # Optional for Informix\n\n# Construct the connection string\nconnection_string = f\"ibm_db_sa://{DB2_USER}:{DB2_PASSWORD}@{DB2_HOST}:{DB2_PORT}/{DB2_DATABASE}\"\nif INFORMIX_SERVER:\n    connection_string += f\"?INFORMIXSERVER={INFORMIX_SERVER}\"\n\nprint(f\"Attempting to connect to: {connection_string.split('@')[-1].split('?')[0]}\")\n\ntry:\n    engine = create_engine(connection_string)\n    with engine.connect() as connection:\n        # Example query for Db2. For Informix, adjust query (e.g., SELECT FIRST 1 current FROM systables)\n        result = connection.execute(text(\"SELECT CURRENT_TIMESTAMP AS current_time FROM SYSIBM.SYSDUMMY1\"))\n        for row in result:\n            print(f\"Successfully connected! Current Db2 timestamp: {row.current_time}\")\nexcept Exception as e:\n    print(f\"Error connecting or executing query: {e}\")\n    print(\"Please ensure your Db2/Informix server is running, credentials are correct, and necessary client drivers are installed.\")","lang":"python","description":"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."},"warnings":[{"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.","message":"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.","severity":"breaking","affected_versions":"<0.4.0"},{"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.","message":"`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.","severity":"gotcha","affected_versions":"All versions"},{"fix":"When connecting to Informix, append `?INFORMIXSERVER=<servername>` to your connection string. Example: `ibm_db_sa://user:pass@host:port/database?INFORMIXSERVER=myserver`.","message":"Connecting to Informix databases requires specific connection string parameters, most notably `INFORMIXSERVER` if not implicitly configured.","severity":"gotcha","affected_versions":"All versions"},{"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.","message":"SSL/TLS connections to Db2 require specific configuration within the connection string parameters or potentially environment variables/client configuration files, which are often overlooked.","severity":"gotcha","affected_versions":"All versions"}],"env_vars":null,"last_verified":"2026-04-16T00:00:00.000Z","next_check":"2026-07-15T00:00:00.000Z","problems":[{"fix":"Verify the `DB2_DATABASE` (or equivalent) parameter in your connection string matches the actual database name on your Db2 server.","cause":"The specified database name in the connection string is incorrect, or the database server is not configured to recognize it.","error":"[IBM][CLI Driver] SQL10007N The database alias or database name \"\" was not found. SQLSTATE=08001"},{"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).","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.","error":"SQLSTATE=08001 SQLCODE=-30081"},{"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).","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.","error":"ImportError: DLL load failed while importing ibm_db: The specified module could not be found."},{"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.","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.","error":"sqlalchemy.exc.DBAPIError: (ibm_db_sa.base.DB2SAException) ibm_db_sa://<username>:***@<host>:<port>/<database> (ibm_db_sa://<username>:***@<host>:<port>/<database>)"}]}