Inserting data from a remote database

You can insert into a table on the local server using a select statement to get rows from a remote server.

To insert all the rows from the SALES table on REMOTESYS for sales made yesterday, use the following statement:

INSERT INTO SALES 
        (SELECT * FROM REMOTESYS.TESTSCHEMA.SALES WHERE SALES_DATE = CURRENT DATE - 1 DAY)

DB2® for i will connect to REMOTESYS to run the SELECT, return the selected rows to the local system, and insert them into the local SALES table. The value for CURRENT DATE will be the current date on REMOTESYS.

Since a three-part object name or an alias that is defined to reference a three-part name of a table or view creates an implicit connection to the application server, a server authentication entry must exist. Use the Add Server Authentication Entry (ADDSVRAUTE) command on the application requestor specifying the server name, user ID, and password. The server name and user ID must be entered in upper case.

ADDSVRAUTE USRPRF(yourprf) SERVER(DRDASERVERNAME) USRID(YOURUID) PASSWORD(yourpwd)

See Distributed Database Programming for additional details on server authentication usage for DRDA.