IBM DB2 LUW Health Check: the right choiceibm-db2-udb-luw-health-checks

Whether your database teams are becoming set in their ways or need a little extra guidance, an impartial review of your IBM DB2 LUW environment can be extremely helpful.

XTIVIA’s DB2 Configuration and Performance Review (DB2-CPR) was developed specifically to assess the performance, stability and availability of your DB2 LUW based systems. This DB2 LUW Health Check can be focused on performance, security, migration, upgrades or availability concerns. Whether the main role of your environment is online transactional processing (OLTP), decision support system (DSS) or a hybrid, the review can focus on what is important your business.

XTIVIA’s DB2-CPR program is a carefully crafted program designed to review the efficiency and effectiveness of your DB2 LUW database management system. The efficiency of the system is evaluated by determining the extent to which the DB2 LUW products have been utilized; essentially, is the system firing on all cylinders—and can it be expected to keep doing so into the foreseeable future? In a complex computing environment, the extent to which these and other issues are addressed will affect the overall efficiency and effectiveness of the systems in place. XTIVIA addresses these issues by having a skilled DB2 LUW engineer check the health your DB2 LUW-based computing environment over a 1-5 day period.

After your review, the results of your analysis will be documented in the form of clear recommendations relating to performance, stability, availability—or the specific focus you requested—of your DB2 database instances. Our team will go over the report and recommendations with you to address any questions or concerns. The XTIVIA DB2 LUW team is then available to assist you with the implementation of any of the recommendations once you approve those efforts. We help clients achieve Increased performance, maximized availability, boosted productivity and peace of mind with their IBM DB2 systems.


The efficiency of a system takes into consideration issues such as:

  • Are the database and operating system properly configured?
  • Is engine performance or query performance tuning needed?
  • Are there sufficient resources (physical or virtual) for database tasks?
  • Have the proper indexes been created?
  • Are critical administrative utilities run regularly?
  • Does the staff have the skills needed to maintain an efficient system?


Ideal System Performance

To achieve optimal performance for a given system, one must ensure optimal performance of each component of the system. The components addressed in the DB2-CPR analysis are:

  • Hardware resource utilization, including CPU, I/O and memory (physical or virtual)
  • Operating system configuration
  • DB2 LUW database manager and its constituent databases
  • Implementation of the physical model
  • Client communications
  • Application Implementation
  • Operations and maintenance: indexing, backup, recovery, consistency checks, etc.


Hardware Resources

The server hosting your DB2 LUW database relies primarily on three hardware subsystems for efficient performance: CPU, memory, and I/O—this is true in physical servers as well as virtual environments. A well-architected system will show optimal CPU utilization. Excessive consumption of CPU by sessions or processes could be indicative of a larger problem.

The I/O subsystem hosting your IBM DB2 LUW system is certainly critical to the performance of the database and applications. The subsystem may be direct-attached storage (DAS), network-attached storage (NAS) or storage area networks (SAN)—and within each, there there are various types available (RAID, iSCSI, SATA, SAS, SSD, etc.)

Ideally, your storage system should respond in a manner that prevents queues from forming. In data warehouse environments, I/O patterns will be analyzed for bottlenecks and limitations. I/O configuration is the most flexible of these resources, since the database and system administrators can easily work together to balance the I/O load across all available resources.

A large server may contain many gigabytes of main memory. The use of that memory must be carefully divided among the functions of the server, including applications, database manager, databases and operating system. The goal is to have ample free memory to meet the peak demands of the workload, while maintaining good cache hit rates in the database. If the database cache is performing well, but there is no free memory, users may suffer from paging and swapping. Bufferpool utilizations must be analyzed, and the adequacy of other instance and database-level memory parameters need to be examined as well.


Operating System: IBM provides a list of parameter recommendations for each platform, and these will be analyzed and addressed. We will also perform a detailed analysis to ensure that you have met minimum patch level requirements for DB2 LUW to run smoothly on the platform. Finally, in order to ensure that your DB2 LUW can take advantage of platform-specific improvements, we will make recommendations regarding the installation of additional OS-level software or configuration parameter changes.


DB2 LUW Database: Key elements of your database performance score will reflect the use of indexes, table fragmentation and I/O balance, optimizer statistics, parameter settings, transaction logging, database layout, session activity, cache utilizations, sorts, buffer pool utilizations, locks, deadlocks and others. These areas will be investigated extensively based on the overall behavior of the database and analyzed in detail in the resulting report.


Client Communications: There are several connection optimizations for the IBM DB2 LUW system. These will be evaluated for appropriateness in the environment for traditional client server or n-tier connections. If you are connecting to DB2 LUW with older software, there is tremendous opportunity for performance improvements through client library upgrades.


Application Implementation: Database applications can often be enhanced with techniques introduced after the application was originally designed. The use of prepared SQL statements, for example, is a common way to gain performance. A discussion with the developers on-site will guide the XTIVIA engineer’s recommendations for improving the application, if need be. We’re also happy to tell you when your applications are well written.

While the above analysis strives to leverage hardware and software to its fullest potential, this area of the assessment strives to provide operational stability to the environment. Looking at batch jobs, backup and recovery strategies, logging strategies, upgrade strategies and test platform capability will help the engineer provide recommendations for improving availability.

We can also analyze sample workloads and make recommendations for incorporating features such as Materialized Query Tables (MQT) or Multidimensional Clustering (MDC). When users report application slowness or other problems which are might be attributed to the database, XTIVIA will work closely with your team to determine exactly where the issue lies—is it really a database performance issue, or an issue with the application request to the database? In many cases, providing constructive feedback to application vendors can produces a better and more efficient user experience.

NOTE: if this evaluation is to be thorough and meaningful, you will need to provide access to your systems and key personnel during the performance analysis. Key individuals may include the DBA, the system administrator, the application team leader and others you identify as being able to provide insight to the design and workings of the current system.


Related Blogs

In a sea of technical advisory services and consulting firms, XTIVIA stands apart by providing innovative technical solutions. Through delivery of hundreds of cutting edge projects and solutions each year, XTIVIA accumulates a rich storehouse of knowledge and ideas that we believe are meant to be shared. Browse our blog posts for practical advice that you can put to use right away.

Contact us today for more information regarding DB2-CPR or our other DB2 DBA services.