Creating DDL audit columns in DB2 for i SQL

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.

1. 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.
2. a special register: this column will be assigned the current value of the special register when the data change operation occurs.
3. 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.

For Example: Create a table PF12 with columns FLD1 and FLD2. Also define the audit columns to keep track of the type of change, the user who made the change, the application that made the change, and the qualified job name where the change started happen.

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