Method for refreshing multicolumn tables in a relational data base using minimal information

• US 4,631,673 A
• Filed: 01/22/1985
• Issued: 12/23/1986
• Est. Priority Date: 01/22/1985
• Status: Expired due to Term

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Lindsay, Bruce G., Haas, Laura M.
• Primary Examiner(s): Zache, Raulfe B.
• Application Number:
  US06/693,500
• Time in Patent Office: 700 Days
• Field of Search: 364/300
• US Class Current: 1/1
• CPC Class Codes: G06F 16/284 Relational databases
• CPC Class Codes: Y10S 707/99939 Privileged access
• CPC Class Codes: Y10S 707/99952 Coherency, e.g. same view to multiple users

Abstract

A method for refreshing a relational data base snapshot manifest in the form of remote read-only copies of selected portions of a base table. The method takes advantage of the fact that each tuple of the base table has a unique identifier TID associated therewith from the time the record is inserted until it is deleted. The TID references the physical location of the tuple within its relation. Two system-maintained fields are added to the base table. These are PREVTID and update ID. One system-maintained column is required in the snapshot table, i.e. BASE TID. Lastly, a column in the catalog of the snapshot is also maintained, i.e. SNAPHIGH.

When the refresh of the snapshot table is required, a single scan of the base relation, in ascending TID sequence, is performed on the base table. The scan produces a series of messages which contain the incremental changes required to update the snapshot table to the current state of the base relation. Upon receipt of the messages at the snapshot table site, a single skip sequential pass across the snapshot in BASE TID sequence is performed to apply the incremental changes. This refreshes the snapshot table to the required state.

Claims

1. A method for updating an extensible multicolumn table (snapshot table) which is a subset of a logically independent extensible multicolumn table (base table) in a nodal network of a distributed relational data base system, each row entry of the base table being assigned a unique identifier, said data base system having local means for generating time stamps in ascending order upon demand, said method comprising the steps of:

(a) defining on the base table a partial ordering of time stamps on the row updates (insertions, deletions, modifications), a total ordering of row entry identifiers, and a backward chaining of the identifiers of adjacent row entries;

(b) scanning the base table and detecting changes thereto as anomalies to the orderings or chaining; and

(c) communicating the anomalies to the snapshot table and altering its contents therewith.

2. In a distributed relational data base facility having an extensible multicolumn table (base table), a second and logically separate multicolumn table forming an operative subset of the base table (snapshot table), and means for generating time stamps (TS) in ascending order upon demand, each row entry of the base table being assigned a unique identifier (TID), a method for updating the entries in the snapshot table, comprising the steps of:

(a) defining column extensions to the base table such that each row entry includes an identifier (PREVTID) of the prior adjacent entry in row major order and a TS value corresponding to the last alteration of the entry, said extensions constituting a total ordering of row entries by TID, a partial ordering by TS values, and a backward chaining by PREVTID;

(b) defining column extensions to the snapshot table such that each row entry includes TID, and associating with the snapshot table the highest valued TS recorded in the base table; and

(c) responsive to a request to update the snapshot table, the further steps of;

(1) scanning the base table in ascending TID order and detecting incremental changes (row insertion, deletion, modification) thereto manifested by changes in orderings or backward chaining, and

(2) communicating and applying the detected changes to the snapshot table by way of a skip sequential pass in ascending TID order.

3. A method according to claim 2, wherein in communicating and applying the detected changes to the snapshot table by way of a skip sequential pass in ascending TID order, said skip sequential pass being modified to utilize either a clustered link or clustering index.