Adaptive workload manager

The adaptive workload manager automatically controls the admission of queries for execution to ensure that concurrently executing queries do not overwhelm the available resources.

Running several queries1 simultaneously can lead to resource competition that, if left unmanaged, can result in system overload and query failure. The database employs an adaptive workload manager that controls admission of queries for execution, based on available resources.

Whether a query is allowed to run depends only on its resource requirements and the resources that are available. There is no fixed limit to the number of queries that can run concurrently. If a query is submitted to the database server but there are insufficient resources available to run the query, it is queued. The query is later admitted for execution, when resources become available.

The adaptive workload manager automatically adjusts the workload concurrency (that is, which parts of the workload can run together) to run the optimal number of queries while maintaining stability of the database and avoiding errors. It also uses a combination of both latency-sensitive and resource-based job scheduling to ensure reliable throughput (that is, it tries to ensure that small queries are not held up by larger queries while still ensuring fairness across different classes of queries).

Adaptive workload manager can be enabled by completing the migration procedure using the stored procedure WLM_ENABLE_ADMISSION_CTRL. When the adaptive workload manager is enabled, it controls admission of work for all the Db2 service classes in a database. It is not possible to enable admission control for a subset of service classes. This is necessary to maintain the stability of the database.

Compared to fixed concurrency limits, the resource-based admission control performed by the adaptive workload manager offers simplicity, responsiveness, and stability when faced with dynamic workloads that consist of both high volumes of operational point queries and concurrent complex analytic queries of varying shapes and sizes. The adaptive workload manager dynamically adjusts the concurrency based on the needs of the workload and on the available resources.

1 For simplicity, the term "query" is used to refer to the statements controlled by the adaptive workload manager, even though it controls both SELECT statements (queries) and INSERT statements. The adaptive workload manager does not control CALL statements.