Abstract: A method, computer product, and system for pushdown analysis during query plan generation is provided. Rather than do pushdown analysis for portions of a query that are query execution plan independent, the present invention provides pushdown analysis for features of a query that are plan dependent. This is done by evaluating a portion of the query plan for pushdownability to a remote database system; and if pushdownable to the remote database system, generating cost parameters for the portion of the plan if executed at a local database system and if executed at the remote database system. With both sets of cost parameters, the query optimizer has added information on which to select an optimal query plan.

Abstract: A query optimization system and method are particularly suitable for generating a least cost query plan expressible on a plurality of heterogeneous database schemas that are restructuring views of each other. The query optimization system receives a query against one of the tables and converts it to a plurality of alternate queries, each formatted for the particular schema of a different one of the plurality of heterogeneous tables. In generating the alternate queries, the system may utilize SchemaSQL view definitions and may employ restructuring operators expressing and capable of conducting a restructuring of a table into a restructuring view of the table. A middleware system involving a canonical schema and a virtual canonical table may also be employed to express a mapping between restructuring views for purposes of query conversion. The alternate queries may be costed and optimized and a query plan returned that has a least cost or service time involved.

Abstract: A database maintenance system and method are particularly suitable for maintaining a consistency of data between two or more database tables having schemas that may be heterogeneous (e.g., restructuring views of each other). Typically, one table is a base table and a second table is a restructuring view of the base table and may have been generated for faster query response, research, or the like. The restructuring view may have occurred naturally, or the base table may have been converted to the restructuring view using a series to of operators and optionally, a middleware schema. The restructuring view maintenance system is preferably configured to monitor changes to the base table, including changes to data and to metadata. The maintenance system is configured to receive notification of a modification to a base table, investigate the modifications, and determine which operator(s) have been used to convert the base table to the restructuring view.

Abstract: A method, computer product, and system for rewriting database without decreasing pushdownability is provided. First, a pushdown analysis of the query in its entirety is performed prior to the application of any query rewrite rules in order to establish a baseline on pushdownability for the query. The results of this analysis is stored with the internal query representation. After each rule is applied to rewrite a portion of a query, that rewritten portion is analyzed again for pushdownability. If pushdownability is not decreased, then the rewritten query remains and the internal representation of the query is updated to reflect the pushdownability of that portion. If pushdownability is decreased, then an undo operation is applied to the rewritten portion of the query to back out the effects of the rule and leave the query in the same state as before the rewrite. Finally, additional rewrite rules that are directed to the heterogeneous database environment are also provided.

Abstract: A system, method, and article of manufacture for supporting summary tables in a distributed database environment is disclosed. The system generally comprises a central program and a plurality of remote database systems that may be heterogeneous. The central program is configured to communicate with the database systems and to support summary tables (also referred to as materialized views) within the central program or within one or more of the database systems. The summary tables may contain summary data from 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.

Abstract: The present invention discloses a method, apparatus, and article of manufacture for optimizing one or more queries. Initially, redundancies in execution steps for the one or more queries are identified. Then, a new set of equivalent execution steps is created by eliminating redundancies in the execution steps. The new set of equivalent execution steps is used to execute the one or more queries when the use results in efficient query processing.

Abstract: In a distributed database system, at least one of the databases operates as a transaction manager and provides transparent commit coordination between an application program and other connected databases. The transaction manager incorporates a commit alteration procedure which enables selection of either a single phase commit protocol or a two phase commit protocol with respect to each database, during operation of the distributed system. The transaction manager establishes a commit protocol for each connected database; responds to a command from an application program by overriding a commit protocol previously established for a selected database and establishes another commit protocol for the selected database; performs a transaction and utilizes the another commit protocol in connection with the transaction; and upon completion of the transaction as evidenced by a successful commit action, enables establishment of another commit protocol.

Abstract: A heterogeneous database system includes plural databases, one database acting as a system manager which performs functions to provide a transparent user interface that encompasses both the system manager database and other databases. The system manager includes a memory which stores (i) a database management procedure, (ii) a catalog of database functions that are supported by the plural databases, and (iii) a query optimizer procedure. A processor is responsive to a user query which requires data stored at a remote database, to execute the query optimizer procedure. The query optimizer procedure employs entries from the catalogue relating to database functions supported by both the system manager database and the remote database and establishes query plans for responding to the query. A first query plan is based upon execution at the remote database and a second query plan is based upon execution at the system manager database.

Abstract: A method enables a data manipulation action to occur between data from a data source and data in a table in a database, where the database is remote from the data source. The method includes the steps of: determining whether data from the data source comprises unique values, and if not, enabling a caching procedure. The caching procedure obtains values from the data source and transmits a query to a database management system which controls the table, including a value from the data source. A result set is received from the table, is cached, and additionally is stored for purposes of responding to the query. Thereafter, if a value from the data source is retrieved which is identical to a previously retrieved data source value, the cached corresponding result set is utilized, in lieu of transmitting a further query to the table.