Category Archives: IBM i

Executing SQL statements from within a CL with RUNSQL

Here’s a CL Command I didn’t know about, RUNSQL, which allows an SQL statement to be run from within a CL program without needing a source file.

Inevitably, I found this because I was looking for a quick solution to a transitory problem. I won’t bore you with the details but, what I wanted to do was create a temporary file, populate it with orders for a (user specified) date and then copy this file into a library that has been made available to an external ftp application.

Here is what I ended up with (field, file and library names have been changed for clarity and to protect the innocent):

  pgm &date
  dcl &date *char 8
  dcl &statement *char 150
  dcl &nbrrcd *dec (10 0)
  cpyf fromfile(template/orders) tofile(qtemp/orders) +
       mbropt(*add) crtfile(*yes)
  clrpfm qtemp/orders
  chgvar &statement value('insert into qtemp/orders +
                           select * from archlib/orders  +
                           where msgdat = ' *cat &date)
  runsql sql(&statement) commit(*none)
  rtvmbrd qtemp/orders nbrcurrcd(&nbrrcd)
  if cond(&nbrrcd *gt 0) then(do)
     cpyf fromfile(qtemp/orders) tofile(ftplib/orders) +
          mbropt(*add) crtfile(*yes)
  dltf qtemp/orders

It’s all pretty simple stuff but being able to embed the SQL statement right in the CL makes a conceptually simple solution very simple to implement.

flattr this!

Using the SQL with clause to group by calculated values

So here’s a question that cropped up recently: In an SQL Select statement, can you group by a calculated field (scalar function)?

The short answer is: No.

The longer answer is: Of course you can.

It did occur to me, last week, to simply post the script that I used to achieve this but there are a number of other things going on in there and I suspect that the unannotated code would be more confusing than clarifying. It is a useful, technique, however and one that I can see myself using again, so here is a simplified version of the problem along with the solution (file1 and field names have been changed for the sake of clarity).

So here’s the question: Can you provide a report showing the latest invoice number and invoice date for each customer (some selection criteria may apply)?

Since we have a sales file for which the sh_invoice_number field is updated when the transaction is invoiced, the first pass of this report is pretty simple:

select sh_sales_customer, max(sh_invoice_number), max(sh_invoice_date)
from sales_history
group by sh_sales_customer

Now for the wrinkle.

The sh_sales_customer field is the delivery point for the sales transaction. In many case, this is the same as the invoice account but there are cases where one invoice account encompasses several delivery points. Finance folk don’t care where goods are shipped, they just want to know where to send the invoice.

We have a file, account_numbers which maps the delivery point to the invoice account, if necessary. So the script needs to be changed so that it checks this file and uses either the retrieved value or the sales_customer, depending on the results of the lookup. The With clause is your friend.

You can’t Group By the calculated field, but you can use the With clause to factor the sh_sales_customer calculation into a subquery. And this is what we end up with:

 customer_accounts as 
   (select dp_delivery_point as ca_delivery_point,
              when an_invoice_account is not null then an_invoice_account 
              else dp_delivery_point
              end as ca_customer
    from delivery_points
    left outer join account_numbers on an_delivery_point = dp_delivery_point)
select ca_customer, max(sh_invoice_number), max(sh_invoice_date)
from sales_history
join customer_accounts on ca_delivery_point = sh_sales_customer
group by ca_customer

The delivery_points file contains all delivery points along with shipping information.


1 In case any of you youngsters are feeling confused by the terminology: a file (in this context) is a table, a record is a row and a field is a column. I am aware that I have the rather bad habit of using these terms interchangably, but I’m too lazy to go back and reword this post.


The field and file names should be reasonably self explanatory, but if you are struggling with what belongs where, here are some definitions:

SALES_HISTORY         SH_SALES_CUSTOMER     CHAR                   8                -
SALES_HISTORY         SH_INVOICE_NUMBER     INTEGER                4               0 
SALES_HISTORY         SH_INVOICE_DATE       DATE                   4                -

DELIVERY_POINTS       DP_DELIVERY_POINT     CHAR                   8                -

ACCOUNT_NUMBERS       AN_INVOICE_ACCOUNT    CHAR                   8                -
ACCOUNT_NUMBERS       AN_DELIVERY_POINT     CHAR                   8                -

The real files contain much more information, of course, but I have stripped these out in order to keep things reasonably simple.

flattr this!

Using an SQL subselect to insert records into a table: An example

The following bit of code should be reasonably self-explanatory but I was a little surprised to discover that there are no examples of doing this (for IBM i folks, at least) on the interwebnet.

So now there’s one.

insert into ignoreiln                                               
    slsrpt as (select * from ignoreiln where mtyp = 'SLSRPT'), 
    invrpt as (select * from ignoreiln where mtyp = 'INVRPT') 
  select a.mdir, 'INVRPT', a.siln, a.diln, a.riln, 
         a.dept, a.brnd, 'Auto Created' 
  from slsrpt a    
  left outer join invrpt b on b.mdir = a.mdir 
       and b.siln = a.siln and b.diln = a.diln 
       and b.riln = a.riln and b.dept = a.dept 
       and b.brnd = a.brnd 
  where b.siln is null

If you want to know more, I would recommend starting with the IBM i Information Centre.

flattr this!

Neat trick of the day: Sorting physical files with RGZPFM

It’s rare that you need to sort a database file (or table), but I have encountered a circumstance in which a sorted file had to be delivered. Being naturally lazy, I started looking around for the least-work method of achieving this and ended up looking at the RGZPFM command. This is a command I have used quite often to compress a physical file (it removes the deleted records), but this time around it was the reorganise part of Reorganise Physical File Member that I was interested in.

From the help text:

If a keyed file is identified in the Key file (KEYFILE) parameter, the system reorganizes the member by changing the physical sequence of the records in storage to either match the keyed sequence of the physical file member’s access path, or to match the access path of a logical file member that is defined over the physical file.

The important thing here is that any keyed file can be specified. So if I have an appropriately keyed logical file, I can very easily sort the physical.

Some names have been changed, but if I have a physical file called ITEMLIST and I want it sorted by field BARCODE, I need to first create an index:

create index ITEMLIST01          
on ITEMLIST (BARCODE);           
label on index ITEMLIST01        
is 'Logical on ITEMLIST (Keyed: BARCODE)'; 

Then I can easily reorder the physical file ITEMLIST into BARCODE sequence with the command:



flattr this!

DSPPGMLCK: Now with help

Here’s a utility that I wasn’t expecting to need again, but I have just encountered another program that sets a flag to indicate that it’s active and then (occasionally) fails to unset it. So, today, I dug out DSPPGMLCK again (it’s handy having the code on GitHub).

Since I was compiling it all anyway, I also too a moment to add some help text to the command. All of the usual updates can be found at the usual place.

flattr this!

Converting the current date to a number in SQL

This is handy if you find yourself needing to populate a numeric timestamp field (eight digit number, yyyymmdd) with the current date.

update TheFile                                                
set TheDate = Dec(Replace(Char(current date, ISO), '-', ''), 8, 0)
where TheSelection

Obviously, TheFile is the file you are populating, TheDate is the field and TheSelection is whatever selection criteria you happen to be using.

flattr this!

Converting dates to numbers in RPG

I keep encountering situations where I have to write a record to some painfully old file that stores a date as an integer. Converting a date to an integer is one of those tasks that is very simple to accomplish but which I never remember how to do.

So, for future reference, here is the command for converting the current date to a seven digit integer (cyymmdd format), wrapped in a bit of code that displays the results because we all like to know what we’re writing befor we write it.

D DateNum         s              7s 0                                      
     DateNum = %dec(%char(%date():*cymd0):7:0);                            
     dsply DateNum;                                                        
     *INLR = *ON;                                                          

flattr this!

Vim syntax highlighting for the IBM i

As you may have noticed, some of the personal projects I have mentioned on here have been written for the IBM i. I generally develop these locally in Vim and then copy the source files to a server so that I can compile and test them as and when I have a chance.

I like Vim. When I want to just sit down and get something down, Vim provides a distraction-free environment with a powerful collection of features. One of these features is syntax highlighting, and Vim comes with syntax files for a host of languages as standard. Unfortunately, RPG is not one of the languages supported out of the box.

Inevitably, however, it didn’t take much time with Google to discover that someone else had the same thought as me some time ago. So I must say thank you to Martin Rowe of DBG/400 for this rather gorgeous collection of syntax files.

flattr this!

RGZLIB – Now with online help and working compile commands

A couple of weeks ago, I talked about adding help text to CL commands. At the time, I discussed the (rather trivial) process for adding help text to the DSPOSRLS command that I wrote some time ago. Now, I have mad a bit of time to do the same for the RGZLIB command.

While doing this, I discovered that the compile commands I’d included with the README had made a few too many assumptions about the state of my library list. These instructions would work fine if your development library was at the top of your library list but, if not, you would have enciountered issues with members not found.

This is now fixed and the latest version of the README, along with a help-enabled version of the command, can now be found at the usual place.

flattr this!

Adding help text to CL commands

CL commands are handy, and they become even handier when you add enough help text for other people to get the most out of your utilities. So I’m thankful to Ted Holt of the increasingly misnamed Four Hundred Guru for pointing out just how straighforward a process this is.

Because I should, I have gone back to some of my earlier commands and started adding a bit of help text. DSPOSRLS is, admittely, a pretty trivial command as far as documentation goes – there are no parameters and the command can only be used interactively – but I have the source in front of me and the command does provide a conveniently simple place to start.

First, you need a source file in which to enter your help text.
crtsrcpf Library/qpnlsrc

The command I didn’t know about was this one: Generate Command Documentation (GENCMDDOC):

The Generate Command Documentation (GENCMDDOC) command generates an output file which contains documentation for a Control Language (CL) command. The generated file will be one of the following:

  • If *HTML is specified for the Generation options (GENOPT) parameter, the file will contain HyperText Markup Language (HTML) source. The generated file can be displayed using a standard internet browser, and conforms to HTML 4.0 specifications. The information used to generate the file is retrieved from the specified command (*CMD) object and any command help panel group (*PNLGRP) objects associated with the command.
  • If *UIM is specified for the GENOPT parameter, the file will contain User Interface Manager (UIM) source. The generated source is an outline for the online command help for the command. The information used to generate the file is retrieved only from the specified command (*CMD) object. This option is intended to simplify the task of writing online help for CL commands.

The command doesn’t write your documentation for you, but it does provide all the structure you need if you want your command to look like all the other ones on your system. It also makes it quite easy to see how the panel group syntax works.

And so, to the command:
GENCMDDOC CMD(Library/DSPOSRLS) TODIR('/qsys.lib/Library.lib/qpnlsrc.file') TOSTMF(dsposrls.mbr) GENOPT(*UIM)

This will create a member (DSPOSRLS) of type UIM in file QPNLSRC. Change the type to PNLGRP and then you can start editing the the generated text until you have something meaningful. The syntax struck me as being very reminiscient of Markdown which makes it quite easy to get up to speed.

Once you are hapy with your help text, you will need to create the panel group:

And now you need to rebuild the command with the help text added:

Because the panel group and command are separate objects, you do not need to recreate the command every time you change the panel group. The panel group can be changed as and when you notice the inevitable typos and the updated version will be loaded when you next press F1.

Having the help text baked into your command is handy enough as it is but there is one more rather neat trick worth mentioning. If, once you are happy with your help text, you enter the following command
GENCMDDOC CMD(DSPOSRLS) TODIR('/home/expatpaul') TOSTMF('dsposrls.html') GENOPT(*HTML)

… GENCMDDOC will spit out an IFS file, named dsposrls.html, with all the same text, but marked up for HTML. I have sent the file to my home directory but, if you use TODIR('/www/apachedft/htdocs'), the file will be created in the default location for HTML files that are served by the Apache HTTP server for i.

flattr this!