db2_fdw

Overview

Version

Install

You can install db2_fdw directly. First, make sure the PGDG repository is added and enabled:

pig repo add pgdg -u          # Add PGDG repo and update cache

Install the extension using pig or apt/yum/dnf:

• Install
• pig
• dnf

pig install db2_fdw;          # Install for current active PG version

pig ext install -y db2_fdw -v 18  # PG 18
pig ext install -y db2_fdw -v 17  # PG 17
pig ext install -y db2_fdw -v 16  # PG 16
pig ext install -y db2_fdw -v 15  # PG 15
pig ext install -y db2_fdw -v 14  # PG 14

dnf install -y db2_fdw_18       # PG 18
dnf install -y db2_fdw_17       # PG 17
dnf install -y db2_fdw_16       # PG 16
dnf install -y db2_fdw_15       # PG 15
dnf install -y db2_fdw_14       # PG 14

Create Extension:

CREATE EXTENSION db2_fdw;

Usage

db2_fdw: Foreign data wrapper for DB2 access

Create Server

CREATE EXTENSION db2_fdw;

CREATE SERVER db2srv FOREIGN DATA WRAPPER db2_fdw
  OPTIONS (dbserver 'SAMPLE');

Server Options: dbserver (required, DB2 database connection string).

Create User Mapping

CREATE USER MAPPING FOR PUBLIC SERVER db2srv
  OPTIONS (user 'db2inst1', password 'secret');

Use empty strings for user and password to enable external authentication.

Create Foreign Table

CREATE FOREIGN TABLE employee (
  empno char(6) OPTIONS (key 'true'),
  firstname varchar(12),
  lastname varchar(15),
  salary numeric
)
SERVER db2srv
OPTIONS (schema 'DB2INST1', table 'EMPLOYEE');

Table Options: table (required, DB2 table name, case-sensitive, typically uppercase), schema (table owner), readonly (default false), prefetch (rows per round-trip, default 200, range 0-10240), max_long (max LONG column length, default 32767).

Column Options: key (set to true for primary key columns, required for UPDATE/DELETE).

Import Foreign Schema

IMPORT FOREIGN SCHEMA "DB2INST1" FROM SERVER db2srv INTO public;

Import Options: case (keep, lower, or smart, default smart), readonly.

CRUD Operations

SELECT * FROM employee WHERE empno = '000010';
INSERT INTO employee (empno, firstname, lastname, salary) VALUES ('999999', 'John', 'Doe', 50000);
UPDATE employee SET salary = 55000 WHERE empno = '999999';
DELETE FROM employee WHERE empno = '999999';

Data Type Mapping

WHERE conditions and column projections are pushed down to DB2 to minimize data transfer.