Example SQL iQuery Script for IBM i

Since releasing SQL iQuery for the IBM i operating system, my customers have primarily been using a very cool feature of it: iQuery Scripts. iQuery Scripts, as the name implies, is a scripting language to do stuff on the system. Like any programming language it has control statements, loops, and variables.

One the thing I use iQuery Script for most is to process a list of things; object names, libraries, user profiles, user data, and so on. iQuery Script is effectively the easiest way to do that today and is the subject of today's edition of this newsletter.

Using SQL iQuery itself, one runs the now infamous RUNIQRY CL command. This allows you to run an SQL statement as specified on its SQL parameter. You can also specify SQL(*SRCF) to indicate that instead of running an ad hoc SQL statement, you want to run the contents of a source file member. When SQL(*SRCF) is used, the RUNiQRY command processing program looks at the SRCMBR, SRCFILE and SETVAR parameters and uses them to run the specified source file member.

In this context that source file member is considered an SQL iQuery Script.

For example, I can easily run the following SELECT statement interactively:

RUNIQRY SQL('SELECT * FROM QIWS.QCUSTCDT')        

But if I place that SELECT statement into the source member named CUSTLIST in QSQLSRC in my library list, I can also run it as an iQuery Script, like this:

RUNIQRY SRCMBR(CUSTLIST)        

and you would get identical results to running it directly. Pretty cool.

Where it gets good is that you can add additional statements to that iQuery Script source member. Things like headers, output targets, email options, and even additional SQL statements. You can control how the script statements flow using traditional conditional logic (i.e., IF/THEN/ELSE logic). Let's say you want to check if the User Profile running the iQuery Script is the user profile named SHIPPING. You've decided that only the SHIPPING user can run this script so you check if its them at runtime and continue or bail out. Let's see how that looks.

IF (*USER <> 'SHIPPING');
 #msg Invalid User. Request cancelled.
 return;
endif;

select * from QIWS.QCUSTCDT;        

Pretty simple, huh?

The *USER figurative constant is one of several constants built-into SQL iQuery Script, including *SYSNAME *DATE and several others.

I created iQuery Script with RPG IV and SQL conditional logic in mind. So the IF condition can be as shown previously, but it can also include SQL primitives, such IN or LIKE, basically anything you can specify on a WHERE clause can be specified on SQL iQuery Script IF statements. For example, if you wanted to see if the user profile was one of several users, such as SHIPPING, SHIPPER, SHIP1, SHIP2, etc. you could use the LIKE predicate on the if statement as follows:

IF (*USER LIKE 'SHIP%');        

Looping in iQuery Script

SQL iQuery has a pretty awesome looping capabilitity. Its powerful FOR EACH opcode let's you cycle through a resultSet, one row at a time. Let's say you need to change the owner of several objects. The CHGOBJOWN command supports one-at-a-time processing, there is no generic support. While there are other commands, such as CHGOWN that do generic-like action indirectly, it does not give you the granularity you may need. Besides, this is an example of SQL iQuery Script's FOR EACH opcode, not a challenge to find the best way to do Change Object Owner.

The FOR EACH opcode allows you to specify a SELECT statement and loop through each resultSet row and do whatever you want with the data. It is effectively a read loop.

FOR EACH  SELECT CUSNUM,LSTNAM,CITY,STATE 
 FROM QIWS.QCUSTCDT;         

You might say, "But Bob! where does the data go?" and you'd be correct. To read the resultSet row data into variables, we need to utilize another feature of SQL iQuery Script called Session Variables. These are similar to fields in RPG IV and CL but behave a little differently. More on this later.

To read the resultSet row into variables, you use the INTO clause, just like you normally would in any high-level language, like RPG IV.

 FOR EACH 
SELECT CUSNUM,LSTNAM,CITY,STATE 
INTO :CUSTNBR, :LASTNAME, :CITY, :STATE
 FROM QIWS.QCUSTCDT;         

The variables named &CUSTNBR, &LASTNAME, &CITY, and &STATE are initialized with the current row's data by the FOR EACH opcode. Where do you define those variables? Well, you don't. This is where iQuery Script is different from RPG IV and CL; iQuery Script dynamically defines variables when you use them. No need to define them first, then use them. Just use them.

Now you can do something with that data. In my case I want to simply write it to the joblog. So here's that logic.

FOR EACH 
SELECT CUSNUM,LSTNAM,CITY,STATE 
INTO :CUSTNBR, :LASTNAME, :CITY, :STATE
 FROM QIWS.QCUSTCDT; 
#sndmsg Customer &CUSTNBR for &LASTNAME Address: &CITY, &STATE
endfor;        

The #SNDMSG is used to write an *INFO message to the joblog. The session variables are identified in iQuery Script the same way they are in CL programs. That is, they start with an ampersand symbol followed by the variable name. The only exception is on the INTO clause. On an INTO clause you are allowed to use either the colon to match that of RPG IV or the ampersand; your choice.

Okay, so that gives you the basics of looping in iQuery Script. Now lets look at a working script that allows you to change the object owner for a list of objects. Here is that script. (Remember, LinkedIn wordwraps code for some reason)

     -- SQL iQuery Script  -- Change Object Owner
     -- Input parameters:  OBJ  (Object name, generic full, or *ALL)
     --                    LIB  (Library name or *LIBL, *ALLUSR, etc.)
     --                    TYPE or OBJTYPE (Object type)
     --                    NEWOWN or NEWOWNER (New object owner)
     --                    OLDOWN or OLDOWNER (Old Owner's authority *REVOKE *SAME)
     --  Specify the parameters using the SETVAR parameter of the RUNiQRY command.
     --    e.g.,  RUNiQRY SRCMBR(CHGOWNER)
     --                     SETVAR((LIB MYLIB) (OBJ *ALL) (NEWOWN COZZI) (OBJTYPE *PGM))

if not defined(&LIB);
  #sndmsg LIB required.  SETVAR((LIB mylib))
  return;
end if;

if not defined(&obj);
  #sndmsg OBJ required.  SETVAR((OBJ PGM*)) or SETVAR((OBJ *ALL))
  return;
end if;

if not defined(&newowner) and not defined(&NEWOWN);
  #sndmsg NEWOWNER required.  SETVAR((OBJ PGM*) (NEWOWNER QPGMR))
  return;
end if;

if isNotEmpty(&NEWOWN);
 &NEWOWNER = &NEWOWN;
endif;

if not defined(&OBJTYPE) and not defined(&TYPE);
  #sndmsg OBJTYPE required.  SETVAR((OBJTYPE *PGM)) or SETVAR((OBJTYPE *ALL))
  return;
end if;

if isNotEmpty(&TYPE);
 &OBJTYPE = &TYPE;
endif;

     -- Write each CL command to the joblog as a *RQS (*CMD) message
#logcl true

     -- Read a list of the specified objects
     -- then change the owner for each object read
for each
 select objlib, objname, objtype, objowner
  INTO &L, &N, &T, &OldOwn
   from table(SQLTOOLS.object_list('&LIB','&OBJ','&OBJTYPE')) OL;

 cl: CHGOBJOWN OBJ(&L/&N) OBJTYPE(&T) NEWOWN(&NEWOWNER) CUROWNAUT(*SAME);
end for;        

The first 6 conditional statements check if the input parameters are specified. There is also some flexibility provided in this script for those parameter names. For example, the Object Type parameter can be OBJTYPE or TYPE and the New Object Owner parameter may be NEWOWN or NEWOWNER. It would be trivial to add an additional "Current Owner Action" parameter to the script to control the CUROWNAUT parameter of the CHGOBJOWN command.

After the conditional statements, I turn on a flag that causes iQuery Script to write any CL command it processes to the joblog as a *RQS message. This allows you to do the F9/F8 thingy and retrieve that command within your interactive job. In batch jobs its useful to log the CL commands for reference purposes.

The FOR EACH loop is then used to retrieve a list of objects based on the input parameters passed to the script. I'm using my own OBJECT_LIST table function from SQL Tools (another no-charge product I created) because I can't rely on the IBM-provided table functions returning the same results from V7R2 through V7R5 like I can with SQL Tools. So since they perform just as well or sometimes better than their IBM equivalents, I tend to favor SQL Tools. If you don't have SQL Tools you can get them over on my Github.com/bobcozzi page. Otherwise you can use IBM's OBJECT_STATISTICS instead of OBJECT_LIST. However, if you are running V7R2, the library name (OBJLIB) column does not exist in OBJECT_STATISTICS, but there are work-arounds for that too.

The syntax of the FOR EACH opcode is:

 FOR EACH <sql-SELECT-statement>;
 -- additional code goes here
end for;        

The SELECT statement immediately follows the FOR EACH opcode. the INTO clause and host variable list must follow the SELECT's list of field/column names an appear before the FROM clause. Since iQuery Script statements are terminated with a semicolon, they can span multiple lines. Note: There are also iQuery Directives, such as #sndmsg and #logcl used here. Directives, unlike statements and opcodes, are single-line entities that being with the hashtag (pound sign for those of you, like me who are over 50). For example:

#SNDMSG hello world        

There is no terminating semicolon as # directives auto-terminated at the end of the line.

There is also the CL: directive. This enables the iQuery Script processor to run the statement as an IBM i CL command. This is similar to the CL: directive supported in other SQL processors on the systems, such as RUNSQLSTM and IBM ACS.

CL commands in this context are required to be terminated with a semicolon as well. Thus they may also span multiple lines.

The above example reads the object names from the user-specified library and then issues the CHGOBJOWN command for each of those objects. The ENDFOR statement closes the loop and it cycles back to the FOR EACH where the next row is retrieved.

If EOF is detected, the code within the loop is not performed and the logic branches to the ENDFOR statement and continues. This EOF logic is built into the FOR EACH opcode.

Calling the Example Script

To run this script once you've stored it into a source file member, use RUNIQRY as follows:

RUNiQRY SRCMBR(CHGOWN) SETVAR((OBJ INV*) (LIB TESTLIB) (TYPE *PGM) (NEWOWN COZZI))        

The script in the above example is stored in a source member name CHGOWN and is passed the OBJ, LIB and NEWOWN parameter. The values for which are "INV*" in "TESTLIB" to have their object owner changed to "COZZI" respectively.

Caveats

SQL iQuery Script is a full programming language. It is interpreted, not compiled so it is not going to run as fast as compiled RPG IV but it does run fast enough for its intended purpose. Since it has no RPG cycle or startup overhead, however in "start to finish" benchmarks with RPG, CL and iQuery Script, iQuery Script runs just about the same as a compiled CL program.

When an SQL iQuery Script runs, the final statement controls whether a resultSet is returned to the caller and then routed to the output media, or if it is run as part of the Script itself. If the final statement is a SELECT statement, then that statement is actually processed back in the RUNiQRY engine, not in iQuery Script. Thus you can use SQL iQuery Script to create resultSets that are used for Excel output, printed output, displayed to the user, sent back to the web/cgi interface, or written to the IFS. By default, output for iQuery is routed to the display for interactive jobs, print for batch jobs, and stdout (standard output) for CGI/Web jobs.

The ENDxxx statements in iQuery Script can be one or two words; it figures it out. That is, "END FOR" and "ENDFOR" are the same, as are "ENDIF" and "END IF". So pick your favorite style and just go with it.

I have clients with hundreds of SQL iQuery Scripts that do things like generating reports, pulling in data from a remote partition, processing their entire end-of-day routine, and generate Excel files and email those Excel files to end-users. Once client generates a daily Google Chart graph as an HTML page and emails it to the manufacturing floor manager so he can review individual time studies.

There are thousands of lines of iQuery Script code in use everyday, much of which has been running for over 10 years without a single issue. That's a pretty great track record.

Today SQL iQuery combined with IBM "Services" and SQL Tools provides a way to do things using SQL that has moved me to abandon CL for any "programmer tool" I need. I now use iQuery Script do system utility type stuff, systems admin reports, and much more. I simply compose an SQL statement that does what I need, (typically using IBM ACS RUNSQL Scripts or a 3rd party query client named dBeaver) then stuff it into a source file member and use it as an iQuery Script--enhancing it were needed. It is one reason why I complete my assigned tasks so quickly for my clients--No more Rube Goldberg CL routines with outfiles and quasi RUNSQL to create cluggy Views just to re-read the same data.

Once again, SQL iQuery is a no-charge licensed program available free to all IBM i Customers and can be download from my github page.

That's all there is to it.