When Who Did What
March 6, 2013 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
|
