We needed to do a table update remotely over the internet from a very old version of OS/400 (V4R3) using SQL in an RPGLE program on a table which was on a remote machine running a much newer version (V7R1). The connection of the two machines is across the internet. Normally we would use SSL for the DRDA connection but this is not available on the V4R3 machine so we will implement the connection over a VPN at some point.
This is possible using DRDA/DDM but there are a few things to put in place before it will work.
Step 1: Because V4R3 only supports 10 character upper case passwords the password on the remote (target) machine at V7R1 needs to be a maximum of 10 long and upper case only.
Step 2: On the target system enter WRKRDBDIRE and press enter. Using the name of the database (Relational Database) that is listed against *LOCAL do a WRKRDBDIRE on the source system and add an entry that points to the WAN or public IP address of the target system. I have used an IP address (Only works if you have a static IP address) on my entry but a domain or subdomain name could be used as long as this is resolvable by the source IBM i machine. Example below of WRKRDBDIRE entry being made on source machine:
Relational database = REMOTE_DB_NAME (From *LOCAL entry on WRKRDBDIRE on target machine) NNN.NNN.NNN.NNN = IP address or domain name of the remote WAN router where the target IBM i is.
Step 3: Now we need to make sure the port is open on the WAN router at the location for the target machine that allows the DRDA connection from the source machine to the target machine. This port is 446. So a port forwarding entry needs to be added on the WAN router for port 446 and pointed at the internal (local) IP address of the target IBM i machine.
Step 4: Next create an SQLRPGLE program. Somewhere in the C specs at the beginning for the SQL setup and connection add the following lines (For the RPG variables :USER and :USING enter the User ID and Password created on the target machine in step 1. You can test out connecting to the remote database using STRSQL from the command line and manually entering a CONNECT statement, prompt with F4, and do a SELECT statement on a table on the target machine):
C/EXEC SQL CONNECT TO REMOTE_DB_NAME USER :USER USING :USING C/END-EXEC C/EXEC SQL WHENEVER NOT FOUND CONTINUE C/END-EXEC C/EXEC SQL WHENEVER SQLERROR CONTINUE C/END-EXEC C/EXEC SQL ROLLBACK C/END-EXEC C/EXEC SQL SET TRANSACTION ISOLATION LEVEL NO COMMIT C/END-EXEC
… then do the inserts or updates etc on the required table on the remote machine for example:
C/EXEC SQL C+ INSERT INTO REMLIBNAM/REMTABNAM (COLUMN1, COLUMN2) C+ VALUES(:COL1, :COL2) WITH NC C/END-EXEC C/EXEC SQL C+ UPDATE REMLIBNAM/REMTABNAM SET COLUMN2 = :COL2 C+ WHERE COLUMN1 = :COL1 WITH NC C/END-EXEC
Step 5: Now on the source machine we need to create an SQL package that gets sent to the target machine to tell the target machine about the tables that are going to be accessed by the SQL statements that are going to be run from the source machine. In order to do that create a CLLE source member with the following source member statements inside:
PGM CRTSQLPKG PGM(LOCLIBNAM/LOCPGMNAM) RDB(REMOTE_DB_NAME) USER(USERID) PASSWORD(PASSWORD) ENDPGM
where LOCLIBNAM and LOCPGMNAM are the library name and program name of the program created in step 3 above and the User ID and Password are those created in step 1 above.
Step 6: Compile the CL program and then call it.
If it runs ok it will return to the command line without giving any errors.
Now you are set to run the program you created in step 4 above.
I did get some broken pipe messages occasionally (CPE3025) in the job logs, TCP time outs etc, when I first ran the SQL insert/update program created in step 3 so I changed the CHGTCPA parameters on the V4R3 source machine to be the following:
