Creating a table with remote server data

You can create a table on the local server that references one or more tables on a remote server.

Along with the select-statement, you can specify copy options to get attributes such as the default values or identity column information copied for the new table. The WITH DATA or WITH NO DATA clause must be specified to indicate whether to populate the table from the remote system.

For example, create a table named EMPLOYEE4 that includes column definitions from the EMPLOYEE table on remote server REMOTESYS. Include the data from the remote system as well.

CREATE TABLE EMPLOYEE4 AS
   (SELECT PROJNO, PROJNAME, DEPTNO
    FROM REMOTESYS.TESTSCHEMA.EMPLOYEE
    WHERE DEPTNO = 'D11') WITH DATA

You can also create this table as a global temporary table, which will create it in QTEMP. In this example, different column names are provided for the new table. The table definition will pick up the default values for its columns from the remote server.

DECLARE GLOBAL TEMPORARY TABLE EMPLOYEE4 (Project_number, Project_name, Department_number) AS
   (SELECT PROJNO, PROJNAME, DEPTNO
    FROM REMOTESYS.TESTSCHEMA.EMPLOYEE
    WHERE DEPTNO = 'D11') WITH DATA INCLUDING DEFAULTS

The following restrictions apply to using a remote server as the source for the new table:

The materialized query table clauses are not allowed.
A column with a FIELDPROC cannot be listed in the select list.
The copy options cannot be specified if the remote server is DB2® for LUW or DB2 for z/OS®.