Using SQL to copy data between partitions

I work in a multi-partition environment and frequently use the three part name in a SQL statement to "pull" data from one partition onto another. I was recently asked if I could give some more examples of it in action.

The three part names describes a method where I can get information from another SQL database. With IBM i that translates to another partition. Using IBM i terms the format of the three part name is:

partition.library.file

Used in its simplest it could just be:

SELECT * FROM RMTSYS.MYLIB1.TESTFILE

Before I get started I need to explain the names of the partitions I will be using:

• LCLSYS:  This is the name of the local partition, the one I am working on
• RMTSYS:  This is the remote partition, the one I am going to be sending or retrieving data from

I have the same user id name and the same password, therefore, I do not need to signon to the remote partition. If they were different I would need to use the CONNECT TO statement. I don't think I have written about that, I will have to do so.

My example scenario uses the following:

Partition	LCLSYS	RMTSYS
Library	MYLIB	MYLIB1
File/table	TESTFILE	TESTFILE

First I need to add data to both files. This I can do this from the local partition, LCLSYS.

01 INSERT INTO RMTSYS.MYLIB1.TESTFILE VALUES('1','REMOTE'),('2','REMOTE'),('3','REMOTE') 02 INSERT INTO MYLIB.TESTFILE VALUES('11','LOCAL'),('12','LOCAL'),('13','LOCAL')

Line 1: I am using the three part name to designate the table/file on RMTSYS into which I want to insert the data into. Here I am inserting three rows in the one statement.

Line 2: This is the equivalent on the local partition.

Using the three part name in a Select statement I can see those three added records in the remote file:

SELECT * FROM RMTSYS.MYLIB1.TESTFILE

Which shows me:

F1 F2 -- ------ 1 REMOTE 2 REMOTE 3 REMOTE

Inserting constant values into a file are one thing. What about results from a sub-query? I can insert data from the remote partition:

INSERT INTO MYLIB.TESTFILE SELECT * FROM RMTSYS.MYLIB1.TESTFILE

I can use this basic Select statement to view the contents of the file on the local partition:

SELECT * FROM TESTFILE

And the data from the remote partition's file is in the local file.

F1 F2 -- ------ 11 LOCAL 12 LOCAL 13 LOCAL 1 REMOTE 2 REMOTE 3 REMOTE

What about the other way? Inserting data into the remote partition from the local partition using a sub-query:

INSERT INTO RMTSYS.MYLIB1.TESTFILE SELECT * FROM MYLIB.TESTFILE

Alas, I receive the following error in ACS's Run SQL Scripts:

SQL State: 56023 Vendor Code: -512 Message: [SQL0512] Statement references objects in multiple databases. Cause . . . . . : The statement refers to objects that reside on multiple databases.

After pondering how I could overcome this I came up with a solution that involved embedded SQL statements in a RPG program. If SQL allows to me insert constant values into the remote file, I am able to do that in a program.

01 **free 02 dcl-ds Data extname('TESTFILE') qualified ; 03 end-ds ; 04 dcl-ds DS_Array likeds(Data) dim(10) ; 05 dcl-s Rows packed(2) inz(%elem(DS_Array)) ; 06 dcl-s Counter like(Rows) ; 07 DS_Array(*) = x'FF' ; 08 exec sql DECLARE C0 CURSOR FOR 09 SELECT * FROM TESTFILE 10 FOR READ ONLY ; 11 exec sql OPEN C0 ; 12 exec sql FETCH C0 FOR :Rows ROWS INTO :DS_Array ; 13 exec sql CLOSE C0 ; 14 Rows = %lookup(x'FF' : DS_Array(*).F1 : 1) - 1 ; 15 for Counter = 1 to Rows ; 16 Data = DS_Array(Counter) ; 17 exec sql INSERT INTO RMTSYS.MYLIB1.TESTFILE VALUES(:Data) ; 18 endfor ; 19 *inlr = *on ;

Lines 2 - 3: I am defining this data structure, by using the EXTNAME I am using its definition to define the subfields.

Line 4: Here I am defining a data structure array. Thanks to the LIKEDS it is defined to be identical to Data. This array only has 10 elements as I am only working with six records.

Line 5: I have defined this variable to contain the number of elements there are in the data structure array. I will be using this in my SQL Fetch statement.

Line 6: This variable will be used later in a For group.

Line 7: I am initializing the first position of every element in the array with the hexadecimal value of 'FF'. Why? Now I can easily find the first unused element in the array. I never feel safe searching for the first blank element in an array as blank could be a valid value. I always play safe knowing that no-one ever enters hex 'FF' into their data.

Lines 8 – 10: I am defining a SQL cursor to that I can get the data from my local TESTFILE.

Line 11: I open the cursor.

Line 12: Here I am performing a multiple row Fetch, fetching the same number of rows from the file as there elements in the data structure array.

Line 13: I have to close the cursor.

Line 14: I only want to use the used elements in the data structure array. By all means "reading" all the elements in this 10 element array would not be a big deal, but what would happen if this array had 9,999 elements? I need to determine the number of used elements and only perform the For group that number of times.

I could have done this several ways. Today I decided to use the %LOOKUP BiF. As I initialized the array with hex 'FF' when I find the first element that is equal to that I have the first unused element. I then subtract 1 from that number and I have the last used element.

Lines 15 – 19: My For group, this will only be performed the same number of times as there are used elements in the data structure array.

Line 16: I need to move the values in the element of the data structure array into the data structure Data.

Line 17: The insert statement inserts the contents of the data structure into the file in the remote partition.

This may seem a little complicated, but it does work. I can use this statement to retrieve the data from the remote partition:

SELECT * FROM RMTSYS.MYLIB1.TESTFILE

And I can see the records added to the file:

F1 F2 -- ------ 1 REMOTE 2 REMOTE 3 REMOTE 11 LOCAL 12 LOCAL 13 LOCAL

As I now have data from the remote partition in the local file I can use the Delete statement to delete those records from my files:

01 DELETE FROM MYLIB.TESTFILE WHERE F2 = 'REMOTE' ; 02 DELETE FROM RMTSYS.MYLIB1.TESTFILE WHERE F2 = 'LOCAL' ;

Line 1: This deletes the records from the local file that were copied from the remote partition.

Line 2: And this does the opposite, it deletes the records copied form the remote file that were copied from the local file.

Last, but not least, I can update the remote file:

UPDATE RMTSYS.MYLIB1.TESTFILE SET F2 = 'NOT HERE'

There is nothing special in this statement, except for the three part name.

The results, below, show that the values in the field F2 have been changed.

SELECT * FROM RMTSYS.MYLIB1.TESTFILE F1 F2 -- -------- 1 NOT HERE 2 NOT HERE 3 NOT HERE

I am sure you can think of many better ways of using the three part name than the simple examples I have given.

 

December 6, 2023
I found a way to use Table Functions with the 3 part name. You can read about it: An earlier way to get Table function data using 3 part name

August 22, 2023
REMOTE_TABLE added to allow you to use 3 part name to connect to a remote table function: Now able to 3 part name table functions

 

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