SQL/MED support in PostgreSQL has produced a large ecosystem of foreign data wrappers; a GitHub search for postgres plus fdw gives a sense of the volume. Maintenance quality varies and many wrappers fall out of use, but db2_fdw remains current and does its main job well: moving data from DB2 into PostgreSQL.
Prerequisites and environment checks
The setup described here uses PostgreSQL 12.2 on Ubuntu 18.04.4. Before the extension itself can be built, the IBM Data Server Client Packages have to be installed and configured. Two checks cover the essentials.
Environment variables:
|
1 2 3 4 5 |
# env |grep DB2 DB2_HOME=/home/user/sqllib DB2LIB=/home/user/sqllib/lib DB2INSTANCE=instance_name |
Connecting to the DB2 instance with the db2 tool:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
# db2 connect to testdb user db2inst1 using my_password Database Connection Information Database server = DB2/LINUXX8664 11.5.0.0 SQL authorization ID = DB2INST1 Local database alias = TESTDB # db2 => SELECT EMPLOYEE_ID, DATE_OF_BIRTH FROM EMPLOYEES LIMIT 5; EMPLOYEE_ID DATE_OF_BIRTH ----------- ------------- 1. 08/19/1971 2. 06/15/1978 3. 12/23/1979 4. 11/14/1979 5. 02/08/1977 5 record(s) selected. |
If the DB2 instance lives on another host, its address must also be listed in pg_hba.conf.
Building and installing the extension
Installation follows the usual steps for building an extension from source:
|
1 2 3 |
# git clone https://github.com/wolfgangbrandl/db2_fdw.git # make # sudo make install |
The extension is then created inside PostgreSQL and verified:
|
1 2 3 4 5 6 7 8 9 10 |
pgdb=# create extension db2_fdw; pgdb=# dx List of installed extensions Name | Version | Schema | Description ---------+---------+------------+------------------------------------- db2_fdw | 1.0 | public | foreign data wrapper for DB2 access plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language (2 rows) |
Mapping DB2 objects into PostgreSQL
Once the client packages and the extension are working, wrapping DB2 inside PostgreSQL requires a foreign server, a user mapping, and an import of the relevant table or schema definitions.
Foreign server and user mapping
|
1 |
pgdb=# CREATE SERVER testdb FOREIGN DATA WRAPPER db2_fdw OPTIONS (dbserver 'testdb'); |
|
1 |
pgdb=# CREATE USER MAPPING FOR PUBLIC SERVER testdb OPTIONS (user 'db2inst1', password 'my_password'); |
Here db2inst1 and my_password are the DB2 server's username and password, and the corresponding schema name is DB2INST1 — all defaults produced by the DB2 setup wizard. They will differ on other installations.
Importing definitions and querying data
Table or schema definitions are imported from the foreign server into the public schema:
|
1 |
pgdb=# IMPORT FOREIGN SCHEMA 'DB2INST1' FROM SERVER testdb INTO public; |
Read access can then be tested directly from PostgreSQL:
|
1 2 3 4 5 6 7 8 9 |
pgdb=# SELECT EMPLOYEE_ID, DATE_OF_BIRTH FROM EMPLOYEES LIMIT 5; employee_id | date_of_birth -------------+--------------- 1 | 1971-08-19 2 | 1978-06-15 3 | 1979-12-23 4 | 1979-11-14 5 | 1977-02-08 (5 rows) |
A foreign table looks like this in the PostgreSQL catalog:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
pgdb=# d public.employees Foreign table 'public.employees' Column | Type | Collation | Nullable | Default | FDW options ---------------+-------------------------+-----------+----------+---------+-------------- employee_id | numeric | | not null | | (key 'true') first_name | character varying(1000) | | not null | | (key 'true') last_name | character varying(1000) | | not null | | (key 'true') date_of_birth | date | | not null | | (key 'true') phone_number | character varying(1000) | | not null | | (key 'true') junk | character(254) | | not null | | (key 'true') t | text | | | | Server: testdb FDW options: (schema 'DB2INST1', 'table' 'EMPLOYEES') |
The FDW options on the table definition are the marker that the relation is not stored locally, while remaining fully usable.
CRUD operations and EXPLAIN
Foreign tables accept CRUD operations, and explain returns details about how a query will be executed. In this case it invokes db2expln, exposing the cost and the access path chosen inside the DB2 engine.
|
1 2 3 4 5 6 7 8 9 |
pgdb=# EXPLAIN SELECT EMPLOYEE_ID, DATE_OF_BIRTH FROM EMPLOYEES LIMIT 5; QUERY PLAN --------------------------------------------------------------------------------------------------- Limit (cost=10000.00..10050.00 rows=5 width=36) -> Foreign Scan on employees (cost=10000.00..20000.00 rows=1000 width=36) DB2 query: SELECT /*30a9058135496929abaa9e871e651ec4*/ r1.'EMPLOYEE_ID', r1.'DATE_OF_BIRTH' FROM 'DB2INST1'.'EMPLOYEES' r1 DB2 plan: Estimated Cost = 70.485092 DB2 plan: Estimated Cardinality = 1000.000000 (5rows) |
Wrap-up
db2_fdw is a mature, straightforward and dependable extension for migrating data out of DB2 and into PostgreSQL, and the steps above show the full path from client-package configuration to querying imported tables.
