International Business Machines Corporation

Shawn Joseph Baranczyk

Brian Robert Muras

Rafal Przemyslaw Konik

Roger Alan Mittelstadt

Paul Reuben Day

Robert J Bestgen

A sequence object manager provides a sequence object with a dynamic cache block size that indicates a block size of values to be added to the sequence object when the cache values are exhausted. The dynamic block size allows the sequence object manager to optimize performance and storage space depending on applications using the sequence object. The dynamically block size is set and maintained by the sequence object manager based on observed performance and historical trends of the applications. A seed value may be provided by the user to initially set the dynamic block size.

Database sequence object having a dynamic cache block size

A query is received. Based on statistics for a database, a first query access plan is generated for executing the query on the database. A first result set for the query is obtained by implementing the first query access plan on the database. Based on at least one trend in the statistics, a target time after which implementing the first query access plan will fail a set of criteria is forecasted. In response to the forecasting and based on the statistics, a replacement query access plan is generated prior to the target time. After the target time, a second result set for the query is obtained by implementing the replacement query access plan rather than the first query access plan.

Forecasting query access plan obsolescence

Systems, methods, and computer program products to perform an operation comprising receiving, by a database management system (DBMS) from an application, a request specifying to generate a global temporary table (GTT) of a first format, identifying a first GTT, of a plurality of GTTs stored by the DBMS, that has a second format, different than the first format, modifying the first GTT such that at least a portion of the modified first GTT is of the first format, and providing the modified first GTT to the application.

Dynamic and predictive global temporary tables

Disclosed aspects relate to query hint learning in a database management system. A hint in a first query is detected by the database management system. The first query has a first signature. A second query is received by the database management system. The second query has a second signature. A determination is made by the database management system that the second signature correlates with the first signature. A query plan is established by the database management system. The query plan is based on the second query and uses the hint in the first query. In response to receiving the second query, the database management system processes the query plan.

Query hint learning in a database management system

Disclosed aspects relate to query hint management for a database management system. With respect to a hint for a query, a set of hint profile data is established. The database management system detects a triggering event. Based on the set of hint profile data, the database management system determines to perform a hint action in response to the triggering event. Performance of the hint action is initiated.

Query hint management for a database management system

Techniques are provided for managing a database cursor threshold. One embodiment provides a method that includes setting a database cursor threshold, for a first database job of a plurality of database jobs, to a first value. Generally, the cursor threshold defines the maximum number of database cursors that are permitted to be open at a time in a database system. One or more system resources of the database system are monitored, and upon detecting that a current state of the one or more system resources satisfies predefined criteria, embodiments set the database cursor threshold to a second value.

Adaptive query cursor management

An enhanced mechanism for intelligently managing an open query cursor built from a query access plan (AP) by a query optimizer of a database management system (DBMS). In an embodiment, a cursor management system (CMS) detects whether an open query cursor was previously requested for use within another job. If the open query cursor was previously requested, the CMS intelligently manages the query cursor for subsequent use within a different job. For example, the open query cursor may be recycled for subsequent use within the different job. Hence, system resources are not wasted in building and destroying the same cursors for multiple jobs. Alternatively, the query cursor may be pre-built before the cursor is requested for subsequent use within the different job. The CMS may also obtain, release and re-obtain locks for cursors being managed. Alternatively, the job that uses the cursor may obtain the lock.

Growing community of inventors

Rochester, MN, United States of America

Shawn Joseph Baranczyk