Support for summary tables in a database system that does not otherwise support summary tables

• US 6,532,470 B1
• Filed: 12/17/1999
• Issued: 03/11/2003
• Est. Priority Date: 12/17/1999
• Status: Expired due to Term

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Kleewein, James C., Venkataraman, Shivkumar, Subramanina, Narayana, Pirahesh, Mir Hamid, Leung, Ting Yu, Cochrane, Roberta Jo
• Primary Examiner(s): Metjahic, Safet
• Assistant Examiner(s): NGUYEN, CAM LINH T
• Application Number:
  US09/466,861
• Time in Patent Office: 1,180 Days
• Field of Search: 707/3, 707/5, 707/6, 707/102
• US Class Current: 1/1
• CPC Class Codes: G06F 16/27 Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

A system, method, and article of manufacture for supporting summary tables in a database system that does not otherwise support summary tables is disclosed. The system generally comprises a central program and one or more database systems that may be heterogeneous. At least one of the database systems does not support the generation, maintenance, and/or querying of summary tables. The central program is configured to communicate with the database systems and to identify relations corresponding to summary tables (also referred to as materialized views) within one or more of the database systems. The central program may initiate the generation of summary tables, which may be populated local to the central program or local to one or more of the database systems. The central program may also maintain or coordinate maintenance of the summary tables. In addition, the central program is preferably configured to receive user queries on one or more of the database systems and to generate optimized query plans based upon the user queries, considering in so doing, the contents of the summary tables.

Claims

1. A system for support of a summary table corresponding to a database system that does not otherwise support summary tables, the apparatus comprising:

a communication module configured to communicate with the database system and initiate the generation of an unrecognized summary table containing summary data corresponding to the database system that does not otherwise support summary tables; and

an identification module in communication with the communication module and configured to identify the unrecognized summary table as a summary table.

2. The system of claim 1, wherein the unrecognized summary table is stored with the database system that does not otherwise support summary tables.

3. The system of claim 1, wherein the unrecognized summary table is stored local to the database system.

4. The system of claim 1, wherein the unrecognized summary table is recognized by the database system as a normal table.

5. The system of claim 1, further comprising providing a central program, the communication module and the identification module located within the cental program.

6. The system of claim 5, wherein the identification module comprises a catalog, the central program configured to list an alias of the unrecognized summary table within the catalog, and to list corresponding information within the catalog identifying the unrecognized summary table as a summary table.

7. The system of claim 5, wherein the unrecognized summary table is generated within the central program, the unrecognized summary table containing summary data corresponding to the database system, the database system being unaware of the existence of the unrecognized summary table.

8. The system of claim 1, further comprising a summary table creation module, the summary table creation module configured to initiate the generation of the unrecognized summary table within the database system.

9. The system of claim 8, wherein the summary table creation module is configured to transmit commands from the central program to the database system to instruct the database system to generate the unrecognized summary table.

10. The system of claim 9, wherein the commands comprise DDL commands.

11. The system of claim 10, wherein the DDL commands comprise a SQL command selecting entries from a table of the database system where a selected criterion is met.

12. The system of claim 8, wherein the summary table creation module is configured to receive from a user a summary table create command, and in response generate a SQL command and transmit the SQL command to the database system to instruct the database system to generate the unrecognized summary table.

13. The system of claim 12, wherein the summary table creation module is further configured to assign the unrecognized summary table an alias and the central program is configured to store the alias and information regarding the contents of the summary table within the catalog.

14. The system of claim 1, further comprising the database system, the database system configured without summary table support capability, the database system recognizing the unrecognized summary table as a normal table.

15. The system of claim 1, further comprising a query processing module configured to generate a query plan that directs SQL queries to the unrecognized summary table.

16. The system of claim 1, further comprising a query processing module configured to receiving into the central program a query from a user requesting data from the database system;

consider the contents of the unrecognized summary table; and

generate a SQL query to be transmitted to the database system, the SQL query directed at least partially to the unrecognized summary table, the communication module configured to transmit the SQL query to the database system.

17. A method for support of a summary table within a database system that does not otherwise support summary tables, the method comprising:

generating an unrecognized summary table and populating the summary table with data corresponding to a database system; and

identifying the unrecognized summary table as a summary table.

18. The method of claim 17, further comprising storing the unrecognized summary table within a central program of which the communication module and the identification module are part.

19. The method of claim 17, further comprising storing the unrecognized summary table within the database system that does not otherwise support summary tables.

20. The method of claim 17, further comprising recognizing the unrecognized summary table by the database system as a normal table.

21. The method of claim 17, further comprising providing a central program, the central program initiating the generating of an unrecognized summary table and wherein the step of identifying the unrecognized summary table comprises listing an alias of the unrecognized summary table within a catalog of the central program, and listing information within the catalog identifying the unrecognized summary table as a summary table.

22. The method of claim 21, wherein generating an unrecognized summary table comprises generating a summary table within the central program, the summary table containing summary data corresponding to the database system, the database system being unaware of the existence of the unrecognized summary table.

23. The method of claim 21, further comprising:

receiving into the central program a query from a user requesting data from the database system;

considering the contents of the unrecognized summary table;

generating a SQL query to be transmitted to the database system, the SQL query directed at least partially to the unrecognized summary table; and

transmitting the SQL query to the database system.

24. The method of claim 17, further comprising providing a central program, the database system remote to the central program and wherein generating the unrecognized summary table comprises transmitting commands from the central program to the database system, and in response to the commands, the database system generating the unrecognized summary table.

25. The method of claim 24, wherein the commands comprise DDL commands.

26. The method of claim 25, wherein the DDL commands comprise a SQL command selecting entries from a table of the database system where a selected criterion is met.

27. The method of claim 26, further comprising assigning the unrecognized summary table an alias and storing the alias and information regarding the contents of the summary table within a catalog of the central program.

28. The method of claim 24, wherein generating an unrecognized summary table comprises presenting to the central program from a user a summary table create command, and in response the central program generating a SQL command, transmitting the SQL command to the database system, and in response, the database system creating the unrecognized summary table.

29. The method of claim 17, further comprising querying the unrecognized summary table.

30. An article of manufacture comprising a program storage medium readable by a processor and embodying one or more instructions executable by the processor to perform a method for support of a summary table within a database system that does not otherwise support summary tables, the method comprising:

generating an unrecognized summary table and populating the summary table with data corresponding to a database system; and

identifying the unrecognized summary table as a summary table.

31. The article of manufacture of claim 30, wherein the method further comprises storing the unrecognized summary table within a central program of which the communication module and the identification module are part.

32. The article of manufacture of claim 31, wherein the method further comprises:

receiving into the central program a query from a user requesting data from the database system;

considering the contents of the unrecognized summary table;

generating a SQL query to be transmitted to the database system, the SQL query directed at least partially to the unrecognized summary table; and

transmitting the SQL query to the database system.

33. The article of manufacture of claim 30, wherein the method further comprises storing the unrecognized summary table is stored local to the database system.

34. The article of manufacture of claim 30, wherein the method further comprises storing the unrecognized summary table within the database system that does not otherwise support summary tables.

35. The article of manufacture of claim 30, wherein the method further comprises providing a central program, the central program initiating the generating an unrecognized summary table and wherein the step of identifying the unrecognized summary table comprises listing an alias of the unrecognized summary table within a catalog of the central program, and listing information within the catalog identifying the unrecognized summary table as a summary table.

36. The article of manufacture of claim 35, wherein generating an unrecognized summary table comprises generating a summary table within the central program, the summary table containing summary data corresponding to the database system, the database system being unaware of the existence of the unrecognized summary table.

37. The article of manufacture of claim 30, wherein the method further comprises providing a central program, the database system remote to the central program and wherein generating the unrecognized summary table comprises transmitting commands from the central program to the database system, and in response to the commands, the database system generating the unrecognized summary table.

38. The article of manufacture of claim 37, wherein the commands comprise DDL commands.

39. The article of manufacture of claim 38, wherein the DDL commands comprise a SQL command selecting entries from a table of the database system where a selected criterion is met.

40. The article of manufacture of claim 37, wherein generating an unrecognized summary table comprises presenting to the central program from a user a summary table create command, and in response the central program generating a SQL command, transmitting the SQL command to the database system, and in response, the database system creating the unrecognized summary table.

41. The article of manufacture of claim 40, wherein the method further comprises assigning the unrecognized summary table an alias and storing the alias and information regarding the contents of the summary table within a catalog of the central program.

42. The article of manufacture of claim 30, wherein the method further comprises querying the unrecognized summary table.