Postgresql queries the DB2 \ Oracle table through the fdw_JDBC connection

Postgresql queries the DB2 \ Oracle table through the fdw_JDBC connection
1. Search for the libjvm. so common library
[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

2. Download The JDBC_FDW Module
[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/

3. view the DB2 target table

[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.

4. Create a jdbc_fdw extension module
[S @ db2 ~] $ Psql
Psql (9.5.5)
Type "help" for help.


S = # 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 '000000'); // the DB2 JDBC driver is required.
CREATE SERVER
S = # create user mapping for s 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 = #


V. Postgresql uses fdw_jdbc to connect to an Oracle External table


----- Postgresql connects to the Oracle External table through fdw_jdbc


SQL> create table test (id int, name varchar (20 ));
SQL> insert into test values (1, 'zhang Guohan ')

SQL> select * from test;


ID NAME
------------------------------
1 Guohan
1 Guohan
1 Guohan
1 Guohan
1 Guohan
1 Guohan


You have selected 6 rows.


SQL> commit;

6. Create an oracle Extension Service


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 '200 ');
CREATE SERVER
S = # create user mapping for s 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 | Zhang Guohan
1 | Zhang Guohan
1 | Zhang Guohan
(3 rows)