Wednesday, July 6, 2016

Run a SQL statement on another IBM i

strqmqry to get data from another ibm i

I work in an environment with many different IBM i systems. I use the word "systems" as some of these are partitions on the same PowerSystems server, while others reside on different servers. Often there is a need to extract information from one system, and send it to another where it will be used.

I have previously written about using DDM files to get information from other systems. But as I use SQL more I find they are limiting, as I cannot extract information from a DDM file using SQL statements. If only there was a way to extract data from one system by using a SQL Select statement issued from a second system.

After searching the new KnowledgeCenter I discovered a way!

If I placed my SQL statement in a Query Management Query object, I could then use the Start Query Management Query command, STRQMQRY, to execute it on another system.

If you are not familiar with Query Management (QM) Queries they are, in very simple terms, a way to put a single SQL statement in an object that can then be executed. There is a whole lot more you can do with them, but I am not going to go into the details here. I always compose the statement myself in a source member, with the source type of "QMQRY", and then compile it using the Create QM Query command, CRTQMQRY.

In this scenario I want to extract data from the Order Header file on another system, SYSTEM2. I need to have a copy of the Order Header file, ORDHDR, from the other system on the system I am composing the QM query upon. Fortunately, the same software is used on both systems. If it was not I could simply use the SAVRSTOBJ to copy an empty ORDHDR to the system I am programming upon. Like this, which would be executed on SYSTEM2 and what will be the remote system to my program:

CRTDUPOBJ OBJ(ORDHDR) FROMLIB(PRODLIB) OBJTYPE(*FILE)
            TOLIB(MYLIB) CST(*NO) TRG(*NO) FILEID(*NO)
            ACCCTL(*NONE)

SAVRSTOBJ OBJ(ORDHDR) LIB(MYLIB) RMTLOCNAME(SYSTEM1)
            OBJTYPE(*FILE)

Now I can compose the SQL code for my QM query:

01  SELECT CUSTID,ORDDTE,ORDTME,ORDNBR,ORDSTS,ORDAMT,
02         CONCAT(DIGITS(ORDDTE),DIGITS(ORDTME)) AS DATETIME
03    FROM PRODLIB.ORDHDR
04   WHERE CONCAT(DIGITS(ORDDTE),DIGITS(ORDTME))
05         BETWEEN &START AND &END

Line 2: I am using the DIGITS to convert the numeric date and time fields to alphanumeric, which I am concatenating together with the CONCAT, and giving this new column the name DATETIME.

Line 3: I am using the dot as the separator between the schema (library) and the table (file), rather than the slash, as I will be executing the QM query using SQL naming conventions.

Lines 4 and 5: I want to select a date and time range of dates. Therefore, I concatenate the date and time and then use the BETWEEN to give the from and to values, which will be passed when the STRQMQRY command is used.

I compile the source code using the CRTQMQRY command, which produces an object with the type of "*QMQRY".

Then I have this very simple CL program to show how the STRQMQRY command is used:

01  PGM

02  DCL VAR(&START) TYPE(*CHAR) LEN(15)
03  DCL VAR(&END) TYPE(*CHAR) LEN(15)
04  DCL VAR(&SYSTEM) TYPE(*CHAR) LEN(8)
05  DCL VAR(&USERID) TYPE(*CHAR) LEN(10)
06  DCL VAR(&PASSWORD) TYPE(*CHAR) LEN(10)

07  CHGVAR VAR(&START) VALUE('''1160501070000''')
08  CHGVAR VAR(&END) VALUE('''1160602210000''')

09  CHGVAR VAR(&SYSTEM) VALUE('SYSTEM2') 

10  STRQMQRY QMQRY(MYLIB/QMQRY1) +
11             OUTPUT(*OUTFILE) +
12             OUTFILE(QTEMP/&SYSTEM) +
13             OUTMBR(*FIRST *ADD) +
14             NAMING(*SQL) +
15             SETVAR((START &START) +
16                    (END &END)) +
17             RDB(&SYSTEM) +
18             RDBCNNMTH(*RUW) +
19             USER(&USERID) +
20             PASSWORD(&PASSWORD)

21  MONMSG MSGID(QWM2701) EXEC(+
22    SNDPGMMSG MSG('Unable to connect to server ' +
23                   || &SYSTEM) +
24              TOMSGQ(*SYSOPR))

25  ENDPGM

Lines 7 and 8: This is where I am creating the values that will be passed to the QM query. As the parameters are alphanumeric I have to start and end each value with the three apostrophes ( ' ). I want all the invoice information in the range May 1, 2016 at 7:00 AM to June 2, 2016 at 9:00 PM.

Lines 10 – 20: This is the STRQMQRY command. I entered it this way to make it easier for you to see the command's parameters.

Line 11 and 12: When using a QM query to get data from another system I have to place it in an outfile. I am placing this file in the library QTEMP and calling the file the same name as the system I got the data from.

Line 14: I did say earlier that I will be using the SQL naming convention.

Lines 15 and 16: This is where I define the parameters I am passing to the QM query. I covered this in more detail in an earlier post about passing parameters to a Query.

Line 17: This is where I give the name of the other system that this will be executed upon.

Line 18: There are two possible values for this parameter, one for connection to multiple databases, and the other to one. By using *RUW I am stating that this connection is just to one database.

Lines 19 and 20: Did you really think I would give my user id and password?

Lines 21 – 24: There are multiple reasons why the STRQMQRY command might fail, once you have ensured that the Select statement is valid. In my testing I have found the most common reason is that the connection to the remote system is down, which results in a QWM2701. When that is issued by the STRQMQRY command this MONMSG captures the error and sends a message to the system message queue that the connection failed.

As you see this is surprisingly easy, and another reason why using and getting more familiar with SQL will help you with your work.

 

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.

11 comments:

  1. On our 7.1 TR 11 system we can use "select * from SERVER.LIBRARY.TABLE" . You must set up the relational DB entry using WRKRDBDIRE to the target server.

    By the way DDM files at version 7.1 and higher do allow SQL to use them for connecting to remote IBM servers as long as the target has the same relational DB.

    Matt

    ReplyDelete
  2. I 2nd what Matt says about the 'new' server.library.table ability. I utilize this ability to refresh tables in a Test Environment.
    It can also be used to create a table on the local system based on a remote system.

    Create Table LocalTable As (
    Select * From RemoteSys.RemoteLib.RemoteTab) With Data

    ReplyDelete
  3. Have you tried DRDA? Maybe it will be a little easier?

    ReplyDelete
  4. Why not do a :

    CONNECT TO remoteserver USER username USING password;
    SET CONNECTION remoteserver; -- ready to rock and roll

    SELECT statement -- do your SQL here...

    SET CONNECTION localserver; -- back to original


    Works in RUN SQL Scripts and Interactive SQL just fine as long as you have access to the system and the WRKRDBDIRE is used to configure the systems...

    ReplyDelete
    Replies
    1. Even better would be to just add the domain name as prefix to the table e.g. select * from domainname.schemaname.tablename

      Delete
  5. I work in an environment with 19 LPARs that I'm currently dealing with. We have a single developer LPAR that I have replicated select data from all the LPARs. I add the LPAR and Library name to all the files so that I know where they came from. I also convert any decimal date or time fields to date, time or timestamp data formats. I use stored procedures with date and timestamp data types, much more flexibility than converting numeric data to character. I also have a diagnostic file to capture any issues encountered as opposed to sending a message. I use a control table that has the LPARs defined, and a control table that has the stored procedures defined. I have an RPGLE program that uses my control files and the system catalog tables for processing so if a new environment is set up on one of the LPARS I don't have to adjust my process. I can send source samples if you're interested.

    ReplyDelete
    Replies
    1. I would be interested in seeing the code you describe. Contact me using the Contact Form on the right and I will reply with my email address.
      Thanks

      Delete
    2. Jon -- I too am interested as I've just been dropped into a multi-LPAR system without much recent sys admin experience... Could you forward your tools? thanks in advance, tim.

      Delete
  6. I know this is several years old but I am interested in some examples/sample code as well. Thank you.

    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.