Reduction of join operations when archiving related database tables

• US 7,836,022 B2
• Filed: 10/12/2005
• Issued: 11/16/2010
• Est. Priority Date: 10/12/2005
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Gillespie, Allan E.
• Primary Examiner(s): Ali; Mohammad
• Assistant Examiner(s): LAWSON, DONALD L
• Application Number:
  US11/250,095
• Publication Number:
  US 20070083573A1
• Time in Patent Office: 1,861 Days
• Field of Search: 707/204, 707/2, 707/3, 707/661, 707/667, 707/673, 711/1, 717/122
• US Class Current: 707/661
• CPC Class Codes: G06F 16/284 Relational databases

Abstract

Provided are techniques for receiving a request to archive a child table. In response to receiving the request, a join operation is performed on the child table and parent archive data to create child archive data.

Claims

1. A method for archiving data, comprising:

receiving at a computer including a processor a list of tables to be archived, wherein one table is a root table and each other table is a child table, wherein at least one child table has a parent table that is not the root table; and

processing the list by walking an archive unit tree from top to bottom, wherein the archive unit tree represents the list of tables to be archived, and by;

archiving the root table in the list to create parent archive data in a parent archive table, wherein the parent archive data represents data removed from the parent table; and

processing each child table in the list by selecting a next child table in the list to be archived whose immediate parent table has already been archived, and for the selected child table;

receiving information to generate a predicate from a user;

generating a statement to perform the join operation that includes the predicate to qualify the rows of the child table that are to be archived; and

executing the statement to perform the join operation on the child table and the parent archive table to create child archive data in a child archive table, wherein the child archive table is capable of being a parent archive table for another child table in the list, wherein the parent archive data is used to qualify rows of the child table that are to be moved from the child table into the child archive data, and wherein the join uses columns in the child table and the parent archive table that represent connection keys between the child table and the parent archive table.

2. The method of claim 1, further comprising:

determining tables that are related to the received list of tables based on referential integrity; and

adding the determined tables to the list of tables to be archived.

3. The method of claim 1, further comprising:

storing the child archive data in a child archive data structure.

4. The method of claim 1, further comprising:

creating an index for the child archive data; and

committing changes.

5. A computer program product for archiving data comprising a computer readable storage medium including a computer readable program, wherein the computer readable program when executed by a processor on a computer causes the computer to:

receive a list of tables to be archived, wherein one table is a root table and each other table is a child table, wherein at least one child table has a parent table that is not the root table; and

process the list by walking an archive unit tree from top to bottom, wherein the archive unit tree represents the list of tables to be archived, and by;

archiving the root table in the list to create parent archive data in a parent archive table, wherein the parent archive data represents data removed from the parent table; and

processing each child table in the list by selecting a next child table in the list to be archived whose immediate parent table has already been archived, and for the selected child table;

receiving information to generate a predicate from a user;

generating a statement to perform the join operation that includes the predicate to qualify the rows of the child table that are to be archived; and

executing the statement to perform the join operation on the child table and the parent archive table to create child archive data in a child archive table, wherein the child archive table is capable of being a parent archive table for another child table in the list, wherein the parent archive data is used to qualify rows of the child table that are to be moved from the child table into the child archive data, and wherein the join uses columns in the child table and the parent archive table that represent connection keys between the child table and the parent archive table.

6. The computer program product of claim 5, wherein the computer readable program when executed on a computer causes the computer to:

determine tables that are related to the received list of tables based on referential integrity; and

add the determined tables to the list of tables to be archived.

7. The computer program product of claim 5, wherein the computer readable program when executed on a computer causes the computer to:

store the child archive data in a child archive data structure.

8. The computer program product of claim 5, wherein the computer readable program when executed on a computer causes the computer to:

create an index for the child archive data; and

commit changes.

9. A system for archiving data, comprising:

a processor; and

hardware logic performing operations, the operations comprising;

receiving at a computer including a processor a list of tables to be archived, wherein one table is a root table and each other table is a child table, wherein at least one child table has a parent table that is not the root table; and

processing the list by walking an archive unit tree from top to bottom, wherein the archive unit tree represents the list of tables to be archived, and by;

archiving the root table in the list to create parent archive data in a parent archive table, wherein the parent archive data represents data removed from the parent table; and

processing each child table in the list by selecting a next child table in the list to be archived whose immediate parent table has already been archived, and for the selected child table;

receiving information to generate a predicate from a user;

generating a statement to perform the join operation that includes the predicate to qualify the rows of the child table that are to be archived; and

executing the statement to perform the join operation on the child table and the parent archive table to create child archive data in a child archive table, wherein the child archive table is capable of being a parent archive table for another child table in the list, wherein the parent archive data is used to qualify rows of the child table that are to be moved from the child table into the child archive data, and wherein the join uses columns in the child table and the parent archive table that represent connection keys between the child table and the parent archive table.

10. The system of claim 9, wherein the operations further comprise:

determining tables that are related to the received list of tables based on referential integrity; and

adding the determined tables to the list of tables to be archived.

11. The system of claim 9, wherein the operations further comprise:

storing the child archive data in a child archive data structure.

12. The system of claim 9, wherein the operations further comprise:

creating an index for the child archive data; and

committing changes.