Using three part name when the user profile or passwords are different

This post is to take care of an oversight that was brought to my attention. I have written several posts about using the SQL three part name, all of the examples assumed that the user name and password were the same on all of the partitions. What happens if they are not? This post will explain what to do.

For these examples I am using three partitions:

1. DEV740: This partition is running IBM i 7.4, and is the "local" partition, the one I am performing all the calls from
2. DEV730: Runs IBM i 7.3. When the connection was configured on DEV740 to DEV730 the serial number was used, therefore, DEV730 is known as G102A08R.
3. DEV720: This is the oldest partition with IBM i 7.2. My using it proves that the three part name has been around for longer than just the last few releases

I am not going to do anything too fancy for these examples. I have a file, called WHERE_AM_I, on all of these partitions. It has just one field, FLD001 that contains a unique message in each file. All I am going to do is to access these files, and show the contents on DEV740.

If I was doing this in ACS's Run SQL Scripts, RSS, I could do the following:

01 SELECT CURRENT_SERVER,A.* FROM WHERE_AM_I A ; 02 CONNECT TO DEV720 USER SIMON720 USING 'pwd720' ; 03 SELECT CURRENT_SERVER,A.* FROM WHERE_AM_I A ; 04 DISCONNECT DEV720 ; 05 CONNECT TO G102A08R USER SIMON730 USING 'pwd730' ; 06 SELECT CURRENT_SERVER,A.* FROM WHERE_AM_I A ; 07 DISCONNECT G102A08R ; 08 SET CONNECTION DEV740 ; 09 SELECT CURRENT_SERVER,A.* FROM WHERE_AM_I A ;

Line 1: This statement will return the name of the server I am attached to and the contents of the file. The result for this statement is:

00001 FLD001 -------- --------------------------- DEV740 I am local on DEV740

Line 2: The Connect statement is what signs me onto another partition. Here I need to give the name of the partition, user name and password. In this example 'DEV720' is the partition, 'SIMON720' is my user profile, and ' 'pwd720' ' is the password which is given within single quotes ( ' ). The password can be given in either case.

Line 3: This is exactly the same statement as the one on line 1. As I am now connected to DEV720 I don't have to give its name in any of my SQL statements. The results for this statement are:

00001 FLD001 -------- --------------------------- DEV720 Hello, I am on DEV720

Line 4: As I am finished on DEV720 I use the Disconnect statement to disconnect from it.

Line 5: I connect to 'G102A08R', or DEV730, with a different user id and password.

Line 6: This is the same statement as lines 1 and 3. The result is:

00001 FLD001 -------- --------------------------- G102A08R I am on DEV730 (or G10208R)

Line 7: I disconnect from DEV730, using the server name 'G102A08R'.

Line 8: I am in limbo, not connected to any database. To connect back to DEV740 I need to set the connection to it.

Line 9: Just to prove I am back on DEV740 I use the same statement I have used before to get:

00001 FLD001 -------- --------------------------- DEV740 I am local on DEV740

Now to the way I am more likely to use this, which is within a program. This RPG program basically does what I did with RSS. I am going to break the source code into parts to make it easier for me describe, and for you to understand. First are all the definitions.

01 **free 02 ctl-opt main(Main) option(*srcstmt) dftactgrp(*no) ; 03 dcl-ds Systems qualified dim(*auto : 10) ; 04 Partition char(10) ; 05 UserId char(10) ; 06 Password char(10) ; 07 end-ds ; 08 dcl-ds SingleSystem likeds(Systems) ; 09 dcl-ds Data extname('WHERE_AM_I') qualified dim(*auto : 10) ; 10 end-ds ;

Line 1: In 2024 all newly written RPG code must be in modern RPG, no excuses.

Line 2: This program has a Main procedure, therefore, it is linear and does not use the RPG cycle. I always add my favorite control option. And this program cannot run in the default activation group as it calls subprocedures.

Lines 3 - 7: This data structure array will contain the information for the remote partitions. It contains three subfields:

1. Name of the remote partition
2. User profile for remote partition
3. Password for the user profile

I have defined this array as an auto extending array of up to ten elements.

Line 8: I have defined this data structure to be the same as Systems by using the LIKEDS keyword.

Lines 9 and 10: This data structure array will contain the results from the SQL statements I will run on each partition. I have defined it to have the same layout as the file WHERE_AM_I.

Next is the main procedure, that I have called Main.

11 dcl-proc Main ; 12 GetSystems() ; 13 Connect() ; 14 //dow (*on) ; 15 GetData() ; 16 //enddo ; 17 on-exit ; 18 Disconnects() ; 19 end-proc ;

Line 11: Start of the Main procedure.

Line 12: This subprocedure will load the names, user ids, and passwords for each partition into the Systems data stricture array.

Line 13: In this subprocedure I connect to all of the remote partitions.

Line 15: This is subprocedure that gets the data from all the partitions. If I wanted to perform this more than once I would remove the comment markers ( // ) from line 14 and 16.

Line 17: I have an ON-EXIT group here to execute the connection disconnects no matter how this program ends.

The next subprocedure is the one that loads the details for each remote partition into the data structure.

20 dcl-proc GetSystems ; 21 %elem(Systems) = 0 ; 22 Systems(1).Partition = 'G102A08R' ; 23 Systems(1).UserId = 'SIMON730' ; 24 Systems(1).Password = 'pwd730' ; 25 Systems(2).Partition = 'DEV720' ; 26 Systems(2).UserId = 'SIMON720' ; 27 Systems(2).Password = 'pwd720' ; 28 end-proc ;

Line 21: By setting the number of elements in the array to zero initializes the array.

Lines 22 – 24: These are the details for DEV730.

Lines 25 – 27: And these are the details for DEV720.

Having loaded the remote partitions into the array, the next subprocedure connects to each of those partitions.

29 dcl-proc Connect ; 30 dcl-s System char(10) ; 31 dcl-s UserId char(10) ; 32 dcl-s Password char(10) ; 33 for-each SingleSystem in Systems ; 34 System = SingleSystem.Partition ; 35 UserId = SingleSystem.UserId ; 36 Password = SingleSystem.Password ; 37 exec sql CONNECT TO :System USER :UserId USING :Password ; 38 endfor ; 39 end-proc ;

Lines 30 – 32: I cannot use data structure subfields to connect. I need variables to hold the values from the array.

Line 33: The FOR-EACH group reads every element in the Systems data structure array and place the elements, one at time, into the SingleSystem data structure. Using a variable array my array only contains two elements, therefore, this group is only performed twice. If I was using a fixed length array the group would be performed for all the elements in the array whether or not they contain data.

Lines 34 – 36: Values are moved from the data structure subfields in the variables I defined in line 30 – 32.

Line 37: I use these to connect to the remote server.

As the connections have been established I can then go get the data from the files. The GetData subprocedure gets the data for the local partition, DEV740, before calling a subprocedure to get the data from the remote partitions.

40 dcl-proc GetData ; 41 dcl-s Elements uns(5) inz(%elem(Data : *max)) ; 42 %elem(Data) = 0 ; 43 exec sql SET CONNECTION DEV740 ; 44 exec sql DECLARE C0 CURSOR FOR SELECT * FROM WHERE_AM_I FOR READ ONLY ; 45 exec sql OPEN C0 ; 46 exec sql FETCH C0 FOR :Elements ROWS INTO :Data ; 47 exec sql CLOSE C0 ; 48 for-each SingleSystem in Systems ; 49 GetRemoteData() ; 50 endfor ; 51 end-proc ;

Line 41: I am defining this variable and initializing it to contain the maximum number of elements that the array Data could contain.

Line 42: Initialize the array Data.

Line 43: Connect to the local partition DEV740.

Line 44: Define a SQL cursor for all the columns in the file WHERE_AM_I.

Line 45: Open the cursor.

Line 46: Fetch results for the cursor up to the number of the maximum number of elements in the array Data, into the array Data.

Line 47: Close the cursor.

Lines 48 – 50: Call the subprocedure to get the data for each remote system.

How do I get the data from the remote systems?

52 dcl-proc GetRemoteData ; 53 dcl-ds Fetched dim(*auto : 500) likeds(Data) ; 54 dcl-s Elements uns(5) inz(%elem(Fetched : *max)) ; 55 dcl-s System char(10) ; 56 dcl-s Plus1 like(Elements) ; 57 %elem(Fetched) = 0 ; 58 System = SingleSystem.Partition ; 59 exec sql SET CONNECTION :System ; 60 exec sql DECLARE C1 CURSOR FOR SELECT * FROM WHERE_AM_I FOR READ ONLY ; 61 exec sql OPEN C1 ; 62 exec sql FETCH C1 FOR :Elements ROWS INTO :Fetched ; 63 exec sql CLOSE C1 ; 64 if (%elem(Fetched) > 0) ; 65 Plus1 = %elem(Data) + 1 ; 66 %elem(Data) += %elem(Fetched) ; 67 %subarr(Data : Plus1) = %subarr(Fetched : 1) ; 68 endif ; 69 end-proc ;

Line 53: This auto-extending array will contain the rows fetched for this one remote partition.

Line 54: This variable is initialized with the maximum number of array elements the array Fetched can have.

Line 55: Variable for the name of remote partition. I do not need the user profile and password again as I have already made the connection.

Line 57: I initialize the array FETCHED.

Line 58: I take the name of the partition from the SingleSystem data structure and move it into the variable System.

Line 59: I connect to the partition with that name.

Line 60: Cursor definition for the file WHERE_AM_I.

Line 61: Open the cursor.

Line 62: Fetch the number of rows contain in the variable Elements into the data structure array Fetched.

Line 63: Close the cursor.

Lines 64 – 68: This is where I merge the data in the array Fetched into the array Data.

Line 64: If the number of elements in the array Fetched is greater than zero then I know that some data was fetched from the file and is in the array Fetched.

Line 65: Update Plus1 with the value of the next unused element in the array Data.

Line 66: Extended the number of elements in the array Data by the number of elements in the array Fetched. I have to do this so I can do the next line.

Line 67: Here I am using the Substring Array built in function, %SUBARR, to move the elements in the array Fetched into the Data starting at the first unused element.

Last subprocedure is the one to disconnect from all the remote partitions.

70 dcl-proc Disconnects ; 71 dcl-s System char(10) ; 72 for-each SingleSystem in Systems ; 73 System = SingleSystem.Partition ; 74 exec sql DISCONNECT :System ; 75 endfor ; 76 exec sql SET CONNECTION DEV740 ; 77 end-proc ;

Lines 72 – 75: In this FOR-EACH group I am reading all of the elements of the Systems array and using the partition name contained within to disconnect from that partition.

Line 76: After disconnecting from all the remote partitions I then set the connection to the local partition, DEV740.

After compiling this program I started debug, added a breakpoint on the ON-EXIT line, line 17, and called the program. When the program debug broke at this line I could then check to see what is in the array Data:

> EVAL data DATA.FLD001(1) = 'I am local on DEV740 ' DATA.FLD001(2) = 'I am on DEV730 (or G10208R) ' DATA.FLD001(3) = 'Hello, I am on DEV720 '

This shows I was able to get the data from the file from all three of the partitions.

---

After I initially published this post Krister Karlsson added a really interesting comment. I think it is so helpful I am going to keep the comment and add its content here too.

Putting both user id and passwords in clear text in a procedure is not the best pratice for security.

There also exist a better, easier and more secure solution for this already builtin to the operating system, use the ADDSVRAUTE.

First make sure entries for the other systems exist in the Relation Database Directory, WRKRDBIRE, on the source system, otherwise add them with the ADDRDBDIRE command. But as you already can use the three part naming they should already be in place.

Then use the ADDSVRAUTE command to add the credintials for the server: Like you example:
ADDSVRAUTE USRPRF(*CURRENT) SERVER(G102A08R) USRID(SIMON730) PASSWORD('pwd730')
ADDSVRAUTE USRPRF(*CURRENT) SERVER(DEV720) USRID(SIMON720) PASSWORD('pwd720')

You can now use the three part name without hadling the user and password.

If you change the password on DEV720, just use the CHGSVRAUTE command on the source system:
CHGSVRAUTE USRPRF(*CURRENT) SERVER(DEV720) USRID(SIMON720) PASSWORD('newpwd720')

 

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