Federating DB2 and PostgreSQL

In its 9.7 release, InfoSphere Federation Server added a "JDBC wrapper." This is an interface that lets the Federation Server access a database through that database's JDBC driver. Someone asked me if I thought PostgreSQL could be accessed this way and then federated with DB2. I couldn't see why not, so I gave it a try.

I found the official documentation in the Information Center to be a little light. However, a quick email to the friendly Federation Server team got me the guidance I needed. I was then easily able to federate DB2 and PostgreSQL tables. The SQL script at the end of this post reflects the information provided by the Federation Server team. It also ends with queries that join DB2 and PostgreSQL tables.

To be fair, I did hit two problems with the level of Federation Server I installed. Both happened when I tried to create nicknames:

1. Create nickname failed when PostgreSQL table columns had been dropped.
2. Create nickname failed when I had a primary key on a PostgreSQL table.

These may sound major, but they have simple work arounds:

1. Drop and recreate the PostgreSQL table to resolve the first problem.
2. For the second, drop the primary key then create the nickname and add the primary key to the table and nickname.

Better yet, the Federation Server team gave me a fix... :)

If you have a copy of Federation Server, you can use the following instructions and one of my DB2 SQL scripts to federate DB2 and PostgreSQL yourself:

1. Install Federation Server on the same system as your PostgreSQL server (make sure you select the JDBC wrapper).
2. Update your Federation Server instance's database manager configuration to have Federate Yes.
3. Start your Federation Server instance (db2start).
4. Create the Sample database (db2sample).
5. Connect to the sample database.
6. Cut and paste the DB2 SQL script at the bottom of this post into a file.
7. Edit the file.
8. Change the following on the Create Server statement:
-- Set Driver_Package to the full path of your PostgreSQL JDBC driver.
-- Set URL to the JDBC URL of your PostgreSQL databsae.
9. Change the Create User Mapping statement so that is uses your PostgreSQL user and password instead of mine :)
10. Save your changes.
11. Assume you named the file postg.sql.
12. From a DB2 command window, run: db2 -vtf postg.sql >mystdout.txt
13. Look at mystdout.txt to see the results.

Here's the DB2 SQL script you can cut and paste into a file:

-- -- Overall assumptions: -- ..You've installed the Federation Server's JDBC wrapper -- ..You've set the instance's dbm cfg to Federate Yes -- ..You've connected to a db2sampl (e.g., SAMPLE) database -- drop wrapper jdbc; create wrapper jdbc; -- Create Server notes: -- ..uses Driver_Package to point to PostgreSQL JDBC driver -- ..uses 'localhost' in URL -- ..uses DB2_* options to allow -- .. ..positioned update and delete against nicknames -- .. ..(and varchar_no_trailing_blanks for the same reason) -- drop server mypostg; CREATE SERVER mypostg TYPE JDBC VERSION '3.0' WRAPPER JDBC OPTIONS( ADD DRIVER_PACKAGE 'C:\Program Files\PostgreSQL\pgJDBC\postgresql-8.4-701.jdbc3.jar', URL 'jdbc:postgresql://localhost:5432/postgres', DRIVER_CLASS 'org.postgresql.Driver', DB2_IUD_ENABLE 'Y', db2_char_blankpadded_comparison 'Y', db2_varchar_blankpadded_comparison 'Y', VARCHAR_NO_TRAILING_BLANKS 'Y', JDBC_LOG 'Y'); CREATE USER MAPPING FOR tolleson SERVER mypostg OPTIONS ( REMOTE_AUTHID 'postgres', REMOTE_PASSWORD 'user your own! :)'); Commit; -- -- Create some tables in PostgreSQL: -- ..Generate SQL from DB2 Sample database using db2look -- ..Modify SQL to use PostgreSQL data types compatible with DB2 datatypes -- ..Make table and column names lower case as PostgreSQL users like -- .. ..Use an editor. Or a tool like awk, e.g.: -- type departmentddl.sql | awk '{print tolower($0)}' >department.sql -- .. .. Edit out any DDL you don't need -- .. .. (mostly everything past create table) -- set passthru mypostg; DROP TABLE employee; CREATE TABLE employee ( empno character(6) NOT NULL, firstnme character varying(12) NOT NULL, midinit character(1), lastname character varying(15) NOT NULL, workdept character(3), phoneno character(4), hiredate date, job character(8), edlevel smallint NOT NULL, sex character(1), birthdate date, salary numeric(9,2), bonus numeric(9,2), comm numeric(9,2) ) WITH ( OIDS=FALSE ); ALTER TABLE employee OWNER TO postgres; ALTER TABLE employee ADD CONSTRAINT pk_employee PRIMARY KEY (empno); drop table department; create table department ( deptno character(3) not null , deptname character varying(36) not null , mgrno character(6) , admrdept character(3) not null , location character(16) ) WITH ( OIDS=FALSE ); ALTER TABLE department OWNER TO postgres; ALTER TABLE department ADD CONSTRAINT pk_department PRIMARY KEY (deptno); set passthru reset; -- -- create nicknames for our PostgreSQL tables -- drop nickname myemp; drop nickname mydept; CREATE NICKNAME MYEMP FOR mypostg."public"."employee"; CREATE NICKNAME MYDEPT FOR mypostg."public"."department"; -- -- Populate Postgres tables with data from DB2 tables -- delete from myemp; delete from mydept; insert into myemp select * from employee; insert into mydept select * from department; -- -- Join 1: Both tables in local DB2 database -- select ee.empno, ee.lastname, ee.firstnme, dd.deptname from employee ee, department dd where dd.deptno='E21' and ee.workdept=dd.deptno; -- -- Join 2: First table in DB2, second in PostgreSQL -- select ee.empno, ee.lastname, ee.firstnme, dd."deptname" from employee ee, mydept dd where dd."deptno"='E21' and ee.workdept=dd."deptno"; -- -- Join 3: First table in PostgreSQL, second in DB2 -- select ee."empno", ee."lastname", ee."firstnme", dd.deptname from myemp ee, department dd where dd.deptno='E21' and ee."workdept"=dd.deptno; -- -- Join 4: Both tables in PostgreSQL -- select ee."empno", ee."lastname", ee."firstnme", dd."deptname" from myemp ee, mydept dd where dd."deptno"='E21' and ee."workdept"=dd."deptno"; Commit;

Tags: PostgreSQL, data virtualization, fast start, federation

0 members like this

Comment

You need to be a member of ChannelDB2 to add comments!

Join ChannelDB2

Comment by Raghu Cherukuru on January 15, 2013 at 4:25pm: Hello David, Thank you for posting this blog.

We implemented a similar setup at our shop. Replication is not working as expected (or postgres is not behaving as other DBMS), this is for a situation where a SQL apply program is updating a non-existent row on target nickname, in this case usually on DB2 or other DBMS such as Oracle or SQL server it will be reworked as an insert.

But this is not happening with postgres. Postgres is returning a SQLCODE of 0 where it should be returning a SQLCODE of 100 and SQLSTATE of 02000 for a non-existent row update. We need to get this scenario working. Did you hear any work arounds for this ? Appreciate your time and response.

Thanks!
Raghu.

Comment by Joy Kafka on July 13, 2010 at 10:40am: Thank you for your reply. After a lot of tweaking, I've got it working. Strange thing though, I could only get Federation Server to create 2 nicknames at a time without an error. So, after running a lot of iterations, I have it going.

Thank you again,
Joyela

Comment by david t on July 12, 2010 at 8:15pm: Hi, Joyela,

I have a new machine and had to install new copies of Federation Server (9.7) and PostgreSQL (8.4). When I did, I applied Fix Pack 2 (FP2) to the Federation Server instead of the special build I received from the developers I ran the script in my post and did not have the problems I reported in my post. I will double check with the developers, but I believe this means the fixes are all in FP2. You can download it here:

http://www-01.ibm.com/support/docview.wss?rs=71&uid=swg24026928

I will add a comment when I hear from the developers

thanks
david

Comment by Joy Kafka on July 7, 2010 at 2:08pm: I have a question regarding the nickname error you describe in this post. I'm running Federation Server 9.7.1 with postgres 8.4.1. I've tried drop and recreate the PostgreSQL table to resolve the first problem, then create the nickname and add the primary key to the table (within 'set passthru' in my script) and I've tried creating the primary key within the nickname using the ALTER NICKNAME sql for db2, which results in an error and does not create the key.

First, can I have further information on creating the nickname and add the primary key to the table and nickname, and second, can you point me in the direction of the fix that was provided to you?

Thank you very much,
Joyela