News
Abstract
Following in the tradition of continued investment in the SQL Query Engine (SQE), IBM i 7.5 includes enhanced parallel processing capability in SQE.
Content
You are in: IBM i Technology Updates > Db2 for i - Technology Updates > Db2 for i Performance Enhancements > SQE - Improved SMP processing
When an IBM i client has licensed program product (5770SS1 - Option 26) DB2 Symmetric Multiprocessing installed, it is possible to leverage the current degree and achieve parallel execution for SQL queries, amongst other database operations.
While there are many techniques and settings that can be used when SMP is enabled, the best practice is to use *OPTIMIZE.
When Db2 Symmetric Multiprocessing (SMP) is enabled, the PARALLEL_DEGREE value *OPTIMIZE
enables the query optimizer to determine the optimal number of tasks with which to run a query. The
optimizer selects this value based on the available processor capacity and the costs to perform aspects of the query.
enables the query optimizer to determine the optimal number of tasks with which to run a query. The
optimizer selects this value based on the available processor capacity and the costs to perform aspects of the query.
In IBM i 7.5, SQE is enhanced when *OPTIMIZE is used for parallel execution of a query:
- When a percentage is specified for *OPTIMIZE, SQE will apply the percentage prior to optimization. This will provide the optimizer with more accurate information related to the number of tasks a query may run with.
- Starting in IBM i 7.5, the query engine will dynamically adjust the number of tasks used by queries
running with PARALLEL_DEGREE *OPTIMIZE. As these queries run, the query engine will consider the overall system CPU usage. - SQE will additionally recognize when the system CPU is reaching a peak level, and automatically reduce the amount of parallel processing used by active queries. The default peak level where this safety adjustment kicks in is 80%, but can be adjusted higher or lower via the new QAQQINI parameter PARALLEL_MAX_SYSTEM_CPU.
Simply by moving to IBM i 7.5, production workloads will have improved stability and safety when using *OPTIMIZE and parallel query execution.
[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB57","label":"Power"}}]
Was this topic helpful?
Document Information
Modified date:
03 May 2022
UID
ibm16579159