Wednesday, February 7, 2018

Configuring database connection between two IBM i

configuring database connection to another 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.

2 comments:

  1. This may be beyond the scope of this article, but the trend with the IBM i is that all communications in and out be encrypted. In this case, it is possible that the user ID and password are being transmitted in plain text. In a subsequent article, can you add how to handle this connection using TLS or an alternative encryption method?

    ReplyDelete
  2. Good article, thank you. I'm usually not accessing remote systems via SQL. An interesting scenario would be to join tables for example to find out the differences of the same file in different systems. I usually transfer the table to the same system and compare them there. Is it possible to compare them remotely?

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.