System and method for optimizing distributed and hybrid queries in imperfect environments

• US 7,853,624 B2
• Filed: 03/18/2008
• Issued: 12/14/2010
• Est. Priority Date: 05/02/2006
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Kraemer, James R., Friedlander, Robert R., Hennessy, Richard
• Primary Examiner(s): Wong; Don
• Assistant Examiner(s): LODHI, ANDALIB FT
• Application Number:
  US12/050,267
• Publication Number:
  US 20080177770A1
• Time in Patent Office: 1,001 Days
• Field of Search: None
• US Class Current: 707/803
• CPC Class Codes: G06F 11/1438 Restarting or rejuvenating
• CPC Class Codes: G06F 16/256 in federated or virtual databases
• CPC Class Codes: G06F 21/6227 where protection concerns the structure of data, e.g. records, types, queries
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation

Abstract

Method, system, and program product for configuring and using a federated database and data structure management system with error prone data. The design of the metadata and queries includes the steps of first determining schema and metadata configurations of the data source servers. The next step is enumerating available resources, and enumerating security and confidentiality requirements. These are used for calculating an optimal federated database management system design based on the schema and metadata, the enumerated available resources, and the enumerated security and confidentiality requirements; and designing an optimal federated database management system with provision for exception detection and error handling.

Claims

1. A method for optimizing a federated database and data structure management system having a federated database server and a plurality of data source servers comprising the steps of:

a) determining schema and metadata configurations of the data source servers;

b) enumerating available resources;

c) enumerating security and confidentiality requirements;

d) calculating an optimal federated database and data structure management system design based on the schema and metadata, the enumerated available resources, and the enumerated security and confidentiality requirements at both;

requester level

Institutional, department or external overriding constraints

e) facilitating testing data structure availability and handling exceptions, comprising the steps of;

1) testing data structure availability,

2) testing server availability,

3) testing data availability and data state including schema, namespace, table, column, row, locking, and isolation or file level,

4) testing access credentials including no access, limited access, full access, de-identified access, access purpose (research vs. clinical development vs. pre-clinical) and overriding institutional, department, and external constraints,

5) determining data schema availability and accessibility, and

6) handling exceptions and deviations,

f) designing an optimal federated database and data structure management system based on flags, indicators, syntax modifications determined in step e)-6); and

g) providing functionality for user input including user heuristics and data inputs.

2. The method of claim 1 comprising using one or more of an entity unification scheme, conformal dimensions, an aggregation scheme, unified metadata, and a unified data model.

3. A method of submitting a query to a federated database and data structure management system and obtaining an optimized output therefrom, comprising the steps of:

a) submitting a query to an application associated to a federated server;

b) optimizing the query in the federated server;

c) decomposing the query into fragments for execution at individual data sources;

d) invoking wrappers/services to execute the fragments;

e) determining data structure availability and exception handling, comprising the steps of;

1) testing data structure availability,

2) testing server availability,

3) testing data availability and data state including schema, table, column, row, locking, and isolation level,

4) testing access credentials including no access, limited access, full access and overriding institutional, department, and external constraints,

5) determining data schema availability and accessibility, and

6) handling exceptions and deviations,

7) Generate optimal query execution plan based on known state of source data contributors and overriding constraints

f) extracting/fetching data from the constituent databases of the federated database, files, images, documents, web content;

g) returning streams of data to the federated server;

h) combining returning streams, and performs additional processing not accomplished by a data source; and

i) returning a final result to the application associated to the federated server.

4. The method of claim 3 wherein said final result is a request for user interaction.

5. The method of claim 3 wherein said final result is data.

6. The method of claim 3 comprising executing the fragments by the step of the wrapper performing data source operations to carry out specified function calls and queries, where queries are submitted to the data source in its native query language.

7. The method of claim 3 wherein execution at individual data sources comprises extracting/fetching, transfer, and loading data from a data server.

8. The method of claim 7 wherein execution at individual data sources comprises extracting, transfer, and loading data from a data server in real time on the fly without materialization thereof.

9. A program product comprising a computer writable substrate having written thereon computer readable program code for directing a computer system to carry out the steps of:

a) submitting a query to an application associated to a federated server;

b) optimizing the query in the federated server;

c) decomposing the query into fragments for execution at individual data sources;

d) invoking wrappers to execute the fragments;

e) determining data structure availability and exception handling, comprising the steps of;

1) testing data structure availability,

2) testing server availability,

3) testing data availability and data state including schema, table, column, row, locking, and isolation level,

4) testing access credentials including no access, limited access, full access and overriding institutional, department, and external constraints,

5) determining data schema availability and accessibility, and

6) handling exceptions and deviations,

f) extracting/fetching data from the constituent data sources of the federated information space;

g) returning streams of data to the federated server;

h) combining returning streams, and performs additional processing not accomplished by a data source; and

i) returning a final result to the application associated to the federated server.

10. The program product of claim 9 wherein said final result is a request for user interaction.

11. The program product of claim 10 wherein said final response is data.

12. The program product of claim 10 comprising executing the fragments by the step of the wrapper performing data source operations to carry out specified function calls and queries, where queries are submitted to the data source in its native query language.

13. The program product of claim 10 wherein execution at individual data sources comprises extracting, transfer, and loading data from a data server.

14. The program product of claim 13 wherein execution at individual data sources comprises extracting, transfer, and loading data from a data server in real time on the fly without materialization thereof.

15. A federated data system comprising:

1) a client terminal including an SQL API, Web Service (SOAP/HTTP)

2) a federated database server in communication with the client terminal;

3) the federated database server including;

a) a wrapper application;

b) a database catalog;

c) a metadata database; and

d) computer readable code for;

i) testing data structure availability,

ii) testing server availability,

iii) testing data availability and data state including schema, table, column, row, locking, and isolation level,

iv) testing access credentials including no access, limited access, full access and overriding institutional, department, and external constraints,

v) determining data schema availability and accessibility, and

vi) handling exceptions and deviations; and

4) a plurality of backend data sources with associated data repositories; and

5) wherein the federated database server is configured and controlled to access and receive data from the plurality of the back end data sources with associated data repositories.