Method and apparatus for concurrent DBMS table operations

• US 6,477,535 B1
• Filed: 11/25/1998
• Issued: 11/05/2002
• Est. Priority Date: 11/25/1998
• Status: Expired due to Term

• Current Assignee: CA, Inc. (d/b/a CA Technologies) (Broadcom, Inc.)
• Original Assignee: Computer Associates Think Inc. (Broadcom, Inc.)
• Inventors: Mirzadeh, Rosita
• Primary Examiner(s): Metjahic, Safet
• Assistant Examiner(s): LE, UYEN T
• Application Number:
  US09/200,254
• Time in Patent Office: 1,441 Days
• Field of Search: 707/200, 707/203-206, 707/101, 707/102, 707/100, 707/8, 707/10, 700/1, 700/2, 700/4, 709/200, 709/201
• US Class Current: 1/1
• CPC Class Codes: G06F 16/284 Relational databases
• CPC Class Codes: Y10S 707/99938 Concurrency, e.g. lock management in shared database
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation

Abstract

Database tables are unloaded by launching a number of threads corresponding to a number of export directories located on separate storage devices that maintain the database tables. Each thread is assigned a database table to unload, and data unloaded from each database table is stored in a corresponding export directory. The data is unloaded from each database table by reading data storage blocks from each table and storing the data logs in the export directory. Each thread is handled by a separate process in a Symmetrical Multi-Processing (SMP) environment. The process is repeated until each database table has been unloaded. The data is then loaded into database tables by first creating a number of temporary tables corresponding to the number of threads, reading a set of data stored in the export directory and storing the data read in a corresponding of the temporary tables by launching a load process for each temporary table. If at any time either the load or unload process is faulted or becomes inoperative, the processes are stopped and an internal TS reorganization concurrent is performed on each table that has not been unloaded and loaded successfully.

Claims

1. A method for reorganizing database tables in a symmetrical multi-processing environment having a plurality of CPU'"'"'s, comprising the steps of:

launching a number of threads to process said database tables;

assigning each of said database tables to a different corresponding thread of said threads;

unloading each respective database table by a process of said corresponding thread, wherein said threads operate on different CPU'"'"'s in parallel; and

loading said database tables.

2. The method according to claim 1, wherein said step of launching includes the steps of:

retrieving a number of threads input by a user;

identifying a number of export directories located on separate storage devices that maintain said database tables;

limiting to the number of export directories identified; and

utilizing as said number of threads.

3. The method according to claim 2, wherein said storage devices are disk drives.

4. The method according to claim 1, wherein said step of unloading comprises the steps of:

reading blocks of data from a respective database table, and storing the data blocks read in an export directory.

5. The method according to claim 4, wherein said step of unloading further comprises the steps of:

determining success of completion said steps of reading and storing; and

(1) stopping each of said threads, if said determining success step indicates non-completion,

(2) performing an internal TS Reorganization on the table.

6. The method according to claim 1, further comprising the step of:

repeating said steps of assigning and unloading until each of the database tables is unloaded.

7. The method according to claim 1, further comprising the steps of:

exporting other objects related to each of said tables; and

dropping said tables.

8. The method according to claim 1, wherein said step of loading comprises the steps of:

determining threads for loading data into database tables;

creating temporary tables, each temporary table corresponding to a set of data stored in an export directory;

launching a loading process in conjunction with each thread for loading each temporary table;

loading each temporary table with the data stored in the corresponding export directory via the corresponding loading process.

9. The method according to claim 8, further comprising the steps of:

determining success of the loading step for a respective database table; and

if said step of loading was unsuccessful, performing the steps of;

(1) stopping each of said threads, if said determining success step indicates non-completion,

(2) performing an internal TS Reorganization on the table.

10. The method according to claim 8, further comprising the steps of creating, launching and loading until each table is loaded.

11. The method according to claim 8, further comprising the steps of:

recognizing a fault in said step of loading, and performing the steps of;

(1) recognizing unsuccessfully loaded tables; and

(2) performing an internal TS Reorganization on the unsuccessfully loaded tables.

12. The method according to claim 8, wherein said step of determining includes the steps of:

retrieving a number of threads input by a user;

determining a number of export directories; and

establishing a number of threads equal to the lesser of the number of threads retrieved and the number of export directories.

13. A computer readable medium having computer instructions stored thereon that, when loaded into a computer exhibiting a symmetrical multi-processing environment having a plurality of CPU'"'"'s, cause the computer to reorganize database tables, by performing the steps of:

launching a number of threads to process said database tables;

assigning each of said database tables to a different corresponding thread of said threads;

unloading each respective database table by a process of the corresponding thread, wherein said threads operate on different CPU'"'"'s in parallel; and

loading said database tables.

14. The computer readable medium according to claim 13, wherein said step of launching comprises the steps of:

identifying a number of export directories located on separate storage devices that maintain said database tables; and

utilizing as said number of threads.

15. The computer readable medium according to claim 14, wherein said storage devices are disk drives.

16. The computer readable medium according to claim 13, wherein said step of unloading comprises the steps of:

reading blocks of data from a respective database table, and storing the data blocks read in an export directory.

17. The computer readable medium according to claim 16, wherein said step of unloading further comprises the steps of:

determining success of completion said steps of reading and storing; and

(1) stopping each of said threads, if said determining success step indicates non-completion,

(2) performing an internal TS Reorganization on the table.

18. The computer readable medium according to claim 13, wherein said step of unloading further comprises the step of:

repeating said steps of assigning and unloading until each of the database tables is unloaded.

19. The computer readable medium according to claim 13, wherein said step of unloading further comprises the steps of:

exporting other objects related to each of said tables; and

dropping said tables.

20. The computer readable medium according to claim 13, wherein said step of loading comprises:

determining threads for loading data into database tables;

creating temporary tables, each temporary table corresponding to a set of data stored in an export directory;

launching a loading process for loading each temporary table;

loading each temporary table with the data stored in the corresponding export directory via the corresponding loading process.

21. The computer readable medium according to claim 20, wherein said instructions stored thereon, when loaded into a computer, further cause the computer to perform the steps of:

determining success of the loading step for a respective database table; and

if said step of loading was unsuccessful, performing the steps of;

(1) stopping each of said threads, if said determining success step indicates non-completion,

(2) performing an internal TS Reorganization on the table.

22. The computer readable medium according to claim 20, wherein said instructions stored thereon, when loaded into a computer, further cause the computer to perform the step of repeating said steps of creating, launching and loading until each table is loaded.

23. The computer readable medium according to claim 20, wherein said instructions stored thereon, when loaded into a computer, further cause the computer to perform the step of:

recognizing a fault in said step of loading, and performing the steps of;

(1) recognizing unsuccessfully loaded tables; and

(2) performing an internal TS Reorganization on the unsuccessfully loaded tables.

24. The computer readable medium according to claim 20, wherein said step of determining includes the steps of:

retrieving a number of threads input by a user;

determining a number of export directories; and

establishing a number of threads equal to the lesser of the number of threads retrieved and the number of export directories.

25. An apparatus for reorganizing database tables maintained in a system exhibiting a symmetrical multi-processing environment having a plurality of CPU'"'"'s, comprising:

means for launching a number of threads to process said database tables;

means for assigning each of said database tables to a different corresponding thread of said threads;

means for unloading each respective database table by a process of the corresponding thread, wherein said threads operate on different CPU'"'"'s in parallel; and

means for loading said database tables.

26. The apparatus according to claim 25, wherein said means for launching further includes means for retrieving a number of threads to launch from a user.

27. The apparatus according to claim 25, wherein said means for launching includes means for limiting the number of threads launched to a number of export directories located on separate storage devices of said system.

28. The apparatus according to claim 25, wherein said means for unloading comprises:

means for reading blocks of data from a respective database table, and storing the data blocks read in an export directory associated with the corresponding thread.

29. The apparatus according to claim 25, further comprising means for repeatedly assigning respective database tables to corresponding threads and unloading each respective table until all of said database tables have been unloaded.

30. An apparatus for reorganizing database tables in a symmetrical multi-processing environment having a plurality of CPU'"'"'s, comprising:

a launching device configured to initiate a number of threads to process said database tables;

an assignor configured to assign each of said database tables to a different corresponding thread of said threads launched;

an unloader installed on each corresponding thread, each respective unloader configured to unload each of said database tables by a process of said corresponding thread of said unloader, wherein said threads operate on different CPU'"'"'s in parallel; and

a loader configured to load said database tables.

31. The apparatus according to claim 30, wherein said launching device includes:

a user interface configured to retrieve a number of threads to launch from a user; and

a limit device configured to limit the number of threads to launch to a number of export directories located on separate storage devices of said system.

32. The apparatus according to claim 30, wherein each respective unloader comprises:

a read device configured to read blocks of data maintained within tables assigned to a same thread as the respective unloader is installed; and

a write device configured to store the data blocks read into an export directory corresponding to the same thread as the respective unloader is installed.

33. The apparatus according to claim 30, wherein said assignor assigns a database table to each of said threads, and, upon completion of one of said threads, assigns another of said database tables to one of said initiated threads until each database table has been assigned.

34. The apparatus according to claim 32, wherein said loader is loaded and executed on each of said threads after each database table has been unloaded, each loader configured to, read data blocks stored in an export directory corresponding to the thread executing the loader, and

save the datablocks in fresh tablespace.

35. A method for reorganizing database tables residing on different storage devices in a symmetrical multi-processing environment having a plurality of CPU'"'"'s, at least one of said tables residing on a plurality of storage devices, comprising the steps of:

launching a number of threads to process said database tables;

assigning each respective portion of said database tables residing on a different storage device to a different corresponding thread of said threads;

unloading each respective portion of said database tables by a process of said corresponding thread, wherein said threads operate on different CPU'"'"'s in parallel; and

loading said database tables.

36. A computer readable medium having computer instructions stored thereon that, when loaded into a computer exhibiting a symmetrical multi-processing environment having a plurality of CPU'"'"'s, cause the computer to reorganize database tables residing on different storage devices, at least one of said tables residing on a plurality of storage devices, by performing the steps of:

launching a number of threads to process said database tables;

assigning each respective portion of said database tables residing on a different storage device to a different corresponding thread of said threads;

unloading each respective portion of said database tables by a process of the corresponding thread, wherein said threads operate on different CPU'"'"'s in parallel; and

loading said database tables.

37. An apparatus for reorganizing database tables residing on different storage devices maintained in a system exhibiting a symmetrical multi-processing environment having a plurality of CPU'"'"'s, at least one of said tables residing on a plurality of storage devices, comprising:

means for launching a number of threads to process said database tables;

means for assigning each respective portion of said database tables residing on a different storage device to a different corresponding thread of said threads;

means for unloading each respective portion of said database tables by a process of the corresponding thread, wherein said threads operate on different CPU'"'"'s in parallel; and

means for loading said database tables.

38. An apparatus for reorganizing database tables residing on different storage devices in a symmetrical multi-processing environment having a plurality of CPU'"'"'s, at least one of said tables residing on a plurality of storage devices, comprising:

a launching device configured to initiate a number of threads to process said database tables;

an assignor configured to assign each respective portion of said database tables residing on a different storage device to a different corresponding thread of said threads launched;

an unloader installed on each corresponding thread, each respective unloader configured to unload each respective portion of said database tables by a process of said corresponding thread of the unloader, wherein said threads operate on different CPU'"'"'s in parallel; and

a loader configured to load said database tables.

39. A method for reorganizing database tables in a symmetrical multi-processing environment having a plurality of CPU'"'"'s, comprising:

launching a first set of threads to unload said database tables;

assigning each of said database tables to a different corresponding thread of said first set of threads;

unloading each of said database tables to a separate export directory by a process of said corresponding thread, said first set of threads operate in parallel on a first set of CPU'"'"'s, said database tables assigned to said export directories using a best fit method, said assignment performed by a reorganization utility running on a CPU separate from said first set of CPU'"'"'s;

creating temporary database tables;

launching a second set of threads to load said temporary database tables; and

loading each of said temporary database tables with data from at least one of said export directories, each of said temporary database tables loaded by a separate thread of said second set of threads, said second set of threads operate in parallel on a second set of CPU'"'"'s, said data loaded using a best fit method.

40. A computer readable medium having computer instructions stored thereon that, when loaded into a computer exhibiting a symmetrical multi-processing environment having a plurality of CPU'"'"'s, cause the computer to reorganize database tables by performing the steps of:

launching a first set of threads to unload said database tables;

assigning each of said database tables to a different corresponding thread of said first set of threads;

unloading each of said database tables to a separate export directory by a process of said corresponding thread, said first set of threads operate in parallel on a first set of CPU'"'"'s, said database tables assigned to said export directories using a best fit method, said assignment performed by a reorganization utility running on a CPU separate from said first set of CPU'"'"'s;

creating temporary database tables;

launching a second set of threads to load said temporary database tables; and

loading each of said temporary database tables with data from at least one of said export directories, each of said temporary database tables loaded by a separate thread of said second set of threads, said second set of threads operate in parallel on a second set of CPU'"'"'s, said data loaded using a best fit method.

41. An apparatus for reorganizing database tables maintained in a system exhibiting a symmetrical multi-processing environment having a plurality of CPU'"'"'s, comprising:

means for launching a first set of threads to unload said database tables;

means for assigning each of said database tables to a different corresponding thread of said first set of threads;

means for unloading each of said database tables to a separate export directory by a process of said corresponding thread, said first set of threads operate in parallel on a first set of CPU'"'"'s, said database tables assigned to said export directories using a best fit method, said assignment performed by a reorganization utility running on a CPU separate from said first set of CPU'"'"'s;

means for creating temporary database tables;

means for launching a second set of threads to load said temporary database tables; and

means for loading each of said temporary database tables with data from at least one of said export directories, each of said temporary database tables loaded by a separate thread of said second set of threads, said second set of threads operate in parallel on a second set of CPU'"'"'s, said data loaded using a best fit method.

42. An apparatus for reorganizing database tables in a symmetrical multi-processing environment having a plurality of CPU'"'"'s, comprising:

a first launching device configured to initiate a first set of threads to unload said database tables;

an assignor configured to assign each of said database tables to a different corresponding thread of said first set of threads launched;

an unloader installed on each thread of said first set of threads, each unloader configured to unload at least one of said database tables to a separate export directory by a process of said corresponding thread of said unloader, said first set of threads operate in parallel on a first set of CPU'"'"'s, said database tables assigned to said export directories using a best fit method, said assignment performed by a reorganization utility running on a CPU separate from said first set of CPU'"'"'s;

a table allocation device configured to create temporary database tables;

a second launching device configured to initiate a second set of threads to load said temporary database tables; and

a loader installed on each thread of said second set of threads, each loader configured to load at least one of said temporary database tables with data from at least one of said export directories, each of said temporary database tables loaded by a process of said corresponding thread of said loader, said second set of threads operate in parallel on a second set of CPU'"'"'s, said loaders using a best fit method.

43. A machine-implemented method for reorganizing database tables in a symmetrical multi-processing environment having a plurality of CPU'"'"'s, comprising:

launching a plurality of threads;

assigning each of said threads to a database table;

unloading said tables concurrently, wherein data of each table is unloaded to an export directory by a process of said thread assigned to said table;

creating new tables; and

loading said new tables concurrently, wherein data of said export directories is loaded to said new tables by a process of said thread assigned to said table.

44. The method of claim 43, wherein indexes for said new tables are created concurrently with said method.

45. The method of claim 43, wherein said threads operate concurrently on a first set of said CPU'"'"'s, said export directories selected by a reorganization utility running on a CPU separate from said first set of CPU'"'"'s.

46. The method of claim 43,wherein said unloading step further comprises the steps of:

verifying that said unloading step was successful; and

unloading said data by an alternative unload process if said unloading step was unsuccessful.

47. The method of claim 43, wherein said export directories reside on separate physical disks.

48. The method of claim 43, wherein a maximum number of said threads is selected by a user.

49. The method of claim 43, wherein said unloading to said export directory is performed using a best fit method.

50. The method of claim 43, wherein said loading to said new tables is performed using a best fit method.

51. A machine-implemented method for reorganizing database tables in a symmetrical multi-processing environment having a plurality of CPU'"'"'s, comprising:

launching a plurality of threads;

assigning each of said threads to a database table;

unloading said tables concurrently, wherein data of each table is unloaded to an export directory by a process of said thread assigned to said table; and

creating new tables.