International Business Machines Corporation

Tina Louise Mukai

Eileen Tien Lin

James C Kleewein

Lan Huang

Steven John Watts

Shivakumar Venkataraman

Yun Yu Wang

Inderpal Singh Narang

James Z Teng

Yang Sun

Jeffrey William Josten

Tian Zhang

Amit Radheshyam Somani

Hemant Maheshwari

Tu-An Cheng

An apparatus and computer program product determine a target data type in an environment having a plurality of levels. The plurality of levels comprise a source level, zero or more intermediate levels, and a target level. The zero or more intermediate levels are between the source level and the target level. A target data type representing a transformation of a data type of an associated datum as the datum passes from the source level through the zero or more intermediate levels to the target level is generated. The target data type comprises a sequence of nested transformation functions having an innermost transformation function and an outermost transformation function.

Determining a target data type in a heterogeneous multi-level environment

An inexact query transformation system selects a query element from a query statement. If an inexact transformation improves the query performance, the system prioritizes the candidate transformation rules applicable to the selected query element. The system applies the candidate transformation rules to the selected query element to generate a transformed query element that includes a transformed remote query element. If the transformed remote query element is pushdownable, the system adds the transformed remote query element to a transformed inexact query output. Otherwise, the system applies additional candidate transformation rules to the selected query element to generate the transformed inexact query output. The system applies the transformed inexact query output to a heterogeneous datastore to provide an output query result.

System and computer program product for performing an inexact query transformation in a heterogeneous environment

Method for performing an inexact query transformation in a heterogeneous environment

A method of determining a target data type in an environment having a plurality of levels is provided. Zero or more intermediate levels are between the source level and the target level. A target data type representing the transformation of a data type of an associated datum as the datum passes from the source level through the zero or more intermediate levels to the target level is generated. In another aspect of the invention, the datum is assigned to the target level in accordance with the target data type. In yet another aspect of the invention, the target data type is passed with the associated datum, via the zero or more intermediate levels to the target level.

Technique for determining a target data type in a heterogeneous multi-level environment

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.

Method, computer program product, and system for pushdown analysis during query plan generation

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.

Method, computer program product, and system for rewriting database queries in a heterogenous environment

A database management method enables an improved efficiency processing action in a computer wherein manipulation of data from a table is required in accord with a query request, wherein the table is stored remotely from said computer and at least some data in the table includes large data objects (LOBs). The method includes the steps of: responding to the request by transmitting a query to the table; configuring a result set from the table in response to the query, wherein the result set includes one or more unique identifiers of any LOB data associated with the result set; receiving the result set and performing a data manipulation action on data contained in the result set to form a response; transferring the response to the query requester; and accessing LOB data from the table that is referenced by the one or more unique identifiers in the result set and causing transfer of the LOB data to the query requester.

Method and apparatus for deferring large object retrievals from a remote

An application program interface at a local processor enables transparent access to both a local database and a remote database. The remote database includes (i) a remote DBMS which segregates data into distinct remote datatype categories and (ii) remote data functions which define functions that are executable by the remote DBMS with respect to categories of remote datatypes. The local processor includes a memory which stores (i) a local DBMS that includes local datatypes which segregate locally stored data into local datatypes; (ii) local functions which define actions that are executable by the local DBMS with respect to defined categories of the local datatypes, and (iii) tables which store a mapping between local datatypes and remote datatypes, and local functions and remote functions. The mapping between at least one local datatype and at least one remote datatype indicates that the remote datatype is to be transformed into a local defined datatype that is different than the remote datatype. A local processor employs the table to convert a received instance of the at least one remote datatype to a mapped local datatype and subjects data comprising the instance of the remote datatype to a local function which is not executable for the remote datatype. The application program interface further enables specification of a function to be carried out at the remote database with respect to data stored thereat, which function is not readily performable by the local processor.

System and method for providing a single application program interface

A heterogeneous database system is described which includes plural database systems, one of the database systems acting as an interface (i.e., system manager). The system manager performs functions which provide an application program with transparent access to the entire database system. The system manager includes a memory for storing (i) a matrix of functional capabilities of each of the plural databases, (ii) a catalog of functions that are supportable by various databases and (iii) and entry matrix for providing an interface function between a user and the system manager. A processor is present in the system manager; and is responsive to a user entry, via the entry matrix, with respect to a functional capability of one of the plural databases, to alter a matrix of functional capabilities pertaining to the database. The alteration is carried out in accordance with a corresponding functional capability entry in the catalog. If a user entry is received which refers to a database for which no entry exists in the matrix of functional capabilities, the system manager presents an entry matrix in a form which reflects default functional capabilities. Thereafter, the default capabilities may be altered by a system administrator to reflect the capabilities of the new database.

Heterogeneous database system with data source extensibility and

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. The query optimizer then chooses one of the query plans in accordance with a determined criteria and causes the selected plan to be executed.

Method and apparatus for optimizing queries across heterogeneous

A method enables a computer to manipulate data from a table in a remote database in accord with a query from a query requestor, wherein at least some data in the table includes large data objects (LOBs). The method includes the steps of: responding to the query by configuring a database query and determining (i) if a response to the database query requires transfer of LOB data from the table and if yes, and (ii) the LOB data can be transferred in response to the database query without a data manipulation which uses the LOB data or (iii) a data manipulation required in response to the database query which uses the LOB data can be performed at the remote database, then designating the LOB data for 'streaming'; dispatching the database query to the remote database and receiving a result set from the table from the remote database in response to the query; and storing the result set and causing any LOB data which has been marked for streaming to be then dispatched to the query requestor without being written to disk.

Method and apparatus for accessing of large object data segments from a

An efficient procedure for determining the set of buffer pool database pages that must be externalized to stable storage and for scheduling their write I/O's before release of a committing transaction's locks. In a multisystem database management system (DBMS) with high-speed shared external storage (SES) environment, a DBMS instance may follow 'force-at-commit' protocol for a database that has intersystem read/write interest or may alternatively follow a 'no-force-at-commit' policy when operating with a database for which only one system has interest. By introducing the concept of a series of unique ordinal numbers (ORD#) for each database assigned to buffer pool data pages whenever the page state changes from clean to dirty within a single DBMS instance, this procedure maintains a transaction page list (TPL) for each transaction in ORD# order. Because the dirty page list (DPL) pages are queued in ORD# sequence and each TPL includes all modified pages associated with the transaction, a merge scan with DPL entries can be performed for each TPL at the commit time of the corresponding transaction. This searches the DPL in a single pass with minimal DPL latch contention. The TPL pages requiring destaging are distributed to two processing lists; a first list for immediate force to SES and a second list of 'in-use' pages that are forced to SES if necessary upon release of existing process latches.

Efficient destaging of updated local cache pages for a transaction in a

A method for ensuring data coherence while detecting whether the locally cached copy of a data page is invalid and responsively refreshing the locally cached page from a Shared Electronic Store (SES) in a multisystem shared disk environment. Locally cached data pages may become invalid in a multisystem shared disk environment because of transactions executed by other systems for the common database. Thus, whenever a transaction in a database management system (DBMS) instance desires to read or update a record in a locally cached data page, the DBMS must first verify validity for the locally cached copy and, for stale or invalid copies, must re-read and re-register the latest version in the SES. This invention provides a procedure for the necessary verification and refreshing steps that relies on page latching to serialize the combination of local multiuser and global multisystem activities. The procedure of this invention supports both record and page locking granularities.

Growing community of inventors

San Jose, CA, United States of America

Tina Louise Mukai