International Business Machines Corporation

Other

Paramesh Sampatrai Desai

Hong Sang Tie

James Z Teng

Fen-Ling Lin

Julie Ann Watts

Peter Hoa

Balakrishna Raghavendra Iyer

Terence Patrick Purcell

Thomas Abel Beavin

Piyush Goel

Gautam Bhargava

John Marland Garth

Akira Shibamiya

Jerry Mukai

Bryan Frederick Smith

Li Jun Xia

Michael R Shadduck

Kathryn Ruth Zeidenstein

Qianyang Yu

Kou Horng Yang

William Samuel Perlman

Yun Yu Wang

James Alan Ruddy

Donald J Haderle

Kalpana Shyam

Craig Alan Friske

Tammie Dang

William Robert Bireley

Laura Michiko Kunioka-Weis

Christopher John Crone

Margaret Ann Bernal

Eric N Katayama

Georgia Carroll Fuller

Jerome Quan Wong

Annie S Tsang

RamanaKumari M Croisettier

Terry Dennis Allen

Daniel Keith Courter

Maureen Mae McDevitt

William Y Kyu

David Elmer Raiman

Kevin Daniel Gougherty

Jennifer Wha-Yuh Ou

Ramani M Croisettier

Embodiments presented herein provide techniques for setting different methods of skipping duplicate values when executing a query statement in a relational database. A distance between a two distinct keys in an index, a current index key and a next distinct index key, are estimated. Based on the estimated distance, an appropriate duplicate-skipping method is determined. If the proximity between the distinct keys is relatively far apart (e.g., the keys reside in index pages that are at least an index page apart), then a 'big skip' method is performed. Otherwise, if the proximity between the distinct keys is relatively near (e.g., the keys reside in the same index page), then a 'little skip' method is performed.

Dynamically adjust duplicate skipping method for increased performance

A computer system and a relational database management system (RDMS) computer program product are described for interfacing a number of concurrently running database sessions with a large database in which a plurality of local temporary object database descriptors are used in lieu of a single global temporary object database descriptor where the global and each of the local temporary object database descriptors include identifiers for temporary objects, each of the local temporary object database descriptors is associated with a single database session and each of the global database descriptors is shared among multiple database sessions.

Computer program product and method for database management system supporting multiple temporary tables

Provided are a method, system, and program for managing a database. A request is processed to create an index on at least one key column of a table in a computer readable medium. A determination is made of table partitions in which rows of the table are stored. One index partition is generated for each determined table partition, wherein each index partition includes nodes, wherein each node in each index partition includes a key value from at least one column in a corresponding table row in the table partition associated with the index partition and a location identifier identifying the corresponding table row in the corresponding table partition. In certain embodiments, a technique is implemented during queries to limit the search to a subset of the multiple index partitions when possible and return rows in the order of the key columns of the index while the same range of key values can exist across multiple index partitions.

Method, system, and program for managing database operations with respect to a database table

Disclosed is a method, system, and program for database query execution. A range of data pages is assigned to each of multiple parallel tasks. For each of the multiple parallel tasks, the range of data pages is mapped to one or more physical partitions, and a data partitioned secondary index partition associated with each of the one or more physical partitions is identified. Each of the multiple parallel tasks is executed to process the database query against the assigned range of data pages using the one or more data partitioned secondary index partitions.

Method, system, and program for optimizing database query execution

Provided is a method, system, program, and data structures for making data available to an application program. A result table is generated including rows corresponding to a subset of rows in a base table having data in one or more columns that satisfy a query predicate clause. The result table includes, for each row, a location identifier of the corresponding row in the base table and a column for each column in a query select list. The result table is accessed to return requested data from the base table to the application program.

Method, system, and program for processing modifications to data in tables in a database system

Disclosed is a system, method, and program for extracting data from a record in a first format and inserting data into a record in a second format during an insert operation. A determination is made of location information for data in at least one record in at least one column in the first format involved in the insert operation from which data will be extracted and of location information for data in at least one record in at least one column in the second format involved in the insert operation into which the data extracted from a corresponding column in the first format will be inserted. For each column from which data will be extracted, the determined location information is used to generate at least one instruction to move the data in each column in the record in the first format to each corresponding column of one record in the second format. The location information of the columns in records in the first and second formats involved in the data movement operations is encoded in the at least one instruction. The instruction may be repeated for multiple records in the first format from which data will be extracted into multiple records in the second format.

Method, system, and program for extracting data from database records using dynamic code

A method, apparatus, and article of manufacture for a computer-implemented repartitioning system. Data is repartitioned in a database stored on a data storage device connected to a computer. First, it is detected that a partitioning scheme for the data has been altered. Next, partitions that would be affected by the altered partitioning scheme are identified. Then, the identified partitions are reorganized based on the altered partitioning scheme.

Repartitioning data

A method and apparatus for generating static, dynamic and hybrid sparse indices for use with workfiles used by SQL queries in a relational database management system.

Method and apparatus for generating dynamic and hybrid sparse indices

A method, apparatus and article for manufacture for generating static, dynamic and hybrid sparse indices for use with workfiles used by SQL queries in a relational database management system. A workfile and a sparse index structure are temporarily created in the computer during execution of the query by the computer. The workfile stores intermediate relations resulting from execution of a portion of the SQL query, wherein the intermediate relations comprise sorted rows for an inner table referenced in the SQL query. The sparse index structure contains one or more entries indicating at least an approximate location for at least some of the rows in the workfile. As one or more rows from an outer table referenced in the SQL query are retrieved, the entries of the sparse index structure are searched for a closest matching entry for each retrieved row. The sorted workfile is then scanned for a row matching each retrieved row using the closest matching entry from the sparse index structure as a starting position. The sparse index structure is then updated with an entry corresponding to the row from the sorted workfile matching the retrieved row from the outer table.

A system and method for a computer based system having one or more consuming DBMSs and one or more producing DBMSs such that the producing DBMSs produce workfiles for the consuming DBMSs. A producing DBMS uniquely identifies the bufferpool pages comprising a workfile, uniquely identifies the workfile, and flushes only the bufferpool pages corresponding to the workfile to a storage device. The producing DBMS further notifies the consuming DBMS that the workfile is available and receives a reply from the consuming DBMS indicating that the consuming device retrieved the data from the storage device. The consuming device receives notification from the producing DBMS, retrieves the workfile, and replies to the producing DBMS that it has retrieved the workfile.

Bufferpool coherency for identifying and retrieving versions of workfile

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

Growing community of inventors

San Jose, CA, United States of America

Paramesh Sampatrai Desai