Apparatus and system for reorganizing a set of database partitions

• US 8,086,576 B2
• Filed: 10/07/2008
• Issued: 12/27/2011
• Est. Priority Date: 12/20/2005
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Plow, Gregory Maurice, Pourmirzaie, Farrokh E., Shiomi, Tatsuyuki
• Primary Examiner(s): Breene, John E
• Assistant Examiner(s): Ly, Anh
• Application Number:
  US12/246,988
• Publication Number:
  US 20090049110A1
• Time in Patent Office: 1,176 Days
• Field of Search: 707/693, 707/748, 707/968, 707/953, 707/972, 707/E17.054
• US Class Current: 707/693
• CPC Class Codes: G06F 16/2282 Tablespace storage structures; Management thereof
• CPC Class Codes: Y10S 707/968 Partitioning
• CPC Class Codes: Y10S 707/972 Partitioning
• CPC Class Codes: Y10S 707/99953 Recoverability
• CPC Class Codes: Y10S 707/99957 Garbage collection
• CPC Class Codes: Y10S 715/968 interface for database querying and retrieval

Abstract

An apparatus and system are disclosed for reorganizing a set of database partitions based on a ranked set of database partitions. The apparatus includes an evaluation module to determine a fragmentation indicator for each database partition within a set of database partitions. A ranking module ranks the set of database partitions according to a rank criteria. The rank criteria includes, at a minimum, the fragmentation indicator. A reorganization module reorganizes a subset of the database partitions in the ranked set of database partitions. The subset is selected, at least in part, by a rank order for the ranked set of database partitions. The subset may be selected such that ranked database partitions satisfying a dynamic reorganization threshold are included in the subset.

Claims

1. An apparatus comprising:

a computer readable storage medium storing computer readable program code executable by a processor, the computer readable program code comprising;

an evaluation module that determines a fragmentation indicator for each database partition within a set of database partitions stored on the computer readable storage medium;

a ranking module that ranks the database partitions according to a rank criteria comprising at least the fragmentation indicator;

a presentation module that presents the ranked set of database partitions for evaluation, the presentation module displaying by way of a graphical user interface one or more indicators representing database partitions that are members of a recalculated subset of database partitions, in response to adjusting a graphically represented reorganization threshold slider bar by way of the graphical user interface, the graphical user interface displaying updated resource requirements based on the recalculated subset of database partitions;

an input module that prompts for a dynamic reorganization threshold in response to the evaluation, the dynamic reorganization threshold defining a subset of database partitions, the subset of database partitions dynamically recalculated in response to changing the dynamic reorganization threshold, the dynamic reorganization threshold changed in response to adjusting the graphically represented reorganization threshold slider bar by way of the graphical user interface; and

a reorganization module that reorganizes a subset of database partitions in the ranked set of database partitions that satisfy the dynamic reorganization threshold wherein the subset is defined at least in part by a rank order for the ranked set of database partitions.

2. The apparatus of claim 1, further comprising an estimation module that estimates a quantity of computing resources required to reorganize the subset of database partitions in the ranked set of database partitions that satisfy the dynamic reorganization threshold and estimate a performance improvement that will result from reorganizing the subset of database partitions in the ranked set of database partitions that satisfy the dynamic reorganization threshold.

3. The apparatus of claim 1, further comprising a recommendation module that recommends a dynamic reorganization threshold.

4. The apparatus of claim 1, wherein the dynamic reorganization threshold has a type selected from the group consisting of a number of ranked database partitions and a percentage of ranked database partitions.

5. The apparatus of claim 1, wherein the reorganization module reorganizes the subset of the database partitions in rank order by reorganizing the highest ranked database partition first and the lowest ranked database partition last.

6. The apparatus of claim 1, wherein the evaluation module evaluates a fragmentation function for each database partition within the set of database partitions.

7. The apparatus of claim 6, wherein the fragmentation function comprises determining whether a database partition comprises at least a predetermined amount of free space and determining whether the database partition comprises at least a predetermined number of overflow blocks.

8. The apparatus of claim 1, wherein the input module changes the subset of the database partitions by adding or removing a database partition from the subset in response to an input.

9. The apparatus of claim 1, wherein the subset of database partitions comprises all of the database partitions in the ranked set of database partitions.

10. A system for reorganizing a set of database partitions for a client, the system comprising:

a processor;

one or more storage devices storing a database comprising a set of database partitions;

one or more Input/Output (I/O) devices;

a memory;

a reorganization utility comprising;

an evaluation module that determines a fragmentation indicator for each database partition within a set of database partitions;

a ranking module that ranks the database partitions according to a rank criteria comprising at least the fragmentation indicator;

a presentation module that presents the ranked set of database partitions for evaluation, the presentation module displaying by way of a graphical user interface one or more indicators representing database partitions that are members of a recalculated subset of database partitions, in response to adjusting a graphically represented reorganization threshold slider bar by way of the graphical user interface, the graphical user interface displaying updated resource requirements based on the recalculated subset of database partitions;

an input module that prompts for a dynamic reorganization threshold in response to the evaluation, the dynamic reorganization threshold defining a subset of database partitions, the subset of database partitions dynamically recalculated in response to changing the dynamic reorganization threshold, the dynamic reorganization threshold changed in response to adjusting the graphically represented reorganization threshold slider bar by way of the graphical user interface; and

a reorganization module that reorganizes a subset of database partitions in the ranked set of database partitions that satisfy the dynamic reorganization threshold; and

a communication bus, the communication bus operatively coupling the processor, the one or more storage devices, the one or more I/O devices, and memory.

11. The system of claim 10, wherein the reorganization utility further comprises an estimation module configured to estimate a quantity of computing resources required to reorganize the subset of database partitions in the ranked set of database partitions that satisfy the dynamic reorganization threshold and estimate a performance improvement that will result from reorganizing the subset of database partitions in the ranked set of database partitions that satisfy the dynamic reorganization threshold.

12. The system of claim 10, wherein the reorganization utility further comprises a recommendation module that recommends a dynamic reorganization threshold.

13. The system of claim 10, wherein the dynamic reorganization threshold has a type selected from the group consisting of a number of ranked database partitions and a percentage of ranked database partitions.

14. A computer program product comprising a computer readable storage medium having computer readable program code embodied therein to reorganize a set of Data Entry Data Base (DEDB) Units of Work (UOWs), the computer readable program code configured to:

determine a fragmentation indicator for each DEDB UOW within a set of DEDB UOWs and to evaluate a fragmentation function for each DEDB UOW within the set of DEDB UOWs;

rank the DEDB UOWs according to a rank criteria comprising at least the fragmentation indicator;

define a subset of DEDB UOWs, the DEDB UOWs satisfying a dynamic reorganization threshold;

recalculate the subset of DEDB UOWs in response to changing the dynamic reorganization threshold, the dynamic reorganization threshold changed in response to adjusting a graphically represented reorganization threshold slider bar by way of a user interface;

display by way of a graphical user interface one or more indicators representing DEDB UOWs that are members of the recalculated subset of DEDB UOWs in response to adjusting the graphically represented reorganization threshold slider bar by way of the graphical user interface, and displaying updated resource requirements based on the recalculated subset of DEDB UOWs; and

reorganize the subset of the DEDB UOWs in the ranked set of DEDB UOWs wherein the subset is defined at least in part by a rank order for the ranked set of DEDB UOWs.

15. The computer program product of claim 14, further comprising estimating a quantity of computing resources required to reorganize the subset of DEDB UOWs.

16. The computer program product of claim 14, further comprising estimating a performance improvement that will result from reorganizing the subset of DEDB UOWs.

17. The computer program product of claim 14, wherein determining a fragmentation indicator further comprises evaluating a fragmentation function for each DEDB UOW within the set of DEDB UOWs.

18. The computer program product of claim 17, wherein the fragmentation function comprises determining whether a DEDB UOW comprises at least a predetermined amount of free space and determining whether the DEDB UOW comprises at least a predetermined number of overflow control intervals.

19. The computer program product of claim 14, wherein reorganizing database partitions further comprises reorganizing the subset of the database partitions in rank order by reorganizing the highest ranked database partition first and the lowest ranked database partition last.

20. The computer program product of claim 14, further comprising changing the subset of the DEDB UOWs by adding or removing a DEDB UOW from the subset in response to an input.