Category: Notes

Posted on

A Christmas story about staying sharp

I was in two minds about whether I should schedule a post for today. On one hand, it’s Monday and I would like to maintain my post schedule as far as possible. On the other hand, I am sure that you, I and everyone else has better things to do on Christmas day than read an IT blog.

Then I stumbled across this (very old) post from Steve Will which makes a point well worth repeating:

Andy and Bob were lumberjacks. To determine which was faster at chopping down trees, they entered into a wilderness competition. Placed in the middle of a big forest, each was given camping gear and an axe and told they could chop trees for as long as they wanted each day. They were told the size of trees that were acceptable, and at the end of a month, whoever had chopped more trees would be the winner.

On the first day, Andy and Bob started at the same
wing how dangerous falling trees can be in the dark, Andy resolved to keep going until sunset, but Andy noticed Bob stopped a little earlier than sundown. Andy felt great, sure that this meant Bob was not as tough as Andy, and so Andy pushed on until dark, and even a little after, getting a nice lead in the number of trees he had chopped down.

A couple more days went by like this, with Bob stopping earlier than Andy, and Andy pushing hard. Andy was quite tired at the end of each day work, but he was happy with the lead he was building.

By the middle of the second week, however, he saw that Bob was catching up. For some reason, Bob was able to chop more trees each day than Andy, and Andy was a little frustrated. How could this be happening?

So, at the end of the second week, Andy followed Bob back to Bob camp to catch a look at what Bob was doing with the time he wasn’t spending chopping trees. And what was Bob doing?

He was sharpening his axe.

Happy Christmas, and stay sharp.

Posted on

Counting lines in QShell

I have a large number of stream files that need to be imported into a database file so that they can be reprocessed. What makes this fun is that the data processing is quite slow and I have a lot of data to load. Because of this, I need to know the total number of lines across multiple stream files so that I can ensure that I don’t exceed the available processing window.

It turns out that Qshell has the answer.

This POSIX compliant command environment provides a set of commands and utilities that allow you to, among other things, manage files in any IFS supported file system.

The wc utility…

displays the number of lines, words, and bytes contained in each input file (or standard input, by default) to standard output. A line is defined as a string of characters delimited by a newline character. A word is defined as a string of characters delimited by white space characters. If more than one input file is specified, a line of cumulative counts for all the files is displayed on a separate line after the output for the last file.

In my case, it’s the number of lines I want to know about, so I need to use the -l parameter.

To get a list of all files, their line counts and a final total, this command:

wc -l IMP*

… gives me this output:

    ...
    2028 IMP2016W48.TXT
    2034 IMP2016W49.TXT
    2662 IMP2016W50.TXT
    3807 IMP2016W51.TXT
    2848 IMP2016W52.TXT
    4220 IMP2016W53.TXT
  503232 total

And, as with every Qshell output, this can be directed to a text file which I can use to handle the information automatically.

It’s worth noting that the wildcard (*) can go anywhere. So this command:

wc -l IMP*M*

… looks for file names starting with IMP and with a M somewhere in the filename:

    5987 IMP2016M01.TXT
    9564 IMP2016M02.TXT
    9344 IMP2016M03.TXT
    5757 IMP2016M04.TXT
    3263 IMP2016M05.txt
    2521 IMP2016M06.txt
    5807 IMP2016M07.TXT
    6456 IMP2016M08.TXT
   10439 IMP2016M09.TXT
    8823 IMP2016M10.TXT
    8119 IMP2016M11.TXT
    8007 IMP2016M12.TXT
   84087 total

Qshell provides a powerful and flexible environment and is well worth getting to grips with if you want to effectively manage files in the IFS.

Posted on

Better impact analysis with DSPPGMREF

The IBM i Control Language (CL) provides such a rich set of commands that even after almost 30 years, I can still discover something really useful. DSPPGMREF is a case in point:

The Display Program References (DSPPGMREF) command provides a list of the system objects referred to by the specified programs.

Usefully, I can select all programs in a library and direct the output to a database file:

DSPPGMREF PGM(LSCLIB/*ALL) OUTPUT(*OUTFILE) OUTFILE(LSCLIB/CROSSREF)

This gives me a table showing every object referenced by every program in library LSCLIB which means that identifying the potential impact of (for example) a change to a table is only a simple query away.

The program or programs to search can be *ALL, as above, or a single program name or a generic name. And if I use this:

DSPPGMREF PGM(*ALLUSR/*ALL) OUTPUT(*OUTFILE) OUTFILE(LSCLIB/CROSSREF)

I’ve captured everything.

Posted on

Editing CL source members with Vim

Back in October I mentioned that I had started assembling a set of Vim syntax files, starting with RPG. I have now added the CLP (and CLLE) syntax files and you can find the full set over here.

To give credit where it’s due, I found these via Martin Rowe although the comments at the top of the file indicates that it was originally built by Thomas Geulig. Either way, a bit of maintenance is well overdue and, being a Vim user, it’s in my interest to see it done.

With the arrival of fully free RPG, there really is no excuse to stick with SEU and the productivity gains you will see from using a proper text editor are phenomenal. I am aware that Vim comes with a bit of a learning curve and probably isn’t for everyone, but if you are still using SEU you really should be looking for an alternative.

Posted on

Using %TLOOKUP: An example

Firstly, a disclaimer. The CTDATA definition keyword indicates that an array or table should be loaded at compile time. This is a terrible idea for a whole host of reasons, not least of which is that the data is locked away in the program and that you need a programmer to change the data. It is far, far better to store any and all data outside of the program — physical files, SQL tables and data areas all exist for this purpose.

However, there are times when compile time tables cannot be avoided. These are times that I find myself looking at a really old program which is, inevitably, broken.

%TLOOKUP is weird. The function searches a table for an argument and returns either *ON or *OFF depending on whether or not a match was found. All pretty straightforward so far, if a little pointless. The oddities begin when you include the third, alt-table parameter.

Here’s an example:

        ctl-opt main(Main) dftactgrp(*no) actgrp(*new);

        //---------------------------------------------------------------------
        // Tables
        //---------------------------------------------------------------------
        dcl-s TABA char(3) dim(4) ctdata perrcd(1);
        dcl-s TABB char(2) dim(4) alt(TABA);

        //---------------------------------------------------------------------
        // Program main procedure
        //---------------------------------------------------------------------
        dcl-proc Main;
            dcl-pi *n end-pi;

            dcl-s check ind;


            check = %tlookup('129': TABA: TABB);
            dsply TABB;

            return;

        end-proc;
      * ---------------------------------------------------------------------- *
**  TABA - TABB
112G
113LU
129NL
150B

If I run this program, the tlookup searches table TABA for argument ‘129’. The third element of TABA matches this exactly, so the value of check is set to *ON.

And the value of TABB is set to ‘NL’, which is what we’re looking for.

But TABB is not a real field and cannot, for example, be used as an SQL host variable. For this, you would need to define another standalone field and populate it with TABB.

%TLOOKUP is an odd little function, and one that is very rarely needed. This post goes a very little way towards rectifying the fact that no-one else on the web wanted to provide an example of its use.

Posted on

Writing messages to the joblog with QMHSNDPM

I have previously mentioned using Qp0zLprintf (Print Formatted Job Log Data) to write messages to the joblog. Here’s an alternative approach using QMHSNDPM (Send Program Message). In this case, I am executing an SQL statement and checking the SQL state afterwards. If the state is not 00000 (completed normally), I use the LogError procedure to write the SQL code to the job log.

Obviously, it would be better to put the LogError procedure into a service program both for reusability and so that the QMHSNDPM prototype definition can be tucked out of the way.

This is the program:

        // Simple program to demonstrate sending SQL error states to the joblog
        // Written by Paul Pritchard

        ctl-opt main(Main) dftactgrp(*no) actgrp(*new);

        // --------------------------------------------------------------------
        // External Procedures
        // --------------------------------------------------------------------
        dcl-pr WriteJoblog extpgm('QMHSNDPM');
            MessageID char(7) const;
            MessageFile char(20) const;
            Message char(32767) const options(*varsize);
            MessageLength int(10) const;
            MessageType char(10) const;
            MessageStack char(10) const;
            MessageStackC int(10) const;
            MessageKey char(4);
            MessageError char(32767) options(*varsize);
        end-pr;


        // --------------------------------------------------------------------
        // Program main procedure
        // --------------------------------------------------------------------
        dcl-proc Main;
            dcl-pi *n end-pi;

            exec sql
                update employees
                set empvalid = 0
                where int(empnumber) < 0;

            if SQLSTATE <> '00000';
                LogError(SQLSTATE);
            endif;

            return;

        end-proc;

        // --------------------------------------------------------------------
        // Simple SQL Error Logging
        // --------------------------------------------------------------------
        dcl-proc LogError;

            dcl-pi LogError;
                ErrorCode char(5) const;
            end-pi;

            // The API message fields
            dcl-s MessageID char(7) inz('CPF9897');
            dcl-s MessageFile char(20) inz('QCPFMSG   *LIBL');
            dcl-s Message varchar(32767) inz;
            dcl-s MessageLength int(10) inz;
            dcl-s MessageType char(10) inz('*DIAG');
            dcl-s MessageStack varchar(32767) inz('*');
            dcl-s MessageStackC int(10) inz;
            dcl-s MessageKey char(4) inz;

            dcl-ds MessageError qualified;
                Input int(10) inz;
                Output int(10) inz;
            end-ds;


            Message = 'SQL Error encountered: ' + ErrorCode;
            MessageLength = %len(%trim(Message));
            WriteJoblog(MessageID: MessageFile: Message:
                        MessageLength: MessageType:
                        MessageStack: MessageStackC: MessageKey: MessageError);

            return;

        end-proc;
        // --------------------------------------------------------------------

And this is what it looks like in the joblog:

CPF9897    Diagnostic             40   17-11-12  18:31:37.319725  LSE001R
                                    From module . . . . . . . . :   SQLJOBLOG
                                    From procedure  . . . . . . :   LOGERROR
                                    Statement . . . . . . . . . :   137
                                    To module . . . . . . . . . :   SQLJOBLOG
                                    To procedure  . . . . . . . :   LOGERROR
                                    Statement . . . . . . . . . :   137
                                    Message . . . . :   SQL Error encountered: 22023
                                    Cause . . . . . :   No additional online help information is available.
Posted on

Fun with varying length fields

So here’s a really simple little program that puts the first five characters of a ten character field into a five character field. So why doesn’t it work?

      **Free

        // Fun with variable length fields

        ctl-opt dftactgrp(*no) actgrp(*new)
            main(Main);

        dcl-proc Main;
            dcl-pi *n end-pi;

            dcl-s LongString varchar(10) inz('123');
            dcl-s ShortString char(5) inz;
                                                             
            shortstring = %subst(longstring: 1: 5);                 
            dsply shortstring;                                      
                                                             
            return;                                                 
        end-proc;

If you compile and run this program, it will return an RNX0100 error: “Length or start position is out of range for the string operation”.

The reason, of course, is that LongString is a varying length field. The ten character length in the field definition defines the maximum size but, because the field is initialised with a three character value, the length of the field when the program starts is only three characters. And using the %subst bif to find the first five characters of a three character field will, of course, return an error.

The above program is so simple that the problem probably leapt out at you but when you are dealing with a bunch of externally defined fields, gotchas like this are less immediately apparent. So take this as a reminder to be wary of documentation and to familiarise yourself with the DSPFFD command.

Posted on

Using CPYSPLF to extract spool files via the IFS

I am currently without iSeries Navigator (or IACS, for that matter). I have a 5250 emulator, of course, but nothing else, which makes for an interesting challenge when I want to email a spool file to someone.

Fortunately, there’s CPYSPLF:

The Copy Spooled File (CPYSPLF) command copies the data records in the specified spooled file either to a user-defined physical database file or to a stream file. This allows the use of spooled files in applications using microfiche, data communications, or data processing.

It’s the stream file that makes it handy and allows me to do something like this:

CPYSPLF FILE(SPLF) TOFILE(*TOSTMF) JOB(139853/PAUL/JOBNAME) SPLNBR(7) TOSTMF(OutputTextFile.txt)

And now I have a text file, called OutputTextFile.txt, sitting in my home folder in the IFS. And that is just an FTP GET away.

Posted on

Writing messages to the job log with RPG

So here’s a fun situation. An interface that works perfectly in the test environment but which randomly falls over in production. Better still, it’s part of the overnight batch processing so it tends to fall over at around 9:30 in the evening.

I need to be able to determine what the program is doing when it fails, specifically, in this case, what the data being processed looks like, but I don’t want to stay up half the night in order to do so.

Fortunately, there’s Qp0zLprintf (Print Formatted Job Log Data).

The Qp0zLprintf() function prints user data specified by format-string as an information message type to the job log.

If a second parameter, argument-list, is provided, Qp0zLprintf() converts each entry in the argument-list and writes the entry to the job log according to the corresponding format specification in format-string. If there are more entries in argument-list than format specifications in format-string, the extra argument-list entries are evaluated and then ignored. If there are less entries in argument-list than format specifications in format-string, the job log output for those entries is
undefined, and the Qp0zLprintf() function may return an error.

What this means, is that I can write out to the joblog at various points within the program so that I can pinpoint exactly what the program was doing and what data was being processed when it failed.

So here’s a simplified sample of what I mean

      **Free

        // Simple program to demonstrate writing to the job log
        // Written by Paul Pritchard

        ctl-opt dftactgrp(*no) actgrp(*new)
            main(Main);

        dcl-pr WriteJobLog int(10) extproc('Qp0zLprintf');
            *n pointer value options(*string);
        end-pr;

        dcl-proc Main;
            dcl-pi *n end-pi;

            dcl-s Message varchar(512);
            dcl-c CRLF x'0d25';

            // The easy way to put a message in the joblog
            // Is to construct the text, then print it
            Message = 'Hello World' + CRLF;
            WriteJobLog(Message);

            // And exit
            return;
        end-proc;

That CRLF is the hex codes for Carriage Return and Line Feed. You need to ensure that it’s tacked onto the end of each message to ensure the messages all remain readably laid out.

And this is what the output looks like

*NONE      Information                  17-10-11  14:36:17.490255  QP0ZCPA      QSYS        *STMT    QP0ZCPA     QSYS        *STMT
                                     From module . . . . . . . . :   QP0ZUDBG
                                     From procedure  . . . . . . :   Qp0zVLprintf
                                     Statement . . . . . . . . . :   64
                                     To module . . . . . . . . . :   QP0ZUDBG
                                     To procedure  . . . . . . . :   Qp0zVLprintf
                                     Statement . . . . . . . . . :   64
                                     Message . . . . :   Hello World
Posted on

Read and Update with SQL

One of the advantages of SQL over native IO is that it allows you to handle sets of data rather than having to read and update each row individually. However, there can be cases where you want to handle your data line by line. To do this with SQL, you need to use a cursor.

I have a couple of examples below, both of which are written in fully free RPGLE. Going forward, all examples will be fully free because, as developers, we should all be putting the past behind us.

I am basing this on the previously mentioned Employees file, to which I have added an extra field named EMPVALID. This is numeric — a zero indicates an invalid employee number and a one indicates a valid employee number. Any number of validations or codes could be added, of course, but I want to keep things simple.

So first of all, here’s a simple program that will read and display each invalid employee.

 
      **Free
        
        // Employee records data structure
        dcl-ds EMPDS extname('EMPLOYEES')
        end-ds;

        // Ensure the cursor is not already open
        // This isn't really necessary here, but it is good practice
        exec SQL close C1;

        // Declare and open the cursor
        exec SQL declare C1 cursor for
            select EMPNUMBER, EMPNAME, EMPVALID
            from EMPLOYEES
            where EMPVALID = 0
            for fetch only;
        exec SQL open C1;

        // Read the file
        dou SQLCOD < 0 or SQLCOD = 100;
            exec SQL fetch C1 into :EMPDS;
            if SQLCOD >= 0 and SQLCOD <> 100;
                dsply (EMPNAME);
            endif;
        enddo;

        // Close the cursor and exit
        exec SQL close C1;
        *inlr = *on;

This is fine as far as it goes, but what we really want to do is check whether the employee numbers are valid and update the EMPVALID accordingly. For this, I will use the %check BIF.


      **Free

        // Employee records data structure
        dcl-ds EMPDS extname('EMPLOYEES')
        end-ds;

        // A display field to get around the limitations of the DSPLY OpCode
        dcl-s DISPLAY CHAR(52);

        // And we need a constant, digits, containing the valid characters
        dcl-c DIGITS '0123456789';

        // Ensure the cursor is not already open
        // This isn't really necessary here, but it is good practice
        exec SQL close C1;

        // Declare and open the cursor
        // Note that the last clause is now a 'for update of...'
        exec SQL declare C1 cursor for
            select EMPNUMBER, EMPNAME, EMPVALID
            from EMPLOYEES
            where EMPVALID = 0
            for update of EMPVALID;
        exec SQL open C1;

        // Read the file
        dou SQLCOD < 0 or SQLCOD = 100;
            exec SQL fetch C1 into :EMPDS;
            if SQLCOD >= 0 and SQLCOD <> 100;
                if %check(DIGITS: EMPNUMBER) > 0;
                    DISPLAY = %trim(EMPNAME) + ' is valid';
                    dsply (DISPLAY);

                    // And this is where we set EMPVALID = 1
                    // for the current record
                    exec SQL
                        update EMPLOYEES
                        set EMPVALID = 1
                        where current of C1;
                endif;
            endif;
        enddo;

        // Close the cursor and exit
        exec SQL close C1;
        *inlr = *on;

And this is the output:

DSPLY  Bugs Bunny is valid
DSPLY  Elmer Fudd is valid
DSPLY  Speedy Gonzales is valid
DSPLY  Sylvester is valid

And looking at the file, I can see everything is updated, as below:

EMPNUMBER   EMPNAME                                                  EMPVALID
00001       Bugs Bunny                                                      1
OOOO2       Elmer Fudd                                                      1
0000000003  Daffy Duck                                                      0
-4          Speedy Gonzales                                                 1
00005       Sylvester                                                       1

I told you that Elmer Fudd was tricksey.

As a rule, updating sets of data is much more efficient and should certainly be preferred for any new development. However, there are plenty of programs out there for which the business rules are tucked away in some validation subroutine which has been written to process one line at a time. In these cases, being able to process line by line with an SQL cursor gives you a handy first step towards modernising the application.

I have also found cursors to be useful when building subfiles, but that’s a whole other post.