Setup remote db in Green Screen:

[code language=”sql”]
— Use ADDSVRAUTE so that the system knows the username and password
— when connecting to the remote system
ADDSVRAUTE USRPRF(MYUSRPRF) SERVER(PRODSERVER) PASSWORD()
— Or if you already have an entry you may need to change your entry with
CHGSVRAUTE USRPRF(MYUSRPRF) SERVER(PRODSERVER) PASSWORD()
— Use wrkrdbdire to create/edit remote db connection configurations
WRKRDBDIRE
— Use CHGDDMTCPA to setup how TCP connection will work (enforcing
— password policy and encryption)
CHGDDMTCPA AUTOSTART(*YES) PWDRQD(*USRENCPWD) ENCALG(*DES)
[/code]

alternatively you could create the connection every time doing:

[code language=”sql”]

CONNECT TO REMOTE_SYSTEM_NAME USER USERPRF_NAME USING ‘MY_PASSWD_IN_SINGLE_QUOTES’;

[/code]

SQL

[code language=”sql”]
— ::Local IBMi::

— Let’s back up the table before we wipe and replace it
CREATE SCHEMA BACKUP

–Create backup
CREATE TABLE BACKUP.TABLETOSYNC
AS (SELECT * FROM MYLIB.TABLETOSYNC)
WITH DATA;

–Wipe local table
DELETE FROM MYLIB.TABLETOSYNC;

–Replace with Production data
INSERT INTO MYLIB.TABLETOSYNC (
SELECT * FROM PRODSERVER.MYLIB.TABLETOSYNC);

–View the table data
SELECT * FROM MYLIB.TABLETOSYNC;

–::MORE ADVANCED WAY WITH MERGE::
–::This would be more elegant but time consuming by setting up MERGE with update insert rules
–instead of wiping and replacing. More advanced example here:http://www.itjungle.com/fhg/fhg092210-story02.html

–Create temp table from production data
CREATE TABLE QTEMP.TABLETOSYNC
AS (SELECT * FROM PRODSERVER.MYLIB.TABLETOSYNC)
WITH DATA;

MERGE INTO MYLIB.TABLETOSYNC AS TARGET_TABLE
USING QTEMP.TABLETOSYNC AS SOURCE_TABLE
ON (TARGET_TABLE.TABLEKEYFIELD=SOURCE_TABLE.TABLEKEYFIELD )
WHEN MATCHED THEN
UPDATE SET
TARGET_TABLE.COL1 = SOURCE_TABLE.COL1
WHEN NOT MATCHED THEN
INSERT (COL1)
VALUES(SOURCE_TABLE.COL1 )
ELSE IGNORE;

— More info:
https://publib.boulder.ibm.com/iseries/v5r1/ic2924/index.htm?info/ddp/rbal1mst63.htm
http://www.itjungle.com/fhg/fhg072512-story02.html

— Another thing i looked into using
— The connect to command actually puts you into that server instead of just giving you a reference to it….
— CONNECT TO PRODSERVER USER MYUSRPRF USING ‘password’;
[/code]

UPDATE: I’ve put all this logic into a stored procedure and you can download the code here: https://github.com/phpdave/DB2-for-IBMi/blob/master/Stored%20Procedures/SYNCTABLE.sql Let me know if you have any comments for improvement. The current stored procedure has commitment control to save you from sql exceptions or something going wrong in the middle of execution. If a problem occurs everything will be rolled back to a save point (Imagine deleting the database and then it not inserting.)

This might also be helpful and simpler-  https://www.ibm.com/developerworks/community/wikis/home?lang=en#/wiki/IBM%20i%20Technology%20Updates/page/INSERT%20with%20remote%20SUBSELECT

iSeries Navigator

You can also just go to the table in iSeries navigator MS windows client and right click the table and click copy. Then go to the target system and right click on the library you want to paste the table in. This is great for quick manual copies.