Setup remote db in Green Screen:
-- 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)
alternatively you could create the connection every time doing:
CONNECT TO REMOTE_SYSTEM_NAME USER USERPRF_NAME USING 'MY_PASSWD_IN_SINGLE_QUOTES';
-- ::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';
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
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.