Abstract:
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.

Description:
CROSS REFERENCE TO RELATED APPLICATIONS 
     This application claims priority under 35 U.S.C. §119(e) to the following co-pending and commonly-assigned patent application, which is incorporated herein by reference: 
     Provisional Application Ser. No. 61/012,959, entitled “ARCHIVING METHOD AND SYSTEM,” filed on Dec. 12, 2007. 
    
    
     BACKGROUND 
     Described below is a technique for handling the restore and archive process on an on-line archive. An on-line archive is a process in which database objects, for example database tables, are archived while allowing the objects to be updated. 
     It is desirable that a given archive operation within a database system be associated with a single consistency point. A consistency point can be thought of as a certain point in time such that all changes that committed prior to that point, and only those changes, are visible. All “visible” states of a database are consistent. The transition from one consistent state to another may entail multiple update actions but the intermediate states are only visible within the updating transaction. Only the final state becomes visible when the updating transaction terminates successfully. If the updating transaction does not complete successfully then all changes are automatically voided. 
     Database systems employ various methods to prevent the exposure of transitional states. All such methods are variants of two basic approaches, namely locking and versioning. In a locking system, transactions must request permission from a lock manager to read or update specific data objects. When a lock is granted, it is held by the transaction until it terminates. The lock manager will delay granting a lock while a conflicting lock is held by another transaction. One disadvantage of locking is that transaction delays due to lock conflicts may be excessive under certain conditions. 
     In a versioning system, multiple versions of an object are maintained. Alternatively, at least information sufficient to reconstruct an earlier version is preserved and linked to the new version. Only one version of the object, which is not necessarily the latest version, is visible to a given transaction. With a versioning system, a transaction is never delayed due to a conflicting update. Instead the transaction is given immediate access to some earlier version of the object. Disadvantages of versioning systems include the fact that they require more storage space to maintain multiple versions of objects, additional information must be maintained for each object so that the system can select the correct version and also determine when a version becomes completely obsolete and available for physical removal, and also queries may incur additional processing overhead to filter out unwanted versions of objects. 
     The method described here combines elements of the locking and versioning techniques in a novel way. 
     SUMMARY 
     A method of archiving a set of source tables in a database system is described. 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. 
     Also described below is a method of restoring a target table in a database system from an archive. For each target table, the archive has a plurality of base rows and a set (possibly empty) of log records. Substantially all base rows are copied from the archive to the target table. Substantially all related log records are retrieved from the archive. The target table is then modified by reversing the insert, update, or delete actions indicated by the log records. 
    
    
     
       BRIEF DESCRIPTION OF THE DRAWINGS 
         FIG. 1  is a block diagram of an exemplary large computer system in which the techniques described below are implemented. 
         FIG. 2  is a diagrammatic representation of a preferred form archive process. 
         FIGS. 3A and 3B  illustrate the process of restoring a table from the on-line archive shown in  FIG.2 . 
         FIG. 4  is a flow chart of a preferred form archive process. 
     
    
    
     DETAILED DESCRIPTION 
       FIG. 1  shows an example of a database system  100 , such as a Teradata active data warehousing system available from Teradata Corporation. Database system  100  is an example of one type of computer system in which the techniques of handling a restore and archive process for an on-line archive are implemented. In computer system  100 , vast amounts of data are stored on many disk-storage facilities that are managed by many processing units. In this example, the data warehouse  100  includes a relational database management system (RDBMS) built upon a massively parallel processing (MPP) platform. 
     Other types of database systems, such as object-relational database management systems (ORDMS) or those built on symmetric multi-processing (SMP) platforms are also suited for use here. 
     The data warehouse  100  includes one or more processing modules  105   1 . . . N  that manage the storage and retrieval of data in data-storage facilities  110   1 . . . N . The rows  115   1 . . . Z  of the tables are stored across multiple data-storage facilities  110   1 . . . N  to ensure that the system workload is distributed evenly across the processing modules  105   1 . . . N . A parsing engine  120  organizes the storage of data and the distribution of table rows  115   1 . . . Z  among the processing modules  105   1 . . . N . The parsing engine  120  also coordinates the retrieval of data from the data-storage facilities  110   1 . . . N  over network  125  in response to queries received from a user at a mainframe  130  or a client computer  135  connected to a network  140 . The database system  100  usually receives queries and commands to build tables in a standard format, such as SQL. 
     In one implementation the rows  115   1 . . . Z  are distributed across the data-storage facilities  110   1 . . . N  by the parsing engine  120  in accordance with their primary index. The primary index defines the columns of the rows that are used for calculating a hash value. The function that produces the hash value from the values in the columns specified by a primary index (in Teradata terminology) is called a hash function. 
     It is often necessary to archive rows  115  of the table. In the case of hardware failure a database system manager needs to remove the faulty hardware, replace the hardware and restore the data onto the new hardware from the latest archived copy. It is also helpful for a database developer to conduct testing on data for various reasons. It is helpful to have an archive of the table data so that any changes arising from the testing in the main or primary copy of the data can be restored by the archived copy, or alternatively a complete copy of the archived copy can be made and testing performed on that copy of the archived copy. 
     Shown in  FIG. 1  is a further set of processing modules  150   1 . . . X . Processing modules  150   1 . . . X  manage the storage and retrieval of data in data-storage facilities  155   1 . . . X . It is data-storage facilities  155   . . . X  on which the on-line archive of table rows  115   1 . . . Z  is stored. 
       FIG. 2  illustrates one technique for implementing an on-line archive process. A source table  200  includes base rows  205   1 . . . Z . As part of the archive process, a consistency point is established. A table read lock is placed on source table  200  to establish the consistency point. Once the consistency point is established substantially all of the base rows  205   1 . . . Z  are copied to archive table  220 . Archive table  220  includes base rows  225   1 . . . Z . All base rows  205   1 . . . Z  are copied to base rows  225   1 . . . Z . 
     While the table read lock is in place a private system log  230  is initialized for the table. The read lock is released and the table is again available for regular updating transactions. Any change after the consistency point is recorded in private log  230 . Changes include change to an existing row, deletion of a row, or creation of a new row. 
     In the techniques described below, an on-line archive does not restrict or only minimally restricts updates, deletions, additions and other operations on base rows  205   1 . . . Z . Updates or changes result in a new log record for each change. The system log is maintained in chronological order so that the order of changes to a base row can be determined. 
     In the situation shown in  FIG. 2 , no changes are made to row  205   1 . No entries are made in system log  230 . The corresponding entry row  225   1  in archive table  220  is the same. 
     Row  2  indicated at  205   2  in  FIG. 2  has two subsequent updates made to it. The updates are stored as log record  240  and log record  245  in system log  230 . 
     Row  3  indicated at  205   3  in source table  200  is deleted following the consistency point. Deletion of the row is stored in system log  230  as indicated at  250 . 
     Row  4  does not exist in source table  200  at the consistency point. It is created after the consistency point. It therefore appears in the system log  230  as indicated at  260  and also in archive table  220 , as shown at  225   4 . 
     The copying of rows from the source table  200  to the archive table  220  commences at any  20  time after the consistency point has been established. System log  230  is copied to archive table  220  after all of the regular rows  205   1 . . . Z  have been copied. The logging activity on system log  230  is then stopped and the system log  230  removed from the system. The operation to stop and remove system log  230  does not require any kind of lock on source table  200 . 
       FIGS. 3A and 3B  illustrate the process of restoring a table from the on-line archive of  FIG. 2 . As shown in  FIG. 3A , base rows  225   1 . . . Z  are first copied from archive table  220  to target table  300 . This results in base rows  305   1 . . . Z  in target table  300 . 
     As shown in  FIG. 3B , all changes, deletions and creations from system log  230  are applied to the rows  305   1 . . . Z  of target table  300 . The data from the system log  230  are used to roll back the changes that occurred after the consistency point. There are two log records in system log  230 , namely rows  240  and  245 . Row  240  replaces row  305   2  in target table  300  as shown at  310 , but row  245  is ignored because it reflects a later change. Row  305   3  is deleted from target table  300  due to the application of log record  260 . Row  320  is inserted into target table  300  due to the application of log record  250 . The remaining rows in target table  300  remain unchanged. Resulting table  300  is a table restored from archive table  220 . It is identical to source table  200  prior to archiving. 
       FIG. 4  shows a flowchart of a preferred form process. If there are updates underway  405  at a given point then the updates are committed  410 . Once the updates are committed the consistency point  415  is established. There are no outstanding uncommitted updates at the consistency point. All base rows are copied  420  from the source table to the archive table. 
     The system log is distilled  425  by eliminating multiple log records for the same row. The system log is copied  430  to the target table. 
     A “weak” table level lock is optionally placed on source table  200  during the archiving process. The weak lock allows all DML operations including reading, inserting, updating and deleting rows, but it prevents certain DDL operations such as those that create new indices for a table or otherwise change the structure of the table. Some DDL operations such as collecting statistics are permitted. Alternatively no lock is placed on the source table  200 . 
     In a database in which it is necessary to archive ten different tables all at a consistent point, it is often necessary to wait for individual tables to be out of any update transactions so that locks can be placed on the tables to establish a consistency point. The techniques described here enable changes to rows that occur during the archiving process. These changes are stored in the archived table for subsequent retrieval. 
     As described above, only one log record for each base row is stored in the archive table. This means that the log records do not have any order requirement, so that the changes do not need to be stored in chronological order. 
     The techniques described above enable changes to be made to the table rows during the archive process. 
     The log records do not need to be merged into a single data set or single log. This enables the log records to be handled in parallel and reduces the requirement for a single point tape drive or file that would limit the performance on a large system such as that shown in  FIG. 1 . 
     The restore time for an on-line archive such as that described above is up to and usually less than two times longer than a full read lock dump. This is because in the worst case scenario there is a base row and a corresponding log record in the archive table. Prior techniques involving permanent journals have the potential to be slower. 
     The text above describes one or more specific embodiments of a broader invention. The invention also is carried out in a variety of alternative embodiments and thus is not limited to those described here. Those other embodiments are also within the scope of the following claims.