Simplifying Database Audit Trails using DB2 Temporal
Feb 19

Simplifying Database Audit Trails using DB2 Temporal

Today’s government regulations place strict requirements on enterprises to audit the corporate information access details and produce reports detailing who has changed, or even seen, that information. Consider Health Insurance Portability and Accountability Act (HIPAA) regulations that require healthcare providers to deliver audit trails right down to the row and record. Or the Sarbanes-Oxley Act (SOX), for example, places a wide range of accounting regulations on public corporations. The new European Union General Data Protection Regulation (GDPR) has similar requirements. All kinds of industries – from finance and energy to food service and public works – have similar regulations.

As regulations are imposing much stronger operational controls on databases and their use, your ability to answer very detailed questions about what’s going on in your organization’s databases can make or break a compliance audit or security investigation.  Hence, a common requirement of many enterprise applications is logging data changes in a database – what data got changed, who changed it and when it was changed.

As the list of government regulations that organizations must understand and comply with grows, the compliance pressure intensifies on the data stored in corporate databases. There are multiple ways to handle this situation:

  • The simplest solution would be to create a duplicate record for every change or update. This would require additional columns to enforce integrity constraint adding to the overhead of standard database operations.
  • Another solution would be to create a ‘history’ table, to hold history data. Every time a row is updated in the master table, the row is first copied to the history table and then updated.

Most business applications employ either one of the above solutions to address their auditing and history needs. However, both the above-mentioned solutions involve additional effort during application development especially when querying the historical data such as retrieving data for a particular time frame, data that was current say two weeks back, etc.

An inbuilt functionality of a database to handle the time-bound data would be a better option. In such scenarios, the application developer can concentrate on implementing the business logic rather than worrying about handling the data change and history logging needs.

DB2 Temporal

DB2 provides an in-built support for temporal or time-based data management. The temporal features in the DB2 database enable accurate tracking of data changes over time and provide an efficient and effective way to address the auditing and compliance requirements of business establishments. According to an internal IBM study, the built-in support in DB2 reduced coding requirements by more than 90 percent over both homegrown implementations.

Understanding Temporal

Temporal allow the insertion, updation, deletion, and query of data in the past, the present, and the future while keeping a complete history of “what you knew” and “when you knew it”.

There are three types of temporal tables supported by DB2:

  1. System-period temporal tables to allow for tracking the updates and deletes to the table rows over a period of time. This is achieved through:
    1. A master table with a system time period defined – the table includes additional timestamp columns  to hold the system time period start and end points and transaction timestamps.
    2. An associated history table created as a duplicate of the master table.
    3. This history table is entirely managed by the database to track and manage multiple versions of data from the master table.
    4. System Temporal are mostly used for scenarios wherein you need to track, say, the policy updates for a vehicle over a period of time.
  2. Application-period temporal tables help in tracking business time i.e. when certain business conditions are, were, or will be valid. This is achieved through:
    1. The table itself maintains business period information with additional business start and end time columns to track the business times.
    2. Without a need to maintain a separate history table.
    3. Queries allowing time range clauses like As Of a given date, Between and From-to two dates on the business period.
    4. Application Temporal can be used to trace, say the interest rates applicable over a period of time in past or future.
  3. Bitemporal tables manage both system time and business time, and combine all the capabilities of system-period and application-period temporal tables.

For more details on DB2 Temporal refer to A matter of time: Temporal data management in DB2 10.

Temporal Integration

WaveMaker extends support for auditing and history using DB2 Temporal. You can seamlessly integrate the temporal functionality within WaveMaker apps and take advantage of DB2’s in-built time-based data management.

Auto-detecting Temporal
When a DB2 database is imported, WaveMaker automatically identifies the tables with Temporal and allows for the maintenance and extraction of the history data for these tables.

Auto-generated REST APIs
Whenever a database is connected to a WaveMaker application, the platform generates REST APIs which help integrate the backend DB services with the front-end UI using Variables. These APIs can be used to perform various CRUD operations, and support additional functionalities like find, count, export etc. For DB2 temporal tables additional History APIs are generated which can be used to query the historical data.

Two types of APIs are generated for the temporal tables:

  • Standard APIs mentioned above can be used to deal with data that is valid at the current time i.e. at the application runtime.
  • History APIs to fetch history data for
    • a time different from the current time, or
    • a specific time period.

These REST APIs can be viewed and tested before use in your app from the API Designer.

Working with Temporal

As mentioned earlier, when dealing with the Temporal data, two issues need to be addressed:

  1. Capturing data to track every insert, update and delete; and
  2. Extracting the historical data as per requirements.

Let us see how these two tasks are achieved in a WaveMaker app using DB2 System Temporal. Consider the use case where a HR manager allots resources to various departments within the company. Company’s auditing needs require tracking of who worked in which department during a particular date/month or time period.

Capturing the Updates
As with any database table, you can use Data and Live Widgets tied to Database CRUD APIs to insert and update values to the temporal tables. HR Manager can add a new Employee or change an existing Employee details. (Fig 3: Seen here is an inline editable Data Table).

Updation and deletion of records in the master table results in a corresponding entry made to the History table automatically.

Viewing Historical Data
If the manager wants to track the progression of a particular employee, he can view the details. Fig 4 shows the changes in the selected Employee details with the changes highlighted – Eric moved from Intern to Sales department on 2nd Jan and then to Admin on 2nd Feb. Note that none of the date details were entered by the Manager during the course of these updates. (List widget is used to display the Historical Data).

Viewing Period Data
Suppose the manager wants to see the status of all Employees on a given date. Fig 5 shows the Historical data for Employee details for the selected date – 5th Feb using a Read Only Data Table. Again these entries were made automatically by WaveMaker without anyone entering the date details.

In Conclusion

Increasing volume of corporate data coupled with heightened expectations for security and privacy have greatly intensified the compliance pressure on database professionals. WaveMaker provides a simple yet sophisticated capabilities for managing multiple versions of your data with a seamless integration of DB2 Temporal into WaveMaker apps. The platform eases the access and manipulation of history data from temporal tables, through the invocation of auto-generated history APIs with minimum developer effort.

About The Author