• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • 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

    Journal Forensics 101



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

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    Maxava

    Migrating to New IBM i Hardware?

    Whether you are moving to POWER9, Power10, or a cloud hosted system MAXAVA HAS YOU COVERED!

    Learn More

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Profound Logic Software:  Live Webinar: 4 RPG Keys to Application Modernization Success. March 20
    BCD:  IBM i Webinar - March 7. Jumpstart Web App Development with WebSmart Mobile
    looksoftware:  Free Live Webcast: HTML5 & IBM i - Connect, Discover, Create. March 12 & 13

    More IT Jungle Resources:

    System i PTF Guide: Weekly PTF Updates
    IBM i Events Calendar: National Conferences, Local Events, and Webinars
    Breaking News: News Hot Off The Press
    TPM @ The Reg: More News From ITJ EIC Timothy Prickett Morgan

    OKI Data to Carry Brooks Print Converters Entry Power7+ Servers: Counting The Costs Of CPUs, IBM i, And SWMA

    Leave a Reply Cancel reply

Volume 13, Number 5 -- March 6, 2013
THIS ISSUE SPONSORED BY:

WorksRight Software
SEQUEL Software
Northeast User Groups Conference

Table of Contents

  • Journal Forensics 101
  • When Who Did What
  • Admin Alert: A Checklist For Moving Power i Partitions To A Different Location

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • Unattended IBM i Operations Continue Upward Climb
  • VS Code Is The Full Stack IDE For IBM i
  • Domino Runs on IBM i 7.5, But HCL Still Working on Power10
  • Four Hundred Monitor, March 6
  • IBM i PTF Guide, Volume 25, Number 11
  • You Ought To Be Committed
  • Thoroughly Modern: What You Need to Know About IBM i Security
  • Spring Brings Events To IBM i Community, And More
  • As I See It: AI-AI-O
  • IBM i PTF Guide, Volume 25, Number 10

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2023 IT Jungle