CYBERTEC Logo

Wrapping DB2 with PostgreSQL

06.2020 / Category: / Tags: |

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.

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.

Setting up and testing the environment

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.

  • Environment variables.
  • Connection to DB2 instance with db2 tool.

And remember that if DB2 instance is in another network you should also add it to pg_hba.conf.

Install extension

Same steps as when installing other extensions from the source code.

Now we'll create the extension in PostgreSQL, verify it and that's it.

Expose DB2 to PostgreSQL

If the previous warm-up was successful then there is nothing stopping us now. So to wrap db2 inside PostgreSQL follow the following steps:

  • Define a new foreign server
    • Define a new mapping of user to a foreign server.

    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.

    • Import table or schema definitions from a foreign server into public schema:
    • and finally, test the data:

    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:

    Pay special attention to FDW options, which tell us that this table is not from here, but we can work with it.

    Use explain

    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.

    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.

    0 0 votes
    Article Rating
    Subscribe
    Notify of
    guest
    5 Comments
    Oldest
    Newest Most Voted
    Inline Feedbacks
    View all comments
    Akshay Pande
    Akshay Pande
    2 years ago

    As we have alias in DB2 ..is there any similar thing as well in postgre as well

    Hans-Jürgen Schönig
    Hans-Jürgen Schönig
    2 years ago
    Reply to  Akshay Pande

    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?

    Akshay Pande
    Akshay Pande
    2 years ago

    Yes

    Akshay Pande
    Akshay Pande
    2 years ago
    Reply to  Akshay Pande

    Create synonym kind things

    Balaji
    Balaji
    2 years ago

    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?

    CYBERTEC Logo white
    CYBERTEC PostgreSQL International GmbH
    Römerstraße 19
    2752 Wöllersdorf
    Austria

    +43 (0) 2622 93022-0
    office@cybertec.at

    Get the newest PostgreSQL Info & Tools


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

      ©
      2024
      CYBERTEC PostgreSQL International GmbH
      phone-handsetmagnifiercrosscross-circle
      linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram