Tag: CL

Adding variables to ad-hoc SQL queries with CL

Some time ago I mentioned using REXX to build and execute ad-hoc SQL queries. For the sake of completeness, here is the same solution implemented in CL using the RUNSQL command.

The program does exactly the same as the previously describe REXX script — extracts all records flagged with today’s date so that they can be reviewed at a later date. And here it is:

pgm
dcl &date6 *char 6
dcl &date8 *char 8
dcl &statement *char 255

/* First retrieve the current system date (QDATE)                             */
/* and convert it to an 8 character value                                     */
RTVSYSVAL  SYSVAL(QDATE) RTNVAR(&DATE6)
CVTDAT     DATE(&DATE6) TOVAR(&DATE8) FROMFMT(*JOB) TOFMT(*YYMD) TOSEP(*NONE)

/* Next, build the SQL statement                                              */
CHGVAR     VAR(&STATEMENT) +
           VALUE('insert into EXPATPAUL1/INPUTDATA' *bcat +
                 'select * from EXPATPAUL1/PRODDATA where dhdate =' *bcat +
                 &date8)
runsql sql(&statement) commit(*none)

endpgm

Using QShell and CL to work with Stream files in the IFS

It turns out that there is no simple way of generating a list of files currently residing in a folder on the IBM i IFS. Simple, in this case, would be a command like DSPLNK OUTPUT(*FILE). An API does exist, but a combination of not enough time and too much lazy proved to be quite a disincentive for me going down that route.

The issue was that we recieve a number of very big files and, to save a bit of bandwidth, these files were being zipped before being sent to the IBM i. Dropping the file into the IFS and unzipping it was easy enough but then I found myself with an archive folder containing one of more files. While I can set the name of the folder into which the files should be extracted, I have no way of determining beforehand what the file names will be.

Here’s a solution:

/* -------------------------------------------------------------------------- */
/* Program     : EXTRACTZIP                                                   */
/* Description : Retrieve and unpack a zipped archive                         */
/* Written by  : Paul Pritchard                                               */
/* Date        : 27/05/2015                                                   */
/* -------------------------------------------------------------------------- */
pgm
dcl &library *char 10 value('MYLIB')
dcl &fromfile *char 50
dcl &tombr *char 50 value('/qsys.lib/qtemp.lib/IMPORTP.file/IMPORTP.mbr')
dcl &dltfile *char 50
dclf EXTRACTP

/* Retrieve and the zipped file and unzip it.                                 */
/* I won't bore you with the details here, but the the production program is  */
/* retriving the ZIP file from an FTP server and using PKZIP to unzip it.     */
/* The extract directory is /home/EXPATPAUL/EXTRACTFLR which now contains one */
/* or more stream files                                                       */

/* Retrieve a list of extracted files                                         */
/* First, I use QShell to list the files in EXTRACTFLR. The output of this is */
/* redirected to ExtractedFiles.TXT.                                          */
/* In order to use this information, I copy the ExtractedFiles.TXT stream     */
/* to an ad-hoc physical file (QTEMP/EXTRACTP)                                */
qsh cmd('ls /home/EXPATPAUL/EXTRACTFLR/ > /home/EXPATPAUL/ExtractedFiles.TXT')
crtpf file(QTEMP/EXTRACTP) rcdlen(20)
cpyfrmstmf fromstmf('/home/EXPATPAUL/ExtractedFiles.TXT') +
           tombr('/qsys.lib/qtemp.lib/EXTRACTP.file/EXTRACTP.mbr') +
           mbropt(*REPLACE)

/* And now I can use QTEMP/EXTRACTP to drive my way through EXTRACTFLR and    */
/* copy each of the files in the archive into the IMPORTP physical file.      */
dowhile '1'
    rcvf
    monmsg msgid(CPF0864) exec(LEAVE)

    /* Copy the next sream file from the archive                              */
    chgvar &fromfile value('/home/EXPATPAUL/EXTRACTFLR/' *tcat &EXTRACTP)
    cpyfrmstmf fromstmf(&fromfile) tombr(&tombr) mbropt(*add) +
               STMFCCSID(*PCASCII)

    /* and then delete the stream file                                        */
    chgvar &dltfile value('rm /home/EXPATPAUL/EXTRACTFLR/' *tcat &EXTRACTP)
    qsh cmd(&dltfile)

    enddo

/* Clean up and exit                                                          */    
qsh cmd('rm /home/EXPATPAUL/ExtractedFiles.TXT')
dltf qtemp/EXTRACTP

endpgm

It should go without saying that some of the names have been changed and that the above program should be treated as a sample only.

Being able to move information between the QShell/IFS and traditional i5/OS environments is both useful and (in my experience) increasingly important. Although it does take a bit of thinking about, it isn’t difficult which is why I find that the oft-seen solution of “buy this tool” is both disappointing and (often) overkill.

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)
     enddo
  
  dltf qtemp/orders
  
  endpgm

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.

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.

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.

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:
CRTPNLGRP PNLGRP(Library/DSPOSRLS) SRCFILE(Library/QPNLSRC)

And now you need to rebuild the command with the help text added:
CRTCMD CMD(Library/DSPOSRLS) PGM(*LIBL/DSPOSRLS) SRCFILE(Library/QCMDSRC) SRCMBR(*CMD) HLPPNLGRP(DSPOSRLS) HLPID(*CMD)

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.

Neat trick of the day: Editing database files as if they were stream files

The background

Today I found and interface that works1 by copying a stream file into a database table and then attempting to parse the resulting mess. It’s been running for almost ten years and today it broke because some of the numbers in the stream file had decimal points in them2.

The interface in question is due to be retired shortly, so the program isn’t going to be fixed and the solution is to fix the data and then chase the folks that provided the stream file.

Have you ever tried to query or update a table that not only contains unstructured data but also has Carriage Return and Line Feed characters scattered throughout?

The solution

Work with Object Links is your friend.

This is the command you need:

WRKLNK '/QSYS.LIB/library.LIB/file.FILE/member.MBR'

The editing functionality is a bit basic, but it’s a lot easier than trying to database functionality to edit non-database data.

It’s also worth noting that the Work with Object Links actually knows what to do with Carriage Return and Line Feed characters.

Footnotes

1 In the loosest sense of the word.
2 Yes, really.

Killing queries with SQL

Back in the 20th Century, Query was a handy tool for… well, writing ad-hoc queries. The form-filling interface is remarkably simple – it really can be used by anyone – and rather inflexible. Now, of course, we have SQL – which is a lot more powerful – and a collection of third-party tools to help with the building your query.

But Queries exist. Some of them are on menus. They must be destroyed!

Here’s an approach:

RTVQMQRY QMQRY(querylib/queryname) SRCFILE(sqllib/QSQLSRC) ALWQRYDFN(*ONLY)

The Retrieve Query Management Query (RTVQMQRY) command allows you to retrieve Structured Query Language (SQL) source from a query management query (QMQRY) object. The source records are placed into an editable source file member.

But you don’t have to have a QMQRY object to use this command. ALWQRYDFN allows you to specify whether query information is taken from a query definition (QRYDFN) object when a query management (QMQRY) object cannot be found. There are three possibilities:

  • *NO: Never use the QRYDFN. If the QMQRY object doesn’t exist, the command will fail
  • *YES: If the QMQRY object doesn’t exist, use the QRYDFN
  • *ONLY: Ignore the QMQRY object and just use the QRYDFN

Once the command is executed, you will have a member (called queryname) in library/member sqllib/QSQLSRC, based on query querylib/queryname.

And now your options are endless.

RGZLIB: Reorganise Library

The IBM i CL command Reorganize Physical File Mbr (RGZPFM) removes deleted records from a specified member of a physical file and, optionally, reorganizes that member. It’s a handy command to know about but reorganising physical files member by member can become a very painful process if you have a lot of files to reorganise (after manually purging a large volume of test data, for example).

I did, at a previous employer, have access to a RGZLIB command. This reorganised all of the physical files in a library and made for a much more convenient purge. Unfortunately, this is ont a system supplied CL command and my current emplyer doesn’t have it.

So I wrote my own version.

RGZLIB is a simple command that will reorganise all of the physical file members in a selected library. Depending on the size of the library to be reorganised (and the size of the files in that library) this process can take a while to run, so I strongly suggest that you submit the command to batch.

My original plan was to write this in RPG and include some additional file clean-up options. However, it turns out that there is no API to retrieve a list of files in a library so the program, as it stands, is a very simple CL affair. If I do code up the additional functionality, I will put it into a seperate command to avoid over-complicating things.

As ever, the source is available on GitHub and you are free to browse or download it from there.

Fun with QShell

One thing that a lot of people fail to realise is that the IBM i includes a command environment based on POSIX and X/Open standards. This environment is known as QShell and consistes of two parts:

  • The shell interpreter (qsh) reads commands from an input source, interprets each command, and then runs the command using the services of the operating system.
  • The ulilities – external programs that provide additional functions.

You can use the QShell interactively, but the fun begins when you start integrating it with CL.

The below example is used in an interface that retrieves an FTP file from one location, processes it, and then sends the results on to another location. We wanted to retain the FTP logs and, for reasons best left unexamined, the powers that be decreed that these logs should accumulate in a database file. And they should have a timestamp.

Capturing the FTP log is a straightforward case of overriding the Output file to the log file/member. Including a timestamp is where the Qshell fun comes in.

/* Subroutine: Initialise FTPLOG Member                                     */
Subr SrFTPLOG                                                                   
   AddEnvVar EnvVar(QIBM_QSH_CMD_OUTPUT) Value(NONE) Replace(*YES)              
   Clrpfm FTPLOGFILE FTPLOG                                                     

   RtvMbrD File(FTPLOGFILE) Mbr(FTPLOG) RtnLib(&Library)                        
   ChgVar &SessionLib Value('db2 "Create Alias TMPFTPLOG for ' *Cat            + 
                            &Library *TCat '.FTPLOGFILE(FTPLOG)"')              

   RtvSysVal QDATETIME &Timestamp                                               
   ChgVar &SessionTop Value('db2 "Insert into TMPFTPLOG Values(''New Session ' +
                            *Cat %Sst(&Timestamp 1 4) *Cat '-'                 +
                            *Cat %Sst(&Timestamp 5 2) *Cat '-'                 +
                            *Cat %Sst(&Timestamp 7 2) *Cat ' '                 +
                            *Cat %Sst(&Timestamp 9 2) *Cat ':'                 +
                            *Cat %Sst(&Timestamp 11 2) *Cat ':'                +
                            *Cat %Sst(&Timestamp 13 2) *Cat ':'                +
                            *Cat %Sst(&Timestamp 15 6) *Cat ''')"')             
   Qsh Cmd(&SessionLib)                                                         
   Qsh Cmd(&SessionTop)                                                         
   Qsh Cmd('db2 "Drop Alias TMPFTPLOG"')                                         
   RmvEnvVar EnvVar(QIBM_QSH_CMD_OUTPUT)                                        
Endsubr

Member FTPLOG in file FTPLOGFILE is where the FTP logging data will be captured. This member is then copied to the permanent member as decreed by the PHB.

The example should speak for itself but what I am doing is creating an alias to the FTPLOG member, writing a single line to that member with a timestamp retirieved from the DateTime System value, and then dropping the alias.

If you want to get started with QShell, the best approach is to type Strqsh from a command line and then type help.