Apparatus and method for automating the logging of table changes in a database

• US 20060155747A1
• Filed: 01/13/2005
• Published: 07/13/2006
• Est. Priority Date: 01/13/2005
• Status: Active Grant

• Current Assignee: Kyndryl Incorporated
• Original Assignee: International Business Machines Corporation
• Inventors: Olivieri, Ricardo N., Whelan, Mark B.
• Granted Patent: US 8,103,624 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/219 Managing data history or versioning querying versioned data G06F16/2474; querying temporal data G06F16/2477
• CPC Class Codes: G06F 16/2358 Change logging, detection, and notification replication G06F16/27
• CPC Class Codes: G06F 16/284 Relational databases

Abstract

A system and method for logging changes to a database table are disclosed. The system comprises an administration tool, table metadata, a history table, and a service engine. The administration tool provides an interface through which a DBA, database programmer, or other user can manage the table metadata and the history table, including the selection of columns within the table for which the system will track changes. In the preferred embodiment, the service engine is a middleware application that tracks changes for the selected columns. The service engine processes all user-requested database operations and adds operations to manage the history table before relaying the request operations to a DBMS. Alternatively or additionally, the service engine may be a wrapper to existing services that the database application provides.

Claims

1. An apparatus for creating history tables and batching transactions by entity comprising:

a computer having a memory; and

a program in the memory wherein the program contains instructions to cause the computer to modify a source table by adding a entity column and a timestamp column, generate a history table corresponding to the selected source table, and to generate a plurality of metadata so that when a change is made to the source table by a database application, the history table is automatically updated independent of the database application.

2. The apparatus of claim 1 wherein the metadata further comprises a plurality of mapping instructions between the source table and the history table.

3. The apparatus of claim 2 wherein the metadata further comprises a plurality of instructions to specify the table that will be modified and the columns within that table to be updated.

4. A computer product for creating history tables and batching transactions by entity comprising:

a development tool comprising a plurality of instructions to cause a computer to perform the following steps;

generating a history table corresponding to the selected source table;

generating a plurality of metadata comprising a plurality of mapping instructions between the source table and the history table.

a service engine comprising a plurality having a plurality of instructions to cause a computer to perform the following steps;

defining a transaction;

accessing the metadata;

using the metadata, updating the source table and the history table independent of the database application.

5. The computer program product of claim 4 wherein the development tool further comprises instructions to cause the computer to perform the step of modifying a selected source table by adding an entity column and a timestamp column.

6. The computer program product of claim 4 wherein the development tool further comprises instructions to cause the computer to perform the step of modifying a selected source table by adding an entity column and a timestamp column.

7. The computer program product of claim 4 wherein the development tool further comprises instructions to cause the computer to perform the step of connecting to a database instance hosted by the developer using a parameter.

8. The computer program product of claim 4 wherein the development tool further comprises instructions to cause the computer to perform the step of searching a plurality of tables in a database pursuant to a search criteria;

and responsive to finding a match to the criteria, providing a list of tables that meet the criteria.

9. The computer program product of claim 4 wherein the development tool further comprises instructions to cause the computer to perform the step of receiving update data from an entity application.

10. The computer program product of claim 4 wherein the development tool further comprises instructions to cause the computer to perform the step of enrolling a selected table so that it may interact with the service engine.

11. The computer program product of claim 4 wherein the development tool further comprises instructions to cause the computer to perform the step of storing the metadata.

12. The computer program product of claim 4 wherein the service engine further comprises instructions to cause the computer to perform the step of using a parameter from an application and the metadata to generate statements for updating the source tables and to generate insert statements for the history tables.

13. The computer program product of claim 4 wherein the development tool further comprises instructions to cause the computer to perform the step of including the user name provided for a transaction and a current timestamp of the action in an update statement.

14. A method of employing a service engine in a computer connected to a database application and a database comprising:

modifying a source table by adding a entity column and a timestamp column;

generating a history table corresponding to the source table; and

generating a plurality of metadata so that when a change is made to the source table by a database application, the history table is automatically updated independent of the database application.

15. The method of claim 14 wherein generating a plurality of metadata further comprises:

generating a plurality of mapping instructions between the source table and the history table.

16. The method of claim 14 further comprising:

specifying a column within the source table to be updated.

17. The method of claim 14 further comprising:

defining a transaction.