DBMS log reduction for database online reorganization process
Original Publication Date: 2005-Jan-28
Included in the Prior Art Database: 2005-Jan-28
A program is disclosed that the volume of DBMS (DataBase Management System) log produced for database reorganization can be significantly reduced. By logging the logical information for the database physical changes made through the reorganization process to dissolve the storage fragmentation, it eliminates the ‘user data’ content from the database update log records.
DBMS log reduction for database online reorganization process DBMS log reduction for database online reorganization processDBMS log reduction for database online reorganization process DBMS log reduction for database online reorganization process
Updating records (user data) in DBMS databases stored in external storage devices will suffer storage fragmentation, and it results in a performance degradation and poor space utilization of the devices. Periodic database reorganization is mandatory for removing this fragmentation and for sequencing the records in physical blocks. The 'online' reorganization is nothing but DBMS online database updates accompanied by the 'database update log records' that contain 'before and after image (or only after image)' for the physically changed portion. Moving the existing user data (random image) to another location by the reorganization process is like inserting new user data. A huge volume of the physical image change means huge log volume.
All user data logged, though nothing is changed as for the user data. Logging the user data portion can be eliminated. The information required to be logged is 'which record is moved from there to here' .
The following is the implementation for describing how it works, and the examples to show the effectiveness.
1.Creating DBMS log records for database reorganization process (Database updates logs for database reorganization ) (1)'record moved' reorg. log record (created for the each record moved)
-Record identification: identification as to pinpoint the record, or the length of the image moved
-Move position: 'FROM' address (block + position) and 'TO' address (block + position)
-Additional info.: the control fields updated along with moving the
address as necessary (if required)
(2)'free space' reorg. log record (created for each block that contained the records moved)
-'free space' starting position and its length (Unit of process/recovery log for database reorganization)
(3)'block completed' (created for each block after all the records moved, can be the same record with (2))
2. Database recovery using the above log records (1)Get a log record (existing process)
(2)Unique process for each record (only cases with the reorg. records are described)
If 'record moved',
(Read the related database blocks and prepare the work buffers )
-Read the 'FROM' database block (if not yet read) into the 'FROM' input buffer 'F-I' and output buffer 'F-O'
-Read the 'TO' database block (if not yet read) into the 'TO' input buffer 'T-I' and output buffer 'T-O'
(Create the after image)
-Move (copy) the image of the 'record moved' on 'F-I' to the appropriate position on 'T-O'
-Update the control fields as necessary
If 'free space', create the free space image on the output buffers ('F-O' or 'T-O')
If 'completed block', write the output buffers 'F -O' and 'T-O' (3)Read the next record (back to (1) - existing process)
Examples for the log records created shown below .