Configuring database connection between two IBM i

In my day job I work in an environment where there are multiple PowerSystems servers, and many of them have multiple IBM i partitions. I was tasked to gather information from most of the partitions into one location. I decided to use SQL to fetch the information from the various sources.

I used the following two methods to connect to the other partitions using the partition name:

CONNECT TO SYSTEM2   or   SELECT * FROM SYSTEM2.OTHERLIB.RMTFILE

When debugging my program I found that when I tried to connect to some of the partitions I received a SQLCOD value of -950 and the following message:

Relational database SYSTEM2 not in relational database directory.

When expanded the message it gives me this:

Message ID . . . . : SQL0950 Severity . . . . . : 30 Message type . . . : Information Message . . . . : Relational database SYSTEM2 not in relational database directory. Cause . . . . . : A request for relational database SYSTEM2 was made. However the relational database name was not found in the relational database directory. Recovery . . . : Do one of the following: -- Change the name of the relational database specified on the CONNECT, SET CONNECTION, RELEASE, or DISCONNECT statement or the RDB parameter of the SQL precompile commands. -- Add the relational database name to the relational database directory using the Add Relational Database Directory Entry (ADDRDBDIRE) command. -- If the CREATE TABLE statement specifies a node group, add this system to the relational database directory on the target system.

I had wrongly assumed that all of the partitions Db2 for i databases had been renamed to match the name of the partition. Alas, on some of the less used partitions the database name remained the default or have not been added to the Relational Database, RDB, directory of the partition I was using.

To view what is in the RDB directory I use the WRKRDBDIRE command:

Work with Relational Database Directory Entries Position to . . . . . . Type options, press Enter. 1=Add 2=Change 4=Remove 5=Display details 6=Print details Remote Option Entry Location Text SYSTEM1 *LOCAL SYSTEM3 system3 SYSTEM4 99.99.999.4 SYSTEM5 99.99.999.5

The remote location of *LOCAL shows that this is the name of the Db2 for i database on this partition.

There is no entry for SYSTEM2.

I need to find the name of SYSTEM2's database before I can add it. Having signed onto SYSTEM2 I have two methods of finding its database name.

The first uses what is the longest Db2 for i View name I have encountered:

SELECT * FROM QSYS2.INFORMATION_SCHEMA_CATALOG_NAME

Which returns one row containing the database name.

CATALOG_NAME SXXXXXXX

The database name is an "S" followed by what looks like the serial number of the original server this partition was created upon. In this example I am going to replace the serial number with all "X".

Or I could have used the DSPRDBDIRE command and looked for the entry with *LOCAL in the remote location name.

Remote Option Entry Location Text SXXXXXXX *LOCAL

Back to SYSTEM1, and I have to add SYSTEM2's database to the RDB directory. I can either use the ADDRDBDIRE command or use the add option in WRKRDBDIRE. Whichever one I use I get to the same screen:

The connection to the remote database can be either using SNA:

Add RDB Directory Entry (ADDRDBDIRE) Type choices, press Enter. Entry: Relational database . . . . . > SXXXXXXX Relational database alias . . *NONE Remote location: Name or address . . . . . . . SYSTEM2 Type . . . . . . . . . . . . . *SNA *SNA, *IP

When using SNA I have to put the name of the SNA connection in the "Name" parameter and the "Type" has to be *SNA. After pressing Enter I don't bother to change any of the other parameters, I just keeping pressing Enter until the command has completed.

Or I can use IP for the connection. The IP address of SYSTEM2 must be entered into the "Name" parameter, and *IP into the "Type":

Entry: Relational database . . . . . > SXXXXXXX Relational database alias . . *NONE Remote location: Name or address . . . . . . . 99.99.999.2 Type . . . . . . . . . . . . . *IP *SNA, *IP

After pressing Enter I don't bother to change of the other parameters, I just keeping press Enter until the command completes.

The Alias parameter can be used to give the database a more meaningful name. If I have used the Alias I have to use that in place of the database name in all SQL statements.

Entry: Relational database . . . . . > SXXXXXXX Relational database alias . . SYSTEM2 Remote location: Name or address . . . . . . . 99.99.999.2 Type . . . . . . . . . . . . . *IP *SNA, *IP

I will show what I consider to be the main difference of using SNA and IP later.

Having added the RDB directory I can now get to the data on SYSTEM2 from SYSTEM1. If I use the STRSQL command I can get the data using the three part name in a Select statement:

SELECT * FROM SXXXXXXX.OTHERLIB.RMTFILE FLD001 FLD002 FLD003 FLD004 FLD005 ------ ------ ------ ------ ------ 000001 ONE TWO THREE FOUR FIVE 000002 TWO THREE FOUR FIVE SIX 000003 THREE FOUR FIVE SIX SEVEN 000004 FOUR FIVE SIX SEVEN EIGHT 000005 FIVE SIX SEVEN EIGHT NINE ****** * * * * * E N D O F D A T A * * * * *

I can also use the three part name in a RPG program to connect to SYSTEM2's database too.

01 **free 02 dcl-s FirstColumn char(5) ; 03 exec sql SELECT FLD001 INTO :FirstColumn 04 FROM SXXXXXXX.OTHERLIB.RMTFILE 05 FETCH FIRST ROW ONLY ; 06 dsply ('FLD001 = ' + FirstColumn) ; 07 *inlr = *on ;

Line 3: All I want is the value in the column FLD001.

Line 4: This is the three part name: database, library, and file.

Line 5: I only want to retrieve the value from the first row only.

Line 6: I am using the DSPLY operation code so I can see the value I retrieve.

When I create this RPG program, with the three part name, the compiler creates a SQL Package on the remote partition, SYSTEM2. If there is a library with the same name as the one I am compiling this program into then the package will be created there. If not I need to give a library name in the SQLPKG parameter of the create command.

Create SQL ILE RPG Object (CRTSQLRPGI) Package . . . . . . . . . . . . SQLPKG *OBJ Library . . . . . . . . . . . OTHERLIB

Now the program is compiled when I run it I can see that the first row from the file was retrieved successfully:

DSPLY FLD001 = ONE

What are the differences between using a SNA and an IP connection for the database?

I added the database of SYSTEM2 as SNA then inserted a row into a table on SYSTEM2:

INSERT INTO SXXXXXXX.OTHERLIB.RTMFILE2 VALUES('*SNA',CURRENT_USER)

I changed the RDB entry making the connection type IP and ran the statement a second time:

INSERT INTO SXXXXXXX.OTHERLIB.RTMFILE2 VALUES('*IP',CURRENT_USER)

When I retrieve the rows from the table I receiving the following results:

FTYPE FUSERM 000001 *SNA QUSER 000002 *IP SIMON

When you run jobs on another partition using SNA communications they will use the QUSER profile. This is unsecure, and is a problem. I cannot authorize or block users from files on the remote partition if everyone's jobs run as QUSER.

The IP communications ran using my profile on the remote partition, as I have a user id with the same name there too. This is more secure as I can secure objects on the remote partition by profile. If I do not have the same user profile on the remote partition I would have received an error message. Does this mean I have to create the same profiles on the other partitions as exist on the others? Fortunately no I do not, and in my next post I will describe how to do it with the minimum amount of work.

 

You can learn more about the ADDRDBDIRE command from the IBM website here.

 

This article was written for IBM i 7.3, and should work for earlier releases too.