How to Setup Remote Access for DDM/DRDA, SQL and Output Queue's

As many of you know, I was recently brought into a client that has about 10 individual System i servers scattered throughout the Caribbean. And before you say it, no I am not living there nor do I travel there much so hold the joke about how rough it must be, for now anyway.

In reviewing their legacy systems I noticed that they were using a Rub Goldberg technique to access data on those remote systems. They would do things such as save the database files to a save file (SAVF) and then FTP that SAVF to the main system. On that system they would either restore the data to a library named XXXDATA where "XXX" was a code to identify the remote location or they would restore the file to the main data library and name it XXXfilename where, again, "XXX" is the remote ID and filename was a portion of the original file name.

Then there were the attempts at using DDM. On the main system they setup a handful of DDM definitions that pointed to the files on the remote systems. In this scheme, they used the XXXfilename naming convention for the files, and XXXDATA for the library containing the DDM file.

All I could think of was, "turn around an run Bob, run as fast as you can".

Application Access to Remote Database

All new applications being created by me and my team allow the application user to access the data on the local system or on the remote system. For example, if you want to do an inventory inquiry from the main system to remote location ABC, then you tell the app to switch to site ABC and bam! you're using the data on the remote site.

To implement this, we created a library named RMTDBxxx. In this library we created a DDM definition for all the primary database files. Some isolated applications such as Payroll and whatnot, have their own DDM "libraries" named RMTPRxxx. In both cases the "XXX" is the remote location identifier. Therefore, to switch from the primary system to the ABC location, the library list (for example the Current Library) would be changed to RMTDBABC. When the files in RMTDBABC are used by our applications, the applications are actually using the data on the remote system; remote ABC in this example.

You can store a DDM file in any library, but I prefer to create one for each location. So for the Chicago location, the library named RMTDBORD is used. To set that up and create a DDM definition in that library, the following two commands may be used:

CRTLIB RMTDBORD *PROD TEXT('Chicago Database via DDM')
CRTDDMF FILE(RMTDBORD/CUSTMAST) RMTFILE(APPDATA/CUSTMAST) + 
          RMTLOCNAME('192.168.1.104' *IP) TEXT('Chicago CUSTMAST file') 

This naming scheme allows us to use the same file names in our applications without issuing an OVRDBF command. By simply changing the library list or setting the current library so that RMTDBORD is first on the library list, we access the files we want.

Now when the applications tries to access CUSTMAST, assuming RMTDBORD is on the library list first, the file from the Chicago location is accessed.

Let's take a closer look at the CRTDDMF command.

CRTDDMF FILE(RMTDBORD/CUSTMAT) RMTFILE(APPDATA/CUSTMAST) +
            RMTLOCNAME('192.168.1.104' *IP) TEXT('Chicago CUSTMAST file') 

Parameters

FILE - The name of the DDM definition being created. This is effectively a symbolic link to the file on the remote location. No DDS or SQL DDL is used to create this file, just the CRTDDMF command.

RMTFILE - The name of the actual database file (table) on the remote location's system. This is a real physical file or SQL table that will be opened and processed when the DDM file specified on the FILE parameter is accessed.

RMTLOCNAME - The IP address or HOSTS Table name entry of the remote system. Make sure you specify *IP for the second portion of the parameter.

The remote location name must be either an IP address, as mentioned, or a name from the HOSTS table. To add an entry to the HOSTS table so that you don't have to hardcode an IP address on the DDM file itself, use the ADDTCPHTE command. For example, to add the Chicago location (using ORD as the ID) for the IP 192.168.1.104 the following ADDTCPHTE command could be used:

ADDTCPHTE INTNETADR('192.168.1.104') HOSTNAME((ORD))

NOTE: Use of ADDTCPHTE requires *IOSYSCFG special authority.

Now the value ORD can be used instead of specify the IP address. This is valid for things like TELNET, FTP, PING, Remote Output Queues and of course DDM. To create a DDM file that points to a file named CUSTMAST on the remote system ORD, use the following CRTDDMF command:

CRTDDMF FILE(RMTDBORD/CUSTMAT) RMTFILE(APPDATA/CUSTMAST) +
            RMTLOCNAME( ORD   *IP) TEXT('Chicago CUSTMAST file') 

You can also do an OVRDBF to the DDM file rather than manipulate the library list, which ever works best for your situation.

Relational Database Directory Entries

While DDM definitions work great with RPG, when SQL is in the mix of the application, DDM falls down. When using SQL you have to go after the remote database files using the SQL CONNECT statement and Relational Database Directory Entries (RDB Directory Entry). This is a bit limited and messy but you can make it slightly easier with a small change to your existing config.

When a system is installed, an RDB Directory Entry is created attached to the *LOCAL system. This means when connected to this named directory, you are accessing the local system. The system generates a directory entry using the system serial number (QSRLNBR System Value) as the name for this directory. Therefore to connect to that system from a remote, the remote must have a directory with the same name as the remote.

On a system with serial number S105555 an ADDRDBDIRE command as follows would already be on the system:

ADDRDBDIRE RDB(S1055555) RMTLOCNAME(*LOCAL *IP)

Then on a remote system, you can make that location available by using ADDRDBDIRE as follows:

ADDRDBDIRE RDB(S1055555) RMTLOCNAME('192.168.1.104' *IP)

I prefer to change things up a bit. Often the system name is changed from the serial number to a more practical name, like CHICAGO or ORD (in our example). To make this work, the following two commands would need to be run on the Chicago system:

RMVRDBDIRE RDB(S1055555)
ADDRDBDIRE RDB(ORD) RMTLOCNAME(*LOCAL *IP)

The problem with this is that a message is generating warning that the removal of the entry will cause configuration issues. To solve that you need to run the CHGDDMTCPA command:

CHGDDMTCPA AUTOSTART(*YES) PWDRQD(*VLDONLY) 

The PWDRQD parameter should be set to your system's required setting, not simply to *VLDONLY or some other value.

Now, on my remote system I can add the following entry:

ADDRDBDIRE RDB(ORD) RMTLOCNAME(ORD *IP)

In this example, the Relation Database Directory named ORD is added and points to the CHICAGO IP. This value on the RMTLOCNAME is the one entered on the ADDTCPHTE command earlier. In case you missed it, here it is again:

ADDTCPHTE INTNETADR('192.168.1.104') HOSTNAME((ORD))

SQL Remote DB Access

To get to the remote database using SQL, issue the CONNECT statement; either in Embedded SQL or interactive SQL. Here's an exampled:

EXEC SQL CONNECT TO ORD USER BOBBY USING 'ROSBUD';

Then issue any SQL statement and it will actually go against the files on the remote system. To reconnect back to you local system, issue the DISCONNECT statement as follows:

EXEC SQL DISCONNECT ORD;

Remote Connections

Connecting to remote database files on other IBM i servers is a bit like threading the needle, but it does work. Just don't expect a "push the button and make it work" style of interface and you you should be able to make it work.

This article only scratches the surface, but finding this information in place, in context is relatively difficult. I'd like to ask anyone with additional information or feedback to post a comment below so that we can make sure we have all this in one centralized location.

Call Me

Bob Cozzi is the webmaster for MidrangeNews.com and has been providing the solutions to midrange problems, in the form or articles and books since 1983. He is available for consulting/contract development or on-site RPG IV, SQL, and CGI/Web training. To contact Cozzi, send an email to: bob at rpgworld.com

You can subscribe to RPG Report (we call it "follow") by visiting the RPG Report page on midrangeNews.com and then click the FOLLOW link in the table of contents for that page. To unsubscribe, simply click that same link. You must be signed up and signed in to midrangeNews.com to start following RPG Report.

Follow Bob Cozzi on Twitter