Postgresql通過fdw_JDBC連接查詢DB2\Oracle表

Postgresql通過fdw_JDBC連接查詢DB2\Oracle表

一、查找libjvm.so共態庫
[root@db2 jdbc_fdw-1.0.0]# locate libjvm.so
/opt/IBM/tsamp/sam/java/jre/bin/classic/libjvm.so
/opt/IBM/tsamp/sam/java/jre/bin/j9vm/libjvm.so
/opt/ibm/db2/V9.7/itma/JRE/lx8266/bin/classic/libjvm.so
/opt/ibm/db2/V9.7/itma/JRE/lx8266/bin/j9vm/libjvm.so
/opt/ibm/db2/V9.7/java/jdk64/jre/bin/classic/libjvm.so
/opt/ibm/db2/V9.7/java/jdk64/jre/bin/j9vm/libjvm.so
/opt/ibm/db2/V9.7/java/jdk64/jre/lib/amd64/classic/libjvm.so
/opt/ibm/db2/V9.7/java/jdk64/jre/lib/amd64/compressedrefs/libjvm.so
/opt/ibm/db2/V9.7/java/jdk64/jre/lib/amd64/default/libjvm.so
/opt/ibm/db2/V9.7/java/jdk64/jre/lib/amd64/j9vm/libjvm.so
/usr/lib/gcj-4.1.1/libjvm.so
/usr/lib/jvm/java-1.4.2-gcj-1.4.2.0/jre/lib/x86_64/client/libjvm.so
/usr/lib/jvm/java-1.4.2-gcj-1.4.2.0/jre/lib/x86_64/server/libjvm.so
/usr/lib/jvm/java-1.6.0-openjdk-1.6.0.39.x86_64/jre/lib/amd64/server/libjvm.so

[root@db2 JDBC_FDW]# ln -s /usr/local/jdk1.6.0_45/jre/lib/amd64/server/libjvm.so /usr/lib/libjvm.so

[root@db2 JDBC_FDW]# ldconfig

二、下載JDBC_FDW模塊
[root@db2 JDBC_FDW]# git clone git://github.com/atris/JDBC_FDW.git


[root@db2 JDBC_FDW]# cd JDBC_FDW

4) Execute Make Clean
[root@db2 JDBC_FDW]# sudo PATH=/usr/local/pg9.5.5/bin/:$PATH make USE_PGXS=1

[root@db2 JDBC_FDW]# cp jdbc_fdw.control /usr/local/pg9.5.5/share/postgresql/extension/
[root@db2 JDBC_FDW]# cp jdbc_fdw--1.0.sql /usr/local/pg9.5.5/share/postgresql/extension/
[root@db2 JDBC_FDW]# cp jdbc_fdw.so /usr/local/pg9.5.5/lib/postgresql/

三、查看DB2目標表

[db2inst4@db2-node01 ~]$ db2 describe table TABLEA


Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
ID SYSIBM INTEGER 4 0 Yes
NAME SYSIBM CHARACTER 10 0 Yes
SALARY SYSIBM INTEGER 4 0 Yes


3 record(s) selected.


[db2inst4@db2-node01 ~]$ db2 "select * from tablea"


ID NAME SALARY
----------- ---------- -----------
1001 SAM 10000
1002 PAM 9500
1003 CAM 12500
1004 RAM 7500
1005 HAM 20000
2001 SAM 10000
2002 PAM 9500
2003 CAM 12500
2004 RAM 7500
2005 HAM 20000
3001 SAM 10000
3002 PAM 9500
3003 CAM 12500
3004 RAM 7500
3005 HAM 20000
4001 MAS 2500
4002 MAP 3570
4003 MAC 4560
4004 MAR 5570
4005 MAH 6750


20 record(s) selected.

四、新建jdbc_fdw擴展模塊
[postgres@db2 ~]$ psql
psql (9.5.5)
Type "help" for help.


postgres=# CREATE EXTENSION jdbc_fdw;
CREATE EXTENSION

postgres=# CREATE SERVER jdbc_serv4 FOREIGN DATA WRAPPER jdbc_fdw OPTIONS(drivername 'com.ibm.db2.jcc.DB2Driver',url 'jdbc:db2://108.88.3.106:60000/test',querytimeout '15',jarfile '/usr/local/pg9.5.5/lib/db2jcc4.jar',maxheapsize '600'); //需要用到DB2的JDBC驅動
CREATE SERVER
postgres=# CREATE USER MAPPING FOR postgres SERVER jdbc_serv4 OPTIONS (username 'db2inst4', password 'db2inst4')
;
CREATE USER MAPPING
postgres=# CREATE FOREIGN TABLE ghan_table ( ID integer NOT NULL, NAME varchar(30),SALARY integer ) SERVER jdbc_serv4 OPTIONS (table 'TABLEA');
CREATE FOREIGN TABLE
postgres=# select * from ghan_table
;
id | name | salary
------+------------+--------
1001 | SAM | 10000
1002 | PAM | 9500
1003 | CAM | 12500
1004 | RAM | 7500
1005 | HAM | 20000
2001 | SAM | 10000
2002 | PAM | 9500
2003 | CAM | 12500
2004 | RAM | 7500
2005 | HAM | 20000
3001 | SAM | 10000
3002 | PAM | 9500
3003 | CAM | 12500
3004 | RAM | 7500
3005 | HAM | 20000
4001 | MAS | 2500
4002 | MAP | 3570
4003 | MAC | 4560
4004 | MAR | 5570
4005 | MAH | 6750
(20 rows)


postgres=#


五、Postgresql通過fdw_jdbc連接Oracle外部表


-----Postgresql通過fdw_jdbc連接Oracle外部表


SQL> create table test(id int, name varchar(20));
SQL> insert into test values (1,'張國漢')

SQL> select * from test;


ID NAME
---------- --------------------
1 張國漢
1 張國漢
1 張國漢
1 張國漢
1 張國漢
1 張國漢


已選擇6行。


SQL> commit;

六、新建oracle擴展服務


postgres=# CREATE SERVER jdbc_serv5 FOREIGN DATA WRAPPER jdbc_fdw OPTIONS(drivername 'oracle.jdbc.driver.OracleDriver',url 'jdbc:oracle:thin:@108.88.3.247:1521:orcl',querytimeout '15',jarfile '/usr/local/pg9.5.5/lib/ojdbc6.jar',maxheapsize '600');
CREATE SERVER
postgres=# CREATE USER MAPPING FOR postgres SERVER jdbc_serv5 OPTIONS (username 'zabbix', password 'zabbix');
CREATE USER MAPPING
postgres=# CREATE FOREIGN TABLE ghan_ora ( ID integer NOT NULL, NAME varchar(30)) SERVER jdbc_serv5 OPTIONS (table 'test');
CREATE FOREIGN TABLE
postgres=# select * from ghan_ora;
id | name
----+--------
1 | 張國漢
1 | 張國漢
1 | 張國漢
(3 rows)