Tackling Unique Audit Requirements with RPG
IBM i experts Jon Paris and Susan Gantner share tips to tackle Data Structures and audit requirements
Instead of discussing new RPG features, we thought we’d focus instead on describing an approach to a problem that many of you will have encountered in one form or another. As is often the case, this piece was “inspired” by a question on an IBM i internet forum – in this case the one at code400.com.
The questioner noted that they had two Data Structures (DS) both of which were based off the same external description. One contained the original data as read from the table, the second held the (potentially) updated content coming from the screen. Determining if any changes had been made was straightforward. Simply compare one DS with the other. If they didn’t match then changes had been made.
Now came the tricky part. The questioner had been tasked with writing audit records to identify the specific field(s) that had been modified. Obviously one approach would be to write code to compare each of the fields in turn and report appropriately. But apart from being mind-bogglingly boring to code, it would need to be updated any time a new field was added to the mix and would be just plain ugly. In addition, code would have to be written specifically for each and every table that had this audit requirement. We all know that while we are told that it will be “just this one table” that it will never stop at one, so a more generic approach is needed. There has to be a better way.
Indeed there is, and it makes identifying the field in question a relatively simple matter. The basic requirement is to have a means to determine the start position and length of each field within the DS. That permits you to compare the relevant portions of the DS using a substring operation. The fact that some of the fields will be numeric makes no difference at this stage – we are first interested only in comparing the bytes used to store the values.
There are at least three methods we can think for obtaining the necessary information about such externally described DSs. One method is to use SQL to access the SYSCOLUMNS or SYSCOLUMN2 views which contains all of the required data. Alternatively we could have used the QUSLFLD API or, last but not least, the good old DSPFFD command with an outfile. Since DSPFFD is likely to be familiar to the majority of our readers we chose to use that method for our example.
The DSPFFD outfile contains one row for each field and contains a wealth of information, most of which we don’t need. We devised a DS array to contain the information we needed for this example. This is what it looks like: