Wednesday, February 9, 2022

Using SQL to copy data between partitions

sql 3 part name examples

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.

 

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

7 comments:

  1. Hi Simon.
    Very nice info, but as i recall it the, "remote" has to be defined in WRKRDBDIRE before it is possible.
    Best regards Jan

    ReplyDelete
    Replies
    1. I agree. I wrote about setting up connections using WRKRDBDIRE here.

      Delete
  2. Reynaldo Dandreb MedillaFebruary 10, 2022 at 8:22 AM

    thanks for sharing that Simon, like your hex'FF' style in array

    ReplyDelete
  3. Hi Simon,
    To access another partition, we have to enter an user id ans a password.
    How can we integrate it here?
    Best regards,
    Mirel

    ReplyDelete
    Replies
    1. I do need to write about this.
      You would use CONNECT TO and SET CONNECTION.
      I will be showing this live at Common's FOCUS22.

      Delete
  4. Gustavo Vald├ęs ChavanaFebruary 11, 2022 at 6:52 AM

    Muy buen Tip, muchas gracias

    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.