Use this comparison to identify SQL performance changes
in packages that were rebound due to SQL statement changes, environment
changes, release migration, or running the RUNSTATS command.
Before you begin
- Connect to a DB2® for z/OS® subsystem
that is configured for query workload tuning.
- Packages to be analyzed must be bound with the EXPLAIN(YES) command.
- Ensure that the packages to be analyzed have the same names in
the source and target collections.
- Use the same authorization ID when performing the BIND operation.
The EXPLAIN output for the BIND operation must point to the same set
of EXPLAIN tables that you used when binding the packages the first
time.
- Ensure that the Workload Control Center stored procedures are
enabled and you have the privilege to access the workload repository.
- You must be authorized to access these tables:
- SELECT, INSERT, UPDATE, and DELETE privileges on the following
tables: DB2OSC.AOC_WAPC_SESSIONS, DB2OSC.AOC_WAPC_RS and DB2OSC.AOC_WAPC_EX
- SELECT privilege on the following DB2 system
catalog tables: SYSIBM.SYSPACKAGE, SYSIBM.SYSPACKSTMT, and SYSIBM.SYSPACKDEP
- SELECT privilege on the EXPLAIN tables (PLAN_TABLE and DSN_STATEMNT_TABLE)
under the package owners
- If you are using DB2 for z/OS Version 8 compatibility
mode, ensure that the encoding schema for the EXPLAIN tables (PLAN_TABLE
and DSN_STATEMNT_TABLE) under the package owners is in EBCDIC.
Procedure
To compare access plans for SQL statements in packages:
- Create a workload for access plan comparison. The workload consists of a set of filters that can be used to
compare the access plans and analyze the performance of SQL statements
in packages. The workload can be stored and reused for future comparison.
- In the Data Source Explorer, connect to the subsystem
with the packages for comparison.
- Expand the connection and open the workflow assistant
by clicking .
- Click the Manage tab to open
the Manage and Tune Workloads page and click the Create
Workload for Comparison icon.
- Run the access plan comparison using the workload generated
in step 1.
What to do next
If the performance of the SQL statements meets your goals,
use the rebound packages. Change and rebuild the application program
as necessary.
If the performance of any SQL statements regressed,
you can take one of these actions.
- Create a query workload with the regressed statements. You can
then tune them collectively or individually. Then, rebind the packages.
- Create a plan hint and rebind the packages to apply the hint.
- If you are using DB2 for z/OS Version
9 or higher, you can revert to a previously stored access plan.