Using auditing columns to audit data

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:

• Creating auditing columns
• Special registers
• Built in global variables

 

This article was written for IBM i 7.3.