Automatic prunning for log-based replication

[0002] This application claims the benefit under 35 U.S.C. §119(e) of co-pending and commonly-assigned U.S. Provisional application Serial No. 60/129,618, entitled “AUTOMATIC PRUNING FOR LOG-BASED REPLICATION,” filed on Apr. 16,1999, by Ion T. Ieong and Patricia L. Shimer, which application is incorporated by reference herein.

[0003] 1. Field of the Invention

[0004] This invention relates in general to database management systems performed by computers, and in particular, to automatic pruning for log-based replication of tables within a relational database management system.

[0005] 2. Description of Related Art

[0006] Database replication refers to the process of duplicating the data contained in source database tables and placing it in another set of corresponding target database tables, either completely or partially, as well as either locally or remotely, and synchronously or asynchronously. Often, when the data in the original source tables is updated, it is generally more efficient to propagate the updates to the target tables, rather than duplicating all the data contained in the source tables.

[0007] In general, relational databases store updates in a journal (hereinafter referred to as a log) for recovery reasons, in addition to writing the updates permanently to the tables stored on disks. Log-based replication refers to the asynchronous process of reading the updates to the tables from the log and propagating the updates to the target tables.

[0008] The database log requires significant disk space since it grows as updates are made to the database tables. Therefore, the old data in the log needs to be deleted (hereafter referred to as pruned), if it is no longer needed for database recovery operations. On the other hand, log-based replication requires that the log be available to capture the updates to the database tables. As a result, the log-based replication and the pruning of the log have to be coordinated to prevent missing any updates in the target tables. It is non-trivial to determine what the optimal point is to satisfy both the log-based replication and the pruning of the log.

[0009] To overcome the limitations in the prior art described above, and to overcome other limitations that will become apparent upon reading and understanding the present specification, the present invention discloses a method, apparatus, and article of manufacture for automatic pruning of a log used in log-based replication of database tables within a relational database management system. An optimal point at which to prune the log is periodically determined, such that the optimal point provides a minimum amount of storage space for the log and yet ensures that all updates to the database table can be properly replicated from the log. The log is then automatically pruned of selected records prior to the optimal point.

[0010] Referring now to the drawings in which like reference numbers represent corresponding parts throughout:

[0011] FIG. 1 illustrates the hardware and software environment of the present invention; and

[0012] FIG. 2 is a flowchart that illustrates the logic of automatic pruning according to the preferred embodiment of the present invention.

[0013] In the following description of the preferred embodiment, reference is made to the accompanying drawings which form a part hereof, and in which is shown by way of illustration a specific embodiment in which the invention may be practiced. It is to be understood that other embodiments may be utilized and structural and functional changes may be made without departing from the scope of the present invention.

[0014] The present invention determines an optimal point at which to prune a database log, such that it requires the minimum amount of disk space, and yet ensures all the updates to database tables can be properly replicated. The optimal point is periodically re-determined and the log is automatically pruned. Thus, the present invention not only minimizes the amount of space required to replicate the database tables, but also relieves users of the burden of manual interaction with the computer to continuously monitor space usage and consumption.

[0015] FIG. 1 illustrates an exemplary hardware and software environment that could be used with the preferred embodiment of the present invention. In the exemplary environment, a server computer 100 is connected to one or more client computers 102 or terminals. The server computer 100 executes a relational database management system (RDBMS) 104 that manages user and system tables 106, and includes a log 108. In the preferred embodiment of the present invention, the RDBMS 104 comprises the DataBase 2 (DB2™) Universal DataBase (UDB™) product offered by IBM Corporation, although those skilled in the art will recognize that the present invention has application to any RDBMS 104.

[0016] As illustrated in FIG. 1, the RDBMS 104 includes a log manager process 110 to manage log data sets 108, and a replication process 112 interfaces to the RDBMS 104 and log manager process 110 to manage the replication of updates to the tables 106. The replication process 112 includes one or more main-memory resident data structures 114 that are used to control the operation of the replication process 110.

[0017] Generally, the RDBMS 104, tables 106, log 108, log manager process 110, replication process 112, and data structures 114 each comprises logic and/or data that is embodied in or retrievable from a device, medium, or carrier, e.g., a fixed or removable data storage device, a remote device coupled to the computer by a data communications device, etc. Moreover, this logic and/or data, when read, executed, and/or interpreted by the computer 100, cause the computer 100 to perform the steps necessary to implement and/or use the present invention.

[0018] Thus, the present invention may be implemented as a method, apparatus, or article of manufacture using standard programming and/or engineering techniques to produce software, firmware, hardware, or any combination thereof. The term “article of manufacture”, or alternatively, “computer program carrier”, as used herein is intended to encompass logic or instructions accessible from any computer-readable device, carrier, or media.

[0019] Of course, those skilled in the art will recognize many modifications may be made to this configuration without departing from the scope of the present invention. For example, those skilled in the art will recognize that any combination of the above components, or any number of different components, including computer programs, peripherals, and other devices, may be used to implement the present invention, so long as similar functions are performed thereby.

[0020] In general, the log 108 is comprised of a time series of records. In the preferred embodiment, it is assumed that each log 108 record has a unique ascending log 108 sequence number, although other sequences could be used as well.

[0021] A transaction generally involves updating one or more data rows in one or more of the tables 106, which results in the creation of one or more records in the log 108. Log 108 records belonging to different data rows can interleave with each other on the log 108.

[0022] To search for the log 108 record corresponding to the updates made to the tables 106, the replication process 112 reads the log 108 sequentially. When it processes a log 108 record, the replication process 112 advances a current log 108 record pointer.

[0023] The replication process 112 re-assembles data rows from the log 108 records in transient, main-memory resident, data structures 114. These data structures 114 track all of the data rows being reassembled by the replication process 112.

[0024] The replication process 112 also determines the optimal point to prune the log 108. When the replication process 112 finds the first log 108 record for a data row, the log 108 sequence number for that log 108 record is marked. When a data row is re-assembled, replication process 112 removes it from the main-memory data structures 114 and stores it on disk (or other persistent storage) for later propagation of the updates. The optimal prune point is thus obtained by selecting the minimum log 108 sequence number that marks the first log 108 sequence number encountered for each data row reassembled in the main memory data structure 114. If the minimum log 108 sequence number is null, e.g., there are no data rows being reassembled in the main-memory data structures 114, then the optimal prune point defaults to the current log 108 record pointer being processed by the replication process 112.

[0025] All the log 108 records with log 108 sequence number smaller than the prune point may be safely pruned by the log manager process 110. This is essential to ensure correctness, because if the event occurs that the replication process 112 terminates and is not able to save all of its data, all the updates kept in persistent storage are safe; however, the main-memory data structures 114, being transient, are lost. Therefore, when the replication process 112 restarts, all it needs to do is reconstruct the main-memory data structures 114 by re-scanning the log 108 records from the prune point onward. Therefore, no updates are lost.

[0026] The replication process 112 passes the optimal prune point to the log manager process 100 for automatic pruning. To enhance efficiency, a threshold is selected that can best balance efficiency and performance. The threshold may be a counter that is used to keep track of the number of log 108 records and data rows processed since the last prune point, or the threshold may be a specified time lapse since the last prune point, or the threshold may comprise any number of other events. When the amount of data rows being processed reaches that threshold, the optimal prune point is then determined and delivered to the log manager process 110, which in turn automatically prunes the log 108 records, since they will not be needed in the future. As a result, user interaction and monitoring are not required, and the log manager process 110 frees up unneeded storage as soon as possible during replication.

[0027] FIG. 2 is a flowchart that illustrates the logic of automatic pruning according to the preferred embodiment of the present invention.

[0028] First, at the initialization stage, Block 200 represents the replication process 112 rescanning all of the log 108 records between the last prune point and the current log 108 record pointer for incomplete data rows. Thereafter, the replication process 112 is ready to process new log 108 records.

[0029] Block 202 represents the replication process 112 advancing the current log 108 record pointer.

[0030] Block 204 represents the replication process 112 performing a scan in the log 108 record pointed to by the pointer and reassembling the data rows from the log 108 records in the main-memory data structure 114. This Block also represents the replication process 112 marking the first log 108 sequence number for the first log record for each data row in the main-memory data structure 114 that are to be used in determining the optimal prune point.

[0031] Block 206 is a decision block that represents the replication process 112 determining whether there is a complete data row in the main-memory data structure 114. If so, control transfers to Block 208, which represents the replication process 112 moving the complete data row from the main-memory data structure 114 and storing it on disk (or other persistent storage) for later propagation to the target table; otherwise, control transfers Block 210.

[0032] Block 210 is a decision block that represents the replication process 112 determining whether the prune threshold has been reached. If not, control transfers back to Block 202, which represents the replication process 112 processing more log 108 records; otherwise, control transfers to Block 212.

[0033] Block 212 is a decision block that represents the replication process 112 determining whether there are any incomplete data rows being processed, based on an examination of the main-memory data structure 114. If not, control transfers to Block 216, which represents the replication process 112 setting the optimal prune point to the current log 108 record pointer, otherwise, control transfers to Block 214, which represents the replication process 112 setting the optimal prune point to the smallest of log sequence numbers representing the first log record for the incomplete data rows in the main-memory data structure 114.

[0034] Block 218 represents the log manager process 110 using the optimal prune point to prune all of the log 108 records below the optimal prune point and then releasing the occupied disk space. Thereafter, control transfers back to Block 202, which represents the replication process 112 processing more log 108 records.

[0035] This concludes the description of the preferred embodiment of the invention. The following describes some alternative embodiments for accomplishing the present invention. For example, any type of computer, such as a mainframe, minicomputer, or personal computer, could be used with the present invention. Moreover, many types of databases could benefit from the present invention. In addition, any software program performing log-based replication (or its equivalent) could benefit from the present invention.

[0036] In summary, the present invention discloses a method, apparatus, and article of manufacture for automatic pruning of a log used in log-based replication of database tables within a relational database management system. An optimal point at which to prune the log is periodically determined, such that the optimal point provides a minimum amount of storage space for the log and yet ensures that all updates to the database table can be properly replicated from the log. The log is then automatically pruned of selected records prior to the optimal point.

[0037] The foregoing description of the preferred embodiment of the invention has been presented for the purposes of illustration and description. It is not intended to be exhaustive or to limit the invention to the precise form disclosed. Many modifications and Variations are possible in light of the above teaching. It is intended that the scope of the invention be limited not by this detailed description.