Wednesday, September 7, 2016

Run a SQL statement on another IBM i, part 2

sql to remote database using three party qualified name

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:

 

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

4 comments:

  1. Hi!, nice post, as always.

    is there any way to add the parameters of the compilation command, directly to the source?
    I mean, using DCL-OPT...

    I've tried that, but it seems you can't add anything is in the command.

    Thanks!

    ReplyDelete
  2. I cannot tell you how much I appreciate your articles. This one is 6 years old and helped me today. Thank you Simon.

    ReplyDelete
  3. When trying to specify a remote system, I am getting an SQL error. The following
    SQL State: 08001 Vendor Code: -30082 Message: [SQ30082] Authorization failure on distributed database connection attempt.
    Any thought on what needs corrected on either system. My user profile and password are the same on both system.
    I am able to use the "connect to" with user and password, but then am have trouble getting back to local data.

    ReplyDelete
    Replies
    1. After you disconnect from the remote partition are you:

      SET CONNECTION < my partition >

      Delete

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.