|Creating DDL audit columns in DB2 for i SQL
When a table row is added or changed that has an auditing column defined in the table, then the value of those audit column is generated by the database manager. These generated values are maintained for both SQL and native I/O changes to the table row.
There are 3 types of values that the system uses to maintain status information for any modification to the table row.
- the type of data change: this column will be updated with an I or U to tell whether the last modification was an insert or an update. For a history table, D can be generated to indicate that the row was deleted.
- a special register: this column will be assigned the current value of the special register when the data change operation occurs.
- or a built-in global variable: this column will be assigned the current value of the global variable when the data change operation occurs.
Whenever we create a table, we can define audit columns for the above generated expressions.
CREATE TABLE PF12 (FLD1 CHAR(10), FLD2 CHAR(1), AUDIT_CHANGE_TYPE CHAR(1) GENERATED ALWAYS AS (DATA CHANGE OPERATION), AUDIT_CHANGE_USER VARCHAR(128) GENERATED ALWAYS AS (SESSION_USER), AUDIT_CHANGE_APPLNAME VARCHAR(255) GENERATED ALWAYS AS (CURRENT CLIENT_APPLNAME), AUDIT_CHANGE_JOBNAME VARCHAR(28) GENERATED ALWAYS AS (QSYS2.JOB_NAME))
Execute this above query from STSRQL session and this will create the table in your current library and then insert one row as below where we only insert data in FLD1 and FLD2 as other audit columns are generated automatically with proper values.
INSERT INTO PF12 (FLD1, FLD2) VALUES('A', 'B')
Data in PF12 table
FLD1 FLD2 AUDIT_CHANGE_TYPE AUDIT_CHANGE_USER A B I EASYCLASS AUDIT_CHANGE_APPLNAM E START SQL INTERACTIVE SESSION AUDIT_CHANGE_JOBNAME 796137/EASYCLASS/QPAD193251
Now update the FLD1 value in PF12 as below
UPDATE EASYCLASS1/PF12 SET FLD1 = 'A_CHANGE'
Data in PF12 table will now looks like
FLD1 FLD2 AUDIT_CHANGE_TYPE AUDIT_CHANGE_USER A_CHANGE B U EASYCLASS AUDIT_CHANGE_APPLNAM E START SQL INTERACTIVE SESSION AUDIT_CHANGE_JOBNAME 796137/EASYCLASS/QPAD193251