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

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.

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.

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.