Connect IBM Db2 to dbt Core

The ibm-dbt-db2 adapter allows you to use dbt to transform and manage data on IBM Db2, leveraging its robust SQL capabilities across LUW (Linux, Unix, Windows), z/OS, and iSeries platforms. Before proceeding, ensure you have the following:

• An active IBM Db2 database instance (LUW, z/OS, or iSeries) with connection details (host, port, database, schema, etc).
• Authentication credentials: Username and password.
• Python 3.10, 3.11, or 3.12 (Python 3.9 not supported due to dbt-core 1.11+ requirements).

Refer to Configuring ibm-dbt-db2 to learn more about obtaining and organizing these details.

• Maintained by: IBM
• Authors: Shubham Kapoor, Amit Kumar
• GitHub repo: IBM/db2-dbt
• PyPI package: ibm-dbt-db2
• Slack channel:
• Supported dbt Core version: v1.11.0 and newer
• dbt support: Not Supported
• Minimum data platform version: IBM Db2 LUW 9.7+, z/OS 11+, iSeries

Installing ibm-dbt-db2

Use pip to install the adapter. Use the following command for installation:

python -m pip install ibm-dbt-db2

Configuring ibm-dbt-db2

For IBM Db2-specific configuration, please refer to IBM Db2 configs.

Connecting to IBM Db2

To connect dbt with IBM Db2, you need to configure a profile in your profiles.yml file located in the .dbt/ directory of your home folder. The following is an example configuration for connecting to IBM Db2 instances:

~/.dbt/profiles.yml

my_db2_project:
  outputs:
    dev:
      type: db2
      host: [hostname]
      port: 50000
      database: [database name]
      schema: [schema name]
      username: [username]
      password: [password]
      threads: [1 or more]

  target: dev

Connection with SSL/TLS

For secure connections, you can configure SSL/TLS parameters:

~/.dbt/profiles.yml

my_db2_project:
  outputs:
    prod:
      type: db2
      host: [hostname]
      port: 50001
      database: [database name]
      schema: [schema name]
      username: [username]
      password: [password]
      threads: [1 or more]
      # SSL/TLS settings
      security: SSL
      ssl_server_certificate: /path/to/server-ca.crt
      ssl_client_hostname_validation: true
      retries: 3

  target: prod

Connection using DSN

Alternatively, you can use a Data Source Name (DSN) configured in your Db2 client:

~/.dbt/profiles.yml

my_db2_project:
  outputs:
    dev:
      type: db2
      dsn: MY_DB2_DSN
      schema: [schema name]
      threads: [1 or more]

  target: dev

Host parameters

Use the following required and optional profile fields to configure IBM Db2 connections.

Option	Required/Optional	Description	Example
type	Required	The adapter type. Must be db2.	db2
host	Required*	Hostname for connecting to Db2.	localhost
port	Optional	The port for connecting to Db2.	50000
database	Required	The database name in your Db2 instance.	SAMPLE
schema	Required	The schema name within your Db2 instance database.	ADMIN
username	Required	Username for authentication.	db2user
password	Required	Password for authentication.	password
threads	Optional	Number of threads for parallel execution.	8
dsn	Optional	Data Source Name (alternative to host/port/database).	MY_DB2_DSN

Loading table...

*Not required if using dsn

Optional SSL/TLS Parameters

Option	Required/Optional	Description	Example
security	Optional	Security protocol. Use SSL to enable SSL/TLS.	SSL
ssl_server_certificate	Optional	Path to server CA certificate file for SSL verification.	/path/to/server-ca.crt
ssl_client_keystore	Optional	Path to client keystore database (.kdb file) for mutual TLS.	/path/to/client.kdb
ssl_client_keystash	Optional	Path to client keystash file (.sth file).	/path/to/client.sth
ssl_client_hostname_validation	Optional	Enable hostname verification (true/false).	true
retries	Optional	Number of connection retry attempts.	3

Loading table...

Schemas and databases

When selecting the database and the schema, make sure you have read and write access to both. This selection does not limit your ability to query the database. Instead, they serve as the default location for where tables and views are materialized.

Supported features

Feature	Supported	Notes
Table materialization	✅ Yes
View materialization	✅ Yes
Incremental materialization	✅ Yes	Supports merge and delete+insert strategies
Ephemeral materialization	✅ Yes
Seeds	✅ Yes	Requires explicit data type configuration
Snapshots	✅ Yes
Tests	✅ Yes
Documentation	✅ Yes
Sources	✅ Yes
Custom schemas	✅ Yes
Grants	✅ Yes
Constraints	⚠️ Partial	NOT NULL enforced, others not enforced

Loading table...

Data types

The adapter maps dbt data types to Db2 types:

dbt Type	Db2 Type
string	VARCHAR
text	VARCHAR(max_length)
integer	INTEGER
bigint	BIGINT
float	FLOAT
numeric	DECIMAL
boolean	BOOLEAN
timestamp	TIMESTAMP
date	DATE
time	TIME

Loading table...

Incremental models

The ibm-dbt-db2 adapter supports two incremental strategies:

1. merge (default) - Uses Db2's MERGE statement for efficient upserts
2. delete+insert - Deletes matching records then inserts new ones

Example incremental model:

{{
  config(
    materialized='incremental',
    unique_key='id',
    incremental_strategy='merge'
  )
}}

SELECT * FROM source_table
{% if is_incremental() %}
WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }})
{% endif %}

Case sensitivity

Db2 uppercases unquoted identifiers by default. The adapter handles this automatically, but be aware:

• Unquoted table/column names will be uppercased
• Use quotes in your SQL to preserve case: "MyTable" vs MYTABLE

Notes

• The ibm-dbt-db2 adapter is built on the ibm_db Python driver (version 3.2.8) which is automatically installed with the adapter.
• Python Version Requirements: Requires Python 3.10, 3.11, or 3.12. Python 3.9 is not supported due to dbt Core v1.11+ dependency requirements. Python 3.13+ has not been tested yet.
• Constraints: CHECK, UNIQUE, PRIMARY KEY, and FOREIGN KEY constraints are defined but not enforced by Db2 in the dbt context. Only NOT NULL constraints are enforced.
• LISTAGG Limitation: Db2's LISTAGG function does not support limiting the number of aggregated values.

Was this page helpful?

Privacy policyCreate a GitHub issue

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.