\set ECHO all CREATE EXTENSION db2_fdw; CREATE SERVER sample FOREIGN DATA WRAPPER db2_fdw OPTIONS (dbserver 'SAMPLE'); CREATE USER MAPPING FOR PUBLIC SERVER sample OPTIONS (user 'db2inst1', password 'first'); -- CREATE USER MAPPING FOR PUBLIC SERVER sample OPTIONS (user '', password ''); IMPORT FOREIGN SCHEMA "DB2INST1" FROM SERVER sample INTO public; \c contrib_regression drop foreign table org; CREATE FOREIGN TABLE org ( DEPTNUMB SMALLINT OPTIONS (key 'yes') NOT NULL , DEPTNAME VARCHAR(14) , MANAGER SMALLINT , DIVISION VARCHAR(10) , LOCATION VARCHAR(13) ) SERVER sample OPTIONS (schema 'DB2INST1',table 'ORG'); delete from org; insert into org (DEPTNUMB,DEPTNAME,MANAGER,DIVISION,LOCATION) values(10,'Head Office',160,'Corporate','New York'); insert into org (DEPTNUMB,DEPTNAME,MANAGER,DIVISION,LOCATION) values(15,'New England',50,'Eastern','Boston'); insert into org (DEPTNUMB,DEPTNAME,MANAGER,DIVISION,LOCATION) values(20,'Mid Atlantic',10,'Eastern','Washington'); insert into org (DEPTNUMB,DEPTNAME,MANAGER,DIVISION,LOCATION) values(38,'South Atlantic',30,'Eastern','Atlanta'); insert into org (DEPTNUMB,DEPTNAME,MANAGER,DIVISION,LOCATION) values(42,'Great Lakes',100,'Midwest','Chicago'); insert into org (DEPTNUMB,DEPTNAME,MANAGER,DIVISION,LOCATION) values(51,'Plains',140,'Midwest','Dallas'); insert into org (DEPTNUMB,DEPTNAME,MANAGER,DIVISION,LOCATION) values(66,'Pacific',270,'Western','San Francisco'); insert into org (DEPTNUMB,DEPTNAME,MANAGER,DIVISION,LOCATION) values(84,'Mountain',290,'Western','Denver'); select * from org; deptnumb | deptname | manager | division | location ----------+----------------+---------+-----------+--------------- 10 | Head Office | 160 | Corporate | New York 15 | New England | 50 | Eastern | Boston 20 | Mid Atlantic | 10 | Eastern | Washington 38 | South Atlantic | 30 | Eastern | Atlanta 42 | Great Lakes | 100 | Midwest | Chicago 51 | Plains | 140 | Midwest | Dallas 66 | Pacific | 270 | Western | San Francisco 84 | Mountain | 290 | Western | Denver (8 rows) select * from sales; sales_date | sales_person | region | sales ------------+--------------+---------------+------- 12-31-2005 | LUCCHESSI | Ontario-South | 1 12-31-2005 | LEE | Ontario-South | 3 12-31-2005 | LEE | Quebec | 1 12-31-2005 | LEE | Manitoba | 2 12-31-2005 | GOUNOT | Quebec | 1 03-29-2006 | LUCCHESSI | Ontario-South | 3 03-29-2006 | LUCCHESSI | Quebec | 1 03-29-2006 | LEE | Ontario-South | 2 03-29-1996 | LEE | Ontario-North | 2 03-29-2006 | LEE | Quebec | 3 03-29-2006 | LEE | Manitoba | 5 03-29-2006 | GOUNOT | Ontario-South | 3 03-29-2006 | GOUNOT | Quebec | 1 03-29-2006 | GOUNOT | Manitoba | 7 03-30-2006 | LUCCHESSI | Ontario-South | 1 03-30-2006 | LUCCHESSI | Quebec | 2 03-30-2006 | LUCCHESSI | Manitoba | 1 03-30-2006 | LEE | Ontario-South | 7 03-30-2006 | LEE | Ontario-North | 3 03-30-2006 | LEE | Quebec | 7 03-30-2006 | LEE | Manitoba | 4 03-30-2006 | GOUNOT | Ontario-South | 2 03-30-2006 | GOUNOT | Quebec | 18 03-31-2006 | GOUNOT | Manitoba | 1 03-31-2006 | LUCCHESSI | Manitoba | 1 03-31-2006 | LEE | Ontario-South | 14 03-31-2006 | LEE | Ontario-North | 3 03-31-2006 | LEE | Quebec | 7 03-31-2006 | LEE | Manitoba | 3 03-31-2006 | GOUNOT | Ontario-South | 2 03-31-2006 | GOUNOT | Quebec | 1 04-01-2006 | LUCCHESSI | Ontario-South | 3 04-01-2006 | LUCCHESSI | Manitoba | 1 04-01-2006 | LEE | Ontario-South | 8 04-01-2006 | LEE | Ontario-North | 04-01-2006 | LEE | Quebec | 8 04-01-2006 | LEE | Manitoba | 9 04-01-2006 | GOUNOT | Ontario-South | 3 04-01-2006 | GOUNOT | Ontario-North | 1 04-01-2006 | GOUNOT | Quebec | 3 04-01-2006 | GOUNOT | Manitoba | 7 (41 rows) select * from employee a, sales b where a.lastname = b.sales_person; empno | firstnme | midinit | lastname | workdept | phoneno | hiredate | job | edlevel | sex | birthdate | salary | bonus | comm | sales_date | sales_person | region | sales --------+----------+---------+-----------+----------+---------+------------+----------+---------+-----+------------+----------+--------+---------+------------+--------------+---------------+------- 000110 | VINCENZO | G | LUCCHESSI | A00 | 3490 | 05-16-1988 | SALESREP | 19 | M | 11-05-1959 | 66500.00 | 900.00 | 3720.00 | 04-01-2006 | LUCCHESSI | Manitoba | 1 000110 | VINCENZO | G | LUCCHESSI | A00 | 3490 | 05-16-1988 | SALESREP | 19 | M | 11-05-1959 | 66500.00 | 900.00 | 3720.00 | 04-01-2006 | LUCCHESSI | Ontario-South | 3 000110 | VINCENZO | G | LUCCHESSI | A00 | 3490 | 05-16-1988 | SALESREP | 19 | M | 11-05-1959 | 66500.00 | 900.00 | 3720.00 | 03-31-2006 | LUCCHESSI | Manitoba | 1 000110 | VINCENZO | G | LUCCHESSI | A00 | 3490 | 05-16-1988 | SALESREP | 19 | M | 11-05-1959 | 66500.00 | 900.00 | 3720.00 | 03-30-2006 | LUCCHESSI | Manitoba | 1 000110 | VINCENZO | G | LUCCHESSI | A00 | 3490 | 05-16-1988 | SALESREP | 19 | M | 11-05-1959 | 66500.00 | 900.00 | 3720.00 | 03-30-2006 | LUCCHESSI | Quebec | 2 000110 | VINCENZO | G | LUCCHESSI | A00 | 3490 | 05-16-1988 | SALESREP | 19 | M | 11-05-1959 | 66500.00 | 900.00 | 3720.00 | 03-30-2006 | LUCCHESSI | Ontario-South | 1 000110 | VINCENZO | G | LUCCHESSI | A00 | 3490 | 05-16-1988 | SALESREP | 19 | M | 11-05-1959 | 66500.00 | 900.00 | 3720.00 | 03-29-2006 | LUCCHESSI | Quebec | 1 000110 | VINCENZO | G | LUCCHESSI | A00 | 3490 | 05-16-1988 | SALESREP | 19 | M | 11-05-1959 | 66500.00 | 900.00 | 3720.00 | 03-29-2006 | LUCCHESSI | Ontario-South | 3 000110 | VINCENZO | G | LUCCHESSI | A00 | 3490 | 05-16-1988 | SALESREP | 19 | M | 11-05-1959 | 66500.00 | 900.00 | 3720.00 | 12-31-2005 | LUCCHESSI | Ontario-South | 1 000330 | WING | | LEE | E21 | 2103 | 02-23-2006 | FIELDREP | 14 | M | 07-18-1971 | 45370.00 | 500.00 | 2030.00 | 04-01-2006 | LEE | Manitoba | 9 000330 | WING | | LEE | E21 | 2103 | 02-23-2006 | FIELDREP | 14 | M | 07-18-1971 | 45370.00 | 500.00 | 2030.00 | 04-01-2006 | LEE | Quebec | 8 000330 | WING | | LEE | E21 | 2103 | 02-23-2006 | FIELDREP | 14 | M | 07-18-1971 | 45370.00 | 500.00 | 2030.00 | 04-01-2006 | LEE | Ontario-North | 000330 | WING | | LEE | E21 | 2103 | 02-23-2006 | FIELDREP | 14 | M | 07-18-1971 | 45370.00 | 500.00 | 2030.00 | 04-01-2006 | LEE | Ontario-South | 8 000330 | WING | | LEE | E21 | 2103 | 02-23-2006 | FIELDREP | 14 | M | 07-18-1971 | 45370.00 | 500.00 | 2030.00 | 03-31-2006 | LEE | Manitoba | 3 000330 | WING | | LEE | E21 | 2103 | 02-23-2006 | FIELDREP | 14 | M | 07-18-1971 | 45370.00 | 500.00 | 2030.00 | 03-31-2006 | LEE | Quebec | 7 000330 | WING | | LEE | E21 | 2103 | 02-23-2006 | FIELDREP | 14 | M | 07-18-1971 | 45370.00 | 500.00 | 2030.00 | 03-31-2006 | LEE | Ontario-North | 3 000330 | WING | | LEE | E21 | 2103 | 02-23-2006 | FIELDREP | 14 | M | 07-18-1971 | 45370.00 | 500.00 | 2030.00 | 03-31-2006 | LEE | Ontario-South | 14 000330 | WING | | LEE | E21 | 2103 | 02-23-2006 | FIELDREP | 14 | M | 07-18-1971 | 45370.00 | 500.00 | 2030.00 | 03-30-2006 | LEE | Manitoba | 4 000330 | WING | | LEE | E21 | 2103 | 02-23-2006 | FIELDREP | 14 | M | 07-18-1971 | 45370.00 | 500.00 | 2030.00 | 03-30-2006 | LEE | Quebec | 7 000330 | WING | | LEE | E21 | 2103 | 02-23-2006 | FIELDREP | 14 | M | 07-18-1971 | 45370.00 | 500.00 | 2030.00 | 03-30-2006 | LEE | Ontario-North | 3 000330 | WING | | LEE | E21 | 2103 | 02-23-2006 | FIELDREP | 14 | M | 07-18-1971 | 45370.00 | 500.00 | 2030.00 | 03-30-2006 | LEE | Ontario-South | 7 000330 | WING | | LEE | E21 | 2103 | 02-23-2006 | FIELDREP | 14 | M | 07-18-1971 | 45370.00 | 500.00 | 2030.00 | 03-29-2006 | LEE | Manitoba | 5 000330 | WING | | LEE | E21 | 2103 | 02-23-2006 | FIELDREP | 14 | M | 07-18-1971 | 45370.00 | 500.00 | 2030.00 | 03-29-2006 | LEE | Quebec | 3 000330 | WING | | LEE | E21 | 2103 | 02-23-2006 | FIELDREP | 14 | M | 07-18-1971 | 45370.00 | 500.00 | 2030.00 | 03-29-1996 | LEE | Ontario-North | 2 000330 | WING | | LEE | E21 | 2103 | 02-23-2006 | FIELDREP | 14 | M | 07-18-1971 | 45370.00 | 500.00 | 2030.00 | 03-29-2006 | LEE | Ontario-South | 2 000330 | WING | | LEE | E21 | 2103 | 02-23-2006 | FIELDREP | 14 | M | 07-18-1971 | 45370.00 | 500.00 | 2030.00 | 12-31-2005 | LEE | Manitoba | 2 000330 | WING | | LEE | E21 | 2103 | 02-23-2006 | FIELDREP | 14 | M | 07-18-1971 | 45370.00 | 500.00 | 2030.00 | 12-31-2005 | LEE | Quebec | 1 000330 | WING | | LEE | E21 | 2103 | 02-23-2006 | FIELDREP | 14 | M | 07-18-1971 | 45370.00 | 500.00 | 2030.00 | 12-31-2005 | LEE | Ontario-South | 3 000340 | JASON | R | GOUNOT | E21 | 5698 | 05-05-1977 | FIELDREP | 16 | M | 05-17-1956 | 43840.00 | 500.00 | 1907.00 | 04-01-2006 | GOUNOT | Manitoba | 7 000340 | JASON | R | GOUNOT | E21 | 5698 | 05-05-1977 | FIELDREP | 16 | M | 05-17-1956 | 43840.00 | 500.00 | 1907.00 | 04-01-2006 | GOUNOT | Quebec | 3 000340 | JASON | R | GOUNOT | E21 | 5698 | 05-05-1977 | FIELDREP | 16 | M | 05-17-1956 | 43840.00 | 500.00 | 1907.00 | 04-01-2006 | GOUNOT | Ontario-North | 1 000340 | JASON | R | GOUNOT | E21 | 5698 | 05-05-1977 | FIELDREP | 16 | M | 05-17-1956 | 43840.00 | 500.00 | 1907.00 | 04-01-2006 | GOUNOT | Ontario-South | 3 000340 | JASON | R | GOUNOT | E21 | 5698 | 05-05-1977 | FIELDREP | 16 | M | 05-17-1956 | 43840.00 | 500.00 | 1907.00 | 03-31-2006 | GOUNOT | Quebec | 1 000340 | JASON | R | GOUNOT | E21 | 5698 | 05-05-1977 | FIELDREP | 16 | M | 05-17-1956 | 43840.00 | 500.00 | 1907.00 | 03-31-2006 | GOUNOT | Ontario-South | 2 000340 | JASON | R | GOUNOT | E21 | 5698 | 05-05-1977 | FIELDREP | 16 | M | 05-17-1956 | 43840.00 | 500.00 | 1907.00 | 03-31-2006 | GOUNOT | Manitoba | 1 000340 | JASON | R | GOUNOT | E21 | 5698 | 05-05-1977 | FIELDREP | 16 | M | 05-17-1956 | 43840.00 | 500.00 | 1907.00 | 03-30-2006 | GOUNOT | Quebec | 18 000340 | JASON | R | GOUNOT | E21 | 5698 | 05-05-1977 | FIELDREP | 16 | M | 05-17-1956 | 43840.00 | 500.00 | 1907.00 | 03-30-2006 | GOUNOT | Ontario-South | 2 000340 | JASON | R | GOUNOT | E21 | 5698 | 05-05-1977 | FIELDREP | 16 | M | 05-17-1956 | 43840.00 | 500.00 | 1907.00 | 03-29-2006 | GOUNOT | Manitoba | 7 000340 | JASON | R | GOUNOT | E21 | 5698 | 05-05-1977 | FIELDREP | 16 | M | 05-17-1956 | 43840.00 | 500.00 | 1907.00 | 03-29-2006 | GOUNOT | Quebec | 1 000340 | JASON | R | GOUNOT | E21 | 5698 | 05-05-1977 | FIELDREP | 16 | M | 05-17-1956 | 43840.00 | 500.00 | 1907.00 | 03-29-2006 | GOUNOT | Ontario-South | 3 000340 | JASON | R | GOUNOT | E21 | 5698 | 05-05-1977 | FIELDREP | 16 | M | 05-17-1956 | 43840.00 | 500.00 | 1907.00 | 12-31-2005 | GOUNOT | Quebec | 1 (41 rows) create table orgcopy as select * from org; drop table orgcopy;