Run a SQL statement on another IBM i, part 2

In July I wrote about running SQL statements on remote IBM i systems using Query Management queries, and almost all of the comments upon that post mention using the three part qualified name to achieve the same result.

The three part qualified name, which I believe came out as part of IBM i 7.1 or one of its Technology Refreshes (TRs), consists the following elements, separated by a period:

database.schema.table

This can be translated into more familiar IBM i parlance as:

database.library.file or table

The first question that sprung to my mind was: what are the databases I can connect to? I can see all eligible databases by using the Work with Relational Database Directory Entry command, WRKRDBDIRE. I am not going to go into how to add entries to the Relational Database Directory, but I will provide a link to IBM's documentation on how to do it at the bottom of this post.

To make it easier to understand which systems I am connecting to in these examples my local IBM i is called THIS_SYS, and the remote one OTHER_SYS.

In its simplest for I can just use a Select statement to join files together on OTHER_SYS:

01 SELECT A.VALUE,B.DESC 02 FROM OTHER_SYS.REMOTELIB.CODEMAST A LEFT OUTER JOIN 03 OTHER_SYS.REMOTELIB.DESCRIPTN B 04 ON A.VALUE = B.VALUE 05 ORDER BY VALUE

Which gives me:

VALUE DESC ----- -------------------- 1 ONE 10 TEN 2 TWO 3 THREE 4 - 5 FIVE 6 SIX 7 - 8 EIGHT 9 NINE

As I used a "LEFT OUTER JOIN" the Description field is null for the fourth and seventh Values as there is no matching record in the DESCRIPTN table.

Now what happens if I try to do the same using the file CODEMAST on THIS_SYS:

01 SELECT A.VALUE,B.DESC 02 FROM THIS_SYS.MYLIB.CODEMAST A LEFT OUTER JOIN 03 OTHER_SYS.REMOTELIB.DESCRIPTN B 04 ON A.VALUE = B.VALUE 05 ORDER BY VALUE

Alas, this statement produces a SQL code of -512, which equates to message SQL0512, which tells me: "Statement references objects in multiple databases". In other words I cannot use files from different systems, which is a shame.

The only way I can think to overcome this is to create a table on THIS_SYS using the Select statement of the files on OTHER_SYS:

01 CREATE TABLE QTEMP/TESTTABLE AS 02 (SELECT A.VALUE,B.DESC 03 FROM OTHER_SYS.REMOTELIB.CODEMAST A LEFT OUTER JOIN 04 OTHER_SYS.REMOTELIB.DESCRIPTN B 05 ON A.VALUE = B.VALUE) 06 WITH DATA ;

Now I can use this new table to join with other files on THIS_SYS.

01 SELECT A.VALUE,B.DESC 02 FROM MYLIB.CODEMAST A LEFT OUTER JOIN 03 QTEMP.TESTTABLE B 04 ON A.VALUE = B.VALUE 05 ORDER BY VALUE

If you are interested in creating tables like this I recommend you read Creating a SQL table "on the fly".

I can use the Insert statement to copy rows/records from a table/file or tables/files on OTHER_SYS into an existing file on THIS_SYS:

01 INSERT INTO MYLIB/CODEDESC 02 (SELECT A.VALUE,B.DESC 03 FROM OTHER_SYS.REMOTELIB.CODEMAST A LEFT OUTER JOIN 04 OTHER_SYS.REMOTELIB.DESCRIPTN B 05 ON A.VALUE = B.VALUE)

Alas vice versa, inserting rows/records into a table/file on OTHER_SYS. gives me the -512 SQL Code.

How about if I needed to retrieve a value from a table/file on OTHER_SYS? The RPG code to do this is very simple:

01 dcl-s Value char(3) ; 02 dcl-s Desc char(20) ; 03 Value = '2' ; 04 exec sql SELECT DESC INTO :Desc FROM OTHER_SYS.REMOTELIB.DESCRIPTN WHERE VALUE = :Value ;

I compile the source code using the Create SQL ILE RPG Object command, CRTSQLRPGI, command with my standard options:

01 CRTSQLRPGI OBJ(MYLIB/PROGRAM1) + 02 SRCFILE(MYLIB/DEVSRC) + 03 COMMIT(*NONE) + 04 RPGPPOPT(*LVL2) + 05 CLOSQLCSR(*ENDMOD) + 06 DBGVIEW(*SOURCE)

If you do not recognize the RPGPPOPT parameter, line 4, you should read the Copy and Include section in the post about RPG compiler directives.

When I call the program I receive a new error, SQL code -805, or error message SQL0805, which has the description "SQL package PROGRAM1 in MYLIB not found at DRDA Server". What is a SQL package?

According to IBM's documentation:

SQL packages bind SQL statements in an application program to a relational database. They are used to enhance the performance of applications that use dynamic SQL support by allowing the application to reuse information about the SQL requests.

I need to create a SQL package to bind my SQL statements in my program to the remote database, i.e. OTHER_SYS.

I could use the Create SQL Package command, CRTSQLPKG, on THIS_SYS. It will generate an object with the object type of *SQLPKG, see below. I would then need to copy this to the remote system, OTHER_SYS.

Object Type Attribute PROGRAM99 *SQLPKG PACKAGE

Fortunately the CRTSQLRPGI will do all that for me if I use just two extra parameters:

01 CRTSQLRPGI OBJ(MYLIB/PROGRAM1) + 02 SRCFILE(MYLIB/DEVSRC) + 03 COMMIT(*NONE) + 04 RDB(OTHER_SYS) + 05 RPGPPOPT(*LVL2) + 06 CLOSQLCSR(*ENDMOD) + 07 SQLPKG(REMOTELIB/*OBJ) + 08 DBGVIEW(*SOURCE)

Line 4: RDB = Relational database. This would be OTHER_SYS.

Line 7: SQLPKG = SQL package name. As the library MYLIB does not exist on OTHER_SYS I want to put the SQL package in the library REMOTELIB. *OBJ will mean that this package will have the same name as my program.

When the program is compiled the SQL package is create on OTHER_SYS by the compiler. If I look at my job log I see:

Program PROGRAM1 placed in library MYLIB. 00 highest severity. SQL package PROGRAM1 in REMOTELIB at OTHER_SYS has been created.

Now when I run the program I get the values I expect returned from the Select statement:

DSPLY Value = <2> Desc = <TWO>

If your IBM i is not at the right release or PTF for the three part qualified names you can use the SQL Connect statement:

01 exec sql CONNECT TO OTHER_SYS ; 02 exec sql SELECT DESC INTO :Desc FROM REMOTELIB.DESCRIPTN WHERE VALUE = :Value ; 03 exec sql DISCONNECT OTHER_SYS ; 04 exec sql CONNECT RESET ;

Line 1: This is my statement to connect to the other database, OTHER_SYS. In my case I do not have to give a user and password. But if I did this statement would look like:

01 exec sql CONNECT TO OTHER_SYS USER :User USING :Password ;

Line 2: Same statement as the last example. As I have connected to the remote database I do not have to use the three part qualified name, just the schema and table.

Line 3: I am done on OTHER_SYS so I need to disconnect from it.

Line 4: This Connect statement resets my database connection to the local database, i.e. THIS_SYS.

I do need to have a SQL package on OTHER_SYS to run this program successfully. Again I use the RDB and SQLPKG parameters on the CRTSQLRPGI command.

These examples have shown that by using either method it is possible to get data from a remote system using SQL without having to using a Query Management query.

 

You can learn more about this from the IBM website:

• Working with the relational database directory
• SQL packages
• Create SQL package command, CRTSQLPKG
• Create SQL ILE RPG Object command, CRTSQLRPGI
• SQL statement CONNECT
• SQL statement DISCONNECT

 

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