Abstract: Embodiments of the invention provide techniques for aggregating database queries for energy efficiency. In one embodiment, queries received by a DBMS are aggregated and staged according to hard-disk drives required for query execution. Each group of queries accessing a given drive may be dispatched for execution together. Further, the queries received by a DBMS may be matched to patterns of previously received queries. The matching patterns may be used to predict other queries which are likely to be received by the DBMS. The received queries may be staged to be dispatched with the predicted queries. By aggregating queries to be executed, access to each hard-disk drive may be optimized, thus reducing the overall energy consumption required for executing the queries.

Abstract: Embodiments of the invention provide techniques for aggregating database queries for energy efficiency. In one embodiment, queries received by a DBMS are aggregated and staged according to hard-disk drives required for query execution. Each group of queries accessing a given drive may be dispatched for execution together. Further, the queries received by a DBMS may be matched to patterns of previously received queries. The matching patterns may be used to predict other queries which are likely to be received by the DBMS. The received queries may be staged to be dispatched with the predicted queries. By aggregating queries to be executed, access to each hard-disk drive may be optimized, thus reducing the overall energy consumption required for executing the queries.

Abstract: A method, computer program product and computer system for providing SQL query performance advices to optimize SQL queries of a database, which includes providing a query cache to store records of optimized queries of the database, creating an event-driven web service, sending the records from the query cache to the web service, and analyzing the records using the web service to form SQL query performance advices. The method, computer program product and computer system can further includes outputting the SQL query performance advices to a viewer for display, or outputting the advices to a post-processing application for additional actions.

Abstract: A method, apparatus and program product are provided for performing a query of a database. A database query is received, which includes first and second operations respectively configured to generate first and second disjoint results sets from a dataspace. The database query is analyzed to identify a set of attributes from the dataspace that are used by at least one of the first and second operations in the database query. During execution of the database query, a plurality of records from the dataspace is iteratively processed in a single pass, including, for each of the plurality of records, processing such record by retrieving the plurality of attributes for such record from the dataspace and performing each of the first and second operations on the record using the retrieved attributes for such record to build the first and second disjoint results sets.

Abstract: A stream data node receives real-time streamed data from one or more input devices, dynamically filters the streamed data to reduce the streamed data, and delivers the reduced data when requested. By providing real-time filtering of the data, the amount of data that must be stored in a database may be substantially reduced. The stream data node can perform aggregation functions, group functions, and select functions, thereby also significantly reducing the amount of data that must be stored in a database. The stream data node may also be part of a query execution data structure, where it delivers its data when requested by another node in the query execution data structure.

Abstract: Methods, systems, and computer program products are provided for executing database rollup queries. Methods can include iterating through a database table which has been grouped and ordered on the different columns which are in the ROLLUP clause. In some embodiments, a GROUP BY ROLLUP construct can be executed while only requiring an additional one storage location per ordered column per each aggregate function to be performed on each database row. The higher level aggregate functions can be executed without relying on accessing any lower level aggregate results in some embodiments. A suitably grouped and ordered database table can have a multiple level hierarchical ROLLUP function executed in a single pass without having to retrieve lower level aggregate results.

Abstract: This is an example of a new method for optimization of database queries which uses an asynchronous task which keeps the most current status of the database objects by directly interfacing the existing storage management system. This asynchronous task in combination with query plan and cache data is able to aid in detecting and choosing “warm” query costing alternatives. The cold/warm status information of the asynchronous task helps query optimizer to determine: if it is suitable to use “warm” costing for a new query optimization; validate previous decisions to use a “warm”-optimized query plan, when it comes to potentially reuse and run with such a plan again; decide whether it's worthwhile to still cache such a plan in the plan cache.

Abstract: A method, apparatus, and program product are provided for preserving a query plan cache on a backup system having a dataspace containing a copy of data and a copy of a query plan cache from a production system. An update is initiated of at least a portion of the copy of the data on the backup system with a portion of the data on the production system. A merge is initiated of updated query plans in the query plan cache from the production system with corresponding query plans in the copy of the query plan cache on the backup system. Objects are correlated in the updated query plans in the copy of the query plan cache with the updated copy of the data on the backup system.

Abstract: Methods, systems, and computer program products are provided for improving the processing of database queries. Some embodiments include detecting an overly long running query execution plan, stopping execution of the plan, and utilizing the query optimizer to select an alternate plan for execution. Embodiments may utilize system timers which are set with a time limit for the first selected plan, with instructions to re-optimize and initiate selection of an alternate plan when the timer times out. Risky constructs may be located in the first plan and removed from future consideration for executing the query. In some embodiments, the additional overhead for non-problem queries consists of little more than creating and sending a message or setting a timer when starting query execution, and canceling a timer upon successful query processing completion. A secondary thread may be created responsive to detecting a long running query, to supervise selecting an alternate execution plan.

Abstract: A stream data node receives real-time streamed data from one or more input devices, dynamically filters the streamed data to reduce the streamed data, and delivers the reduced data when requested. By providing real-time filtering of the data, the amount of data that must be stored in a database may be substantially reduced. The stream data node can perform aggregation functions, group functions, and select functions, thereby also significantly reducing the amount of data that must be stored in a database. The stream data node may also be part of a query execution data structure, where it delivers its data when requested by another node in the query execution data structure.

Abstract: An index advice record engine generates and stores index advice records. An index advice policy mechanism allows a user to define an index advice policy that specifies criteria for autonomic index creation, modification and deletion. An autonomic index mechanism reads the index advice records, compares this information with the criteria in the user-defined index advice policies, and determines whether an index should be created, modified or deleted based on the information in the index advice records and the index advice policies. By automating the process of creating, modifying and deleting indexes according to user-defined policies, the preferred embodiments alleviate human users from most of the work of manually creating, modifying and deleting indexes.

Abstract: An index advice record engine generates and stores index advice records. An index advice policy mechanism allows a user to define an index advice policy that specifies criteria for autonomic index creation, modification and deletion. An autonomic index mechanism reads the index advice records, compares this information with the criteria in the user-defined index advice policies, and determines whether an index should be created, modified or deleted based on the information in the index advice records and the index advice policies. By automating the process of creating, modifying and deleting indexes according to user-defined policies, the preferred embodiments alleviate human users from most of the work of manually creating, modifying and deleting indexes.

Abstract: A query facility for database queries saves and re-uses query execution strategies, and automatically determines whether a strategy can be re-used when an imported variable changes. The query facility automatically saves one or more logical conditions associated with the one or more imported variables in the query. When the query is reused, the logical conditions are evaluated using the imported variable values, and the previously saved execution strategy is used only if the condition or conditions are met. Preferably, the logical conditions express a range of imported variable values which are included in a database subset data structure used by the saved query execution strategy, such as a materialized query table or partitioned table. Logical conditions are preferably generated by negating the predicates from a logical expression defining the applicability of the execution strategy, inserting the negated predicates into the query, and performing a transitive closure.

Abstract: A method, apparatus and program product to optimize a database query. In a first system having a first dataspace, a query plan is generated for the database query. Remote statistics collection associated with the database query is initiated on a second system having a second dataspace. The remote statistics collection uses the second dataspace, which includes a current copy of at least a portion of the first dataspace on the first system. Database statistics are collected for the first system by analyzing the second dataspace and then communicating the collected database statistics to the first system for use in optimizing database queries executed by the first system.

Abstract: A query optimizer optimizes a query to a partitioned database table by determining common characteristics of the partitions and generating a virtual maintained temporary index that spans multiple partitions. Using the virtual maintained temporary index allows the query optimizer to generate an access plan based on the virtual maintained temporary index, which relieves the optimizer from having to individually optimize access to each partition for partitions that share common characteristics.

Abstract: An apparatus and method for a multi-partition query governor in a partitioned computer database system. In preferred embodiments a query governor uses data of a query governor file that is associated with multiple partitions to determines how the query governor manages access to the database across multiple partitions. Also, in preferred embodiments, the query governor in a local partition that receives a query request communicates with a query governor in a target partition to accumulate the total resource demands of the query on the local and target partitions. In preferred embodiments, a query governor estimates whether resources to execute a query will exceed a threshold over all or a combination of database partitions.

Abstract: A database query optimizer includes an access plan storage mechanism that stores execution detail corresponding to a query statement in an access plan cache. By storing execution detail in the access plan cache along with the query statement, the execution detail is available when needed to debug database problems, even after a problem occurs. The execution detail stored in the access plan cache may be mined and analyzed after-the-fact, producing a very powerful tool for debugging database problems. Snapshots of the execution detail may also be created by a user. In addition, the user may setup autonomic snapshots and comparisons so that a user may be alerted from a degradation in database performance between two snapshots.

Abstract: An apparatus, program product and method identify a range of values in a table and advise the generation of a maintained index over the identified range of values. Additionally, a method that determines a range of values and generates a maintained temporary index is also provided. By doing so, the maintained index that was advised may be generated over the range of values in the column and used to access data in the table.

Abstract: If the cost of an access plan meets a condition, and an exit program is registered, data is logged for the access plan if logging is requested by a return code from the exit program. Execution of the access plan is continued or canceled if requested by the return code. In another embodiment, an inquiry message is sent to an application, the data is logged and execution of the access plan is continued or canceled if requested by a response to the inquiry message. In another embodiment, the data is logged if the logging if an override parameter requests the logging. In various embodiments, the cost of the access plan is calculated by predicting a predicted time for the execution and a predicted amount of storage needed for the execution or by calculating the actual time and the actual amount of storage used by partial execution of the access plan.

Abstract: An index advice record engine generates and stores index advice records. An index advice policy mechanism allows a user to define an index advice policy that specifies criteria for autonomic index creation, modification and deletion. An autonomic index mechanism reads the index advice records, compares this information with the criteria in the user-defined index advice policies, and determines whether an index should be created, modified or deleted based on the information in the index advice records and the index advice policies. By automating the process of creating, modifying and deleting indexes according to user-defined policies, the preferred embodiments alleviate human users from most of the work of manually creating, modifying and deleting indexes.