Federated external tables

You can use a federated database system to query or manipulate data that is stored remotely in external tables. You can create a new external table on a remote data source by specifying the name of a local delimited text (.txt), comma-separated variable (.csv), or Microsoft Excel (.xls) file that contains the data for that table.

The following examples illustrate how to create external tables on a remote data source, and to query and manipulate the data that is stored in those tables.

Create two external tables on the remote data source FLOWER.CN.IBM.COM, which has the port number 53001:

CONNECT TO SVT_DB
CREATE EXTERNAL TABLE STUDENT1(ID INT, NAME VARCHAR(20)) USING  (DATAOBJECT '/HOME/DB2INST3/FLATFILE0416/STUDENT1.TXT')
CREATE EXTERNAL TABLE STUDENT2(ID INT, NAME VARCHAR(20)) USING  (DATAOBJECT '/HOME/DB2INST3/FLATFILE0416/STUDENT2.CSV')
INSERT INTO STUDENT1 VALUES(13,'ZHANGSA')
INSERT INTO STUDENT2 VALUES(14,'LISI')

Define the server and user mapping for the new external tables on the local database server:

CONNECT TO TESTDB
CREATE SERVER DRDA1 TYPE DB2/UDB VERSION 11.1 AUTHORIZATION "DB2INST3" PASSWORD "PASSW0RD" OPTIONS(HOST 'FLOWER.CN.IBM.COM', PORT '53001', DBNAME 'SVT_DB')
CREATE USER MAPPING FOR USER SERVER DRDA1 OPTIONS(REMOTE_AUTHID 'DB2INST3', REMOTE_PASSWORD 'PASSW0RD')

Define the nicknames for the new external tables on the local database server:

CREATE NICKNAME REMOTE_NK_STUDENT1 FOR DRDA1."DB2INST3"."STUDENT1"
CREATE NICKNAME REMOTE_NK_STUDENT2 FOR DRDA1."DB2INST3"."STUDENT2"

Execute a query with a join:

SELECT * FROM REMOTE_NK_STUDENT1 FULL JOIN REMOTE_NK_STUDENT2 ON REMOTE_NK_STUDENT1.ID = REMOTE_NK_STUDENT2.ID

Execute a query with a union:

SELECT * FROM REMOTE_NK_STUDENT1 UNION ALL SELECT * FROM REMOTE_NK_STUDENT2

Insert records:

INSERT INTO REMOTE_NK_STUDENT1 SELECT * FROM REMOTE_NK_STUDENT2