Database Archiving via Consistency Point and Log Distillation

Solution Overview

Problem

Existing database systems face challenges in maintaining data consistency during archive operations, as locking methods cause transaction delays due to lock conflicts, while versioning systems require excessive storage and processing overhead to manage multiple versions.

Innovation Solution

A method that establishes a consistency point in a database system, logs changes to source tables, and archives these changes, allowing for efficient copying and restoration of data by reversing log records, thereby combining elements of locking and versioning techniques.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Reliability

If locking methods are used to maintain data consistency during archive operations, then data consistency is preserved, but transaction delays increase due to lock conflicts

Engineering Contradiction:

Improvedata consistencyVSAvoidtransaction delays

Core Design Contradiction:

ReliabilityVSLoss of time

Solution Approach 1:

The patent segments the archive operation into distinct phases: a consistency point is established (using locking to ensure consistency), then rows are copied to the archive, and finally log records are applied. This segmentation allows the locking mechanism to be used only briefly at the consistency point rather than continuously, reducing transaction delays while maintaining data consistency during the critical archiving operation.

Inventive Principle:

Principle #1Segmentation

Solution Approach 2:

The patent applies preliminary action by establishing a consistency point before copying rows to the archive. This preliminary locking action ensures that all changes committed before the consistency point are captured, and the lock is released after the consistency point is established but before the actual data copying begins. This preliminary action maintains consistency without causing continuous transaction delays.

Inventive Principle:

Principle #10Preliminary action

2Loss of time

If versioning systems are used to maintain data consistency, then transaction delays are reduced, but storage requirements and processing overhead increase

Engineering Contradiction:

Improvetransaction delaysVSAvoidstorage space

Core Design Contradiction:

Loss of timeVSQuantity of substance

Solution Approach 1:

The patent extracts only the necessary change information (log records) from the full version history and stores only these differences in the archive, rather than storing complete versions of all rows. This extraction approach reduces storage requirements significantly while still enabling restoration of data to any previous consistent state, thus avoiding the storage overhead of full versioning systems.

Inventive Principle:

Principle #2Taking out (Extraction)

Solution Approach 2:

The patent uses copying by creating a log record that captures the changes between versions. Instead of maintaining multiple complete versions of data, the system copies only the change information (inserts, updates, deletes) and applies these logs when restoration is needed. This copying approach reduces storage space while maintaining the ability to restore to any previous consistent state.

Inventive Principle:

Principle #26Copying

3Loss of time

If versioning systems are used to maintain data consistency, then transaction delays are reduced, but processing overhead for queries increases

Engineering Contradiction:

Improvetransaction delaysVSAvoidprocessing overhead

Core Design Contradiction:

Loss of timeVSDevice complexity

Solution Approach 1:

The patent extracts only the necessary change information (log records) from the full version history and stores only these differences in the archive, rather than storing complete versions of all rows. This extraction approach reduces storage requirements significantly while still enabling restoration of data to any previous consistent state, thus avoiding the storage overhead of full versioning systems.

Inventive Principle:

Principle #2Taking out (Extraction)

Solution Approach 2:

The patent uses copying by creating a log record that captures the changes between versions. Instead of maintaining multiple complete versions of data, the system copies only the change information (inserts, updates, deletes) and applies these logs when restoration is needed. This copying approach reduces storage space while maintaining the ability to restore to any previous consistent state.

Inventive Principle:

Principle #26Copying

Data Source

PatentUS8452730B2Archiving method and system

Publication Date: 2013.05.28 TERADATA CORP

AI summary

A method of archiving a set of source tables in a database system. Each source table has a plurality of rows. Initially, a consistency point for the set of source tables is established. The database system creates a log record for each change (insert, modify, or delete) effected to a row of the source tables subsequent to the consistency point. Substantially all source table rows are copied to an archive (a data storage object that is external to the database system). Then the accumulated log records for the affected source tables are distilled and appended to the archive.