Guru: Three Suboptimal I/O Practices

February 5, 2018 Ted Holt

I have on numerous occasions looked at source code that I had written in previous years and asked myself, “Why on earth did I do that? What could I have been thinking?” We live and learn, or at least we hope we learn. Today I share three database practices that I see from time to time that can be simplified. Maybe there’s something for you to learn today.

Before I share the three examples, let me say that I do not consider the more cumbersome code to be wrong. To my way of thinking, any code that produces the correct results within an acceptable time and is easy to maintain is correct code. However, simpler is usually better, if for no other reason that simple code provides less room for error.

Using A Cursor to Fetch One Row

RPG programmers have traditionally lived and died by the CHAIN op code. CHAIN uses a search argument, which may be one or more fields, to randomly read one record from a keyed file. It’s understandable that an RPG programmer who is learning to use SQL for database I/O would use a cursor to accomplish the same. Here’s an example:

D CustData      e ds                  extname(CUSTFILE25)

D CustNumber      s              5p 0

  exec sql  declare c1 cursor for
               select * from CUSTFILE25
                where CustNo = :CustNumber
                order by CustNo, SeqNo;
  exec sql  open c1;
  // check for error & handle accordingly
  exec sql  fetch c1 into :CustData;
  // check for error & handle accordingly
  exec sql  close c1;

It’s necessary to use a cursor to retrieve a result set, but if you only want to retrieve one row, you can use SELECT INTO instead.

D CustData      e ds                  extname(CUSTFILE25)

D CustNumber      s              5p 0

  exec sql     select * into :CustData
                 from CUSTFILE25
                where CustNo = :CustNumber
                order by CustNo, SeqNo
                fetch first row only;
  // check for error & handle accordingly

In either case, you get one row, but the second is simpler. Be sure to include the FETCH FIRST ROW ONLY clause if more than one row could satisfy the query.

Needlessly Re-execute A Dynamic SQL Statement

Here a programmer executes a dynamically-built DELETE statement within a loop.

D Command1        s             96a   varying
D Where           s             96a   varying
D CustNumber      s              5p 0
D DueDate         s              7p 0
D OrderType       s              1a
D DOption         s              1a

  // Something sets DOption here
  dow '1';
     // something gets the data values from somewhere
     if CustNumber = *zero;
        leave;
     endif;
     select;
        when DOption = '1';
           Where = 'CustNo=' + %char(CustNumber);
        when DOption = '2';
           Where = 'DueDate=' + %char(DueDate);
        when DOption = '3';
           Where = 'OrdType=''' + OrderType + '''';
     endsl;
     Command1 = 'delete from SEM07161PF where ' + Where;
     exec sql execute immediate :Command1;
  // check for error & handle accordingly
  enddo;

The same DELETE statement runs over and over within the loop, the only difference being the customer number, due date, or order type plugged into the WHERE clause. We can reduce a bit of overhead by preparing the statement in advance and passing the customer number, due date, or order type to the prepared statement as a parameter.

D Command1        s             96a   varying
D CustNumber      s              5p 0
D DueDate         s              7p 0
D OrderType       s              1a
D DOption         s              1a

  // something sets DOption here
  Command1 = 'delete from SEM07161PF where';
  select;
     when DOption = '1';
        Command1 += ' CustNo=?';
     when DOption = '2';
        Command1 += ' DueDate=?';
     when DOption = '3';
        Command1 += ' OrdType=?';
  endsl;
  exec sql  prepare Stmt1 from :Command1;
  // check for error & handle accordingly

  dow '1';
     // something gets the customer number from somewhere
     if CustNumber = *zero;
        leave;
     endif;
     select;
        when DOption = '1';
           exec sql execute Stmt1 using :CustNumber;
        when DOption = '2';
           exec sql execute Stmt1 using :DueDate;
        when DOption = '3';
           exec sql execute Stmt1 using :OrderType;
     endsl;
     // check for error & handle accordingly
  enddo;

Reading To Delete

The last example comes from the world of record-level access (RLA). In the following example, the programmer deletes all records for a certain customer number.

FCUSTFILE25uf   e           k disk

D CustNumber      s              5p 0

  setll (CustNumber) CUSTFILE25;
  reade (CustNumber) CUSTFILE25;
  dow not %eof(CUSTFILE25);
     delete           CUSTFILE25;
     reade (CustNumber) CUSTFILE25;
  enddo;

RPG’s DELETE op code may include a search argument in Factor 1, just as CHAIN does. The code may be simplified this way:

FCUSTFILE25uf   e           k disk

D CustNumber      s              5p 0

  dou not %found(CUSTFILE25);
     delete (CustNumber) CUSTFILE25;
  enddo;

This feature of DELETE has been around for eons, but from what I see, the fact is not widely known. Notice that DELETE sets the %FOUND condition, not %EOF.

There you have two SQL tips and one RLA tip. Even though I prefer SQL and use it as much as possible, I don’t think the native interfaces are obsolete. As Bob Cozzi pointed out to me in a recent email, there’s no SQL equivalent for CPYF . . . FMTOPT(*MAP *DROP).