Abstract: Disclosed are a method, information processing system, and computer readable medium for scanning a storage medium table. The method includes retrieving location information associated with at least one other storage medium table scan. A storage medium table scan is started at a location within a storage medium table based on at least a location of the one other storage medium table scan. A weight is assigned to at least one storage medium block based on at least a current scanning location within the storage medium table relative to the location of the one other table scan. The method determines if a distance between the current scanning location and the location of the one other table scan is greater than a first given threshold. A current scanning operation is delayed, in response to the distance being greater than the given threshold, until the distance is below a second given threshold.

Abstract: A multi-level locking hierarchy for a relational database includes a locking level applied to a multi-dimensionally clustering table, a locking level applied to blocks within the table, and a locking level applied to rows within the blocks. The hierarchy leverages the multi-dimensional clustering of the table data for efficiency and to reduce lock overhead. Data is normally locked in order of coarser to finer granularity to limit deadlock. When data of finer granularity is locked, data of coarser granularity containing the finer granularity data is also locked. Block lock durations may be employed to ensure that a block remains locked if any contained row remains locked. Block level lock attributes may facilitate detection of at least one of a concurrent scan and a row deletion within a block. Detection of the emptying of a block during a scan of the block may bar scan completion in that block.

Abstract: A block map is associated with a multi-dimensionally clustered (MDC) base table in a database to maintain a “free” or “in use” status for each of the contiguous blocks in the table. Additionally, information may be stored in the block map to indicate whether, after loading data, a given block in the base table requires constraint checking operation or whether, based on changes to the block, a materialized query table dependent upon the table requires a refresh operation. Each time data is loaded into the table, the associated block map is updated. When delayed constraint check or refresh operations are to be performed, the block map is consulted to learn which blocks require the operations. After one of the constraint check or refresh operations is performed, the associated block map is again updated.

Abstract: Multidimensional clustered tables are provided for efficient processing and management in a relational database management system. A multidimensional clustered table is one whose data is simultaneously clustered along one or more independent dimensions, or clustering keys, and physically organized into blocks or pages on disk. When such a table is created, one can specify one or more keys as dimensions along which to cluster the table's data. Each of the dimensions can consist of one or more columns. Further provided are efficient query processing and maintenance techniques for use in conjunction with multidimensional clustered tables.

Abstract: A method to process a query for information stored in a relational database using indexes which have different granularity and/or composition is provided. The method involves selecting indexes of various granularity and/or composition based on the query, ordering and combining them (if necessary) in order to subset the data from a relational table to be optimally scanned. The ability to combine indexes is very useful to answer ad hoc queries for which no specific index exists. This requirement may be met by using index ANDing and index ORing techniques.

Abstract: An information retrieval system and method are provided for minimizing the number of blocks searched in a cell before recording a new record in the table and determining which block can be assigned if a table has space available to store a new record in the case an additional block should be associated with a cell. Dimensions for a table are identified, and at least one block in the table is associated with a dimension value for each dimension, where each block comprises contiguous storage pages. The block can be further associated with a cell; this associated cell has a unique combination of dimension values comprising an dimension value for each of the dimensions. A unique associated bit list for each dimension value for each dimension has a unique corresponding list entry for each block associated with that dimension value, and a unique associated bit list for each cell has a unique corresponding list entry for each block associated with that cell.

Abstract: A multi-level locking hierarchy for a relational database includes a locking level applied to a multi-dimensionally clustering table, a locking level applied to blocks within the table, and a locking level applied to rows within the blocks. The hierarchy leverages the multi-dimensional clustering of the table data for efficiency and to reduce lock overhead. Data is normally locked in order of coarser to finer granularity to limit deadlock. When data of finer granularity is locked, data of coarser granularity containing the finer granularity data is also locked. Block lock durations may be employed to ensure that a block remains locked if any contained row remains locked. Block level lock attributes may facilitate detection of at least one of a concurrent scan and a row deletion within a block. Detection of the emptying of a block during a scan of the block may bar scan completion in that block.

Abstract: A block map is associated with a multi-dimensionally clustered (MDC) base table in a database to maintain a “free” or “in use” status for each of the contiguous blocks in the table. Additionally, information may be stored in the block map to indicate whether, after loading data, a given block in the base table requires constraint checking operation or whether, based on changes to the block, a materialized query table dependent upon the table requires a refresh operation. Each time data is loaded into the table, the associated block map is updated. When delayed constraint check or refresh operations are to be performed, the block map is consulted to learn which blocks require the operations. After one of the constraint check or refresh operations is performed, the associated block map is again updated.

Abstract: Multidimensional clustered tables are provided for efficient processing and management in a relational database management system. A multidimensional clustered table is one whose data is simultaneously clustered along one or more independent dimensions, or clustering keys, and physically organized into blocks or pages on disk. When such a table is created, one can specify one or more keys as dimensions along which to cluster the table's data. Each of the dimensions can consist of one or more columns. Further provided are efficient query processing and maintenance techniques for use in conjunction with multidimensional clustered tables.

Abstract: A method to process a query for information stored in a relational database using indexes which have different granularity and/or composition is provided. The method involves selecting indexes of various granularity and/or composition based on the query, ordering and combining them (if necessary) in order to subset the data from a relational table to be optimally scanned. The ability to combine indexes is very useful to answer ad hoc queries for which no specific index exists. This requirement may be met by using index ANDing and index ORing techniques.

Abstract: A method for localizing execution of subqueries and determining collocation of execution of subqueries in a shared-nothing database. The concept of compatible partitioning is used to localize database operations in order to eliminate excess processes and communication, and thereby improve response time and throughput for the database management system. The method reduces the number of process by reducing the number of nodes involved in processing a query and by combining multiple processes.

Abstract: In parallel database management systems, database update requests typically result in activity at several nodes. Rollback of all updates is required if the update of any node fails. A coordination process monitors for failure or success of updates. The coordinator further provides for distinguishing activities that have taken place at any given node from the other nodes for different database update requests. Savepoints are local. This allows rollback of a selected update without affecting nodes which did not process the update.

Abstract: A procedure for detecting a reordering requirement in a directed record stream during query execution in a relational database processing system. The query compiler component of a relational database processing system includes procedures for building query execution plans (QEPs) for evaluation preparatory to selecting an optimal plan for execution. These plans are constructed from the bottom up using an internal graphical representation for the user query that has a number of relation nodes interconnected by directed record streams (data flows). A relational operation within each node imposes an "order requirement" on the outflow stream represented by an order requirement vector O.sub.R. The records within each directed record stream have an "order property" represented by an order property vector O.sub.P. Order detection occurs when these two vectors are compared to determine whether the order property satisfies the order requirement.

Abstract: Disclosed is a system and method for applying structured query language on user application files not conforming to the definition of a base relation of a relational database management system. A relational database management system has a runtime supervisor and a data manager. For the use of the data manager a plurality of library procedures generalized for manipulation of user application files are provided installed with the database management system. Responsive to user selection, a class for user application files is defined by naming members of a set of the library procedures for use with member application files of the class and by naming the class. Further responsive to user selection, column definitions on an application file within the class are made to create a user defined table. The user defined table corresponds to a specified user application file and to the definition.

Abstract: A method, system and process for providing an improved database blocking technique for updatable and scrollable cursors is disclosed. The invention is facilitated by the operation of a process in the memory of a processor. The processor, under the control of the process, builds a reserved area in the memory of the computer. The reserved area in the memory contains an image of a portion of the information residing on disk in the database. When an application opens a cursor to a database, the type of blocking is specified for that cursor. The application issues a fetch request, and if the row is not in the block in the reserved area in the memory, then a remote procedure call is performed to get the required next block of rows and return the block containing the rows back to the application. Various lock data structures are employed to manage and control the processing.

Abstract: A system and method for enforcing referential integrity within a database system provides for determination of relevant referential constraints at compile time for an access plan. All referential constraints which are relevant to data changes to the database are identified when the access plan is created. Code necessary for performing referential constraint checks is inserted directly into the access plan at compile time, eliminating the necessity for determining the relevant constraints at run time. Constraint checking can be performed using substantially the same low-level procedural statements already used in the access plan for performing database accesses.

Abstract: A method and apparatus are disclosed for maintaining the referential integrity of a relational database by determining the validity of each new referential constraint which is brought about by the proposed creation or alteration of a data table within the database. All relationships of a newly created or altered data table are first examined for known non-error conditions in which the new referential constraint will not affect other data tables. Next, self-referencing relationships are examined to detect known error situations.

Abstract: In a relational database system, a method is utilized that increases the performance of the sort operation. An optimizer routine in the relational database manager analyzes a user's complete query to determine whether the final sorted results can be used directly by the user as sorting occurs. If the sort results can be used, the sort results are sent to Relational Data Services in the relational database manager for output to the user. Depending upon the determination made by the optimizer routine, one of two output modes for the final sorted sequence string of data are selected by the relational database manager. In disk output mode, the last pass of the final sorted sequence spring is written to disk. In fast direct output mode, the records of data are sent to the user as the final sorted sequence string is being merged during the last pass. The system and method of this invention reduces the total sort time by eliminating the overhead of writing the disk during the fast direct output mode.

Abstract: A method for localizing execution of subqueries and determining collocation of execution of subqueries in a shared-nothing database. The concept of compatible partitioning is used to localize database operations in order to eliminate excess processes and communication, and thereby improve response time and throughput for the database management system. The method reduces the number of process by reducing the number of nodes involved in processing a query and by combining multiple processes.