Method and system for defining transactions from a database log

• US 5,907,848 A
• Filed: 03/14/1997
• Issued: 05/25/1999
• Est. Priority Date: 03/14/1997
• Status: Expired due to Term

• Current Assignee: Vision Solutions Incorporated (Precisely Software Incorporated)
• Original Assignee: Lakeview Technology Inc. (Precisely Software Incorporated)
• Inventors: Boggs, Dan, Zaiken, Kenneth A., Dehond, Guy
• Primary Examiner(s): Black, Thomas G.
• Assistant Examiner(s): Mizrahi, Diane D.
• Application Number:
  US08/818,513
• Time in Patent Office: 802 Days
• Field of Search: 707/1, 707/2, 707/8, 707/10, 707/101, 707/103, 707/201, 707/202, 707/203, 707/204, 707/205, 707/6, 707/9, 707/206, 705/4, 705/40, 711/152, 711/168, 711/113, 711/1-7, 711/9, 370/231, 701/2, 1/1
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2358 Change logging, detection, and notification replication G06F16/27
• CPC Class Codes: G06Q 40/02 Banking, e.g. interest calculation or account maintenance credit or loans G06Q40/03
• CPC Class Codes: G06Q 40/08 Insurance
• CPC Class Codes: Y10S 707/99931 Database or file accessing
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99938 Concurrency, e.g. lock management in shared database
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation
• CPC Class Codes: Y10S 707/99952 Coherency, e.g. same view to multiple users
• CPC Class Codes: Y10S 707/99953 Recoverability

Abstract

A method and system of providing external transaction protection for a database using the database log or journal. The method involves creating a set of transaction templates which define transactions, using the templates to determine whether each record or entry in the journal represent part of a transaction, and maintaining a set of index file indicating transactions in progress. Each transaction template contains a number of filenames identifying files in the database affected during the transaction defined by the template. Each template also contains a key value representing one or more data fields in the database included in every action performed during the transaction. For each entry in the journal, a determination is made whether the entry belongs to a transaction based on the data fields represented in the entry and the key values associated with the templates. The index files are maintained by creating a new index file for each new transaction, adding data to a given index file from a journal entry which belongs to the transaction associated with the given index file, and deleting index files when transactions with which they are associated are complete. In the event the database is damaged, existing index files are used to determine which transactions did not complete before the database was damaged. The actions which were completed may be rolled back.

Claims

1. A method of defining transactions from a log of a database, the log comprising data relating to changes to the database, the log data being stored in a plurality of records each having a plurality of fields, the method comprising:

for each of a plurality of records in the log, determining whether the record relates to a transaction included in a transaction template;

for each record that relates to a transaction included in the template, determining whether the record relates to a new transaction or an incomplete transaction;

for each record relating to a new transaction, creating and storing an indicator for the transaction and inserting information relating to the record into the indicator; and

for each record relating to an incomplete transaction, determining whether the record represents the end of the incomplete transaction and, if the record does not represent the end of the incomplete transaction, inserting information relating to the record into the previously created indicator for the incomplete transaction.

2. The method of claim 1 comprising storing a plurality of transaction templates and selecting one of the plurality of templates based on at least part of the data stored in the record.

3. The method of claim 2 wherein each template is assigned a key value based on one or more data fields from the database, wherein the data stored in the record includes data values from data fields in the database, and wherein the step of selecting one of the templates comprises selecting a template having a key value which matches one or more of the data fields whose data values are in the record.

4. The method of claim 1 comprising retrieving records from the log, and wherein the steps set forth in claim 1 are performed immediately after retrieving each record from the log.

5. The method of claim 1 wherein the plurality of fields in each record includes a filename field identifying a database file affected by a change, and wherein the template includes a plurality of filenames representing files changed during a transaction, and wherein the step of determining whether each record relates to a transaction included in the template comprises determining whether the filename stored in the record matches a filename in the template.

6. The method of claim 1 further comprising deleting the previously created indicator when the record represents the end of the incomplete transaction.

7. The method of claim 6 wherein the step of determining whether the record relates to a new transaction or an incomplete transaction comprises searching for an indicator containing data which matches data stored in the record, the failure to find such an indicator being an indication that the record relates to a new transaction.

8. The method of claim 7 wherein the data stored in the log records includes job identifier data, wherein the step of inserting information into the indicator comprises inserting the job identifier data from the record into the indicator, and wherein the step of searching for an indicator comprises searching for an indicator containing job identifier data which matches the job identifier data stored in the record.

9. The method of claim 7 wherein the data in each record includes one or more data values from one or more data fields in the database, the one or more data fields being used as a key value for the template, wherein the step of inserting information into the indicator comprises inserting the key value from the record into the indicator, and wherein the step of determining whether the record relates to a new transaction or an incomplete transaction further comprises comparing the key value in the record to a key value stored in the indicator found in the search, the mismatch of key values being an indication that the record relates to a new transaction.

10. The method of claim 7 wherein the plurality of fields includes a filename field representing a database file affected by a change, wherein the step of inserting information into the indicator comprises inserting the filename from the record into the indicator, and wherein the step of determining whether the record relates to a new transaction or an incomplete transaction further comprises comparing the filename in the record to a filename stored in the indicator found in the search, the match of filenames being an indication that the record relates to a new transaction.

11. The method of claim 1 wherein the step of inserting information relating to the record into the indicator for the incomplete transaction is performed if and only if the record does not represent the end of the incomplete transaction.

12. The method of claim 1 wherein the step of inserting information relating to the record into the indicator comprises inserting information pointing to a location of the record in the log.

13. The method of claim 12 wherein the log comprises a plurality of log sequence numbers each associated with a record, and wherein the step of inserting information pointing to the location of the record in the log comprises inserting the log sequence number associated with the record into the indicator.

14. The method of claim 1 comprising, in the event data is lost from or damaged in the database, using the indicators to recover the lost or damaged data.

15. The method of claim 14 comprising deleting the previously created indicator when the record represents the end of the incomplete transaction, and wherein the step of using the indicators to recover the lost or damaged data comprises identifying incomplete transactions based on the indicators and undoing any changes stored in records relating to incomplete transactions.

16. A method of defining transactions from a log of a database based upon a plurality of transaction templates, the log comprising data relating to changes to the database stored in a plurality of records each including a filename field representing a database file affected by a change, job identifier data, and data values from data fields in the database, each template being associated with a key value comprising one or more data fields in the database, each template comprising a plurality of filenames representing files changed during one or more given transactions, the method comprising:

retrieving a log record from the database log;

identifying one of the plurality of templates by comparing the data fields whose data values are stored in the retrieved record to the key values contained in the templates;

searching for an index file containing job identifier data which matches the job identifier data stored in the retrieved record;

if no index file is found having matching job identifier data, creating an index file and inserting information relating to the record into the index file including the job identifier data, key value of the identified template, and filename from the retrieved record;

if an index file is found having matching job identifier data, comparing the key value contained in the found index file with the data fields whose data values are stored in the retrieved record; and

if the key value contained in the found index file matches one or more of the data fields whose data values are stored in the retrieved record, inserting information relating to the record into the index file including the filename from the retrieved record.

17. The method of claim 16 comprising, if the key value contained in the found index file does not match one or more of the data fields whose data values are stored in the retrieved record, deleting the found index file, creating a new index file and inserting information relating to the record into the new index file including the job identifier data, key value of the identified template, and filename from the retrieved record.

18. The method of claim 16 comprising comparing the filename stored in the retrieved record to the filenames stored in the template and, if the filename stored in the retrieved record does not match any filename stored in the template, adding the filename stored in the retrieved record to the template.

19. The method of claim 16 comprising, if data is lost from or damaged in the database, using the index files to recover the lost or damaged data.

20. A method of providing transaction protection for a database, wherein actions performed on data within the database are recorded in a log having a plurality of entries each storing at least job identifier data representing a source of a job being performed, a filename representing a file in the database affected by an action, an action performed on data in the database file, and one or more data fields from the database file which comprise a key value, and wherein transactions comprise a plurality of actions performed on a plurality of files, the method comprising:

retrieving entries from the log;

storing at least one template listing filenames which are affected by one or more transactions;

for each retrieved log entry, comparing the filename in the entry to the filenames in the at least one template to determine whether the entry represents part of a transaction;

for each entry which represents part of a transaction;

determining based at least in part on the job identifier data whether a transaction is in progress for the job identified by the job identifier data;

if no transaction is in progress for the job, marking the entry as part of a new transaction;

if a transaction is in progress for the job, determining whether the transaction in progress is for the key value stored in the entry and if so, marking the entry as part of the transaction in progress if the filename in the entry has not already been marked in the transaction in progress;

if the transaction in progress is for a key value different than the key value in the entry, or if the filename in the entry has already been marked in the transaction in progress, closing the transaction in progress and marking the entry as part of a new transaction; and

in the event of loss or damage to data in the database, undoing any actions represented by entries marked as part of a transaction in progress which has not been closed.

21. A method of creating a transaction template from a log of a database, the log comprising a plurality of records of changes made to the database, the records containing data values associated with data fields in the database, the method comprising:

retrieving the data values from the records and storing the data values;

comparing the data values from a given record with data values from other records;

when one or more data values from the given record match one or more data values from one or more other records, identifying the one or more data fields from the database files associated with the matching one or more data values; and

creating a transaction template associated with a key value comprising the identified one or more data fields.

22. The method of claim 21 wherein the database comprises a plurality of tables, wherein each log record contains a name of a table in the database affected by a change to the database, and wherein the table names from records having one or more matching data values are stored in the transaction template associated with the key value.

23. The method of claim 22 comprising displaying the table names stored in the transaction template and modifying the table names based on input received from the user.

24. The method of claim 21 comprising displaying the key value to a user and modifying the key value based on input received from the user.

25. A method of providing transaction protection for a database comprising:

creating a plurality of transaction templates each comprising a plurality of filenames identifying files in the database affected during a transaction and a key value representing one or more data fields in the database included in every action performed during the transaction;

for each of a plurality of entries in a log of the database, determining whether the entry belongs to a transaction based on the data in the entry and the key values associated with the templates;

maintaining index files representing transactions in progress by creating a new index file for each new transaction, adding data to a given index file from a log entry which belongs to the transaction associated with the given index file, and deleting index files when transactions with which they are associated are complete; and

in the event the database is damaged, using existing index files to determine at least which transactions did not complete before the database was damaged.

26. For use with a database manager program in which a log of database operations is maintained having a plurality of records each representing an action performed on the database, a system for providing externally defined transaction protection to a database, the system comprising:

means for retrieving records from the log;

a plurality of transaction templates each containing information representing one or more types of transactions;

first comparison means for determining based on the information contained in the templates whether each log record is part of a type of transaction;

second comparison means for indicating which records are part of transactions in progress;

means for, in the event of computer system failure or damage, undoing any actions performed on the database as represented by records which are part of transactions in progress.

27. A computer usable medium having computer readable program code embodied in said medium for causing the computer to perform method steps for creating a transaction template from a log of a database, the log comprising a plurality of records of changes made to the database, the records containing data values associated with data fields in the database, the method steps comprising:

retrieving the data values from the records and storing the data values;

comparing the data values from a given record with data values from other records;

when one or more data values from the given record match one or more data values from one or more other records, identifying the one or more data fields from the database files associated with the matching one or more data values; and

creating a transaction template associated with a key value comprising the identified one or more data fields.

28. A computer usable medium having computer readable program code embodied in said medium for causing the computer to perform method steps for defining transactions from a log of a database, the log comprising data relating to changes to the database, the log data being stored in a plurality of records each having a plurality of fields, the method steps comprising:

for each of a plurality of records in the log, determining whether the record relates to a transaction included in a transaction template;

for each record that relates to a transaction included in the template, determining whether the record relates to a new transaction or an incomplete transaction;

for each record relating to a new transaction, creating and storing an indicator for the transaction and inserting information relating to the record into the indicator; and

for each record relating to an incomplete transaction, determining whether the record represents the end of the incomplete transaction and, if the record does not represent the end of the incomplete transaction, inserting information relating to the record into the previously created indicator for the incomplete transaction.