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