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 foodservice 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:
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 provides 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, updating, 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:
For more details on DB2 Temporal refers to A matter of time: Temporal data management in DB2 10.
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:
These REST APIs can be viewed and tested before use in your app from the API Designer.
As mentioned earlier, when dealing with the Temporal data, two issues need to be addressed:
Let us see how these two tasks are achieved in a WaveMaker app using DB2 System Temporal. Consider the use case where an HR manager allows resources to various departments within the company. The 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 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.
The 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 capability for managing multiple versions of your data with seamless integration of DB2 Temporal into WaveMaker apps. The platform eases the access and manipulation of historical data from temporal tables, through the invocation of auto-generated history APIs with minimum developer effort.