Creating auditing columns

There are three types of values that the system uses to maintain status information for any modification to a row: the type of data change, a special register, or a built-in global variable. You can have multiple columns in a table that track this information. Each column defined as one of these generated expression columns must have a data type that exactly matches the required definition for the item being generated.

• A column defined to contain a generated data change operation column will be updated with an I or U to indicate whether the last modification was an insert or an update. For a history table, a value of D can be generated to indicate that the row was deleted.
• A column defined to contain a generated special register value will be assigned the current value of the special register when the data change operation occurs.
• A column defined to contain a generated built-in global variable value will be assigned the current value of the global variable when the data change operation occurs.

When you create a table, you can define columns for these generated expressions. For example, create a table EMPLOYEE with columns called EMPNO, NAME, WORKDEPT, and SALARY. Define auditing columns to track the type of change, the user who made the change, the application that made the change, and the qualified job name where the change originated.

CREATE TABLE EMPLOYEE
   (EMPNO CHAR(6),
    NAME VARCHAR(50),
    WORKDEPT CHAR(3),
    SALARY DECIMAL(9,2),
    EMP_CHANGE_TYPE CHAR(1) GENERATED ALWAYS AS (DATA CHANGE OPERATION),
    EMP_CHANGE_USER VARCHAR(128) GENERATED ALWAYS AS (SESSION_USER),
    EMP_CHANGE_APPLNAME VARCHAR(255) GENERATED ALWAYS AS (CURRENT CLIENT_APPLNAME),
    EMP_CHANGE_JOBNAME VARCHAR(28) GENERATED ALWAYS AS (QSYS2.JOB_NAME))

When you add a generated expression column to an existing table, defining the IMPLICITLY HIDDEN attribute for the column as well can prevent existing applications that use SQL from requiring modifications. Hidden columns are excluded when a SELECT *, an INSERT without a column list, or an UPDATE using ROW determines its implicit list of columns. The only time a hidden column is included is when it is explicitly mentioned by name.

These auditing columns can be especially useful when using a system-period temporal table. Since all the historical rows are kept in the corresponding history table, an auditing column will complement the history by recording information such as who was responsible for each change.