Scenario for tracking auditing information

Db2 can track some basic auditing information for you. Db2 can track when the data was modified, who modified the data, and the SQL operation that modified the data.

To track when the data was modified, define your table as a system-period temporal table. When data in a system-period temporal table is modified, information about the changes is recorded in its associated history table.

To track who and what SQL statement modified the data, you can use non-deterministic generated expression columns. These columns can contain values that are helpful for auditing purposes, such as the value of the CURRENT SQLID special register at the time that the data was modified. You can define several variations of generated expression columns by using the appropriate CREATE TABLE or ALTER TABLE syntax. Each variation of generated expression column results in a different type of generated values.

In the following scenario, a system-period temporal table is created with non-deterministic generated expression columns to track auditing information.

Suppose that you issue the following statement to create a system-period temporal table called STT:

CREATE TABLE STT (balance INT, 
                  user_id VARCHAR(128) GENERATED ALWAYS AS  ( SESSION_USER ) ,  
                  op_code CHAR(1) 
                              GENERATED ALWAYS AS ( DATA CHANGE OPERATION )  
                  ... SYSTEM PERIOD (SYS_START, SYS_END));

The user_id column is to store who modified the data. This column is defined as a non-deterministic generated expression column that will contain the value of the SESSION_USER special register at the time of a data change operation.

The op_code column is to store the SQL operation that modified that data. This column is also defined as a non-deterministic generated expression column.

Suppose that you then issue the following statements to create a history table for STT and to associate that history table with STT:

CREATE TABLE STT_HISTORY (balance INT, user_id VARCHAR(128) , op_code CHAR(1) ... );

ALTER TABLE STT ADD VERSIONING 
                     USE HISTORY TABLE STT_HISTORY     ON DELETE ADD EXTRA ROW;

In the ALTER TABLE statement, the ON DELETE ADD EXTRA ROW clause indicates that when a row is deleted from STT, an extra row is to be inserted into the history table. This extra row in the history table is to contain values for the non-deterministic generated expression columns (user_id and op_code) at the time of the delete operation.

Now, consider what happens as the STT table is modified. For simplicity, date values are used instead of time stamps for the period columns in this scenario.

Assume that on 15 June 2010, user KWAN issues the following statement to insert a row into STT:

 INSERT INTO STT (balance) VALUES (1)

After the insert, the tables contain the following data.

Table 1. Data after insert
Table	Data (balance, user_id, op_code, sys_start, sys_end)
STT	`(1, 'KWAN', ‘I’, 2010-06-15, 9999-12-30)`
STT_HISTORY	Empty

Later, on 1 December 2011, user HAAS issues the following statement to update the row:

   UPDATE STT SET balance = balance + 9;

This update results in the following data in the tables:

Table 2. Data after update
Table	Data
STT	`(10, 'HAAS', 'U', 2011-12-01, 9999-12-30)`
STT_HISTORY	`row 1 (1, 'KWAN', 'I', 2010-06-15, 2011-12-01)`

On 20 December 2013, user THOMPSON issues the following statement to delete the row:

   DELETE FROM STT;

This deletion results in the following data in the tables:

Table 3. Data after deletion
Table	Data
STT	Empty
STT_HISTORY	`row 1 (1, 'KWAN', 'I', 2010-06-15, 2011-12-01) row 2 (10, 'HAAS', 'U', 2011-12-01, 2013-12-20) row 3 (10, 'THOMPSON', 'D', 2013-12-20, 2013-12-20)`

The rows in STT_HISTORY contain the following information: Start of change

Row 1: Row 1 records the history that resulted from the update statement that was issued by HAAS and reflects the values of the row in the system-period temporal table before HAAS issued the update statement: user KWAN issued an insert statement (‘I’) on 15 June 2010 that set balance=1. This row was valid until 1 December 2011, which is the date that user HAAS issued the update statement that supplanted KWAN’s insert statement.
Row 2: Row 2 records the history that resulted from the delete statement that was issued by THOMPSON and reflects the values of the row in the system-period temporal table before THOMPSON issued the delete statement: user HAAS issued an update statement (‘U’) on 1 December 2011 that set balance=10. This row was valid until 20 December 2013, which is the date that THOMPSON issued the statement that deleted the row.
Row 3: Because the ON DELETE ADD EXTRA ROW clause was specified in the definition of the system-period temporal table, row 3 was added to record information about the delete operation itself. Row 3 indicates that THOMPSON issued a delete statement (‘D’) on 20 December 2013 and that balance=10 at the time the row was deleted.

Row 2 and row 3 are identical for user data (the value of the balance column). The difference is the auditing columns: the new generated expression columns that record who initiated the action and which data change operation the row represents.

A SELECT statement with explicit or implicit FOR SYSTEM_TIME period specifications can transparently access historical data (or a combination of current and historical data). For this type of query, the third row in the history table is not included in the result.