• Contact Us

    info@xtivia.com

     

    (888) 685-3101
    Option 2

     

Bringing Business Insight and Performance to Your OLTP, Data Warehouse and Big Data Platforms

IBM DB2 Health Check and Performance Tuning

Breathe new life into your database with XTIVIA's DB2-CPR

ibm db2 health checks and performance tuningThe DB2 Configuration and Performance Review (DB2-CPR) is targeted at assessing the performance, stability and availability of your DB2 LUW based systems. This IBM DB2 health check analysis can be focused on performance, security, migration, upgrade, or availability issues or concerns. If the main role of your environment is on-line transactional processing (OLTP) or decision support system (DSS), or some hybrid, the review can focus on what is important to you and your business needs. An independent or impartial review of your DB2 environment can sometimes help when teams may be set in their ways or need a little guidance. The goal is to help you save time, money and avoid headaches.

The results of a analysis are documented 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 you may have. The XTIVIA DB2 UDB 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.

DB2-CPR is a carefully crafted program designed to review the efficiency as well as effectiveness of a DB2 UDB-based database management system. The efficiency of the system is evaluated by determining the extent to which the DB2 UDB products have been utilized; essentially, is the system "firing on all cylinders?"

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

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

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. DB2-CPR addresses these issues of efficiency and effectiveness by having a skilled DB2 UDB engineer check the health your DB2 UDB-based computing environment over a one to five day period.

Ideal System Performance

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

  • Hardware resource utilization including CPU, I/O, Memory in either physical and virtual environments
  • Operating System Configuration
  • DB2 UDB Database Manager and its constituent databases
  • Implementation of the physical model
  • Client Communications
  • Application Implementation
  • Operations and Maintenance including indexing, backup, recovery, consistency checks and others.

Each of these components is critically important to the optimal performance of the overall system.

The following are some of the issues related to each system component.

Hardware Resources:

The server hosting your DB2 UDB database relies primarily on three hardware subsystems for efficient performance – CPU, Memory, and I/O. From the database perspective, 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 UDB 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 flavors available of RAID, iSCSI, SATA, SAS, SSD, etc. Ideally, the storage system should respond in a manner such that there will be no queues forming. Additionally, for data warehouse environments, the I/O patterns will be analyzed for bottlenecks and limitations. I/O configuration is the most flexible of the 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 has to be carefully divided among the functions of the server – 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 need to 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. These will be analyzed and addressed. Analysis will be performed to ensure that minimum patch level requirements for DB2 UDB to run smoothly on the platform have been met. Recommendations to install additional OS level software or make OS level configuration parameter changes, which enable DB2 UDB to take advantage of platform specific improvements, will be made.

DB2 UDB Database:

There are an enormous number of items that relate to database performance. However, key elements of this 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. Each component of the analysis will be detailed in the report, which is delivered at the completion of the service.

Client Communications:

There are several optimizations available for connecting to your IBM DB2 UDB system. These will be evaluated for appropriateness in the environment for traditional client server or n-tier connections. Additionally, if clients are connecting to DB2 UDB with older versions of software, there is tremendous opportunity for performance improvements through client library upgrades.

Application Implementation:

Database applications can often be enhanced through techniques that may have been introduced after the application was originally designed. The use of prepared SQL statements, for example, is a common way to gain performance in applications. A discussion with the application developers on-site will lead the engineer to make recommendations for improving the application, or to simply state that the application is 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 enable the engineer to provide recommendations for improving availability of the environment.

Sample workloads can be analyzed and recommendations for incorporating features such as Materialized Query Tables (MQT) or Multidimensional Clustering (MDC) can be made.

Very often users report application slowness or problems which are attributed to the database, XTIVIA has worked with many clients to determine exactly where the issue lies, is it really a database performance issue or an issue with the application request to the database. Working with clients to provide constructive feedback to application vendors produces a better and more efficient user experience

During the Performance Analysis, you will need to provide access to your systems and key personnel if this evaluation is to be thorough and meaningful. Key individuals in your environment will be 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.

Typically a conference call with our technical team to get an overview of your issues or needs and gain some measure of your environment is all it takes. Then we can provide you a proposal and get started when you are ready. So, Contact us today for more information regarding DB2-CPR or other DB2 LUW DBA services.