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® will connect to REMOTESYS to run the SELECT, return the selected rows to the local system, and insert them into the local SALES table.
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.