Database reorganization technique
View Patent ↗A database reorganization technique uses multiple-coordinated read, sort and write tasks to substantially reduce the overall time to reorganize a database object. Coordination between the different functional tasks (e.g., read, sort and write tasks) is facilitated through the logical partitioning of the key values associated with the object to be reorganized. Object key values are determined by interrogating the object's associated database prior to initiating reorganization processing.
1. A computer programmed to perform a database reorganization method, comprising:
determining a key range for a database object in a database, said key range comprising a plurality of consecutive non-overlapping sets of key values, each set of key values referred to as a partition;
identifying a plurality of non-overlapping portions for the database object;
initiating a plurality of read tasks, each read task associated with a single database object portion;
initiating a plurality of sort tasks, each sort task associated with at least one partition and more than one read task,
wherein each read task acquires data having a key value from the associated database object portion and provides the acquired data to that sort task associated with the partition including the acquired key value; and
writing the data sorted by the plurality of sort tasks to the database object in the database.
2. The computer of claim 1 , wherein the act of determining a key range performed by the computer comprises reading a catalog associated with the database to determine a range of key values associated the database object.
3. The computer of claim 2 , wherein the method performed by the computer further comprises reading an index object associated with the database to determine a range of key values associated with the database object.
4. The computer of claim 1 , where in the method performed by the computer the key range comprises a range of values for a primary key of the database object.
5. The computer of claim 1 , wherein the method performed by the computer the entire determined key range is spanned by the plurality of partitions.
6. The computer of claim 1 , wherein the method performed by the computer each of the plurality of sort tasks are associated with a contiguous range of non-overlapping key values.
7. The computer of claim 6 , wherein the method performed by the computer the entire determined key range is spanned by the plurality of sort tasks.
8. The computer of claim 1 , wherein the method performed by the computer the number of sort tasks equal the number of identified partitions.
9. The computer of claim 1 , wherein the method performed by the computer the number of sort tasks is less than the number of identified partitions.
10. The computer of claim 1 , wherein the act of writing performed by the computer is performed by a plurality of write tasks.
11. The computer of claim 10 , wherein the method performed by the computer the number of write tasks is equal to the number of sort tasks.
12. The computer of claim 10 , wherein the method performed by the computer the number of write tasks is less than the number of sort tasks.
13. The computer of claim 1 , wherein the act of writing performed by the computer comprises updating a catalog object associated with the database.
14. The computer of claim 13 , wherein the act of writing performed by the computer further comprises updating an index object associated with the database.
15. The computer of claim 1 , wherein the method performed by the computer the database comprises a DB2 database.
16. The computer of claim 1 , wherein the method performed by the computer the database object comprises a data object of the database.
17. The computer of claim 1 , wherein the method performed by the computer the database object comprises an index object of the database.
18. The computer of claim 1 , wherein the method performed by the computer the database object comprises a data object and an index object of the database.
19. A program storage device, readable by a programmable control device, comprising instructions stored on the program storage device for causing the programmable control device to perform the method of claim 1 .
20. The program storage device of claim 19 , wherein the instructions to initiate a plurality of sort tasks comprise instructions to associate each of said plurality of sort tasks with a contiguous range of non-overlapping key values.
21. The program storage device of claim 19 , wherein the instructions to write comprise instructions to initiate a plurality of write tasks.
22. The program storage device of claim 21 , wherein the instructions to initiate a plurality of write tasks comprise instructions to initiate a number of write tasks equal to the number of sort tasks.
23. The program storage device of claim 19 , wherein the instructions to write comprise instructions to update a catalog object associated with the database.
24. The program storage device of claim 23 , wherein the instructions to write further comprise instructions to update an index object associated with the database.
25. The program storage device of claim 19 , wherein the instructions to read, sort and write the database object comprise instructions to read, sort and write a data object of the database.
26. The program storage device of claim 19 , wherein the instructions to read, sort and write the database object comprise instructions to read, sort and write an index object of the database.
27. The program storage device of claim 19 , wherein the instructions to read, sort and write the database object comprise instructions to read, sort and write a data object and an index object of the database.
28. A database reorganization device, comprising:
memory for storing instructions and a database object in a database;
processor, communicatively coupled to the memory for executing the instructions of claim 19 .
29. The database reorganization device of claim 28 , wherein the memory comprises at least one direct access storage device.
30. The database reorganization device of claim 28 , wherein the memory comprises volatile memory.
31. The database reorganization device of claim 28 , wherein the processor comprises at least one central processing unit.
32. The database reorganization device of claim 28 , wherein the instructions to initiate a plurality of sort tasks comprise instructions to associate each of said plurality of sort tasks with a contiguous range of non-overlapping key values.
33. The database reorganization device of claim 28 , wherein the instructions to write comprise instructions to initiate a plurality of write tasks.
34. The database reorganization device of claim 21 , wherein the instructions to initiate a plurality of write tasks comprise instructions to initiate a number of write tasks equal to the number of sort tasks.
35. The database reorganization device of claim 28 , wherein the instructions to write comprise instructions to update a catalog object associated with the database.
36. The database reorganization device of claim 35 , wherein the instructions to write further comprise instructions to update an index object associated with the database.
37. The database reorganization device of claim 28 , wherein the instructions to read, sort and write the database object comprise instructions to read, sort and write a data object of the database.
38. The database reorganization device of claim 28 , wherein the instructions to read, sort and write the database object comprise instructions to read, sort and write an index object of the database.
39. The database reorganization device of claim 28 , wherein the instructions to read, sort and write the database object comprise instructions to read, sort and write a data object and an index object of the database.