# DB2

IBM DB2 is a family of data management products, including database servers, developed by IBM. Sling supports connecting to DB2 databases via ODBC with a built-in `db2` template that provides optimized SQL syntax and type mappings.

{% hint style="info" %}
**Prerequisites**: DB2 connections use ODBC under the hood. Make sure you have the ODBC driver manager installed on your system before proceeding. See the [ODBC connection guide](https://docs.slingdata.io/connections/database-connections/odbc) for general ODBC setup instructions and troubleshooting.
{% endhint %}

## Setup

Before using DB2 connections, you must install the IBM DB2 ODBC driver for your operating system.

{% tabs %}
{% tab title="macOS" %}
**Download the Driver**

Download the IBM Data Server Driver for ODBC and CLI from [IBM Fix Central](https://www.ibm.com/support/pages/db2-odbc-cli-driver-download-and-installation-information):

* **Intel Macs**: Download `macos64_odbc_cli.tar.gz` (Db2 11.5)
* **Apple Silicon (M1/M2/M3)**: Download the Db2 12.1 ARM64 driver package

**Install the Driver**

```bash
tar -xzf macos64_odbc_cli.tar.gz
cd odbc_cli/clidriver
./installDSDriver
```

**Set Environment Variables**

Add to `~/.zshrc` or `~/.bash_profile`:

```bash
export DB2_CLI_DRIVER_INSTALL_PATH=/path/to/clidriver
export DYLD_LIBRARY_PATH=$DB2_CLI_DRIVER_INSTALL_PATH/lib:$DYLD_LIBRARY_PATH
```

{% endtab %}

{% tab title="Linux" %}
**Download the Driver**

Download the IBM Data Server Driver for ODBC and CLI from [IBM Fix Central](https://www.ibm.com/support/pages/db2-odbc-cli-driver-download-and-installation-information):

```bash
wget https://public.dhe.ibm.com/ibmdl/export/pub/software/data/db2/drivers/odbc_cli/linuxx64_odbc_cli.tar.gz
```

**Install the Driver**

```bash
tar -xzf linuxx64_odbc_cli.tar.gz
cd odbc_cli/clidriver
./installDSDriver
```

**Install unixODBC**

```bash
# Debian/Ubuntu
sudo apt-get install unixodbc unixodbc-dev

# RHEL/CentOS/Fedora
sudo dnf install unixODBC unixODBC-devel
```

**Register the Driver**

Add to `/etc/odbcinst.ini`:

```ini
[IBM DB2 DRIVER]
Description = IBM DB2 ODBC Driver
Driver = /path/to/clidriver/lib/libdb2o.so
FileUsage = 1
```

**Set Environment Variables**

Add to `~/.bashrc`:

```bash
export DB2_CLI_DRIVER_INSTALL_PATH=/path/to/clidriver
export LD_LIBRARY_PATH=$DB2_CLI_DRIVER_INSTALL_PATH/lib:$LD_LIBRARY_PATH
```

{% endtab %}

{% tab title="Windows" %}
**Download the Driver**

Download the IBM Data Server Driver for ODBC and CLI from [IBM Fix Central](https://www.ibm.com/support/pages/db2-odbc-cli-driver-download-and-installation-information)

**Install the Driver**

1. Extract the compressed file to `C:\Program Files\IBM`
2. Open Command Prompt as Administrator and register the driver:

```cmd
cd "C:\Program Files\IBM\clidriver\bin"
db2oreg1 -i
db2oreg1 -setup
```

This registers the DB2 ODBC driver in Windows ODBC Data Sources.
{% endtab %}
{% endtabs %}

## Connection Properties

* `conn_string` **(required)** -> The ODBC connection string for DB2
* `conn_template` **(required)** -> Set to `db2` to use the built-in DB2 template

### Connection String Parameters

Common parameters for the DB2 ODBC connection string:

| Parameter  | Description                                     |
| ---------- | ----------------------------------------------- |
| `Driver`   | The ODBC driver name (e.g., `{IBM DB2 DRIVER}`) |
| `Hostname` | The DB2 server hostname or IP address           |
| `Port`     | The DB2 server port (default: `50000`)          |
| `Database` | The database name                               |
| `Protocol` | Connection protocol (typically `TCPIP`)         |
| `UID`      | Username for authentication                     |
| `PWD`      | Password for authentication                     |

### Using `sling conns`

{% code overflow="wrap" %}

```bash
sling conns set DB2 type=odbc conn_string='Driver={IBM DB2 DRIVER};Hostname=host.ip;Port=50000;Database=testdb;Protocol=TCPIP;UID=db2inst1;PWD=password;' conn_template=db2
```

{% endcode %}

### Environment Variable

See [here](https://docs.slingdata.io/sling-cli/environment#dot-env-file-.env.sling) to learn more about the `.env.sling` file.

{% code overflow="wrap" %}

```bash
export DB2='{ type: odbc, conn_string: "Driver={IBM DB2 DRIVER};Hostname=host.ip;Port=50000;Database=testdb;Protocol=TCPIP;UID=db2inst1;PWD=password;", conn_template: db2 }'
```

{% endcode %}

### Sling Env File YAML

See [here](https://docs.slingdata.io/sling-cli/environment#sling-env-file-env.yaml) to learn more about the sling `env.yaml` file.

```yaml
connections:
  DB2:
    type: odbc
    conn_string: Driver={IBM DB2 DRIVER};Hostname=host.ip;Port=50000;Database=testdb;Protocol=TCPIP;UID=db2inst1;PWD=password;
    conn_template: db2
```

## Replication Example

Here's an example replication configuration to extract data from DB2 to PostgreSQL:

```yaml
source: DB2
target: MY_POSTGRES

defaults:
  mode: full-refresh
  object: public.{stream_table}

streams:
  # Extract entire table
  myschema.customers:

  # Extract using SQL query
  orders:
    sql: SELECT * FROM myschema.orders WHERE order_date >= '2024-01-01'
    object: public.orders

  # Extract specific columns
  products:
    sql: SELECT product_id, name, price FROM myschema.products
    object: public.products
```

## Troubleshooting

### Driver Not Found

If you receive a "driver not found" error:

1. Verify the driver is installed by checking the ODBC configuration:
   * **Linux/macOS**: Check `/etc/odbcinst.ini` or run `odbcinst -q -d`
   * **Windows**: Open "ODBC Data Sources" from Control Panel
2. Ensure the driver name in your connection string matches exactly (including brackets), e.g., `{IBM DB2 DRIVER}`
3. Verify environment variables are set correctly (`DB2_CLI_DRIVER_INSTALL_PATH`, `LD_LIBRARY_PATH`/`DYLD_LIBRARY_PATH`)

### Connection Timeout

For slow connections, add timeout parameters to your connection string:

```
Driver={IBM DB2 DRIVER};Hostname=host.ip;Port=50000;Database=testdb;Protocol=TCPIP;UID=db2inst1;PWD=password;ConnectTimeout=30;
```

### SSL/TLS Connections

For SSL-enabled DB2 connections, add security parameters:

```
Driver={IBM DB2 DRIVER};Hostname=host.ip;Port=50000;Database=testdb;Protocol=TCPIP;UID=db2inst1;PWD=password;Security=SSL;
```

If you are facing issues connecting, please reach out to us at <support@slingdata.io>, on [discord](https://discord.gg/q5xtaSNDvp) or open a Github Issue [here](https://github.com/slingdata-io/sling-cli/issues).