Solution Sheet

Syncsort Optimize DB2

Syncsort Optimize DB2 is a comprehensive family of tuning solutions with each one available separately, or together as a fully integrated suite.

Retrieve CPU capacity with advanced Db2 tuning with Syncsort Optimize DB2

It’s a complicated job to achieve optimal speed and efficiency in the operations of IBM’s Db2 in a production environment and then to maintain it.

But the job is important because it is not uncommon to find Db2 operations that are consuming more than half of mainframe CPU time and putting the squeeze on other critical applications. That means the returns on optimization can be substantial in terms of all-around performance as well as cost savings in CPU usage.

Top DBA concerns

Relational database administrators worry about three things: (1) application response times, (2) throughput, and (3) mainframe CPU utilization — not necessarily in that order. And they have to keep those parameters at reasonably optimal levels.

Those worries are being intensified by growing volumes of dynamic SQL that emanate from distributed Java and .Net applications, as well as ad hoc query reporting tools and packaged applications like SAP® and PeopleSoft®.

As always, the challenge is to ensure that SQL performance meets SLAs and other service-quality goals while avoiding excessive CPU consumption. The latter also means avoiding or postponing costly upgrades.

Also, it is not uncommon to see Db2 shops operating with reduced staff levels and less than adequate optimization tools. Most such tools are essentially performance monitors that are constantly checking on the various events taking place in real time. Knowing the real-time status of these operations is important, of course, but Db2 shops also need a solution that supports a complete view of complex workloads, historic as well as real time, and a solution that enables database administrators to determine quickly where realizable savings exist. This describes a workload-centric approach, and that is where Syncsort Optimize DB2 comes in.

Beyond real time

As an SQL workload performance optimization suite, Syncsort Optimize DB2 goes beyond the capabilities of traditional Db2 tuning tools. While it shares many features of traditional tools, which focus on real-time monitoring of SQL statements and historical analysis of static SQL workloads, it also takes an end-to-end “workload-centric and workload-aware” approach.

That means capturing an SQL workload, then identifying ‘top-n’ SQL statements by consolidating statistics on the total CPU consumed across all instances of like-SQLs in the workload.Syncsort Optimize DB2 consolidates statistics for “otherwise identical” statements where such statements have different literals and other variable information.

In effect, Syncsort Optimize DB2 determines how the workload is distributed across its consolidated SQL statements (“Workload SQL Distribution”), then provides the tools to leverage the information to help better manage and optimize workload performance. See the accompanying illustration of the Syncsort Optimize DB2 environment.

Syncsort Optimize DB2 features

  • Multidimensional analysis of workloads.
  • Ability to see beyond cosmetic differences in otherwise identical SQL statements.
  • “Weighting” of workload SQL distribution
  • Capability for full, automatic analysis
  • Automatic accommodation of Db2 environmental changes


Optimize DB2