Interaction between application of a log and maintenance of a table that maps record identifiers during online reorganization of a database

• US 6,026,412 A
• Filed: 06/30/1997
• Issued: 02/15/2000
• Est. Priority Date: 12/30/1994
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Beavin, Thomas Abel, Sockut, Gary Howard
• Primary Examiner(s): Fetting, Anton W.
• Assistant Examiner(s): LEWIS, CHERYL RENEA
• Application Number:
  US08/885,955
• Time in Patent Office: 960 Days
• Field of Search: 707/200, 707/201, 707/202
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2282 Tablespace storage structures; Management thereof
• CPC Class Codes: Y10S 707/99952 Coherency, e.g. same view to multiple users
• CPC Class Codes: Y10S 707/99953 Recoverability

Abstract

The present invention includes reorganization of a Database Management System (DBMS). The reorganization of the present invention is implemented by recording a first current Relative Byte Address (RBA). Then, data is copied from the old area in the table space to a new area in the table space in reorganized form. In the present invention, throughout most of reorganization a user maintains access to the DBMS'"'"'s normal facilities to read and write to the old area. The DBMS uses its normal facilities to record writing, which occurs during reorganization, in a log. The reorganization in accordance with the present invention reads the log (that has been written to during reorganization) and processes the log to the new area to bring the new area up to date. This process is performed with the use of a RID mapping table. Finally, at the end of reorganization, the user'"'"'s access is switched from the old area to the new area.

Claims

1. A method for performing reorganization in a database management system (DBMS), comprising the steps of:

reorganizing a plurality of data records which originally reside in an old area of a table space, wherein each of said data records has an old record identifier (RID), while read/write access to said old area is retained;

directing said reorganized data records to a new area in said table space, wherein each of said data records in said new area has a new RID, while read/write access to said old area is retained;

maintaining a mapping table that maps between said old RID and said new RID;

translating a log entry RID for a log entry to said new RID using said mapping table, wherein said log entry RID is said old RID; and

applying said translated log entry with said new RID to said data records in said new area in said table space.

2. The method according to claim 1, further comprising the step of copying at least a subset of a log prior to said translating and applying steps.

3. The method according to claim 1, wherein said reorganizing and directing steps further comprise the steps of:

unloading said data records from said old area;

reorganizing said data records;

reloading said data records in said new area; and

inserting an entry containing said old RID and said new RID into said mapping table.

4. The method according to claim 3, wherein said step of reorganizing said data records further comprises the step of sorting said data records by clustering key values.

5. The method according to claim 1, wherein said log entry comprises a plurality of log entries, and said method further comprises the step of sorting said log entries by said old RIDs prior to said translating step.

6. The method according to claim 5, wherein said translating step further comprises the step of calculating a new RID.

7. The method according to claim 1, wherein said log entry comprises a plurality of log entries, and said method further comprises the step of sorting said log entries by said new RIDs prior to said applying step.

8. The method according to claim 1, wherein said step of maintaining a mapping table further comprises the steps of updating said mapping table for each log entry to reflect a state of said data records.

9. The method according to claim 1, wherein said step of maintaining a mapping table further comprises the step of updating said mapping table for each log entry to reflect a type of said log entry.

10. The method according to claim 1, wherein said step of applying said log entry further comprises the step of updating a column containing an indicator of log position for each of said data records.

11. The method according to claim 10, wherein said indicator of log position further comprises a log sequence number or a relative byte address.

12. A method for reorganizing a database, comprising the steps of:

recording a first log sequence number (LSN) from a log as a first variable, while read/write access to an old area of a table space is retained;

performing reorganization of a plurality of data records, each having an old record identifier (RID) from said old area of said table space, such that a reorganized version of said data records, each having a new RID in a new area of said table space, is directed to said new area while read/write access to said old area is retained;

recording a second LSN from said log as a second variable; and

applying each log entry between said first variable and said second variable to said reorganized version of said data record identified by said new RID in said new area by using a RID mapping table to translate said old RID to said new RID, while read/write access to said old area is retained.

13. The method according to claim 12, further comprising the steps of:

recording a third LSN from said log as a third variable;

applying said log entries between said second and third LSN variables to said reorganized version of said data records identified by said new RIDs, by using said RID mapping table to translate said old RIDs to said new RIDs, while read-only access is retained.

14. The method of claim 13, further comprising the steps, after recording said third variable of:

comparing, on a next iteration, a difference between said third variable and said second variable with a first predetermined value;

if said comparison is greater than or equal to a second predetermined value, then implementing an action to allow a log application to catch up.

15. The method of claim 14, wherein said step of allowing said log application to catch up further comprises the step of quiescing write access to said old area.

16. The method of claim 14, wherein said step of allowing said log application to catch up further comprises the step of increasing scheduling priority of reorganization.

17. The method of claim 14, wherein said step of allowing said log application to catch up further comprises the step of asking an operator to choose an action.

18. The method of claim 14, further comprising the step of, if said comparison is not above said predetermined value, recording a new LSN variable and continuing with a next iteration from previous LSN variable to current LSN variable.

19. The method of claim 14, wherein said first predetermined value comprises a difference between said second variable and said first variable.

20. The method of claim 19, wherein said second predetermined value is zero.

21. The method according to claim 14, further comprising the step of iterati vely repeating said steps until all log values have been applied to said reorganized version of said data records.

22. The method of claim 21, further comprising the steps of:

quiescing all access of said old data area;

converting access from said old area to said new area; and

starting read/write access to said table space containing said new area.

23. The method of claim 22, further comprising the step, after the step of starting read/write access to said new area, of erasing said old area and said RID mapping table.

24. The method of claim 22, further comprising the step of creating a backup copy of said new data area.

25. The method of claim 13, further comprising the steps, after recording said third variable of:

comparing, on a next iteration, a difference between said third variable and said second variable with a first predetermined value;

if said comparison is greater than or equal to a second predetermined value, then terminating reorganization.

26. The method of claim 13, further comprising the steps of:

quiescing all access of said old data area;

converting access from said old area to said new area; and

starting read/write access to said table space containing said new area.

27. The method of claim 26, further comprising the step, after the step of starting read/write access to said new area, of erasing said old area and said RID mapping table.

28. The method of claim 26, further comprising the step of creating a backup copying of said new data area.

29. A computer program product, comprising:

a computer usable medium having computer readable program code means embodied in said medium for causing a database management system (DBMS) to be reorganized, the computer readable program code comprising,

computer readable program code means for reorganizing a plurality of data records which originally reside in an old area of a table space, wherein each of said data records has an old record identifier (RID);

computer readable program code means for directing said reorganized data records to a new area in said table space, wherein each of said data records in said new area has a new RID;

computer readable program code means for maintaining a mapping table that maps between said old RID and said new RID;

computer readable program code means for translating a log entry RID for a log entry to said new RID using said mapping table, wherein said log entry RID is said old RID; and

computer readable program code means for applying said translated log entry with said new RID to said data records in said new area in said table space.

30. The computer program product according to claim 29, further comprising computer readable program code means for copying at least a subset of a log.

31. The computer program product according to claim 29, wherein said computer readable program code means for reorganizing and directing further comprises:

computer readable program code means for unloading said data records from said old area;

computer readable program code means for reorganizing said data records;

computer readable program code means for reloading said data records in said new area; and

computer readable program code means for inserting an entry containing said old RID and said new RID into said mapping table.

32. The computer program product according to claim 31, wherein said computer readable program code means for reorganizing said data records further comprises computer readable program code means for sorting said data records by clustering key values.

33. The computer program product according to claim 29, wherein said log entry comprises a plurality of log entries, and said computer program product further comprises computer readable program code means for sorting said log entries by said old RIDs.

34. The computer program product according to claim 33, wherein said computer readable program code means for translating further comprises computer readable program code means for calculating a new RID.

35. The computer program product according to claim 29, wherein said log entry comprises a plurality of log entries, and said computer program product further comprises computer readable program code means for sorting said log entries by said new RIDs.

36. The computer program product according to claim 29, wherein said computer readable program code means for maintaining a mapping table further comprises computer readable program code means for updating said mapping table for each log entry to reflect a state of said data records.

37. The computer program product according to claim 29, wherein said computer readable program code means for maintaining a mapping table further comprises computer readable program code means for updating said mapping table for each log entry to reflect a type of said log entry.

38. The computer program product according to claim 29, wherein said computer readable program code means for applying said log entry further comprises computer readable program code means for updating a column containing an indicator of log position for each of said data records.

39. The computer program product according to claim 38, wherein said indicator of log position further comprises a log sequence number or a relative byte address.

40. A computer program product, comprising:

a computer usable medium having computer readable program code means embodied in said medium for causing a database management system (DBMS) to be reorganized, the computer readable program code comprising,

computer readable program code means for recording a first log sequence number (LSN) from a log as a first variable;

computer readable program code means for performing reorganization of a plurality of data records, each having an old record identifier (RID) from said old area of said table space, such that a reorganized version of said data records, each having a new RID in a new area of said table space, is directed to said new area;

computer readable program code means for recording a second LSN from said log as a second variable; and

computer readable program code means for applying each log entry between said first variable and said second variable to said reorganized version of said data record identified by said new RID in said new area by using a RID mapping table to translate said old RID to said new RID.

41. The computer program product according to claim 40, further comprising:

computer readable program code means for recording a third LSN from said log as a third variable;

computer readable program code means for applying said log entries between said second and third LSN variables to said reorganized version of said data records identified by said new RIDs, by using said RID mapping table to translate said old RIDs to said new RIDs.

42. The computer program product according to claim 41, further comprising:

computer readable program code means for comparing a difference between said third variable and said second variable with a first predetermined value; and

computer readable program code means for implementing an action to allow a log application to catch up.

43. The computer program product of claim 42, wherein said computer readable program code means for allowing said log application to catch up further comprises computer readable program code means for quiescing write access to said old area.

44. The computer program product of claim 42, wherein said computer readable program code means for allowing said log application to catch up further comprises computer readable program code means for increasing scheduling priority of reorganization.

45. The computer program product of claim 42, wherein said computer readable program code means for allowing said log application to catch up further comprises computer readable program code means for asking an operator to choose an action.

46. The computer program product of claim 42, further comprising:

computer readable program code means for recording a new LSN variable; and

computer readable program code means for continuing with a next iteration from previous LSN variable to current LSN variable.

47. The computer program product of claim 42, wherein said first predetermined value comprises a difference between said second variable and said first variable.

48. The computer program product of claim 47, wherein said second predetermined value is zero.

49. The computer program product of claim 41, further comprising:

computer readable program code means for comparing, on a next iteration, a difference between said third variable and said second variable, with a first predetermined value; and

computer readable program code means for terminating reorganization.

50. The computer program product of claim 41, further comprising:

computer readable program code means for quiescing all access of said old data area;

computer readable program code means for converting access from said old area to said new area; and

computer readable program code means for starting read/write access to said table space containing said new area.

51. The computer program product of claim 50, further comprising computer readable program code means for erasing said old area and said RID mapping table.

52. The computer program product of claim 50, further comprising computer readable program code means for creating a backup copying of said new data area.