Lossless distribution of time series data in a relational data base network

• US 5,553,279 A
• Filed: 10/08/1993
• Issued: 09/03/1996
• Est. Priority Date: 10/08/1993
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Goldring, Robert D.
• Primary Examiner(s): AMSBURY, WAYNE P
• Application Number:
  US08/134,229
• Time in Patent Office: 1,061 Days
• Field of Search: 395/600
• US Class Current: 707/615
• CPC Class Codes: G06F 11/1471 involving logging of persistent data for recovery
• CPC Class Codes: G06F 16/2358 Change logging, detection, and notification replication G06F16/27
• CPC Class Codes: G06F 2201/82 Solving problems relating to consistency ensuring consistency in mirrored systems G06F11/2064
• CPC Class Codes: Y10S 707/99952 Coherency, e.g. same view to multiple users
• CPC Class Codes: Y10S 707/99954 Version management

Abstract

A computer processing system that receives sequences of changes to a data base and records them into an activity log for later retrieval also maintains a consistent change data table that contains sufficient change information for each of the changes to the data base such that the changes can be propagated through multiple copies of the data base by consulting the consistent change data table. The consistent change data includes information sufficient to permit reconstruction of the data base to reflect the condition of the data base at any moment of time in the activity log. Because the consistent change data is complete, it permits producing multi-generational copies of data base tables for replication from one copy level to any other subsequent level, or iteration, of copy.

Claims

1. A method of propagating a sequence of data base table changes that have occurred since a predetermined time in a computing system from a source data table to multiple target tables, the tables being stored in a memory unit of the computing system, the computing system further having a central processor unit operating under control of a system clock for controlling input and output to the memory, receiving transactional updates comprising data change operations from a plurality of concurrent processes, recording time stamp data and the transactional updates in an activity log of a storage medium connected to the central processor unit, and implementing the data change operations, the method comprising the steps of:

(a) defining a change data table for each source data table and target table, each change data table having a column for storing the data change operations for a transactional update received by the central processor and columns for a commit sequence number, an intent sequence number, and an indicator of the data change operation;

(b) reading recorded events from the activity log and identifying a time stamp value more recent than the predetermined time;

(c) reading recorded events from the activity log having a time stamp value more recent than the predetermined time and identifying committed transactional updates to the source data table from the activity log;

(d) assigning a commit sequence number to the committed transactional updates;

(e) updating the change data table by recording each data change operation of the committed transactional updates in the change data table;

(f) copying the data change operations recorded in the change data table of the updated source data table into the change data table of the target table; and

(g) applying the recorded data change operations to the source data table.

2. A method as defined in claim 1, wherein the predetermined time comprises the time stamp value of a last propagated transactional update.

3. A method of propagating a sequence of data base table changes that have occurred since a predetermined time in a computing system from a source data table to at least one target table, the tables being stored in a memory unit of the computing system, the computing system further having a central processor unit operating under control of a system clock for controlling input and output to the memory, receiving transactional updates comprising data change operations from a plurality of concurrent processes, recording time stamp data and the transactional updates in an activity log of a storage medium connected to the central processor unit, and implementing the change operations, the method comprising the steps of:

(a) defining a change data table for each source data table and target table, each change data table having a column for storing the data change operations for each transactional update received by the central processor and a column for storing the time stamp data;

(b) reading recorded events from the activity log and identifying a time stamp value more recent than the predetermined time;

(c) reading recorded events from the activity log having a time stamp value more recent than the predetermined time and identifying committed transactional updates to the source data table from the activity log;

(d) assigning a commit sequence number to the committed transactional updates;

(e) updating the change data table by recording each data change operation of the committed transactional updates in the change data table;

(f) copying the change operations recorded in the change data table of the updated source data table into the change data table of the target table; and

(g) applying the recorded data change operations to the source data table,

wherein the step of defining a change data table comprises defining a data table that includes columns for time stamp data, a commit sequence number, an intent sequence number, and an operation identifier that specifies the data change operation as being either an insert, update, or delete operation.

4. A method as defined in claim 3, wherein the step of updating the consistent change data table comprises recording only the most recent change operation read from the activity log.

5. A method as defined in claim 3, wherein the intent sequence number comprises a number that is sequentially assigned to each update change operation in the order in which the operation is performed.

6. A method as defined in claim 3, wherein the commit sequence number comprises a number that is sequentially assigned to each transactional update in the order in which the update was committed.

7. A method of propagating a sequence of data base table changes that have occurred since a predetermined time in a computing system from a source data table to at least one target copy table, the tables being stored in a memory unit of the computing system, the computing system further having a central processor unit operating under control of a system clock for controlling input and output to the memory, receiving transactional updates comprising data change operations from a plurality of concurrent processes, recording commit time data and the transactional updates in an activity log of a storage medium connected to the central processor unit, and implementing the data change operations, the method comprising the steps of:

(a) defining a change data table for each source data table and target table, the change data tables having a column for storing the data change operations for each transactional update received by the central processor and a column for storing a serially increasing progress sequence number;

(b) defining a pruning control table for each source data table, the pruning control tables having a column for storing a limit sequence number for controlling propagation of the data change operations and having a row for each copy of a source data table;

(c) applying the data change operations in the change data table to the source data table in the order of the progress sequence number;

(d) setting the limit sequence number in the pruning control table row for the source data table equal to the progress sequence number from the most recent applied data change operation;

(e) determining the minimum limit sequence number in the rows of the pruning control table; and

(f) deleting rows in the change data table having a progress sequence number less than the minimum limit sequence number of the pruning control table.

8. A method as defined in claim 7, wherein the limit sequence number of the pruning control table in a row of the table corresponding to a copy table is set to zero when the copy is initialized.

9. A method as defined in claim 7, wherein the progress sequence number of the consistent change data table is equal to the commit time data read from the activity log corresponding to an approximate transactional update commit time of a committed transactional update.

10. A method of responding to user requests in a computing system for copying selected portions of a source table to a target copy table, the tables being maintained in a memory unit of the computing system by a data base management system, the computing system further having a central processor unit operating under control of a system clock for controlling input and output to the memory, receiving transactional updates comprising data change operations from a plurality of concurrent processes that modify source tables in the storage medium, recording time stamp data and the transactional updates in an activity log of a storage medium connected to the central processor unit, and implementing the data change operations, the method comprising the steps of:

(a) defining change data tables and target tables and associating them with each source table, each change data table having a column for storing the data change operations for each transactional update received by the central processor and a column for storing a serially increasing progress sequence number;

(b) defining a pruning control table for each source table, the pruning control tables having a column for storing a limit sequence number for controlling propagation of the data change operations and having a row for each copy of a source table;

(c) responding to a target table initialization by setting the limit sequence number in the pruning control table row corresponding to the target table to zero;

(d) copying the selected portions of the source table to its associated target table;

(e) identifying a target change data table associated with the target table;

(f) refreshing the target table by copying the columns of the source change data table that have a progress sequence number greater than a predetermined starting value to the target change data table and applying the change operations in the source change data table to the target table;

(g) setting the limit sequence number in the pruning control table row for the target table equal to the progress sequence number from the most recent applied data change operation;

(h) determining the minimum limit sequence number in the rows of the pruning control table; and

(i) deleting rows in the target change data table having a progress sequence number less than the minimum limit sequence number of the pruning control table.

11. A method as defined in claim 10, wherein the limit sequence number in a row of the pruning control table corresponding to a target table is set to zero when the target table is initialized.

12. A method as defined in claim 10, wherein the progress sequence number of the change data table is equal to the commit time data read from the activity log corresponding to an approximate transactional update commit time of a committed transactional update.

13. A computing system that propagates a sequence of changes to a collection of user defined data tables from a source user table to at least one target copy table of the computing system, the computing system comprising:

a memory unit for containing the tables;

a non-volatile storage medium;

a system clock;

a central processor unit, operating under control of the system clock, that controls input and output to the memory unit, receives transactional updates comprising data change operations from a plurality of concurrent processes, records commit time data and the transactional updates in an activity log of the storage medium, and implements the data change operations;

process means for defining a change data table for each source user table and target copy table in the memory unit, the consistent change data tables having a column for storing the data change operations for each transactional update received by the central processor unit and a column for storing a serially increasing progress sequence number;

copy means for propagating a transactional update from the source user table to the target copy table by causing the central processor unit to apply the data change operations in the change data table to the source user table in the order of the progress sequence number; and

pruning means for defining a pruning control table for each source user table, the pruning control tables having a column for storing a limit sequence number for controlling propagation of the data change operations and having a row for each copy of a source user table and setting the limit sequence number in the pruning control table row for the source user table equal to the progress sequence number from the most recent applied data change operation, determining the minimum limit sequence number in the rows of the pruning control table, and deleting rows in the change data table having a progress sequence number less than the minimum limit sequence number of the pruning control table.