A Db2 Analytics Assessment can help you get more from Db2 for z/OS

The following article was provided by Cuneyt Goksu from IBM:

The Db2 Analytics Accelerator for z/OS (Accelerator) has improved the performance and reduced the resource consumption of hundreds of Db2 for z/OS applications. Db2 for z/OS Data Gate (Db2 Data Gate) provides synchronized Db2 data to cloud-based applications reducing read-only operational processing and the associated zSystems resources and offers query redirection and acceleration.

Many organizations want to understand more specifically how these technologies could improve their unique workloads. That is why IBM developed the Db2 Analytics Assessment process.

A workload assessment can help evaluate Db2 for z/OS workload and identify the benefits of routing a portion of the SQL execution to the Accelerator or Db2 Data Gate.

The “Accelerator Modeling” feature in Db2 for z/OS adds information during (dynamic or static) SQL statement execution to your SMF data. Then this data is used for analysis. An Accelerator does not have to be connected or configured for the system to perform this analysis.

Optionally, you may use EXPLAIN for individual SQL statements to analyze SQL level eligibility for acceleration.

A query workload assessment can be useful in the following scenarios:

• You have not used an Accelerator yet, but you want to analyze whether existing Db2 for z/OS queries or workloads would benefit from acceleration.

• You already use an Accelerator, but you want to know whether new queries or workloads would benefit from acceleration before you prepare accelerators for running these queries or workloads.

A sample analysis

In this example, the same workload is executed in different times and its eligibility was analyzed.

• Elapsed Time and CPU Time eligibility is monitored.

• How much of the workload was eligible in which part of the execution is assessed. 

In this example, another workload is executed in different times and eligibility on PLAN level is calculated.

SMF has valuable information about eligibility.

• QWAC_ACCEL_ELIG_ELA, the accumulated elapsed time spent processing SQL in DB2 that may be eligible for execution on an accelerator

• QWAC_ACCEL_ELIG_CP, the accumulated CPU time spent processing SQL in DB2 that may be eligible for execution on an accelerator

Those fields can be found in Statistics Reports such as a sample section from Omegamon for Db2

For static SQL statement assessment, you must issue a BIND or REBIND PACKAGE for the DB2 packages you plan to include for this workload assessment. You may use option APREUSE(ERROR) with the REBIND PACKAGE statement to make sure that the current access plan remains unchanged. ACCELMODEL=YES is required during BIND/REBIND only, not at static SQL statement execution time

Full details of the workload assessment are documented in this Technote and Statement based Accelerator Modeling can be found here. https://www.ibm.com/support/pages/node/725265