Creating a table with remote server data in DB2 for i SQL

Creating a table with remote server data in DB2 for i SQL

We can create a table on the local server that points to the one or more table on the remote server machine. We can also use copy options to get attributes such as the default values/identity column information copied to the new table on local server machine. The WITH DATA or WITH NO DATA must be specified to tell whether data is to be populated from the remote system table to the local system newly created table.

Example 1:Create a table that includes column definitions and data from the table PF1 on remote server named 'REMOTEMAC'

CREATE TABLE PF1_LOCAL AS
   (SELECT FLD1, FLD2
    FROM REMOTEMAC.SchemaName.PF1) WITH DATA

Here we are creating table named PF1_LOCAL in the local server by selecting fld1 and fld2 data from table PF1 resides in the library named SchemaName and on remote server machine name REMOTEMAC. We are also populating the data from the remote system file to the local system file during its creation.

Example 1:Create a global temporary table that includes column definitions and its defaults and data from the table PF1 on remote server named 'REMOTEMAC'

DECLARE GLOBAL TEMPORARY TABLE PF1_LOCAL(Field1, Field2) AS
   (SELECT FLD1, FLD2
    FROM REMOTEMAC.SchemaName.PF1) WITH DATA INCLUDING DEFAULTS

Here we are creating global temporary table named PF1_LOCAL in the QTEMP library by renaming the fields to Field1 and Field2 in the local server by selecting fld1 and fld2 data from table PF1 resides in the library named SchemaName and on remote server machine name REMOTEMAC. The table definition will pickup the default values for its column from the remote server. We are also populating the data from the remote system file to the local system file during its creation.

Note: A column with a FIELDPROC cannot be listed in the select list.