Since SQL/MED (Management External Data) was implemented in PostgreSQL, hundreds of projects have emerged that try to connect PostgreSQL with other data sources. Just by doing a simple search on GitHub with the keys “postgres” + “fdw" you can figure that out.
Table of Contents
Sadly not all extensions are well maintained and as a consequence they are deprecated. Fortunately, the extension that we have worked on db2_fdw and with which I've achieved to connect to DB2 is updated and works well for its main objective: Migrating db2's data to PostgreSQL.
From the next paragraph and if you follow our tips you'll also be able to read data from DB2 and expose it to PostgreSQL, so we'll see how to do it.
For this particular case, PostgreSQL 12.2 was used on Ubuntu 18.04.4 and the first important tip is that you need to install "IBM Data Server Client Packages". It must be configured properly before installing the extension. Here we got some tips.
|
1 2 3 4 5 |
# env |grep DB2 DB2_HOME=/home/user/sqllib DB2LIB=/home/user/sqllib/lib DB2INSTANCE=instance_name |
|
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. |
And remember that if DB2 instance is in another network you should also add it to pg_hba.conf.
Same steps as when installing other extensions from the source code.
|
1 2 3 |
# git clone https://github.com/wolfgangbrandl/db2_fdw.git # make # sudo make install |
Now we'll create the extension in PostgreSQL, verify it and that's it.
|
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) |
If the previous warm-up was successful then there is nothing stopping us now. So to wrap db2 inside PostgreSQL follow the following steps:
|
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'); |
Important: db2inst1 and my_password are DB2 database server username and password, respectively. In the next item we will see that the name of the schema is: DB2INST1. They are values created by default by the db2 wizard setup. In your particular case these values may differ.
|
1 |
pgdb=# IMPORT FOREIGN SCHEMA 'DB2INST1' FROM SERVER testdb INTO public; |
|
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) |
As you can see we've achieved to connect to the DB2 instance and get data, now we'll see what these kinds of tables look like in PostgreSQL:
|
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') |
Pay special attention to FDW options, which tell us that this table is not from here, but we can work with it.
In fact, we can perform CRUD operations on these tables, and we can even have detailed information about the query through explain. It makes a call to db2expln and gives us information about the cost and access that is used internally in the DB2 engine. We can see that below.
|
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) |
In conclusion, we can say that db2_fdw is a mature, simple and reliable extension to migrate data from DB2 to PostgreSQL as you have seen here. If you found it interesting that how fdw is useful to connect to external data then I invite you to look at this post.
Read on to find out more in Hans-Jürgen Schönig's blog Understanding Lateral Joins in PostgreSQL.
In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Facebook or LinkedIn.
You need to load content from reCAPTCHA to submit the form. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from Facebook. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from X. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More Information
As we have alias in DB2 ..is there any similar thing as well in postgre as well
hello. do you mean "CREATE SYNONYM"? in postgresql this is on the list of "undesired features". i helped implement that 10 years ago but as stated ... it is most likely never going to happen. may i ask about your use case?
Yes
Create synonym kind things
Hi, Can we perform all SQL operations in DB2 Foreign Tables with PosteSQL in addition to CRUD? WHERE Clause, JOINING, ORDER BY, SCALAR Function etc? Is this extension is licensed by PostgreSQL?