Run a SQL statement on 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:

• CRTQMQRY
• STRQMQRY

 

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