Integrating databases

• US 8,965,912 B2
• Filed: 10/29/2007
• Issued: 02/24/2015
• Est. Priority Date: 10/30/2006
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Fukuda, Takeshi, Ohkawa, Masahiro, Kurosawa, Ryoji
• Primary Examiner(s): BHATIA, AJAY M
• Assistant Examiner(s): Huang, Miranda
• Application Number:
  US11/926,504
• Publication Number:
  US 20080306904A1
• Time in Patent Office: 2,675 Days
• Field of Search: 707/713, 707717-719, 707/759, 707999001-999005
• US Class Current: 707/759
• 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:

one or more machine readable media encoded with,

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 that,

accesses the list to determine the common table is associated with the first virtual table and the second virtual table,

rewrites a received database query, which indicates the common table, to generate a first rewritten database query to indicate the first virtual table instead of the common table and to generate a second rewritten database query to indicate the second virtual table instead of the common table, and

generates a plurality of access plans that include at least a first access plan for the first rewritten database query and at least a second access plan for the second rewritten database query, wherein each of the plurality of access plans are for either the first rewritten database query or the second rewritten database query;

a selecting section that selects one of the plurality of access plans generated by the generating section;

a storing section that caches the plurality of access plans generated by the generating section;

a managing section that,

in response to being notified that the first data has become unavailable, disables those of a plurality of access plans generated for the first rewritten database query, and

in response to being notified that the first data has become available, enables those of the plurality of access plans generated for the first rewritten database query; and

a monitoring section that,

monitors whether or not the first and second data are available,

once the first data has become unavailable, notifies the managing section that the first data has become unavailable, and

once the first data has become available, notifies the manage section that the first data has become available.

2. The system according to claim 1, further comprising

an estimating section that estimates a cost of each of the plurality of access plans, wherein

the selecting section selects the one of the plurality of access plans based on the costs of the access plans estimated by the estimating section.

3. The system according to claim 1, further comprising

an estimating section that estimates a cost of each of the plurality of access plans, wherein

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

4. The system according to claim 1, wherein the generating section determines that no access plan is cached in the storing section.

5. The system according to claim 1, further comprising

an executing section that executes the one of the plurality of access plans selected by the selecting section to implement the received database query.

6. The system according to claim 1, further comprising

a receiving section that receives the received database query sent from a client via a network.

7. The system according to claim 1,

wherein the managing section further, once a replica of the first data has newly been created, adds information indicating creation of the new replica to those of the plurality of access plans that were generated for the first rewritten database query.

8. A method comprising:

receiving a query for a federated database, wherein the query indicates a common table at a federated database system that manages the federated database;

using a dependency list to determine that a first virtual table and a second virtual table are associated with the common table and to determine names of the first virtual table and the second virtual table;

writing a first rewrite of the query to indicate the first virtual table instead of the common table;

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

writing a second rewrite of the query to indicate the second virtual table instead of the common 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;

selecting either the first access plan or the second access plan to execute the received query, wherein the first virtual table references a first actual table on a first database system and the second virtual table references a second actual table on a second database system

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

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

9. The method of claim 8, 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.

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

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

receiving a query for a federated database, wherein the query indicates a common table at a federated database system that manages the federated database;

using a dependency list to determine that a first virtual table and a second virtual table are associated with the common table and to determine names of the first virtual table and the second virtual table;

writing a first rewrite of the query to indicate the first virtual table instead of the common 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;

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

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

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

12. The program product of claim 11, 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.