Author: Paul

Posted on

On terminology

No, I’m not going to revive the perennial — and pointless — argument about what we call the platform. It’s an IBM Power System running the IBM i operating system and there really is nothing else to discuss here.

Beyond this, however, there is terminology that we use which is at odds with that used elsewhere in the IT industry. This can, and does, create unnecessary communication barriers when talking to colleagues. It can also come back to haunt us when we try to explain that the i on Power is as modern and as capable — if not more so — than whatever shiny the tech press has alighted on this week.

With that in mind, I propose that we stop talking about files, records and fields.

Like it or not, the terminology that comes with SQL has become the language people use when discussing relational databases and those of us working with DB2 for i need to make clear that we know what we are talking about before we start discussing the advantages we enjoy.

So here’s a handy guide:

  • Don’t say library, say schema
  • Don’t say (physical) file, say table
  • Don’t say record, say row
  • Don’t say field, say column
  • Don’t say logical file, say index (or view)

Even if you have avoided SQL and stuck with native IO, using modern terminology will help you. It will ensure that you and your colleagues are using the same words when talking about the same things. Updating your terminology will also underline the fact that we are using a modern, fully featured relational database with more than enough power to remain relevant for many years to come.

Happy New Year.

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