Method and apparatus for synchronizing not-logged application temporary tables in a multi-node relational database management system

• US 6,735,605 B2
• Filed: 02/28/2001
• Issued: 05/11/2004
• Est. Priority Date: 06/02/2000
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Lee, Jesse, Chan, Yuk Kuen, Wong, Catharina K., See, Yeung-Kuen, Pereyra, Hebert W., Bird, Paul M., Snowbell, Michael J.
• Primary Examiner(s): Homere, Jean R.
• Assistant Examiner(s): Ali, Mohammad
• Application Number:
  US09/796,843
• Publication Number:
  US 20020078058A1
• Time in Patent Office: 1,168 Days
• Field of Search: 707/1-10, 707/100-141, 715/509, 709/223-229
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2365 Ensuring data consistency and integrity
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface
• CPC Class Codes: Y10S 707/99956 File allocation

Abstract

A multi-node relational database management system is provided that can be embodied in software for managing a database having declared temporary tables associated with respective software application programs. The system comprises a plurality of nodes. The nodes include a catalog node for maintaining a list of identifiers for declared temporary tables associated with any application; a coordinator node for an application; at least one subordinate node for the application, the subordinate node having at least one partition of the declared temporary tables of the application; and a declared temporary table manager for causing the coordinator node to maintain for the application a list of declared temporary tables.

Claims

1. A multi-node relational database management system for managing a database comprising:

a catalog node for reserving identifiers for declared temporary tables associated with an application, wherein the catalog node maintains a list of said reserved identifiers;

a coordinator node for said application, wherein said coordinator node receives said reserved identifiers from said catalog node, and wherein said coordinator node maintains a list of said declared temporary tables;

at least one subordinate node for said application, said subordinate node having at least one partition of said declared temporary tables; and

a declared temporary table manager for causing said coordinator node to maintain said list of said declared temporary tables.

2. The multi-node relational database management system of claim 1 wherein said coordinator node maintains for said application a list of said declared temporary tables being dropped.

3. The multi-node relational database management system of claim 1 wherein said coordinator node maintains for said application a list of action entries indicating identification of tables on which actions are to be performed, and actions to be performed.

4. The multi-node relational database management system of claim 1 wherein said coordinator node maintains for said application a transaction node list indicating identification of those nodes involved in a current transaction.

5. The multi-node relational database management system of claim 1 wherein said coordinator node maintains for said applications an application node list for indicating identification of all nodes that have participated in execution of said application.

6. The multi-node relational database management system of claim 1 wherein the said declared temporary table manager causes said subordinate node for said application to maintain a list of said declared temporary tables that are partitioned at said subordinate node.

7. The multi-node relational database management system of claim 1 wherein a node failure list is provided for identifying nodes that have failed.

8. The multi-node relational database management system of claim 1 wherein

the declared temporary table manager also causes:

(a) said coordinator node to maintain for said application;

a list of said declared temporary tables for tracking said temporary tables;

a list of said declared temporary tables being dropped containing information to be sent to said catalog node to inform it of declared temporary tables being dropped so said catalog node can release the identification of said declared temporary tables being dropped or add said identification to its deferred drop list to release said identification when execution of said application ends;

a list of action entries indicating identification of tables on which actions are to be performed, and any actions to be performed to be used to inform subordinate nodes to maintain their partitions of said declared temporary tables in synchronization;

a transaction node list indicating identification of those nodes involved in a current transaction to inform the subordinate nodes involved in said current transaction to appropriately end a transaction as required for rollback or commit; and

;

an application node list for indicating identification of all nodes that have participated in execution of the application, to identify all subordinate nodes participating in the application so that they can perform node specific application termination processing including cleanup; and

(b) said subordinate node for said application to maintain a list of said declared temporary tables that are partitioned at said subordinate node for synchronization.

9. The multi-node relational database management system of claim 1 wherein said coordinator node for an application is located at said catalog node;

and wherein said at least one subordinate node for an application can be at any node except said application'"'"'s coordinator node.

10. The multi-node relational database management system of claim 8 wherein said coordinator node for an application is located at said catalog node;

and wherein said at least one subordinate node for an application can be at any node except said application'"'"'s coordinator node.

11. A computer readable storage medium comprising program instructions for establishing a multi-node relational database management system for managing a database comprising, the program instructions for:

establishing a catalog node for reserving identifiers for declared temporary tables associated with an application, wherein the catalog node maintains a list of said reserved identifiers;

establishing a coordinator node for said application, wherein said coordinator node receives said reserved identifiers from said catalog node, and wherein said coordinator node maintains a list of said declared temporary tables;

establishing at least one subordinate node for said application, said subordinate node having at least one partition of said declared temporary tables; and

establishing a declared temporary table manager for causing said coordinator node to maintain said list of said declared temporary tables.

12. The computer readable medium of claim 11 wherein said coordinator node maintains for said application a list of said declared temporary tables being dropped.

13. The computer readable medium of claim 11 wherein said coordinator node maintains for said application a list of action entries indicating identification of tables on which actions are to be performed, and actions to be performed.

14. The computer readable medium of claim 11 wherein said coordinator node maintains for said application a transaction node list indicating identification of those nodes involved in a current transaction.

15. The computer readable medium of claim 11 wherein said coordinator node maintains for said applications an application node list for indicating identification of all nodes that have participated in execution of said application.

16. The computer readable medium of claim 11 wherein the said declared temporary manager causes said subordinate node for said application to maintain a list of said declared temporary tables that are partitioned at said subordinate node.

17. The computer readable medium of claim 11 wherein a node failure list is provided for identifying nodes that have failed.

18. The computer readable medium of claim 11 wherein

the declared temporary table manager also causes

(a) said coordinator node to maintain for said application:

a list of said declared temporary tables for tracking said temporary tables;

a list of said declared temporary tables being dropped containing information to be sent to said catalog node to inform it of declared temporary tables being dropped so said catalog node can release the identification of said declared temporary tables being dropped or add said identification to its deferred drop list to release said identification when execution of said application ends;

a list of action entries indicating identification of tables on which actions are to be performed, and any actions to be performed to be used to inform subordinate nodes to maintain their partitions of said declared temporary tables in synchronization;

a transaction node list indicating identification of those nodes involved in a current transaction to inform the subordinate nodes involved in said current transaction to appropriately end a transaction as required for rollback or commit; and

,

an application node list for indicating identification of all nodes that have participated in execution of the application, to identify all subordinate nodes participating in the application so that they can perform node specific application termination processing including cleanup; and

(b) said subordinate node for said application to maintain a list of said declared temporary tables that are partitioned at said subordinate node for synchronization.

19. The computer readable medium of claim 11 wherein said coordinator node for an application is located at said catalog node;

and wherein said at least one subordinate node for an application can be at any node except said application'"'"'s coordinator node.

20. A computer readable storage medium comprising program instructions to establish a multi-node relational database management system for managing a database, the program instructions for:

establishing a node failure list identifying nodes that have failed;

establishing a catalog node for reserving identifiers for declared temporary tables associated with an application, wherein the catalog node maintains a list of said reserved identifiers;

establishing a coordinator node for an said application, wherein said coordinator node receives said reserved identifiers from said catalog node, and wherein said coordinator node maintains a list of said declared temporary tables;

establishing at least one subordinate node for said application, said subordinate node having at least one partition of said declared temporary tables;

establishing a declared temporary table manager for causing;

(a) said coordinator node to maintain for said application;

a list of said declared temporary tables;

a list of said declared temporary tables being dropped;

a list of action entries indicating identification of tables on which actions are to be performed, and actions to be performed;

a transaction node list indicating identification of those nodes involved in a current transaction; and

an application node list for indicating identification of all nodes that have participated in execution of said application; and

;

(b) said subordinate node for said application to maintain a list of said declared temporary tables that are partitioned at said subordinate node.

21. The computer readable storage medium of claim 20 further comprising program instructions for causing:

(a) said coordinator node to maintain for said application;

a list of said declared temporary tables for tracking said temporary tables;

a list of said declared temporary tables being dropped containing information to be sent to said catalog node to inform it of declared temporary tables being dropped so said catalog node can release the identification of said declared temporary tables being dropped or add said identification to its deferred drop list to release said identification when execution of said application ends;

a list of action entries indicating identification of tables on which actions are to be performed, and any actions to be performed to be used to inform subordinate nodes to maintain their partitions of said declared temporary tables in synchronization;

a transaction node list indicating identification of those nodes involved in a current transaction to inform the subordinate nodes involved in said current transaction to appropriately end a transaction as required for rollback or commit; and

,

an application node list for indicating identification of all nodes that have participated in execution of the application, to identify all subordinate nodes participating in the application so that they can perform node specific application termination processing including cleanup.

22. In a multi-node relational database management system a method for managing a database, the method comprising the steps of:

(a) using a catalog node for reserving identifiers for declared temporary tables associated with an application, wherein the catalog node maintains a list of said reserved identifiers;

(b) associating a coordinator node for said application, wherein said coordinator node receives said reserved identifiers from said catalog node, and wherein said coordinator node maintains a list of said declared temporary tables;

(c) using at least one subordinate node for said application, said subordinate node having at least one partition of said declared temporary tables; and

(d) using a declared temporary table manager for causing said coordinator node to maintain said list of said declared temporary tables.

23. The method of claim 22 wherein the declared temporary table manager is used for managing said declared temporary tables for causing:

a transaction node list indicating identification of those nodes involved in a current transaction;

an application node list for indicating identification of all nodes that have participated in execution of said application; and

wherein said subordinate node for said application is used to maintain a list of said declared temporary tables that are partitioned at said subordinate node.

24. The method of claim 22 wherein said declared temporary table manager is used for managing said declared temporary tables for causing:

(a) said coordinator node to maintain for said application;

a list of said declared temporary tables for tracking said temporary tables;

a list of said declared temporary tables being dropped containing information to be sent to said catalog node to inform it of declared temporary tables being dropped so said catalog node can release the identification of said declared temporary tables being dropped or add said identification to its deferred drop list to release said identification when execution of said application ends;

a list of action entries indicating identification of tables on which actions are to be performed, and any actions to be performed to be used to inform subordinate nodes to maintain their partitions of said declared temporary tables in synchronization;

a transaction node list indicating identification of those nodes involved in a current transaction to inform the subordinate nodes involved in said current transaction to appropriately end a transaction as required for rollback or commit; and

an application node list for indicating identification of all nodes that have participated in execution of the application, to identify all subordinate nodes participating in the application so that they can perform node specific application termination processing including cleanup; and

(b) said subordinate node for said application to maintain a list of said declared temporary tables that are partitioned at said subordinate node for synchronization.

25. A multi-node relational database management system for managing a database, the system comprising:

a catalog node for reserving identifiers for declared temporary tables associated with an application, wherein the catalog node maintains a list of said reserved identifiers;

a coordinator node for said application, wherein said coordinator node receives said reserved identifiers from said catalog node, and wherein said coordinator node maintains a list of said declared temporary tables;

at least one partition of said declared temporary tables on said coordinator node; and

a declared temporary table manager for causing said coordinator node to maintain said list of said declared temporary tables.

26. A computer readable storage medium comprising program instructions to establish a multi-node relational database management system for managing a database having declared temporary tables associated with respective applications, the program instructions for:

establishing a catalog node for maintaining a list of identifiers for declared temporary tables associated with any application;

establishing a coordinator node for an application;

establishing at least one partition of said declared temporary tables of said application on said coordinator node; and

program instructions for establishing a declared temporary table manager for causing said coordinator node to maintain said list of said declared temporary tables.

27. A method for establishing a multi-node relational database management system for managing a database having declared temporary tables associated with respective applications, the method comprising the steps of:

(a) establishing a catalog node for maintaining a list of identifiers for declared temporary tables associated with any application;

(b) establishing a coordinator node for an application;

(c) establishing at least one partition of said declared temporary tables of said application on said coordinator node; and

(d) establishing a declared temporary table manager for causing said coordinator node to maintain said list of said declared temporary tables.

28. A multi-node relational database management system for managing a database, the system comprising:

a catalog node for maintaining a list of identifiers for declared temporary tables associated with any application;

a coordinator node for an application;

at least one subordinate node for said application, said subordinate node having at least one partition of said declared temporary tables of said application; and

a declared temporary table manager for causing said coordinator node to maintain for said application a list of said declared temporary tables, wherein the declared temporary table manager also causes;

(a) said coordinator node to maintain for said application;

a list of said declared temporary tables for tracking said temporary tables;

a list of said declared temporary tables being dropped containing information to be sent to said catalog node to inform it of declared temporary tables being dropped so said catalog node can release the identification of said declared temporary tables being dropped or add said identification to its deferred drop list to release said identification when execution of said application ends;

a list of action entries indicating identification of tables on which actions are to be performed, and any actions to be performed to be used to inform subordinate nodes to maintain their partitions of said declared temporary tables in synchronization;

a transaction node list indicating identification of those nodes involved in a current transaction to inform the subordinate nodes involved in said current transaction to appropriately end a transaction as required for rollback or commit; and

an application node list for indicating identification of all nodes that have participated in execution of the application, to identify all subordinate nodes participating in the application so that they can perform node specific application termination processing including cleanup; and

(b) said subordinate node for said application to maintain a list of said declared temporary tables that are partitioned at said subordinate node for synchronization.

29. A multi-node relational database management system for managing a database comprising:

a catalog node for maintaining a list of identifiers for declared temporary tables associated with any application;

a coordinator node for an application;

at least one subordinate node for said application, said subordinate node having at least one partition of said declared temporary tables of said application, wherein said coordinator node for an application is located at said catalog node; and

wherein said at least one subordinate node for an application can be at any node except said application'"'"'s coordinator node; and

a declared temporary table manager for causing said coordinator node to maintain for said application a list of said declared temporary tables, wherein the declared temporary table manager also causes;

(a) said coordinator node to maintain for said application;

a list of said declared temporary tables for tracking said temporary tables;

a list of said declared temporary tables being dropped containing information to be sent to said catalog node to inform it of declared temporary tables being dropped so said catalog node can release the identification of said declared temporary tables being dropped or add said identification to its deferred drop list to release said identification when execution of said application ends;

a list of action entries indicating identification of tables on which actions are to be performed, and any actions to be performed to be used to inform subordinate nodes to maintain their partitions of said declared temporary tables in synchronization;

a transaction node list indicating identification of those nodes involved in a current transaction to inform the subordinate nodes involved in said current transaction to appropriately end a transaction as required for rollback or commit; and

an application node list for indicating identification of all nodes that have participated in execution of the application, to identify all subordinate nodes participating in the application so that they can perform node specific application termination processing including cleanup; and

(b) said subordinate node for said application to maintain a list of said declared temporary tables that are partitioned at said subordinate node for synchronization.

30. A computer readable storage medium containing program instructions to establish a multi-node relational database management system for managing a database comprising:

establishing a catalog node for maintaining a list of identifiers for declared temporary tables associated with any application;

establishing a coordinator node for an application;

program instructions for establishing at least one subordinate node for said application, said subordinate node having at least one partition of said declared temporary tables of said application; and

,

establishing a declared temporary table manager for causing;

(a) said coordinator node to maintain for said application;

a list of said declared temporary tables;

a list of said declared temporary tables for tracking said temporary tables;

a list of said declared temporary tables being dropped containing information to be sent to said catalog node to inform it of declared temporary tables being dropped so said catalog node can release the identification of said declared temporary tables being dropped or add said identification to its deferred drop list to release said identification when execution of said application ends;

a list of action entries indicating identification of tables on which actions are to be performed, and any actions to be performed to be used to inform subordinate nodes to maintain their partitions of said declared temporary tables in synchronization;

a transaction node list indicating identification of those nodes involved in a current transaction to inform the subordinate nodes involved in said current transaction to appropriately end a transaction as required for rollback or commit; and

an application node list for indicating identification of all nodes that have participated in execution of the application, to identify all subordinate nodes participating in the application so that they can perform node specific application termination processing including cleanup; and

(b) said subordinate node for said application to maintain a list of said declared temporary tables that are partitioned at said subordinate node for synchronization.

31. A computer readable storage medium for containing program instructions to establish a multi-node relational database management system for managing a database comprising:

a node failure list identifying nodes that have failed;

program instructions for establishing a catalog node for maintaining a list of identifiers for declared temporary tables associated with any application;

program instructions for establishing a coordinator node for an application;

program instructions for establishing at least one subordinate node for said application, said subordinate node having at least one partition of said declared temporary tables of said application;

program instructions for establishing a declared temporary table manager for causing;

(a) said coordinator node to maintain for said application;

a list of said declared temporary tables;

a list of said declared temporary tables for tracking said temporary tables;

a list of said declared temporary tables being dropped;

a list of said declared temporary tables being dropped containing information to be sent to said catalog node to inform it of declared temporary tables being dropped so said catalog node can release the identification of said declared temporary tables being dropped or add said identification to its deferred drop list to release said identification when execution of said application ends;

a list of action entries indicating identification of tables on which actions are to be performed, and actions to be performed;

a list of action entries indicating identification of tables on which actions are to be performed, and any actions to be performed to be used to inform subordinate nodes to maintain their partitions of said declared temporary tables in synchronization;

a transaction node list indicating identification of those nodes involved in a current transaction;

a transaction node list indicating identification of those nodes involved in a current transaction to inform the subordinate nodes involved in said current transaction to appropriately end a transaction as required for rollback or commit;

an application node list for indicating identification of all nodes that have participated in execution of said application; and

an application node list for indicating identification of all nodes that have participated in execution of the application, to identify all subordinate nodes participating in the application so that they can perform node specific application termination processing including cleanup; and

(b) said subordinate node for said application to maintain a list of said declared temporary tables that are partitioned at said subordinate node.

32. In a multi-node relational database management system a method for managing a database comprising:

using a catalog node for maintaining a list of identifiers for declared temporary tables associated with any application;

associating a coordinator node for an application;

using at least one subordinate node for said application, said subordinate node having at least one partition of said declared temporary tables of said application; and

using a declared temporary table manager for causing;

(a) said coordinator node to maintain for said application a list of said declared temporary tables;

wherein the declared temporary table manager is used for managing said declared temporary tables for causing;

a transaction node list indicating identification of those nodes involved in a current transaction; and

an application node list for indicating identification of all nodes that have participated in execution of said application; and

(b) said subordinate node for said application to maintain a list of said declared temporary tables that are partitioned at said subordinate node.

33. In a multi-node relational database management system a method for managing a database, the method comprising the steps of:

(a) using a catalog node for maintaining a list of identifiers for declared temporary tables associated with any application;

(b) associating a coordinator node for an application;

(c) using at least one subordinate node for said application, said subordinate node having at least one partition of said declared temporary tables of said application;

(d) using a declared temporary table manager for causing said coordinator node to maintain for said application a list of said declared temporary tables, wherein said declared temporary table manager is used for managing said declared temporary tables for causing;

(d1) said coordinator node to maintain for said application;

(d1i) a list of said declared temporary tables for tracking said temporary tables;

(d1ii) a list of said declared temporary tables being dropped containing information to be sent to said catalog node to inform it of declared temporary tables being dropped so said catalog node can release the identification of said declared temporary tables being dropped or add said identification to its deferred drop list to release said identification when execution of said application ends;

(d1iii) a list of action entries indicating identification of tables on which actions are to be performed, and any actions to be performed to be used to inform subordinate nodes to maintain their partitions of said declared temporary tables in synchronization;

(d1iv) a transaction node list indicating identification of those nodes involved in a current transaction to inform the subordinate nodes involved in said current transaction to appropriately end a transaction as required for rollback or commit; and

(d1v) an application node list for indicating identification of all nodes that have participated in execution of the application, to identify all subordinate nodes participating in the application so that they can perform node specific application termination processing including cleanup; and

(d2) said subordinate node for said application to maintain a list of said declared temporary tables that are partitioned at said subordinate node for synchronization.