Wednesday, January 11, 2017

Using auditing columns to audit data

generated audit columns

The germ for this post came from a comment made by a work colleague. I always insist that all master files have a "Last update user” and "Last update timestamp" field, making it possible to know when a particular record was last changed and who did it. "Wouldn't it be nice if the system updated the audit user and time for us," my colleague said looking at an old master file without these fields. This sent me to IBM's KnowledgeCenter to see if this was possible, and after a while of poking around I found the way to do it, auditing columns.

If a file or table has auditing columns, every time data is added to or changed the auditing columns will be automatically updated by the IBM i database manager, no extra coding needed in my RPG or SQL. I cannot move values to these columns, thereby, ensuring that the information contained within is sacrosanct. I cannot define auditing columns in DDS, if I need to add them to DDS file I would use the SQL ALTER TABLE statement. There are 21 types auditing columns covering all kinds of information, but in my scenario I just want to add auditing columns that will allow me to see:

  1. Was this record added or changed?
  2. Who did it?
  3. When did they do it?
  4. What is the name of the job that did it?

Let me start with the DDS of my file, TESTFILE:

01  A          R TESTFILER
02  A            KEYFLD         3A
03  A            SEQNBR         2S 0
04  A          K KEYFLD

I am going to use DFU to add two records to the file, which will now look like:

KEYFLD  SEQNBR
 1ST       0
 2ND       0

Now I am going to add the audit columns to the file. To do I must use SQL's ALTER TABLE:

01  ALTER TABLE TESTFILE
02    ADD COLUMN AUDIT_TYPE_CHANGE FOR "AUDITTYPE" CHAR(1)
03      GENERATED ALWAYS AS (DATA CHANGE OPERATION)

04    ADD COLUMN AUDIT_USER FOR "AUDITUSER" VARCHAR(18)
05      GENERATED ALWAYS AS (USER)

06    ADD COLUMN AUDIT_JOB_NAME FOR "AUDITJOBNM" VARCHAR(28)
07      GENERATED ALWAYS AS (QSYS2.JOB_NAME)

08    ADD COLUMN AUDIT_TIME FOR "AUDITTIME" TIMESTAMP
09      FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP NOT NULL ;

10  LABEL ON COLUMN TESTFILE (
11    AUDIT_TYPE_CHANGE TEXT IS 'Audit type',
12    AUDIT_USER        TEXT IS 'Audit user',
13    AUDIT_JOB_NAME    TEXT IS 'Audit job name',
14    AUDIT_TIME        TEXT IS 'Audit timestamp'
15  ) ;

16  LABEL ON COLUMN TESTFILE (
17    AUDIT_TYPE_CHANGE IS 'Audit               type',
18    AUDIT_USER        IS 'Audit               user',
19    AUDIT_JOB_NAME    IS 'Audit               job name',
20    AUDIT_TIME        IS 'Audit               timestamp'
21  ) ;

Line 1: When using the ALTER TABLE I need to give the name of the file I am altering.

Lines 2 and 3: The DATA CHANGE OPERATION will give me a I when a record/row has been added/inserted, and U when changed/updated. I will not, of course, have a record of deleted records. If I want to keep that information then it is best to use a temporal table. I am giving both a long and a short name for these new fields, the short names could be used in a RPG program.

Lines 4 and 5: The next piece of information I want to track is the user profile.

Lines 6 and 7: This is the field for the job name. This is a "built in global variable", therefore, it is wise to give the library name with the variable's name, notice how they are separated by a dot/period ( . ) as SQL naming convention is used.

Lines 8 and 9: I want the timestamp of when the insert or update happened, and this does it. I also must have the NOT NULL as this date can never be null.

Lines 10 – 15: This is where I am adding the equivalent of DDS's TEXT.

Lines 16 – 21: I also want to add the equivalent of DDS's COLHDG.

When I execute this code using the Run SQL statement command, RUNSQLSTM, my file is changed:

KEYFLD  SEQNBR  Audit  Audit   Audit                    Audit
                type   user    job name                 timestamp
 1ST       0      -    -       -                        2017-01-11-17.04.59.784982
 2ND       0      -    -       -                        2017-01-11-17.04.59.785006

Even though the records were added about 30 minutes apart, the value used is the time when the record was updated by the ALTER TABLE. The other audit fields are null.

Warning: As I have changed the file with the ALTER TABLE any programs using this file will now level check, CPF4131. Any programs using the changed file should be recompiled. You could change the file to be LVLCHK(*NO), but I strongly advise against doing this as it could cause you major problems in the future.

Let me use this changed file in a RPG program.

01  ctl-opt option(*nodebugio:*srcstmt)
           dftactgrp(*no) ;

02  dcl-pr sleep extproc('sleep') ;
03   *n uns(10) value ;
04  end-pr ;

05  dcl-f TESTFILE keyed usage(*output:*update) ;

06  KEYFLD = '3RD' ;
07  write TESTFILER ;

08  sleep(23) ;

09  KEYFLD = '4TH' ;
10  write TESTFILER ;

Line 1: My standard control options. I need the DFTACTGRP(*NO) as I will be using an external procedure.

Lines 2 – 4: And here is the procedure prototype for the sleep external procedure. sleep allows me to get the program to wait for the number of seconds given. I gave a detailed explanation of sleep in the post about temporal tables.

Line 5: The definition of my now changed file. I will be using it for both output and update.

Lines 6 and 7: Writing a record to the file.

Line 8: Wait for 23 seconds.

Line 9 and 10: Write another record to the file.

After this my file looks like:

KEYFLD  SEQNBR  Audit  Audit   Audit                    Audit
                type   user    job name                 timestamp
 1ST       0      -    -       -                        2017-01-11-17.04.59.784982
 2ND       0      -    -       -                        2017-01-11-17.04.59.785006
 3RD       0      I    SIMON   028290/SIMON/QPADEV0002  2017-01-11-17.07.49.230928
 4TH       0      I    SIMON   028290/SIMON/QPADEV0002  2017-01-11-17.08.12.267630

The two new records have all the information I wanted, and I did not have to move anything to the new fields, the IBM i database manager did it all for me.

My RPG program continues:

11  sleep(72) ;

12  chain (KEYFLD) TESTFILER ;
13  SEQNBR += 1 ;
14  update TESTFILER %fields(SEQNBR) ;

Line 11: A longer sleep to make the difference in the audit timestamp more obvious.

Line 12: I retrieve the last record from the file, with the CHAIN operation code.

Line 13: And increment the sequence field by one.

Line 14: Then update only the sequence number field. Normally the other fields in the file would remain unchanged. But the audit fields are changed.

KEYFLD  SEQNBR  Audit  Audit   Audit                    Audit
                type   user    job name                 timestamp
 1ST       0      -    -       -                        2017-01-11-17.04.59.784982
 2ND       0      -    -       -                        2017-01-11-17.04.59.785006
 3RD       0      I    SIMON   028290/SIMON/QPADEV0002  2017-01-11-17.07.49.230928
 4TH       1      U    SIMON   028290/SIMON/QPADEV0002  2017-01-11-17.10.12.790657

The last record, 4TH, now has an audit type of U to indicate the record had been updated. The job name remains the same. The audit timestamp shows the time the update happened.

The IBM i database manager protects these fields from being changed by "outside forces”, for example if I try to update the audit job name, using this RPG:

01  dcl-f TESTFILE usage(*update) ;

02  read TESTFILER ;
03  AUDITJOBNM = 'test' ;
04  update TESTFILER %fields(AUDITJOBNM) ;

I get the following error:


Message ID . . . . . . :   CPF5029       Severity . . . . . . . :   30

Message . . . . :   Data mapping error on member TESTFILE.
Cause . . . . . :   A data mapping error occurred on member TESTFILE file
  TESTFILE in library MYLIB, because the data fields in record number 1,
  record format TESTFILER, member number 1 are in error.

The message is, well at best, cryptic. If I try to update the same field using SQL:

  UPDATE TESTFILE SET AUDIT_JOB_NAME = 'test'

I get, what I consider, to be a better message:


Message ID . . . . . . :   SQL0798       Severity . . . . . . . :   30

Message . . . . :   Value cannot be specified for GENERATED ALWAYS column
  AUDIT_JOB_NAME.
Cause . . . . . :   A value cannot be specified for column AUDIT_JOB_NAME
  because it is defined as GENERATED ALWAYS.

This leaves me in no doubt what the problem was.

If I want to keep these columns secret/hidden from others so only I can see what is within them I would use the IMPLICITLY HIDDEN for each of the new fields:

ALTER TABLE TESTFILE
  ADD COLUMN AUDIT_TYPE_CHANGE FOR "AUDITTYPE" CHAR(1)
    GENERATED ALWAYS AS (DATA CHANGE OPERATION)
    IMPLICITLY HIDDEN

  ADD COLUMN AUDIT_USER FOR "AUDITUSER" VARCHAR(18)
    GENERATED ALWAYS AS (USER)
    IMPLICITLY HIDDEN

  ADD COLUMN AUDIT_JOB_NAME FOR "AUDITJOBNM" VARCHAR(28)
    GENERATED ALWAYS AS (QSYS2.JOB_NAME)
    IMPLICITLY HIDDEN

  ADD COLUMN AUDIT_TIME FOR "AUDITTIME" TIMESTAMP
    FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP NOT NULL
    IMPLICITLY HIDDEN ;

If I just use Query or SQL with the following statement I do not see the audit fields.

  SELECT * FROM TESTFILE


KEYFLD  SEQNBR
 1ST       0
 2ND       0
 3RD       0
 4TH       1

To see them I have to give them in the SQL statement:

  SELECT KEYFLD, SEQNBR, AUDIT_TYPE_CHANGE, AUDIT_USER,
         AUDIT_JOB_NAME, AUDIT_TIME 
    FROM TESTFILE


KEYFLD  SEQNBR  Audit  Audit   Audit                    Audit
                type   user    job name                 timestamp
 1ST       0      -    -       -                        2017-01-11-17.04.59.784982
 2ND       0      -    -       -                        2017-01-11-17.04.59.785006
 3RD       0      I    SIMON   028290/SIMON/QPADEV0002  2017-01-11-17.07.49.230928
 4TH       1      U    SIMON   028290/SIMON/QPADEV0002  2017-01-11-17.10.12.790657

I can add these audit fields/columns to my SQL DDL Tables when I create them, there is no need to use the ALTER TABLE, unless the table already exists:

CREATE TABLE MYLIB.TESTTABLE (
 KEYFLD CHAR(3) NOT NULL DEFAULT '',
 SEQNBR NUMERIC(2,0) NOT NULL DEFAULT 0,
 AUDIT_TYPE_CHANGE FOR "AUDITTYPE" CHAR(1)
   GENERATED ALWAYS AS (DATA CHANGE OPERATION),
 AUDIT_USER FOR "AUDITUSER" VARCHAR(18)
   GENERATED ALWAYS AS (USER),
 AUDIT_JOB_NAME FOR "AUDITJOBNM" VARCHAR(28)
   GENERATED ALWAYS AS (QSYS2.JOB_NAME),
 AUDIT_TIME FOR "AUDITTIME" TIMESTAMP
   FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP NOT NULL
) ;

There are 21 audit fields/columns that can be added. These are the ones I have found that are updated by RPG or SQL using STRSQL.

Generated Column Description Definition Inserted/Updated by...
RPG SQL
DATA CHANGE OPERATION CHAR(1) Type of operation Y Y
USER VARCHAR(28) User id Y Y
CURRENT CLIENT_USERID VARCHAR(255) User information from the current client connection Y Y
QSYS2.JOB_NAME VARCHAR(28) Full job name Y Y
TIMESTAMP TIMESTAMP Timestamp when operation performed Y Y
CURRENT SERVER VARCHAR(18) Current application server Y Y
SYSIBM.CLIENT_IPADDR VARCHAR(128) IP address of the client Y
SYSIBM.CLIENT_PORT INTEGER Port number used by the client Y

The others may be updated by SQL via a web client or using Global variables.

If I was to define all the possible audit columns my ALTER TABLE would look like:

ALTER TABLE TESTFILE
  ADD COLUMN A_TYPE CHAR(1)
    GENERATED ALWAYS AS (DATA CHANGE OPERATION)

  ADD COLUMN A_USER1 VARCHAR(18)
    GENERATED ALWAYS AS (USER)

  ADD COLUMN A_JOB_NAME VARCHAR(28)
    GENERATED ALWAYS AS (QSYS2.JOB_NAME)

  ADD COLUMN A_TIME TIMESTAMP
    FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP NOT NULL

  ADD COLUMN A_ACCOUNTING VARCHAR(255)
    GENERATED ALWAYS AS (CURRENT CLIENT_ACCTNG)

  ADD COLUMN A_APPLICATION VARCHAR(255)
    GENERATED ALWAYS AS (CURRENT CLIENT_APPLNAME)

  ADD COLUMN A_PROGRAM_ID VARCHAR(255)
    GENERATED ALWAYS AS (CURRENT CLIENT_PROGRAMID)

  ADD COLUMN A_USER2 VARCHAR(255)
    GENERATED ALWAYS AS (CURRENT CLIENT_USERID)

  ADD COLUMN A_WORKSTATION VARCHAR(255)
    GENERATED ALWAYS AS (CURRENT CLIENT_WRKSTNNAME)

  ADD COLUMN A_SERVER VARCHAR(18)
    GENERATED ALWAYS AS (CURRENT SERVER)

  ADD COLUMN A_USER3 VARCHAR(128)
    GENERATED ALWAYS AS (SESSION_USER)

  ADD COLUMN A_SYSTEM_MODE_JOB_NAME VARCHAR(28)
    GENERATED ALWAYS AS (QSYS2.SERVER_MODE_JOB_NAME)

  ADD COLUMN A_CLIENT_HOST VARCHAR(255)
    GENERATED ALWAYS AS (SYSIBM.CLIENT_HOST)

  ADD COLUMN A_CLIENT_IPADDR VARCHAR(128)
    GENERATED ALWAYS AS (SYSIBM.CLIENT_IPADDR)

  ADD COLUMN A_CLIENT_PORT INTEGER
    GENERATED ALWAYS AS (SYSIBM.CLIENT_PORT)

  ADD COLUMN A_PACKAGE_NAME VARCHAR(128)
    GENERATED ALWAYS AS (SYSIBM.PACKAGE_NAME)

  ADD COLUMN A_PACKAGE_SCHEMA VARCHAR(128)
    GENERATED ALWAYS AS (SYSIBM.PACKAGE_SCHEMA)

  ADD COLUMN A_PACKAGE_VERSION VARCHAR(64)
    GENERATED ALWAYS AS (SYSIBM.PACKAGE_VERSION)

  ADD COLUMN A_ROUTINE_SCHEMA VARCHAR(128)
    GENERATED ALWAYS AS (SYSIBM.ROUTINE_SCHEMA)

  ADD COLUMN A_ROUTINE_SPECIFIC_NAME VARCHAR(128)
    GENERATED ALWAYS AS (SYSIBM.ROUTINE_SPECIFIC_NAME)

  ADD COLUMN A_ROUTINE_TYPE CHAR(1)
    GENERATED ALWAYS AS (SYSIBM.ROUTINE_TYPE) ;

 

You can learn more about this from the IBM website:

 

This article was written for IBM i 7.3.

17 comments:

  1. Cool...thanks Simon

    ReplyDelete
  2. big thanks Simon ! i'll have many change to add this fields and delete some manually used but it is so great !!!

    ReplyDelete
  3. Good stuff, I cant wait to get to 7.3...still languishing in 7.1. Look forward to some Temporal Table articles....need me some period data type examples.

    ReplyDelete
  4. Hey Simon,

    Where can we see the keywords that can be used in "GENERATE ALWAYS AS"?

    To try something like below which is not working

    AUDIT_TIME_INSERT FOR AUDITTIMEI TIMESTAMP
    GENERATED ALWAYS AS (CURRENT_TIMESTAMP)
    NOT NULL IMPLICITLY HIDDEN,

    I doesn't want like this...
    AUDIT_TIME_INSERT FOR AUDITTIMEI TIMESTAMP
    NOT NULL DEFAULT CURRENT_TIMESTAMP
    IMPLICITLY HIDDEN,

    ReplyDelete
  5. Is there a scenario where the USER and the JOB User values would be different?

    ReplyDelete
    Replies
    1. Yes, server jobs. If the server job program inserts/updates the table record, but who is the user that initiated or caused the insert/update?

      Delete
    2. My guess would be the user profile that the job runs under.

      Delete
  6. Hello Simon,

    I use SET SESSION_USER before update my file.
    The AUDIT_USER and SESSION_USER column are not set with the profile used in SET SESSION_USER, I have always the connection user.
    I use the Temporal tables to track changes.
    Regards.

    ReplyDelete
  7. This is really cool

    ReplyDelete
  8. These "built in global variable" are indeed a cool thing to learn. Can you further educate on them? Thanks.

    ReplyDelete
  9. I'll test it tomorrow. Very interesting

    ReplyDelete
  10. Hi Simon, just a typo on line 4 of the third greyed window. archer for USER should be 128 no 18. Thanks for your work, cheers, Elio

    ReplyDelete
  11. I ran into one quirk with this that I am wondering if there is a way around. I am very interested in using the "IMPLICITLY HIDDEN" for many existing tables. However, I found that an insert like this:
    "INSERT INTO mytable VALUES('xxx', '999-999-9999')"
    DOES NOT work. Gives error that the number of values is not the same as the number of columns

    While
    "INSERT INTO table (NAME, PHONENUM) VALUES('myname', '999-999-9999')"
    DOES work.

    Of course the idea was to not change any code. Is there any way around this?

    ReplyDelete
    Replies
    1. There is not a way "around" this. If you hide columns they still need to be used when you insert.

      Delete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.