When Who Did What

Published: March 6, 2013

by Ted Holt

When you create a new database table (physical file), you should include those columns (fields) that are fully functionally dependent on the key fields. I would like to mention some additional columns that you might like to include also.

The columns to which I refer are ones that store creation and change data. For example, you may want to be able to tell who inserted a row (added a record) and who last changed it. Such information may allow you to give a quick answer to a user's conundrum.

Look at this simple CREATE TABLE statement. Notice the last six columns.

create table MyFile
  (Key                    char (24  ) not null,
   OneField               char ( 1  ),
   TwoField               char (10  ),
   RedField               dec  ( 7,2),
   BlueField              dec  ( 5,0),
   CreateUser             char ( 10 ),
   CreateTime        timestamp,
   CreateJob              char ( 10 ),
   ChangeUser             char ( 10 ),
   ChangeTime        timestamp,
   ChangeJob              char ( 10),
   Primary key (Key));

The "create" columns track who created a row, when they created it, and the name of the job from which they created it. The job name is probably most helpful in terminal-based applications. The "change" columns report the same information for the last update to the row.

SQL can populate the user profile and timestamp columns, but as far as I know, has no function or special value that returns the job name. You can create a simple SQL function to retrieve that information.

First, you need a module that returns the job name.

H nomain
H option(*srcstmt)

D*** *entry plist
D RtvJobName      pr            10a

D psds           sds                  qualified
D  JobName              244    253a

P RtvJobName      b                   export
D                 pi            10a
 /free
     return psds.JobName;
 /end-free
P                 e

Create the module.

CRTRPGMOD MODULE(RTVJOBNAME) SRCFILE(QRPGLESRC) 
SRCMBR(RTVJOBNAME)

Create a service program from the module.

CRTSRVPGM SRVPGM(RTVJOBNAME) MODULE(RTVJOBNAME) 
EXPORT(*ALL)

Create an SQL function from the service program.

create function RtvJobName ()
   returns char(10)
   language rpgle
   parameter style general
   not deterministic
   no sql
   no external action
   not fenced
   no final call
   allow parallel
   no scratchpad
   external name 'MYLIB/RTVJOBNAME(RTVJOBNAME)'

It would be a mistake to try to make all applications update the creation and change columns. Instead, use two simple triggers. An insert trigger:

create trigger MyFileIns
   before insert on MyFile
   referencing new row as NewRow
   for each row mode db2row
      set NewRow.CreateUser = User,
          NewRow.CreateTime = Current_timestamp,
          NewRow.CreateJob  = RtvJobName(),
          NewRow.ChangeUser = User,
          NewRow.ChangeTime = Current_timestamp,
          NewRow.ChangeJob  = RtvJobName();

And a change trigger:

create trigger MyFileChg
   before update on MyFile
   referencing new row as NewRow
   for each row mode db2row
      set NewRow.ChangeUser = User,
          NewRow.ChangeTime = Current_timestamp,
          NewRow.ChangeJob  = RtvJobName();

Your applications won't need to load those columns. In fact, it will be useless for the applications to do so, because the triggers will overwrite those columns. For that reason, you may want to create views and/or logical files that omit the creation and change columns.

create view MyFile01 as
   select Key, OneField, TwoField, RedField, BlueField
     from MyFile

Some of your inquiry and report programs may need the creation and change columns. But any that do not need them, as well as programs that manipulate the database table, can work through the views and logical files.

Will these columns provide all answers when users want to know who did what and when? Of course, not. For that, you need something more powerful, such as Steven Wolk's excellent Display Journal utility, which is also found in this issue of Four Hundred Guru. But in many cases, these columns are sufficient.

RELATED STORY

Journal Forensics 101

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot