Db2

Sync and async IBM Db2 adapter built on ibm_db. It binds positional parameters (?), pools connections, reflects the Db2 catalog, and compiles SQL through SQLSpec's built-in db2 SQLGlot dialect.

Supported Databases

• Db2 for Linux, UNIX and Windows (LUW) 11.5 and later. Catalog queries read the SYSCAT views, and feature detection assumes the 11.5 SQL level.

• Db2 for z/OS and Db2 for IBM i are not supported. They expose different catalogs and SQL, and SQLSpec does not test against them.

SQLSpec's integration suite runs the Db2 adapter against Db2 Community Edition 11.5.9 alongside the other databases; the test container is started automatically and needs a Docker daemon that allows privileged containers.

Installation

pip install "sqlspec[db2]"

The extra installs ibm_db 3.3.0 or later, which bundles the IBM CLI driver (clidriver). Wheels are published for CPython 3.9 through 3.14. Linux wheels are manylinux_2_34 x86_64 because the bundled CLI driver needs glibc 2.34 or newer. On other platforms pip builds ibm_db from source: set CLIDRIVER_VERSION=v11.5.9 to download a compatible CLI driver, or point IBM_DB_HOME at an existing Db2 client installation.

Quick Start

from sqlspec import SQLSpec
from sqlspec.adapters.db2 import Db2SyncConfig

spec = SQLSpec()
db = spec.add_config(
    Db2SyncConfig(
        connection_config={
            "database": "SAMPLE",
            "hostname": "db2.example.com",
            "port": 50000,
            "user": "db2inst1",
            "password": "secret",
        }
    )
)

with spec.provide_session(db) as session:
    rows = session.select("SELECT id, name FROM users WHERE active = ?", 1)

Use Db2AsyncConfig with async with sessions for asyncio applications. It runs on ibm_db_dbi.AsyncConnection and accepts the same connection parameters plus the async pool settings below.

from sqlspec.adapters.db2 import Db2AsyncConfig

config = Db2AsyncConfig(
    connection_config={
        "database": "SAMPLE",
        "hostname": "db2.example.com",
        "user": "db2inst1",
        "password": "secret",
        "max_size": 10,
        "acquire_timeout": 30.0,
    }
)


async def active_users() -> list[dict]:
    async with config.provide_session() as session:
        return await session.select("SELECT id, name FROM users WHERE active = ?", 1)

Connection Parameters

Each parameter renders as one IBM CLI connection keyword. Unknown keys raise ImproperConfigurationError when the config is created.

Parameter	CLI keyword	Notes
database	DATABASE	Required, directly or through dsn.
hostname	HOSTNAME	Omit it to connect to a cataloged database alias.
port	PORT	Defaults to 50000 when hostname is set.
protocol	PROTOCOL	Defaults to TCPIP when hostname is set.
user / password	UID / PWD
current_schema	CURRENTSCHEMA	Default schema for unqualified names.
security	SECURITY	"SSL" enables TLS.
ssl_server_certificate	SSLSERVERCERTIFICATE	Path to the server certificate (ARM or PEM file).
authentication	AUTHENTICATION	For example "SERVER_ENCRYPT".
connect_timeout	CONNECTTIMEOUT	Seconds.
autocommit	(none)	Autocommit mode new connections open in. Defaults to True.
dsn	(parsed)	KEY=VALUE;... string or db2://user:password@host:port/database?Key=Value URL. Explicit parameters override values parsed from it.
extra	(verbatim)	Additional CLI keywords, for example {"ClientApplName": "billing"}.

Values that contain ; or {, or that start or end with whitespace, are wrapped in braces when the connection string is rendered, so passwords such as "pa;ss" work as written. CLI connection strings cannot represent }; a value containing it raises ImproperConfigurationError.

TLS example:

from sqlspec.adapters.db2 import Db2SyncConfig

config = Db2SyncConfig(
    connection_config={
        "database": "SAMPLE",
        "hostname": "db2.example.com",
        "port": 50001,
        "user": "db2inst1",
        "password": "secret",
        "security": "SSL",
        "ssl_server_certificate": "/etc/db2/server.arm",
        "current_schema": "APP",
    }
)

config.get_connection_string() returns the rendered CLI connection string.

Connection Pooling

Db2SyncConfig keeps one connection per thread that uses the config, including worker threads that run async_() wrappers. The pool has no maximum size: the number of open connections equals the number of threads that have used it. Connections are replaced after pool_recycle_seconds (default 86400) and pinged with SELECT 1 FROM SYSIBM.SYSDUMMY1 before reuse once idle for health_check_interval seconds (default 30).

Db2AsyncConfig holds at most max_size connections (default 10). A session that cannot get a connection within acquire_timeout seconds (default 30) raises ConnectionTimeoutError. Recycling and health checks use the same two settings as the sync pool.

Transactions

Connections open in autocommit mode by default, so each statement outside a transaction commits immediately.

• session.begin() turns autocommit off for the unit of work; commit() and rollback() end it and turn autocommit back on.

• session.transaction() wraps a block in begin()/commit() and rolls back when the block raises. Nested blocks use savepoints.

• provide_session(transaction=True) starts the session inside a transaction.

• A session that exits with an unfinished transaction rolls it back and restores the connection's autocommit mode before the connection returns to the pool.

with config.provide_session() as session:
    with session.transaction():
        session.execute("INSERT INTO users (id, name) VALUES (?, ?)", 101, "Ada")
        session.execute("UPDATE accounts SET owner_id = ? WHERE id = ?", 101, 7)

Set "autocommit": False in connection_config to make every session a single unit of work that you commit explicitly.

Result Column Names

Db2 folds unquoted identifiers to uppercase, so SELECT id FROM users reports a column named ID. With enable_lowercase_column_names (default True), names that consist only of uppercase letters, digits, and underscores are lowercased in result rows, so row["id"] works. Quoted mixed-case names such as "MixedCase" are kept as written. Set driver_features={"enable_lowercase_column_names": False} to keep the names Db2 reports.

The Db2 Dialect

SQLSpec registers a db2 SQLGlot dialect. Use it to transpile SQL written for other databases or with the query builder (sql.select(..., dialect="db2")):

import sqlglot

import sqlspec.dialects.db2  # noqa: F401

print(sqlglot.transpile("SELECT id FROM users ORDER BY id LIMIT 10 OFFSET 20", read="postgres", write="db2")[0])
# SELECT id FROM users ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY
print(sqlglot.transpile("SELECT CURRENT_TIMESTAMP + INTERVAL '1 day'", read="postgres", write="db2")[0])
# SELECT CURRENT TIMESTAMP + 1 DAY FROM SYSIBM.SYSDUMMY1

The dialect covers:

• FETCH FIRST n ROWS ONLY and OFFSET m ROWS FETCH NEXT n ROWS ONLY paging.

• SYSIBM.SYSDUMMY1 for SELECT statements without a FROM clause, and VALUES statements.

• Special registers (CURRENT TIMESTAMP, CURRENT DATE, CURRENT SCHEMA, CURRENT SERVER, CURRENT TIMEZONE) and labeled durations (CURRENT DATE + 1 DAYS - 2 MONTHS).

• Isolation and lock clauses (WITH UR, WITH CS, WITH RS, WITH RR, USE AND KEEP ... LOCKS), which round-trip unchanged.

• Db2 types such as DECFLOAT, GRAPHIC, VARGRAPHIC, DBCLOB, CLOB and BLOB.

• MERGE statements for builder upserts (sql.upsert(..., dialect="db2")).

Row locks from the query builder translate to Db2 isolation clauses:

• for_update() renders WITH RS USE AND KEEP UPDATE LOCKS.

• for_share() renders WITH RS USE AND KEEP SHARE LOCKS.

• skip_locked=True appends SKIP LOCKED DATA.

• nowait=True and of=... raise SQLBuilderError. Db2 has no NOWAIT; set CURRENT LOCK TIMEOUT on the session instead.

from sqlspec import sql

query = (
    sql.select("id", "payload", dialect="db2")
    .from_("jobs")
    .where("status = 'new'")
    .order_by("id")
    .limit(5)
    .for_update(skip_locked=True)
)
print(query.build().sql)
# ... FETCH FIRST 5 ROWS ONLY WITH RS USE AND KEEP UPDATE LOCKS SKIP LOCKED DATA

Warning

IBM's db2-sqlglot-dialect package registers the same db2 entry point in sqlglot.dialects and pins sqlglot<30.10, which conflicts with SQLSpec's sqlglot>=30.13 requirement. Do not install both packages in one environment.

Data Dictionary

session.data_dictionary reads the SYSCAT catalog views. Unqualified lookups use CURRENT SCHEMA.

with config.provide_session() as session:
    tables = session.data_dictionary.get_tables(session)
    columns = session.data_dictionary.get_columns(session, table="USERS")
    foreign_keys = session.data_dictionary.get_foreign_keys(session, table="ORDERS")

Arrow

session.select_to_arrow() converts result rows into a pyarrow.Table. The conversion runs in Python; for columnar reads straight from the database, use the arrow-odbc adapter with the Db2 ODBC driver (see its IBM Db2 section).

Extensions

Both configs provide stores for the Litestar session backend, the events queue, and the Google ADK session and memory services. Stores create their tables on first use, and every timestamp they write is stored in UTC.

Extension	Sync config	Async config
Litestar sessions	sqlspec.adapters.db2.litestar.Db2SyncStore	sqlspec.adapters.db2.litestar.Db2AsyncStore
Events queue	sqlspec.adapters.db2.events.Db2SyncEventQueueStore	sqlspec.adapters.db2.events.Db2AsyncEventQueueStore
ADK sessions	sqlspec.adapters.db2.adk.Db2SyncADKStore	sqlspec.adapters.db2.adk.Db2AsyncADKStore
ADK memory	sqlspec.adapters.db2.adk.Db2SyncADKMemoryStore	sqlspec.adapters.db2.adk.Db2AsyncADKMemoryStore

SQLSpec migrations run on both configs.

API Reference

Configuration

class sqlspec.adapters.db2.Db2SyncConfig

Bases: SyncDatabaseConfig[Connection, Db2SyncConnectionPool, Db2SyncDriver]

Configuration for IBM Db2 synchronous connections.

driver_type

alias of Db2SyncDriver

connection_type

alias of Connection

migration_tracker_type

alias of Db2SyncMigrationTracker

__init__(*, connection_config=None, connection_instance=None, migration_config=None, statement_config=None, driver_features=None, bind_key=None, extension_config=None, observability_config=None, **kwargs)

Initialize Db2 configuration.

create_connection()

Open a standalone connection owned by the caller.

Returns:

A newly opened physical connection.

Return type:

Connection

get_connection_string()

Generate a valid IBM Db2 CLI DSN connection string.

Return type:

str

get_signature_namespace()

Get namespace for dependency injection resolution.

Return type:

dict[str, typing.Any]

get_event_runtime_hints()

Return runtime hints for Db2 event channels.

Return type:

EventRuntimeHints

class sqlspec.adapters.db2.Db2AsyncConfig

Bases: AsyncDatabaseConfig[AsyncConnection, Db2AsyncConnectionPool, Db2AsyncDriver]

Configuration for IBM Db2 asynchronous connections over ibm_db_dbi.AsyncConnection.

driver_type

alias of Db2AsyncDriver

connection_type

alias of AsyncConnection

migration_tracker_type

alias of Db2AsyncMigrationTracker

__init__(*, connection_config=None, connection_instance=None, migration_config=None, statement_config=None, driver_features=None, bind_key=None, extension_config=None, observability_config=None, **kwargs)

Initialize Db2 async configuration.

max_size and acquire_timeout are kept for the pool; every other key is validated and normalized like the sync configuration's connection parameters.

async create_connection()

Open a standalone connection owned by the caller.

Returns:

A newly opened ibm_db_dbi.AsyncConnection.

Return type:

AsyncConnection

get_connection_string()

Generate a valid IBM Db2 CLI DSN connection string.

Return type:

str

get_signature_namespace()

Get namespace for dependency injection resolution.

Return type:

dict[str, typing.Any]

get_event_runtime_hints()

Return runtime hints for Db2 event channels.

Return type:

EventRuntimeHints

class sqlspec.adapters.db2.Db2ConnectionParams

Bases: TypedDict

IBM Db2 connection parameters.

Each modeled parameter renders under one CLI keyword:

database: DATABASE. Required, either directly or through dsn. hostname: HOSTNAME. Omit it to connect to a cataloged database alias. port: PORT. Defaults to 50000 when hostname is set. protocol: PROTOCOL. Defaults to TCPIP when hostname is set. user: UID. password: PWD. current_schema: CURRENTSCHEMA. security: SECURITY, for example "SSL". ssl_server_certificate: SSLSERVERCERTIFICATE. authentication: AUTHENTICATION. connect_timeout: CONNECTTIMEOUT in seconds. autocommit: Autocommit mode new connections start in. Never rendered into the DSN. dsn: KEY=VALUE;... connection string or db2://user:password@host:port/database?Key=Value

URL. Explicit parameters override values parsed from it.

extra: Additional CLI keywords rendered verbatim after the modeled parameters.

class sqlspec.adapters.db2.Db2PoolParams

Bases: Db2ConnectionParams

IBM Db2 pool parameters.

pool_recycle_seconds: Seconds after which a pooled connection is replaced. Defaults to 86400. health_check_interval: Idle seconds after which a pooled connection is pinged before reuse.

Defaults to 30.0.

class sqlspec.adapters.db2.Db2AsyncPoolParams

Bases: Db2PoolParams

IBM Db2 async pool parameters.

max_size: Maximum number of connections checked out or being opened at once. Defaults to 10. acquire_timeout: Seconds to wait for a free connection before raising

ConnectionTimeoutError. Defaults to 30.0.

class sqlspec.adapters.db2.Db2DriverFeatures

Bases: TypedDict

IBM Db2 driver feature flags.

json_serializer: Custom JSON serializer function.

Defaults to sqlspec.utils.serializers.to_json.

json_deserializer: Custom JSON deserializer function.

Defaults to sqlspec.utils.serializers.from_json.

on_connection_create: Callback executed when a connection is created.

Receives the raw Db2 connection (ibm_db_dbi.Connection for Db2SyncConfig, ibm_db_dbi.AsyncConnection for Db2AsyncConfig) for low-level driver configuration. Runs after connection creation; Db2AsyncConfig awaits an awaitable result.

enable_events: Enable database event channel support. events_backend: Event channel backend selection. enable_lowercase_column_names: Normalize implicit uppercase column names to lowercase.

Defaults to True.

Drivers

class sqlspec.adapters.db2.Db2SyncDriver

Bases: SyncDriverAdapterBase

IBM Db2 database driver.

__init__(connection, statement_config=None, driver_features=None)

Initialize driver adapter with connection and configuration.

Parameters:

• connection (Any) -- Database connection instance

• statement_config (StatementConfig | None) -- Statement configuration for the driver

• driver_features (dict[str, typing.Any] | None) -- Driver-specific features like extensions, secrets, and connection callbacks

• observability -- Optional runtime handling lifecycle hooks, observers, and spans

dispatch_execute(cursor, statement)

Execute a single SQL statement.

Must be implemented by each driver for database-specific execution logic.

Parameters:

• cursor (Any) -- Database cursor/connection object

• statement (SQL) -- SQL statement object with all necessary data and configuration

Return type:

ExecutionResult

Returns:

ExecutionResult with execution data

dispatch_execute_many(cursor, statement)

Execute SQL with multiple parameter sets (executemany).

Must be implemented by each driver for database-specific executemany logic.

Parameters:

• cursor (Any) -- Database cursor/connection object

• statement (SQL) -- SQL statement object with all necessary data and configuration

Return type:

ExecutionResult

Returns:

ExecutionResult with execution data for the many operation

dispatch_execute_script(cursor, statement)

Execute a SQL script containing multiple statements.

Default implementation splits the script and executes statements individually. Drivers can override for database-specific script execution methods.

Parameters:

• cursor (Any) -- Database cursor/connection object

• statement (SQL) -- SQL statement object with all necessary data and configuration

Return type:

ExecutionResult

Returns:

ExecutionResult with script execution data including statement counts

begin()

Begin a transaction by turning autocommit off for its duration.

Does nothing while a transaction started by this driver is active. When the connection was in autocommit mode, commit() and rollback() switch it back on.

Raises:

SQLSpecError -- When the driver reports an error.

Return type:

None

commit()

Commit the current unit of work and restore the autocommit baseline.

Raises:

SQLSpecError -- When the driver reports an error.

Return type:

None

rollback()

Roll back the current unit of work and restore the autocommit baseline.

Raises:

SQLSpecError -- When the driver reports an error.

Return type:

None

release_open_work(*, autocommit_baseline)

Roll back work left open before the connection is returned to its pool.

A transaction started by this driver is always rolled back; on a connection whose autocommit baseline is off, any pending unit of work is rolled back as well. A rollback failure is logged and not raised, so it never masks an error from the session body.

Parameters:

autocommit_baseline (bool) -- Autocommit mode the connection was opened in.

Return type:

None

with_cursor(connection)

Create and return a context manager for cursor acquisition and cleanup.

Returns a context manager that yields a cursor for database operations. Concrete implementations handle database-specific cursor creation and cleanup.

Return type:

Db2SyncCursor

handle_database_exceptions()

Handle database-specific exceptions and wrap them appropriately.

Return type:

Db2SyncExceptionHandler

Returns:

Exception handler with deferred exception pattern for mypyc compatibility. The handler stores mapped exceptions in pending_exception rather than raising from __exit__ to avoid ABI boundary violations.

dispatch_select_stream(statement, chunk_size)

Return a native Db2 row stream backed by cursor.fetchmany().

Return type:

Optional[SyncRowStream[dict[str, typing.Any]]]

create_savepoint(name)

Create a transaction savepoint retaining open cursors.

Return type:

None

set_migration_session_schema(schema)

Switch the session's current schema, remembering the schema in effect on the first switch.

Parameters:

schema (str) -- Schema to make current. Unquoted all-lowercase names fold to uppercase.

Return type:

None

reset_migration_session_schema()

Restore the current schema captured by set_migration_session_schema.

Return type:

None

has_schema(schema)

Return whether the schema exists in the catalog.

Parameters:

schema (str) -- Schema name. Unquoted all-lowercase names fold to uppercase.

Return type:

bool

Returns:

True when SYSCAT.SCHEMATA lists the schema.

property data_dictionary: Db2SyncDataDictionary

Return the Db2 data dictionary bound to this driver.

Returns:

The lazily created data dictionary instance.

collect_rows(cursor, fetched)

Collect rows from cursor after fetchall for the direct execution path.

Adapters should override this method to provide optimized row collection that bypasses full dispatch_execute overhead.

Parameters:

• cursor (Any) -- Database cursor with description metadata.

• fetched (list[typing.Any]) -- Rows returned from cursor.fetchall().

Return type:

tuple[list[typing.Any], list[str], int]

Returns:

Tuple of (data, column_names, row_count).

Raises:

NotImplementedError -- If the adapter does not implement this method.

resolve_rowcount(cursor)

Resolve the number of affected rows from cursor for the direct execution path.

Adapters should override this method to provide optimized rowcount resolution that bypasses full dispatch_execute overhead.

Parameters:

cursor (Any) -- Database cursor with rowcount metadata.

Return type:

int

Returns:

Number of affected rows, or 0 when unknown.

Raises:

NotImplementedError -- If the adapter does not implement this method.

class sqlspec.adapters.db2.Db2AsyncDriver

Bases: AsyncDriverAdapterBase

IBM Db2 async database driver over ibm_db_dbi.AsyncConnection.

__init__(connection, statement_config=None, driver_features=None)

Initialize driver adapter with connection and configuration.

Parameters:

• connection (Any) -- Database connection instance

• statement_config (StatementConfig | None) -- Statement configuration for the driver

• driver_features (dict[str, typing.Any] | None) -- Driver-specific features like extensions, secrets, and connection callbacks

• observability -- Optional runtime handling lifecycle hooks, observers, and spans

async dispatch_execute(cursor, statement)

Execute a single SQL statement.

Must be implemented by each driver for database-specific execution logic.

Parameters:

• cursor (Any) -- Database cursor/connection object

• statement (SQL) -- SQL statement object with all necessary data and configuration

Return type:

ExecutionResult

Returns:

ExecutionResult with execution data

async dispatch_execute_many(cursor, statement)

Execute SQL with multiple parameter sets (executemany).

Must be implemented by each driver for database-specific executemany logic.

Parameters:

• cursor (Any) -- Database cursor/connection object

• statement (SQL) -- SQL statement object with all necessary data and configuration

Return type:

ExecutionResult

Returns:

ExecutionResult with execution data for the many operation

async dispatch_execute_script(cursor, statement)

Execute a SQL script containing multiple statements.

Default implementation splits the script and executes statements individually. Drivers can override for database-specific script execution methods.

Parameters:

• cursor (Any) -- Database cursor/connection object

• statement (SQL) -- SQL statement object with all necessary data and configuration

Return type:

ExecutionResult

Returns:

ExecutionResult with script execution data including statement counts

async begin()

Begin a transaction by turning autocommit off for its duration.

Does nothing while a transaction started by this driver is active. When the connection was in autocommit mode, commit() and rollback() switch it back on.

Raises:

SQLSpecError -- When the driver reports an error.

Return type:

None

async commit()

Commit the current unit of work and restore the autocommit baseline.

Raises:

SQLSpecError -- When the driver reports an error.

Return type:

None

async rollback()

Roll back the current unit of work and restore the autocommit baseline.

Raises:

SQLSpecError -- When the driver reports an error.

Return type:

None

async release_open_work(*, autocommit_baseline)

Roll back work left open before the connection is returned to its pool.

A transaction started by this driver is always rolled back; on a connection whose autocommit baseline is off, any pending unit of work is rolled back as well. A rollback failure is logged and not raised, so it never masks an error from the session body.

Parameters:

autocommit_baseline (bool) -- Autocommit mode the connection was opened in.

Return type:

None

with_cursor(connection)

Create and return an async context manager for cursor acquisition and cleanup.

Returns an async context manager that yields a cursor for database operations. Concrete implementations handle database-specific cursor creation and cleanup.

Return type:

Db2AsyncCursor

handle_database_exceptions()

Handle database-specific exceptions and wrap them appropriately.

Return type:

Db2AsyncExceptionHandler

Returns:

Exception handler with deferred exception pattern for mypyc compatibility. The handler stores mapped exceptions in pending_exception rather than raising from __aexit__ to avoid ABI boundary violations.

dispatch_select_stream(statement, chunk_size)

Return a native Db2 row stream backed by AsyncCursor.fetchmany().

Return type:

Optional[AsyncRowStream[dict[str, typing.Any]]]

async create_savepoint(name)

Create a transaction savepoint retaining open cursors.

Return type:

None

async set_migration_session_schema(schema)

Switch the session's current schema, remembering the schema in effect on the first switch.

Parameters:

schema (str) -- Schema to make current. Unquoted all-lowercase names fold to uppercase.

Return type:

None

async reset_migration_session_schema()

Restore the current schema captured by set_migration_session_schema.

Return type:

None

async has_schema(schema)

Return whether the schema exists in the catalog.

Parameters:

schema (str) -- Schema name. Unquoted all-lowercase names fold to uppercase.

Return type:

bool

Returns:

True when SYSCAT.SCHEMATA lists the schema.

property data_dictionary: Db2AsyncDataDictionary

Return the Db2 async data dictionary bound to this driver.

Returns:

The lazily created data dictionary instance.

collect_rows(cursor, fetched)

Collect rows from cursor after fetchall for the direct execution path.

Adapters should override this method to provide optimized row collection that bypasses full dispatch_execute overhead.

Parameters:

• cursor (Any) -- Database cursor with description metadata.

• fetched (list[typing.Any]) -- Rows returned from cursor.fetchall().

Return type:

tuple[list[typing.Any], list[str], int]

Returns:

Tuple of (data, column_names, row_count).

Raises:

NotImplementedError -- If the adapter does not implement this method.

resolve_rowcount(cursor)

Resolve the number of affected rows from cursor for the direct execution path.

Adapters should override this method to provide optimized rowcount resolution that bypasses full dispatch_execute overhead.

Parameters:

cursor (Any) -- Database cursor with rowcount metadata.

Return type:

int

Returns:

Number of affected rows, or 0 when unknown.

Raises:

NotImplementedError -- If the adapter does not implement this method.

Connection Pools

class sqlspec.adapters.db2.pool.Db2SyncConnectionPool

Bases: object

Thread-local connection manager for IBM Db2.

__init__(connection_parameters, recycle_seconds=86400, health_check_interval=30.0, on_connection_create=None)

Initialize the thread-local connection manager.

Parameters:

• connection_parameters (dict[str, typing.Any]) -- Normalized Db2 connection parameters. The CLI connection string is rendered from them once, here; autocommit (default True) sets the autocommit mode every new connection opens in.

• recycle_seconds (int) -- Connection recycle time in seconds (default 24h).

• health_check_interval (float) -- Seconds of idle time before running health check.

• on_connection_create (typing.Callable[[MyTypeAliasForwardRef('typing.Any')], None] | None) -- Callback executed when connection is created.

new_connection()

Open a standalone connection configured like a pooled one.

The connection opens in the pool's autocommit mode.

The result is owned by the caller: it is not thread-local and is not tracked for pool shutdown.

Returns:

A newly opened, fully configured Db2 connection.

Return type:

Any

Raises:

MissingDependencyError -- When ibm_db is not installed.

get_connection()

Context manager to yield a thread-local connection.

Yields:

A thread-local Db2 database connection.

close()

Close every connection this pool opened across all threads.

Return type:

None

acquire()

Acquire a thread-local connection.

Return type:

Any

release(connection)

Release connection back to the thread-local pool.

Return type:

None

size()

Return the count of active connections allocated to the current thread.

Return type:

int

checked_out()

Return the number of checked out connections from the perspective of this thread.

Return type:

int

class sqlspec.adapters.db2.pool.Db2AsyncConnectionPool

Bases: object

Bounded asyncio pool of ibm_db_dbi.AsyncConnection objects.

At most max_size connections are checked out or being opened at once; callers waiting longer than acquire_timeout get ConnectionTimeoutError. Idle connections are reused most-recently-released first, replaced once older than recycle_seconds, and pinged when idle for longer than health_check_interval. Closing the pool closes idle connections immediately and checked-out connections when they are released.

__init__(connection_parameters, *, max_size=10, acquire_timeout=30.0, recycle_seconds=86400, health_check_interval=30.0, on_connection_create=None)

Initialize the pool.

Parameters:

• connection_parameters (dict[str, typing.Any]) -- Normalized Db2 connection parameters. The CLI connection string is rendered from them once, here; autocommit (default True) sets the autocommit mode every new connection opens in.

• max_size (int) -- Maximum number of connections checked out or being opened at once.

• acquire_timeout (float) -- Seconds to wait for a free slot before raising.

• recycle_seconds (int) -- Connection age in seconds after which it is replaced (0 disables).

• health_check_interval (float) -- Seconds of idle time before a connection is pinged on reuse.

• on_connection_create (typing.Callable[[MyTypeAliasForwardRef('typing.Any')], Awaitable[None] | None] | None) -- Callback run on every new connection; awaited when it returns an awaitable.

async new_connection()

Open a standalone connection configured like a pooled one.

The connection opens in the pool's autocommit mode and the creation hook runs on it. The result is owned by the caller and is not tracked by the pool.

Returns:

A newly opened ibm_db_dbi.AsyncConnection.

Return type:

Any

Raises:

MissingDependencyError -- When ibm_db is not installed.

async acquire()

Check a connection out of the pool.

Returns:

A pooled ibm_db_dbi.AsyncConnection.

Return type:

Any

Raises:

• DatabaseConnectionError -- When the pool is closed.

• ConnectionTimeoutError -- When no slot frees up within acquire_timeout seconds.

async release(connection)

Return a checked-out connection to the pool.

Connections the pool did not hand out are ignored. After close() the connection is closed instead of being kept.

Parameters:

connection (Any) -- Connection previously returned by acquire().

Return type:

None

get_connection()

Return an async context manager that acquires and releases a pooled connection.

Returns:

The connection context manager.

Return type:

Db2AsyncPoolConnectionContext

async close()

Close the pool and every idle connection.

Checked-out connections are closed when they are released.

Return type:

None

size()

Return the number of open connections owned by the pool.

Return type:

int

checked_out()

Return the number of connections currently checked out.

Return type:

int

Data Dictionaries

class sqlspec.adapters.db2.Db2SyncDataDictionary

Bases: SyncDataDictionaryBase

IBM Db2 sync data dictionary for metadata reflection.

dialect: ClassVar[str] = 'db2'

Dialect identifier. Must be defined by subclasses as a class attribute.

__init__()

Initialize Db2 sync data dictionary.

get_metadata_capabilities(driver, domains=None)

Get Db2 data-dictionary capability profile.

Return type:

MetadataCapabilityProfile

get_version(driver)

Get Db2 database version information.

The instance service level is parsed once per driver and cached. Query errors propagate as mapped driver errors.

Parameters:

driver (Db2SyncDriver) -- Db2 driver.

Return type:

Db2VersionInfo | None

Returns:

The parsed version, or None when the service level cannot be parsed.

get_feature_flag(driver, feature)

Check whether Db2 supports a feature.

Return type:

bool

get_optimal_type(driver, type_category)

Get optimal Db2 type for a category.

Return type:

str

list_available_features()

List available feature flags for this dialect.

Return type:

list[str]

get_tables(driver, schema=None)

Get tables sorted by dependency order with catalog fallback.

Return type:

list[TableMetadata]

get_columns(driver, table=None, schema=None)

Get columns for a table or schema from SYSCAT.COLUMNS.

Return type:

list[ColumnMetadata]

get_indexes(driver, table=None, schema=None)

Get indexes for a table or schema from SYSCAT.INDEXES and SYSCAT.INDEXCOLUSE.

Return type:

list[IndexMetadata]

get_foreign_keys(driver, table=None, schema=None)

Get foreign keys from SYSCAT.REFERENCES and SYSCAT.KEYCOLUSE.

Return type:

list[ForeignKeyMetadata]

get_constraints(driver, table=None, schema=None)

Get Db2 constraint metadata.

Return type:

MetadataResult

get_views(driver, schema=None)

Get Db2 view metadata.

Return type:

MetadataResult

get_objects(driver, schema=None)

Get tables, views, aliases, sequences and routines from the Db2 catalog.

Parameters:

• driver (Db2SyncDriver) -- Db2 driver.

• schema (str | None) -- Schema to list; defaults to the session's CURRENT SCHEMA.

Return type:

MetadataResult

Returns:

Objects-domain metadata result.

get_schemas(driver)

Get Db2 schema metadata.

Return type:

MetadataResult

class sqlspec.adapters.db2.Db2AsyncDataDictionary

Bases: AsyncDataDictionaryBase

IBM Db2 async data dictionary for metadata reflection.

dialect: ClassVar[str] = 'db2'

Dialect identifier. Must be defined by subclasses as a class attribute.

__init__()

Initialize Db2 async data dictionary.

async get_metadata_capabilities(driver, domains=None)

Get Db2 data-dictionary capability profile.

Return type:

MetadataCapabilityProfile

async get_version(driver)

Get Db2 database version information.

The instance service level is parsed once per driver and cached. Query errors propagate as mapped driver errors.

Parameters:

driver (Db2AsyncDriver) -- Async Db2 driver.

Return type:

Db2VersionInfo | None

Returns:

The parsed version, or None when the service level cannot be parsed.

async get_feature_flag(driver, feature)

Check whether Db2 supports a feature.

Return type:

bool

async get_optimal_type(driver, type_category)

Get optimal Db2 type for a category.

Return type:

str

list_available_features()

List available feature flags for this dialect.

Return type:

list[str]

async get_tables(driver, schema=None)

Get tables sorted by dependency order with catalog fallback.

Return type:

list[TableMetadata]

async get_columns(driver, table=None, schema=None)

Get columns for a table or schema from SYSCAT.COLUMNS.

Return type:

list[ColumnMetadata]

async get_indexes(driver, table=None, schema=None)

Get indexes for a table or schema from SYSCAT.INDEXES and SYSCAT.INDEXCOLUSE.

Return type:

list[IndexMetadata]

async get_foreign_keys(driver, table=None, schema=None)

Get foreign keys from SYSCAT.REFERENCES and SYSCAT.KEYCOLUSE.

Return type:

list[ForeignKeyMetadata]

async get_constraints(driver, table=None, schema=None)

Get Db2 constraint metadata.

Return type:

MetadataResult

async get_views(driver, schema=None)

Get Db2 view metadata.

Return type:

MetadataResult

async get_objects(driver, schema=None)

Get tables, views, aliases, sequences and routines from the Db2 catalog.

Parameters:

• driver (Db2AsyncDriver) -- Async Db2 driver.

• schema (str | None) -- Schema to list; defaults to the session's CURRENT SCHEMA.

Return type:

MetadataResult

Returns:

Objects-domain metadata result.

async get_schemas(driver)

Get Db2 schema metadata.

Return type:

MetadataResult