SYSTEM, METHOD, AND PROGRAM PRODUCT FOR INTEGRATING DATABASES

• US 20080306904A1
• Filed: 10/29/2007
• Published: 12/11/2008
• Est. Priority Date: 10/30/2006
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Fukuda, Takeshi, Ohkawa, Masahiro, Kurosawa, Ryoji
• Granted Patent: US 8,965,912 B2
• Time in Patent Office: Days
• US Class Current: 707/2
• CPC Class Codes: G06F 16/2471 Distributed queries
• CPC Class Codes: G06F 16/25 Integrating or interfacing systems involving database management systems

Abstract

A system for integrating databases comprises a first virtual table, a second virtual table, a common table, a list, a generating section, and a selecting section. The first virtual table corresponds to a first data. The second virtual table corresponds to a second data. The common table is associated with the first and second virtual tables. The list indicates information about associations between the first and second virtual tables and the common table. The generating section generates two or more access plans from a database query on the common table. The two or more access plans involve at least one of the first and second virtual tables in accordance with the list. The selecting section selects one of the two or more access plans generated by the generating section to be used to execute the database query.

Claims

1. A system comprising:

a first virtual table corresponding to a first data;

a second virtual table corresponding to a second data;

a common table that is associated with the first and second virtual tables;

a list with information about associations between the first and second virtual tables and the common table;

a generating section for generating two or more access plans from a database query on the common table, the two or more access plans involving at least one of the first and second virtual tables in accordance with the list; and

a selecting section for selecting one of the two or more access plans generated by the generating section to be used to execute the database query.

2. The system according to claim 1, further comprising

an estimating section for estimating a cost of each of the two or more access plans, wherein

the selecting section selects an access plan from the two access plans based on the costs of the access plans estimated by the estimating section.

3. The system according to claim 1, wherein the generating section acquires a name of the common table from the database query, acquires names of the first and second virtual tables from the name of the common table using the list, rewrites the database query so that the name of the common table in the database query is replaced with the name of the first or second virtual table, and compiles the rewritten query.

4. The system according to claim 1, further comprising

a storing section for caching the two or more access plans generated by the generating section.

5. The system according to claim 4, further comprising

an estimating section for estimating a cost of each of the two or more access plans, wherein

the storing section further caches the costs of the access plans estimated by the estimating section.

6. The system according to claim 4, wherein the generating section generates an access plan if no access plan is cached in the storing section.

7. The system according to claim 4, wherein if one or more access plans are cached in the storing section, the selecting section selects one access plan from the cached one or more access plans.

8. The system according to claim 1, further comprising

an executing section for executing the database query using the access plan selected by the selecting section.

9. The system according to claim 1, further comprising

a receiving section for receiving the database query sent from a client via a network.

11. The system according to claim 4, further comprising

a managing section for, once the first or second data has become unavailable, disabling a cached access plan in which the first or second virtual table is used.

12. The system according to claim 11, further comprising

a monitoring section for monitoring whether or not the first and second data are available, and once the first or second data has become unavailable, notifying the managing section that the first or second data has become unavailable.

13. The system according to claim 11, wherein once the unavailable first or second data has become available again, the managing section enables the disabled access plan.

14. The system according to claim 1, further comprising

a managing section for, once a replica of the first data has newly been created, adding information indicating creation of the new replica to an access plan in which the common table is used.

15. The system according to claim 14, wherein if information indicating creation of the new replica exists, the generating section generates an additional access plan regarding the new replica.

16. The system according to claim 15, further comprising

a storing section for caching the additional access plan.

17. A method comprising:

receiving a query for a database, wherein the query indicates a common table, which is associated with a plurality of virtual tables;

using a dependency list to determine names of a first and a second virtual table of the plurality of virtual tables, wherein the dependency list indicates information about associations between the common table and the plurality of virtual tables;

writing a first rewrite of the query for the first virtual table;

generating a first access plan based on the first rewrite of the query;

writing a second rewrite of the query for the second virtual table;

generating a second access plan based on the second rewrite of the query;

estimating a first cost of the first access plan;

estimating a second cost of the second access plan;

storing the first and second access plans along with the first and second costs; and

selecting either the first access plan or the second access plan to execute the received query, wherein the first virtual table corresponds to first data and the second virtual table corresponds to second data.

18. The method of claim 17, wherein the first cost of the first access plan is based on at least one of response time to carry out the first access plan and amount of resources used to carry out the first access plan.

19. The method of claim 17, wherein said selecting is based, at least in part, on the first cost and the second cost.

20. The method of claim 17 further comprising:

maintaining a status of the first and the second access plans, wherein the status of the first access plan corresponds to status of first data and status of the second access plan corresponds to status of the second data; and

setting status of the first access plan to disabled if the status of the first data is unavailable.

21. The method of claim 17, wherein the first virtual table corresponds to first data and the second virtual table corresponds to second data, which is a replica of the first data.

22. A program product encoded in one or more machine-readable, which when executed by a machine causes the machine to perform operations that comprise:

receiving a query for a database, wherein the query indicates a common table, which is associated with a plurality of virtual tables;

using a dependency list to determine names of a first and a second virtual table of the plurality of virtual tables, wherein the dependency list indicates information about associations between the common table and the plurality of virtual tables;

writing a first rewrite of the query for the first virtual table;

generating a first access plan based on the first rewrite of the query;

writing a second rewrite of the query for the second virtual table;

generating a second access plan based on the second rewrite of the query;

estimating a first cost of the first access plan;

estimating a second cost of the second access plan;

storing the first and second access plans along with the first and second costs; and

selecting either the first access plan or the second access plan to execute the received query.

23. The program product of claim 22, wherein the first cost of the first access plan is based on at least one of response time to carry out the first access plan and amount of resources used to carry out the first access plan.