Improve DB2 Performance by Reducing Your Other Time

DB2 performance tuning is always different for every system and application.  In addition, the different DB2 LUW DBM configurations and z/OS zParms provide customizable options to improve your system and application DB2 performance mix.  These parameters are usually standard with the DB2 LUW AUTOCONFIGURE options. The DB2 z/OS standard settings from years of previous versions and suggestions from the continuously improved installation z/OS IVP – (Installation Verification Process) dictate the installations zParm settings.

Recently at three different clients, the application research showed huge amounts of time spent waiting within the application or “Other time spent within the application.”  DB2 performance monitoring showed this through the CLASS 2 time in the DB2 performance monitoring reports on z/OS and through queries of many performance snapshots of SYSIBMADM tables, views, monitoring routines and especially the LOCKWAITS information.

Within one DB2 performance analysis, the performance reports showed that the system and application were spending on average 40% of its time suspended.  Another application client was spending sometimes as much as 35% of the application SQL time suspended while another was spending up to 90% of the time suspended in some SQL statements.  These 35%, 40% and sometimes 90% times are tremendously high, and system and application performance cannot be achieved with this much time waiting within any business process.

Some of these times are affected by the system parameters around lock wait time and timeout contention time.  Pay special attention to these parameters and review the other feeder system parameters to understand your settings for your systems and application profile.  Also, understand how these parameters interact with your web servers, and application code.  A slow web server or hung web transaction will have a huge impact on your processing time.

If you find this type of “Other Time” within your systems or applications also look at the CPU resources within the overall box.  With DB2 LUW, has your environment been virtualized out of CPU resources?  Has the typical I/O for your virtual machine gone above 20ms or spiked above 50ms?  If so, the CPU and I/O resources are under stress. The system and application should be improved by moving into a new virtual location or by giving them more resources within the overall box.

Within the z/OS environment look at the utilization of the system. Is it always 100%. Are the DB2 configuration zParms optimized for the type of web, OLTP or Data Warehousing workload that exists?  The DB2 subsystem priority, the optimization level and lock waiting settings must all be validated to see whether they are appropriate.  This is especially important after a migration to a new DB2 version to make sure a typing error is not hindering your performance.

Also, look to your locking within your application on either the z/OS or LUW platforms.  Does your application serialize access through the use of identity, sequence or surrogate keys that cause locking within your application?  Are there too many levels of referential integrity within your database design for the application?  Check these database design situations out because your application may not be deadlocking, but waiting for your other transactions to be committed.

Review your zParms and DBM configuration setting, Lock waits and resources and you will usually find your main culprits for “Other time spent in your application.”  Fixing the appropriate areas, your overall DB2 performance time and your system “Other Time” should improve significantly.

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>