ARCHIVAL OF DATA IN A RELATIONAL DATABASE MANAGEMENT SYSTEM USING BLOCK LEVEL COPY

• US 20180025034A1
• Filed: 07/20/2016
• Published: 01/25/2018
• Est. Priority Date: 07/20/2016
• Status: Active Grant

• Current Assignee: Kyndryl Incorporated
• Original Assignee: International Business Machines Corporation
• Inventors: Gnanamani, Senthil Nathan, Kothandaraman, Gnanam Badhmanaban, Lakshminarasimhan, Sriram
• Granted Patent: US 10,915,513 B2
• Time in Patent Office: Days
• CPC Class Codes: G06F 16/219 Managing data history or versioning querying versioned data G06F16/2474; querying temporal data G06F16/2477
• CPC Class Codes: G06F 16/2272 Management thereof

Abstract

Archiving data in a relational database management system through a process in which an invisible index is created for each primary index of a data. When an input is received to initiate block copy of data to be archived; the data tables are locked and the identified blocks of data form the data table are block copied to an archive table. The invisible index is updated to correspond to the data table which had data archived. The primary index is then swapped with the invisible index and the data tables are unlocked. Data blocks from the main table which were copied into the archival table is deleted along with the old primary index.

Claims

1. A method of archiving data in a relational database management system comprising the steps of:

a computer creating an invisible index for each primary index of a data table by copying data from the data table of the primary indexes that will not be archived;

the computer updating both the primary index and invisible indexes based on changes to the data table until an input is received to initiate a block copy of data to be archived;

the computer receiving an input to initiate block copy of data to be archived;

the computer locking the data tables;

the computer block copying identified blocks of data from the data table to an archive table and updating the invisible index to correspond to the data table;

the computer swapping the primary index with the invisible index;

the computer unlocking the data tables;

the computer deleting data blocks from a main table which were copied into the archival table; and

the computer deleting the primary index in which the invisible index was created from prior to archiving.

2. The method of claim 1, wherein the identified blocks of the main table being block level copied correspond to a partition of the main table.

3. The method of claim 1, wherein prior to the step of the computer creating an invisible index for each primary index of a data table further comprises the steps of:

the computer setting an archive indicator and an archive key for a column of the data table;

the computer storing records in order of the archive key;

the computer tracking and maintaining mapping of data ranges versus block numbers through a system mapping table.

4. The method of claim 3, wherein the system mapping table stores ranges of data and blocks for archiving.

5. The method of claim 3, wherein the system mapping table is updated when an inserted value in a block falls outside of an established high archive key range and low archive key range.

6. The method of claim 1, further comprising the computer compressing the main table to eliminate the space formerly occupied by the deleted data blocks.

7. The method of claim 1, in which the method is started at a time established by a maintenance schedule.

8. A computer program product for archiving data in a relational database management system, a computer comprising at least one processor, one or more memories, one or more computer readable storage media, the computer program product comprising a computer readable storage medium having program instructions embodied therewith, the program instructions executable by the computer to perform a method comprising:

creating, by the computer, an invisible index for each primary index of a data table by copying data from the data table of the primary indexes that will not be archived;

updating, by the computer, both the primary index and invisible indexes based on changes to the data table until an input is received to initiate a block copy of data to be archived;

receiving, by the computer, an input to initiate block copy of data to be archived;

locking, by the computer, the data tables;

block copying, by the computer, identified blocks of data from the data table to an archive table and updating the invisible index to correspond to the data table;

replacing, by the computer, the primary index with the invisible index;

unlocking, by the computer, the data tables;

deleting, by the computer, data blocks from a main table which was copied into the archival table; and

deleting, by the computer, the primary index in which the invisible index was created from prior to archiving.

9. The computer program product of claim 8, wherein the identified blocks of the main table being block level copied correspond to a partition of the main table.

10. The computer program product of claim 8, wherein prior to the program instructions of creating, by the computer, an invisible index for each primary index of a data table further comprises the program instructions of:

setting, by the computer, an archive indicator and an archive key for a column of the data table;

storing, by the computer, records in order of the archive key;

tracking and maintaining, by the computer, mapping of data ranges versus block numbers through a system mapping table.

11. The computer program product of claim 10, wherein the system mapping table stores ranges of data and blocks for archiving.

12. The computer program product of claim 10, wherein the system mapping table is updated when an inserted value in a block falls outside of an established high archive key range and low archive key range.

13. The computer program product of claim 8, further comprising compressing, by the computer, the main table to eliminate the space formerly occupied by the deleted data blocks.

14. The computer program product of claim 8, in which the program instructions are started at a time established by a maintenance schedule.

15. A computer system for archiving data in a relational database management system comprising a computer comprising at least one processor, one or more memories, one or more computer readable storage media having program instructions executable by the computer to perform the program instructions comprising:

creating, by the computer, an invisible index for each primary index of a data table by copying data from the data table of the primary indexes that will not be archived;

updating, by the computer, both the primary index and invisible indexes based on changes to the data table until an input is received to initiate a block copy of data to be archived;

receiving, by the computer, an input to initiate block copy of data to be archived;

locking, by the computer, the data tables;

block copying, by the computer, identified blocks of data from the data table to an archive table and updating the invisible index to correspond to the data table;

replacing, by the computer, the primary index with the invisible index;

unlocking, by the computer, the data tables;

deleting, by the computer, data blocks from a main table which was copied into the archival table; and

deleting, by the computer, the primary index in which the invisible index was created from prior to archiving.

16. The computer system of claim 15, wherein the identified blocks of the main table being block level copied correspond to a partition of the main table.

17. The computer system of claim 15, wherein prior to the program instructions of creating, by the computer, an invisible index for each primary index of a data table further comprises the program instructions of:

setting, by the computer, an archive indicator and an archive key for a column of the data table;

storing, by the computer, records in order of the archive key;

tracking and maintaining, by the computer, mapping of data ranges versus block numbers through a system mapping table.

18. The computer system of claim 17, wherein the system mapping table stores ranges of data and blocks for archiving.

19. The computer system of claim 17, wherein the system mapping table is updated when an inserted value in a block falls outside of an established high archive key range and low archive key range.

20. The computer system of claim 15, further comprising compressing, by the computer, the main table to eliminate the space formerly occupied by the deleted data blocks.