Virtual Db2 User Group Recap | Presented by Denis Tronin, Broadcom
When it comes to Db2 for z/OS performance, a well-written SQL statement is only part of the equation. The access path selected by the Db2 optimizer determines how the database retrieves data, which indexes it uses, and ultimately how efficiently a query executes.
But what happens when an access path changes unexpectedly? Or when an otherwise routine REBIND causes a performance regression?
During a recent Virtual Db2 User Group session, Denis Tronin of Broadcom explored these challenges in Mastering Access Path Management in Db2 for z/OS: Simplify, Optimize, and Succeed. His presentation covered techniques for understanding, preserving, and controlling access paths, from EXPLAIN and statistics management to package management, dynamic SQL stabilization, and optimization hints.
Here are 10 practical takeaways for Db2 professionals looking to improve performance and reduce surprises.
1. Understand What Influences an Access Path
An SQL statement describes what data should be retrieved, but it doesn’t necessarily specify how Db2 should retrieve it.
That decision belongs to the Db2 optimizer, which evaluates multiple factors to select an execution strategy.
These factors include:
- SQL statement structure, including predicates, joins, subqueries, and functions
- Available indexes and their characteristics
- Object statistics, including cardinality and data distribution
- System configuration, including buffer pools, processor capacity, and subsystem parameters
- Optimization hints that influence the optimizer’s decisions
The distinction between static and dynamic SQL is also important.
For static SQL, access paths are established during BIND or REBIND and stored with the package. Dynamic SQL typically undergoes preparation when executed, although caching and stabilization can help preserve previously selected paths.
Understanding these differences provides the foundation for effective access path management.
2. Make EXPLAIN Your First Stop for Troubleshooting
One of Tronin’s central recommendations was to use EXPLAIN to understand how Db2 plans to execute SQL statements.
EXPLAIN externalizes access path information into a collection of tables that administrators and developers can analyze.
While many professionals are familiar with the traditional EXPLAIN PLAN statement, Tronin highlighted several additional capabilities worth knowing.
EXPLAIN PACKAGE allows administrators to retrieve access path information from an existing package, even if the package was originally bound without EXPLAIN(YES).
EXPLAIN STMTCACHE provides access path information for dynamic statements already held in the statement cache.
These capabilities are particularly valuable when investigating existing workloads rather than testing newly written SQL.
Db2 13 provides approximately 20 EXPLAIN tables, but Tronin identified several that deserve particular attention:
- PLAN_TABLE: Access methods, indexes, join operations, sorting, and other execution details
- DSN_STATEMNT_TABLE: Optimizer cost estimates and related information
- DSN_PREDICAT_TABLE: Details about predicates used in SQL execution
- DSN_STATEMENT_CACHE_TABLE: Information about statements captured from the dynamic statement cache
- DSN_STAT_FEEDBACK: Feedback about potentially missing or conflicting statistics
Together, these tables provide a much clearer picture of why an access path was selected.
3. Pay Attention to the Optimizer’s Cost Estimates
The Db2 optimizer uses available information to estimate the cost of executing a statement.
However, not all estimates are equally reliable.
Tronin highlighted the cost category information available in DSN_STATEMNT_TABLE.
A cost category of A indicates that the optimizer had sufficient statistical information to produce its estimate without relying heavily on defaults.
A cost category of B indicates that some information was unavailable or uncertain, requiring the optimizer to make assumptions.
The table also provides information about why a particular cost category was assigned.
This can be useful when investigating a query that performs differently than expected.
Rather than immediately changing SQL or introducing optimization hints, administrators can first determine whether missing statistics or other uncertainty contributed to the optimizer’s decision.
4. Keep Statistics Current, but Don’t Collect Everything
Accurate statistics are essential to efficient access path selection.
Db2 relies on RUNSTATS information to understand the characteristics of tables, indexes, and data distributions.
However, Tronin cautioned that collecting excessive or unnecessarily complex statistics can create additional overhead during optimization, particularly during BIND and PREPARE operations.
His recommendations included collecting statistics alongside LOAD and REORG activities when appropriate, rather than always scheduling RUNSTATS as a separate operation.
He also encouraged administrators to use Db2’s statistics feedback capabilities to identify where additional information may be needed.
The DSN_STAT_FEEDBACK table and related catalog feedback mechanisms can help identify missing, conflicting, or questionable statistics.
One particularly important warning concerned statistics profiles.
Using RUNSTATS USE PROFILE can remove existing statistics that are not included in the profile. Administrators should therefore understand the profile’s contents before relying on it.
Tronin also discouraged manually modifying catalog statistics simply to force a preferred access path. Changes to an object’s statistics can influence multiple statements, potentially creating unintended consequences elsewhere.
The goal is not to collect the maximum possible statistics. It is to collect the information the optimizer actually needs.
5. Use Extended Plan Management as a Safety Net
Access path changes are sometimes unavoidable, especially when packages are rebound.
Extended plan management provides a way to preserve previous package copies and recover from undesirable changes.
With PLANMGMT(EXTENDED), Db2 maintains three package copies:
- Original: The package copy preserved from the original bind
- Current: The active package copy
- Previous: The preceding package copy
This gives administrators options when a REBIND introduces an access path that performs poorly.
Rather than immediately attempting to reconstruct the previous configuration, administrators can use the SWITCH functionality to activate a preserved package copy.
Tronin strongly recommended extended plan management as a practical safeguard against performance regressions.
For organizations managing large numbers of static SQL packages, this relatively simple configuration can provide valuable recovery options.
Don't miss these other great articles
6. Use APREUSE and APCOMPARE to Control REBIND Changes
Db2 provides two useful parameters for managing access path changes during BIND and REBIND operations.
APCOMPARE identifies whether the access path has changed.
APREUSE attempts to reuse an existing access path.
Although the two features serve different purposes, Tronin recommended using them together.
For example, an organization willing to accept access path changes but wanting visibility into them might use APCOMPARE(WARN).
A more conservative strategy could combine APREUSE(WARN) and APCOMPARE(WARN) to encourage reuse of existing paths while reporting differences.
For environments where access path stability is critical, error-level settings can prevent a REBIND from completing when the specified conditions cannot be satisfied.
The important point is that APCOMPARE detects changes, while APREUSE attempts to prevent them.
Neither feature should be treated as a substitute for evaluating performance, but together they provide greater control over the REBIND process.
7. Dynamic SQL Can Be Stabilized, Too
Static SQL benefits from package management capabilities, but dynamic SQL presents a different challenge.
Dynamic statements can be prepared repeatedly, and changes in statistics, indexes, or system conditions can influence the access paths selected.
Db2’s dynamic SQL statement stabilization capability addresses this issue by preserving selected dynamic SQL access paths in the Db2 catalog.
Tronin explained that administrators can use the START DYNQUERYCAPTURE command to stabilize statements from the dynamic statement cache.
Statements can be selected individually or based on execution-frequency thresholds.
For example, an administrator could stabilize frequently executed statements that exceed a specified number of executions.
The capability can also operate in monitoring mode, capturing statements that meet the criteria during a defined period.
Once stabilized, Db2 can retrieve the preserved access path rather than performing the normal optimization process when the statement must be loaded again.
There is an important limitation: unlike extended plan management for static SQL, dynamic SQL stabilization preserves only one current access path copy.
Even with that limitation, stabilization offers an important option for controlling performance variability in frequently executed dynamic workloads.
8. Know When to Use Reoptimization
One challenge in access path selection involves host variables.
When SQL contains literal values, the optimizer may have more information about the specific values being evaluated.
With host variables, that information may not be available when the original access path is selected.
Db2 provides the REOPT bind option to address this situation.
Depending on the setting, Db2 can reoptimize eligible statements using actual parameter values at execution time.
Options include reoptimization once, always, or automatically when Db2 determines it is appropriate.
However, reoptimization involves tradeoffs.
Performing optimization more frequently can improve access path selection for certain workloads, but it also introduces additional processing overhead.
Tronin emphasized the importance of understanding these options rather than automatically enabling more aggressive reoptimization.
As with most Db2 performance decisions, the best approach depends on the workload.
9. Prefer Statement-Level Optimization Hints Over Older PLAN_TABLE Hints
Sometimes administrators need to influence an access path directly.
Db2 supports optimization hints that can guide the optimizer toward particular execution strategies.
Tronin discussed two approaches: traditional PLAN_TABLE hints and newer statement-level hints.
PLAN_TABLE hints rely on information such as query numbers, package names, and other identifiers. They require the relevant PLAN_TABLE information to be available when the hint is evaluated.
Statement-level hints offer a more modern alternative.
They are associated with SQL statement text and can be scoped to specific package versions, packages, or the subsystem.
To create a statement-level hint, administrators use the DSN_USERQUERY_TABLE and appropriate EXPLAIN information, then issue the BIND QUERY command.
Once created, the hint is stored in the Db2 catalog.
This removes the ongoing dependency on the input tables used during hint creation.
Tronin recommended statement-level hints because they offer more flexible management and are based on capabilities Db2 also uses internally for access path reuse and dynamic SQL stabilization.
10. Treat Optimization Hints as Controlled Interventions
Optimization hints can be powerful, but they require care.
During the session, Tronin demonstrated how to influence a statement’s access path by modifying the relevant EXPLAIN information and creating a hint.
His example changed an index-based access path to a table scan to demonstrate how the mechanism works.
In practice, the desired change would depend on the actual performance characteristics of the workload.
A few operational considerations stood out.
First, statement text matters. When creating statement-level hints, Tronin recommended retrieving the exact SQL text from the statement cache rather than manually retyping it.
Second, administrators should clean up input tables after creating hints. The BIND QUERY command processes entries in the input table, so leaving outdated entries could result in unintended hint creation during later operations.
Third, hint scope matters. When multiple hints could apply, Db2 gives precedence to more specific definitions, such as a package-version-level hint over a subsystem-wide hint.
Finally, hints should remain manageable. The FREE QUERY command can remove hints that are no longer needed.
The larger lesson is that optimization hints should be deliberate, documented interventions rather than the first response to every performance concern.
Building a More Predictable Db2 Environment
Access path management is ultimately about balancing performance improvement with operational stability.
Db2’s optimizer is designed to select efficient execution strategies, but changes in data, statistics, SQL, and system configurations can affect those decisions.
Tronin’s presentation demonstrated that Db2 professionals have several tools available to understand and manage those changes.
EXPLAIN provides visibility. Statistics feedback helps identify gaps in optimizer information. Extended plan management offers recovery options. APREUSE and APCOMPARE provide controls during package maintenance. Dynamic statement stabilization and optimization hints offer additional ways to preserve or influence execution strategies.
Used together, these capabilities can help organizations reduce unexpected performance regressions while maintaining the flexibility to optimize workloads as requirements change.
The key is knowing when to trust the optimizer, when to investigate its decisions, and when intervention is justified.
0 Comments