International Business Machines Corporation

Donald J Haderle

Akira Shibamiya

Richard A Crus

Yun Yu Wang

James Z Teng

Hong Sang Tie

Julie Ann Watts

Balakrishna Raghavendra Iyer

Chandrasekaran Mohan

Robert William Lyle

Jerry Mukai

Steven John Watts

Atul Chadha

Randy Mitchell Nakagawa

Robert W Engles

Howard W Herron

Mir Hamid Pirahesh

Thomas Abel Beavin

Fen-Ling Lin

Michael J Dockter

Josephine Miu Cheng

Bryan Frederick Smith

Tammie Dang

William Robert Bireley

Paramesh Sampatrai Desai

Thomas Majithia

Jerome Quan Wong

Kathryn Ruth Zeidenstein

Maureen Mae McDevitt

Kou Horng Yang

Richard W Hedges

Jerry W Baker

James Zu-Chia Tseng

Sheldon J Finkelstein

Josephine M-k Cheng

A method, apparatus, and article of manufacture for a computer implemented insert system. Data is stored in a database stored on a data storage device connected to a plurality of computers. At least one look-aside space map is allocated to each of the computers. At each computer, when data is to be inserted into the database, a look-aside space map allocated to that computer is identified, and a free data page is located using the identified look-aside space map.

Insert performance on a multi-system transaction environment

The present invention discloses a technique for restoring a database in a computer. In accordance with the present invention, the database contains objects and is stored on a data storage device connected to the computer. After a system failure, a log file is read. The log file contains one or more modifications to the database objects. Each modification has an associated data page and time stamp or sequence number. The modifications are sorted by at least one predefined sorting key value. The sorted modifications are then grouped by database object. The sorted modifications are applied to each database object in parallel.

Fast log apply

A method, apparatus, and article of manufacture for a computer implemented query compiler. A query is executed in a computer, the query being performed by the computer to retrieve data from a database stored on a data storage device connected to the computer. The query is received containing at least one variable. At execution time, a value is determined for each variable in the query. A filter factor is estimated using the determined value for each variable. Then, an access path is determined for the query using the estimated filter factor.

Access path selection for SQL with variables in a RDBMS

A method and apparatus to provide DBMS restart recovery that allows transactions to access data that does not have restart recovery work pending. Access to data requiring restart recovery work to be performed is restricted, and a transaction is denied access to this data. In another embodiment, the invention allows certain transactions to access restricted data that has restart recovery work pending. These transactions are transactions that do not require data consistency. Regardless of the embodiment, the invention allows full recovery to be completed concurrent to the processing of new transactions requiring access to the database. An amount of restart recovery processing may be postponed until after the DBMS has begun accepting new work requests.

Method and apparatus providing system availability during DBMS restart recovery

Determining the optimal access path for a query at execution time using

A method, apparatus, and article of manufacture for an encoder for encoding members in a concept hierarchy. A query is executed in a computer. The query is performed by the computer to retrieve data from a database stored on a data storage device connected to the computer. Members in the database that are related by one or more concept hierarchies are encoded. Then, members in one of the concept hierarchies that are descendants of one or more selected concepts are identified based on the encoding.

Encoding method of members related by multiple concept or group

A relational data base management system includes a query processor that permits consideration of alternative user-specified access paths, which the processor will validate and incorporate into the query execution plan it otherwise generates. The optimizer of the query processor retrieves user-specified data that defines an access path for a query during bind operations. Any access path parameters that are invalid are replaced with processor-selected values.

User specification of query access paths in a relational database

The present invention provides a system and method for utilizing the proximity of keys in sequential or near sequential index probes to avoid complete index tree traversal. Page information from three pages (LAST, PARENT and NEXT) are stored in separate information fields within an Index Lookaside Buffer. The LAST information field contains information on the most recent leaf page accessed during an index probe in a read key or an insert key operation, the PARENT information field contains information on the parent page of the most recently accessed leaf page described in the LAST information field, and the NEXT information field contains information on the most recent leaf page accessed during a fetch-next key or delete key operation. On a subsequent index probe, the LAST, NEXT and PARENT information fields are sequentially analyzed to determine if the search key is contained within the leaf page described by the LAST or NEXT information fields or if the search key is contained within one of the leaf pages pointed to by the parent page described by the PARENT information field. If none of the LAST, NEXT or PARENT information fields identify the leaf page containing the search key then the default root-to-leaf traversal would commence.

Computer program product for avoiding complete index tree traversals in

System and method for avoiding complete index tree traversals in

A system and method for a computer system having a plurality of DBMS providing a coordinating and assisting function. If a DBMS performs a coordinating function, it is referred to as a coordinating DBMS. If a DBMS performs an assisting function, it is referred to as an assisting DBMS. Each coordinating DBMS receives a query from a user application, decomposes the query into multiple parallel tasks, and allocates the parallel tasks to all of the DBMSs in the system. Each assisting DBMS receives one or more parallel tasks from a coordinating DBMS, executes the parallel tasks and returns the results to the coordinating DBMS. The coordinating DBMSs and assisting DBMSs access one or more storage devices. Furthermore, the present invention comprises a system and method for dynamically disabling a parallel mode on the coordinating DBMSs and the assisting DBMSs.

Query parallelism in a shared data DBMS system

A system and method are provided for recovering a computerized database. During transaction processing of a database, at least one log range is tracked comprising update transaction log records. The log range has a begin value and an end value. The begin value corresponds to a log RBA location of a first access of the database by an updating transaction. The end value corresponds to a log RBA location at a commit or abort of a last updating transaction. The end value corresponds to a point when the database is designated as being read only (called pseudo open). During recovery of the database, the update transaction log records in the log range are applied to a version of the database stored in non-volatile memory.

System and method for improving data recovery performance

Computer program product and program storage device for improving data

Database files containing records include pages called free space inventory pages (FSIPs) describing field space information relating to data pages. In a transaction processing system, the invention provides correct sequences for logging of updates to FSIPs when the updates are required by updates or UNDOs to data records. If, during operation to insert a data record to a data page, the FSIP containing free space information for the page indicates that the page is empty and there are no uncommitted deletes to the page, page I/O is avoided by formatting the page directly in a data buffer pool without reading the page from disk. During a cursor stability-level table scan with data record-level locking, excessive I/O and some record locking are avoided by using space reservation fields on an FSIP to ensure that there is no space reserved on the data page for a later undo of uncommitted data records deletes from the page.

Method for managing logging and locking of page free space information

An automated method is provided for optimizing a query for retrieving data from a relational database in a computer relational database management system. The method uses rules for identifying queries for which duplicates are a factor in retrieving the proper data from the database. The method then merges queries with table expressions, views and subqueries when possible without causing duplicates to be unnecessarily retrieved.

Computer automated system and method for optimizing the processing of a

Results of a relational data base management system are joined in a process requiring, first, existence of an index on the join columns of the inner table, and, second, ordering on the join column of the first table. First, the index on the inner table's join column is scanned for rows of the inner table having join column values matching such values of rows in the outer table. This is done in a single pass through the outer table. Next, a temporary work table containing the identifiers of inner table rows having join column values matching those of the outer table is produced by concatenating the row identifiers to their matching outer table rows. Following this, the temporary work table is ordered by the identifiers. Last, the identifier list of inner table rows is used to retrieve the corresponding rows of the inner table. All predicates local to the inner table are applied to the retrieved rows, and those satisfying these local predicates are combined with their matching outer table rows and returned to the user.

Hybrid technique for joining tables

Growing community of inventors

Los Gatos, CA, United States of America

Donald J Haderle