Monday, December 04, 2023

My Top Ten Db2 Performance Posts

Regular readers of this blog will notice that the past few months have been consumed by my posts outlining my Top Ten Db2 Performance tips.

So, it being December and the end of the year, I thought it would be a good idea to publish a post with links to each of the Top Ten tips... so here it is!

Number 1 - Analyze Query Execution Plans

Number 2 - Optimize Your Database Design

Number 3 - Efficient Indexing Strategies

Number 4 - Effective Memory Configuration

Number 5 - Workload Management and Query Optimization

Number 6 - Monitoring and Performance Metrics

Number 7 - Configuration Parameters and Tuning

Number 8 - I/O Optimization

Number 10 - Stay Up-to-Date

Of course, a list such as this will not be comprehensive in terms of listing everything that you may encounter on your path to efficient Db2 performance... but it can serve as a good place to start that journey.

Let me know your thoughts on anything in this list... or share your performance stories and headaches with us here on the blog!

Tuesday, November 28, 2023

Top Ten Db2 Performance Tips - No. 10 Stay Up to Date

Keeping Db2 up to date is of paramount importance for ensuring the security, stability, and optimal performance of your database environment. For Db2 for z/OS this means formulating a plan for applying PTFs (Program Temporary Fixes) and correlating your DB2 fixes with your system, ideally following IBM's RSU (Recommended Service Upgrade) preventive service philosophy. For Db2 for LUW it means keeping up with fix packs.

Why is it important to stay current with maintenance? And what are the benefits of doing so?

  • Security: Cyber threats and vulnerabilities are ever-evolving, and database systems are potential targets for malicious activities. By staying up to date with the latest patches and fixes, you can ensure that your Db2 systems are protected with the latest security enhancements. Patching known vulnerabilities helps protect sensitive data, prevent unauthorized access, and reduce the risk of security breaches.
  • Bug Fixes and Stability: Software systems, including Db2, are not immune to bugs and glitches. The release of patches and fixes addresses identified issues, improving the overall stability and reliability of the database system. Applying these updates helps mitigate performance issues, software crashes, or data inconsistencies that may arise from known bugs. Keeping your Db2 environment updated with the latest fixes contributes to a smoother and more reliable database experience.
  • Performance: IBM regularly releases performance enhancements for Db2. Applying the latest patches and fixes ensures that you benefit from these improvements, allowing your database to operate at its optimal efficiency. The updates may include query optimization, resource management enhancements, or other performance-related optimizations, helping to maximize the speed and responsiveness of your Db2 systems and applications.

Another important aspect of staying up to date is tracking and upgrading the version and level of your Db2 systems. IBM clearly provides information on the product lifecycle for Db2 (and all of their products). Information for DB2 for z/OS is provided here. The first thing to be aware of is that you always want to be running supported software. 

If your software is not supported and you run into issues or problems, IBM will not provide support for unsupported software. When your software versions reach their EOS date and support is no longer available through standard Software S&S, you have the option to purchase Continuing Support (typically at a higher rate). It is usually more cost-effective and less risky to migrate to the new, supported version. Furthermore, documentation for unsupported versions of IBM software typically is not available in IBM Knowledge Center.

Therefore, it is wise to be watchful of the EOM and EOS announcements made for your current version of Db2. EOM is the End of Marketing date. When this date is reached you can no longer order that version of the software. IBM Db2 12 for a/OS reached its EOM date in June 2023. More important is the EOS, or End of Service, date. After this date is reached that version of the software is out of support. For Db2 12 for z/OS EOS is scheduled for 2023-12-31 (at the time this blog is being published). At times, EOS and EOM date may be revised due to customer and/or IBM needs.

I maintain links to the IBM product lifecycle pages on my web site at

Furthermore, as of Db2 12 for /OS, it is also important to keep up to date with the Function Level for your subsystems. Instead of requiring new versions or releases to provide new functionality, IBM is now using a continuous delivery model. So, instead of waiting years for a new version. This means that you should also be tracking function levels, applying maintenance when needed to support them, and activating new function levels on a continuous basis. Failing to do so means that you will be behind the curve when an eventual new version is released.

To access the latest patches and fixes for Db2, IBM provides resources and support channels that you can leverage:

  • IBM Fix Central: IBM Fix Central is a centralized portal where you can search, download, and apply patches, fixes, and updates for various IBM software products, including Db2. It offers an intuitive interface to locate the required fixes based on specific product versions and platforms.
  •  IBM Support Portal: The IBM Support Portal is a comprehensive resource for accessing product documentation, technical articles, and obtaining support for IBM software. It provides a wealth of information on Db2, including links to downloads, useful articles, and official product documentation.
  •  IBM Passport Advantage: If you have a valid software maintenance subscription or license agreement with IBM, you can access the latest patches, fixes, and updates through the IBM Passport Advantage website. This platform enables authorized users to download software updates and manage their entitlements.

You should also keep up to date by following useful online forums and user groups. IDUG offers a good central site for such groups, and useful DB2 information is also offered by SHARE. Both of these organizations offer vibrant community forums and discussion boards that can provide valuable insights into the latest information about Db2. Furthermore, community members often share their experiences, recommendations, and helpful tips for staying up to date with Db2 updates.

Finally, I have to promote my web site Mullins Consulting, Inc. where I frequently post new information about Db2 as well as my articles and books on Db2.

Summing It All Up

In conclusion, staying up to date with the latest patches and fixes for Db2 is essential for maintaining a secure, stable, and high-performing database environment. By applying the latest updates, you benefit from enhanced security, bug fixes, stability improvements, and performance optimizations. Leveraging the resources available to you (such as those discussed above), and engaging with the Db2 community forums ensures that you have access to the most recent updates and support to keep your Db2 environment current and reliable.

Thursday, November 09, 2023

Top Ten Db2 Performance Tips – No. 9 Regular Maintenance and Housekeeping

When thinking about the performance characteristics of your Db2 databases and applications keep in mind the routine maintenance that can help or hinder your performance. Regular maintenance and housekeeping tasks are essential for ensuring the optimal performance and stability of your Db2 environment. 

By performing routine maintenance activities, database administrators can proactively address performance degradation, optimize query execution, and maintain a healthy database environment. In today’s post, we will highlight the importance of regular maintenance tasks and discuss key activities that contribute to database performance.

Index Maintenance

Indexes play a crucial role in query performance, as they facilitate quick data retrieval. Over time, indexes can become fragmented, leading to increased disk I/O and decreased query performance. Regularly reorganizing indexes helps eliminate fragmentation and enhances query execution efficiency. By scheduling index reorganization tasks based on the fragmentation level and database activity, administrators can maintain optimal index performance and minimize the impact of fragmentation on query response times.

You should also monitor index usage and consider removing any unused indexes. You can identify unused indexes relatively easily using the LASTUSED information in the RTS SYSINDEXSPACESTATS table in the Db2 Catalog.

Tablespace Reorganization

As your database grows and data is modified or deleted, storage space can become fragmented, leading to suboptimal performance. Regularly reorganizing database structures helps to consolidate data, reclaim unused space, and optimize storage allocation. Reorganizing your tablespaces can improve I/O performance, reduce disk fragmentation, and enhance overall system efficiency. By performing regular database reorganizations based on data growth patterns and workload characteristics, administrators can maintain a well-organized and performant database environment.

Up-to-Date Statistics

Accurate and up-to-date statistics are essential for the Db2 optimizer to make informed decisions on query execution plans. As data in the database changes, statistics need to be updated to reflect the current distribution of data. Regularly updating statistics (using the RUNSTATS utility) ensures that the optimizer has the most accurate information to generate optimal execution plans. By analyzing data distribution patterns and scheduling statistics updates accordingly, administrators can improve query performance and avoid suboptimal query plans caused by outdated statistics.

For packages using static SQL, taking advantage of updated statistics requires rebinding. However, you may not want to rebind every time you run RUNSTATS unless application performance is suffering.

Routine Backups

Regular backups are vital for data protection and disaster recovery. Performing routine database backups not only safeguards the integrity of the database but also contributes to performance optimization. In the event of a failure or data loss, having a recent backup minimizes the recovery time and ensures business continuity. DBAs should establish a backup schedule based on the criticality of the data, recovery time objectives (RTOs), and workload requirements. 

And do not forget to regularly test your recovery plans and capabilities. Too often DBAs focus on backups at the expense of recovery… and backups are needed primarily to enable recovery, right?

Transaction Log Management

And let’s not forget the transaction logs! Logs play a critical role in ensuring data consistency and recoverability. Regularly monitoring and managing the transaction log space helps prevent log-related performance issues and ensures uninterrupted database operations. Activities such as transaction log backups, log file sizing, optimizing log offloading, and log file utilization monitoring are crucial for maintaining optimal transaction log performance and managing log space efficiently.

Buffer Pool Review

Regularly monitoring the efficiency of your Db2 buffer pools is important to ensure that you are achieving expected hit ratios and performance. As new applications are added, your amount of data increases, and access patterns change it will be necessary to adjust buffer pool sizes and parameters to optimize performance.

System Maintenance

Be sure to keep your Db2 subsystem updated with recent maintenance. You can use the -DIS GROUP command, even if you are not running data sharing, to display the current status of your Db2 software.

This command returns the message DSN7100I which is documented at It will show you the current version and function level, the current code level, and also the highest possible function level you can activate for your Db2 environment.

Be sure too to follow the IBM recommended preventive maintenance strategy to apply the appropriate maintenance using the IBM supplied RSU (Recommended Service Upgrade).


By incorporating these regular maintenance tasks into your database administration and management routine, DBAs and systems programmers can optimize performance, prevent performance degradation, and mitigate potential issues. Automation tools and scripts can streamline these maintenance activities and ensure consistency and timeliness in execution.

Tuesday, October 17, 2023

Top Ten Db2 Performance Tips – No. 8 I/O Optimization

I/O operations play a critical role in the performance of an IBM DB2 database and the applications that access it. Efficient disk I/O can significantly impact query response times and overall system performance. Let’s explore the importance of I/O optimization and discuss techniques for maximizing disk I/O performance.

The performance of I/O directly affects the speed at which data is read from or written to disk. Slow or inefficient I/O can become a performance bottleneck, leading to increased query execution times and degraded system responsiveness.

Data Set Placement

One technique for optimizing disk I/O is strategic data placement. Placing frequently accessed data on faster storage devices, such as solid-state drives (SSDs) or high-performance disk arrays, can significantly improve I/O performance. Identifying hot spots, such as frequently accessed tables or indexes, and ensuring they are stored on optimal storage media can help minimize I/O bottlenecks and accelerate query processing.

When it comes to the placement of Db2 data sets, the most critical consideration is certainly the log data sets and the BSDS. Be sure to place the active logs and BSDS data sets on the fastest disk you have at your disposal. The log can quickly become a bottleneck because all changes (with few exceptions) are written to the logs. Db2 writes first to log buffers and then from the buffers to an active log data set. The log is there to ensure recoverability. So, using the highest-speed disk for your logs can help to minimize the potential for pain here.

You also need to take care to make sure that your logs are efficient and effective by running with dual logs. By doing so you ensure that if one copy of the log is damaged, Db2 can continue operating with the other copy. If there is no log available Db2 operations screech to a halt. To run with dual logging, one set of log data sets should be on separate volumes than the other set. This should remove a disk failure as the cause of an outage.

As the active logs are filled, a process called log offloading is invoked by Db2 to move the log information to archive log data sets. This process prohibits the active logs from filling up during Db2 processing which would stifle processing. The archive logs are needed for recovery situations, such as rolling back transactions and recovering table spaces and indexes.

It is a wise course of action to keep your archive logs on disk, and over time migrate them to tape. Think about the trade-off in saving tape versus recovery speed, and your decision should be obvious. Before backing up any logs to tape, it’s a good idea to always have at least 24 hours covered by the active log and the least 48 hours by archive logs on disk.

And then there is the BSDS (bootstrap data set), which is a VSAM key-sequenced data set (KSDS). The BSDS contains important information for Db2 operations. One such piece of information is the names of the logs. Db2 uses information in the BSDS for system restarts and for any activity that requires reading the log. So again, keeping the BSDS on high-speed disk further boosts the operational performance of Db2.

Of course, there are other important Db2 data sets that you will want to carefully consider placing on optimal I/O devices.  For example, the Db2 Directory (DSNBD01) and Db2 Catalog (DSNBD06) data sets are frequently accessed and should be cared for to ensure the performance of the entire Db2 subsystem. And you may have some user database objects that are critically important or frequently accessed that you can place on faster disk than your other user database objects. This, of course, requires an understanding of your business and Db2 applications, as well as the appropriate budget to accomplish.

The overall goal of data set placement should be to balance I/O activity between different volumes, control units, and channels. This can minimize I/O elapsed time and I/O queuing.


RAID (Redundant Array of Independent Disks) configurations also play a significant role in I/O optimization. RAID levels, such as RAID 0, RAID 1, RAID 5, or RAID 10, offer different levels of performance, fault tolerance, and data protection. Configuring RAID appropriately based on workload requirements and system resources can improve I/O performance and provide the desired level of data redundancy. RAID striping, in particular, can distribute data across multiple disks, enabling parallel I/O operations and enhancing throughput.

What About Extents?

Some folks think “With RAID/modern storage devices and the latest Db2 and z/OS features, extents are no longer anything to worry about.” And this is a largely true statement, but there are some things you still should think about with regard to extents.

First, keep in mind that the latest extent management features only work with SMS-managed data sets, so if you are still using user-managed data sets then all of the old rules apply! For SMS-managed data set you can have up to 123 extents on each of 59 volumes. So as of z/OS 1.7, the limit is 7,257 extents for a data set instead of the 255 we’ve been used to for some time. Again though, to enable this requires DFSMS.

Extent consolidation also requires SMS-managed STOGROUPs. If a new extent is adjacent to an old extent, they will be merged together automatically. This can result in some extents being larger than the PRIQTY or SECQTY specification(s). Note that this feature was introduced in z/OS 1.5.

So, what if everything is SMS-controlled? Even then it is possible for extents to impact performance, albeit probably only slightly. Each extent on a disk file has different control blocks controlling access. This means that elapsed time can increase if there is heavy insert activity. For other types of processing (read and update) the number of extents really does not impact on performance.

Another thing to consider is that Db2 can allocate sliding scale secondary extents. This is enabled by setting MGEXTSZ DSNZPARM to YES. Note that the default is YES as of DB2 9 for z/OS. With sliding scale extents the extent sizes allocated gradually increase. Db2 uses a sliding scale for secondary extent allocations of table spaces and indexes when:

  • You do not specify a value for the SECQTY option of a CREATE TABLESPACE or CREATE INDEX statement
  • You specify a value of -1 for the SECQTY option of an ALTER TABLESPACE or ALTER INDEX statement.

Otherwise, Db2 uses the SECQTY value for secondary extent allocations, if one is explicitly specified (and the SECQTY value is larger than the value that is derived from the sliding scale algorithm). If the table space or index space has a SECQTY greater than 0, the primary space allocation of each subsequent data set is the larger of the SECQTY setting and the value that is derived from a sliding scale algorithm. Without going into all of the gory details, sliding scale extent allocation can help to reduce the number of extents for your Db2 objects as they grow in size over time. And it can help when you do not have a firm understanding of how your data will grow over time.

At any rate, things are not like the olden days where you had to regularly monitor extents and clean them up all the time by reorganizing your table spaces and index spaces.

Additional I/O Considerations

Optimizing I/O parallelism settings can help to improve disk I/O performance. Tuning I/O parallelism settings, such as the number of concurrent I/O operations or I/O thread configuration, can also enhance I/O performance by allowing simultaneous access to multiple disks or storage devices.

Regular monitoring of I/O performance metrics, such as disk read/write rates, queue lengths, and average response times, is essential for identifying potential I/O bottlenecks and fine-tuning the I/O subsystem. Analyzing performance data and workload patterns can help identify areas for improvement and guide decision-making regarding data placement, file system selection, RAID configuration, and other I/O optimization techniques.


In conclusion, optimizing disk I/O is vital for improving the performance of Db2 databases and the applications that access them. By strategically placing data, properly configured your logs and BSDS, configuring RAID appropriately, tuning I/O parallelism settings, and regular monitoring, you can enhance I/O performance, reduce latency, and accelerate query processing. 

Tuesday, August 29, 2023

Top Ten Db2 Performance Tips - No. 7 Configuration Parameters and Tuning

Configuration parameters play a crucial role in optimizing the performance of an IBM Db2 database environment. By understanding the impact of these parameters and tuning them appropriately, database administrators can fine-tune the system to achieve optimal performance. In this blog post, we will explore key configuration parameters and discuss recommended settings and tuning options.

The first thing to understand is that the actual parameters, their specific names, and how they are implemented and modified will differ between Db2 LUW and Db2 for z/OS. This blog post will talk mostly in generalities but the ideas resonate across all Db2 database management systems (and, indeed, even across other relational DBMS platforms).

The first type of parameters are used to configure Database Manager options. The database manager configuration parameters govern the overall behavior of the Db2 instance. Parameters such as database shared memory, lock list, and log buffer size have a significant impact on performance. Adjusting the shared memory size to accommodate the workload, appropriately sizing the lock list to handle concurrent transactions, and setting an optimal log buffer size based on the transaction rate are essential considerations.

Next we have the Buffer Pool parameters. Buffer pools act as a cache for frequently accessed data pages, reducing disk I/O and improving query performance. The size and configuration of buffer pools are critical for efficient memory utilization. Allocating an appropriate amount of memory to buffer pools based on workload characteristics and monitoring buffer pool hit ratios helps optimize performance. 

Here are some crucial considerations for configuring Db2 for z/OS buffer pools:

  • Data Access Patterns: Understand the access patterns of your applications. Buffer pool configuration should align with the types of queries and transactions that are frequently executed. If your workload involves mostly random access, a larger buffer pool may be required. If it involves mostly sequential access, specific settings to favor sequential reads may be required.
  • Buffer Pool Sizing: Determine the appropriate size for each buffer pool. Consider the amount of available memory on your system, the size of the database objects, and the anticipated workload. Larger buffer pools can improve performance, but it's essential to balance memory usage across other system components.
  • Multiple Buffer Pools: Db2 for z/OS allows you to create multiple buffer pools. Consider segregating data based on access patterns or table spaces. For example, you could use separate buffer pools for frequently accessed tables and indexes versus less frequently accessed ones.
  • Page Sizes: Db2 for z/OS supports various page sizes. Ensure that the buffer pool page size matches the page size of the objects being cached. Using the correct page size can reduce internal I/O operations and improve efficiency.
  • Thresholds and Actions: Set appropriate thresholds for buffer pool thresholds, such as the high water mark and low water mark. Define actions to be taken when these thresholds are crossed, such as dynamically adjusting the buffer pool size or issuing alerts.
  • Asynchronous I/O: Enable asynchronous I/O to allow Db2 to overlap I/O operations with processing. This can improve performance by reducing wait times caused by synchronous I/O operations.
  • Monitor and Analyze: Regularly monitor buffer pool performance using Db2's monitoring tools. Analyze statistics and performance metrics to identify areas for improvement or potential issues.
  • Buffer Pool Replacement Policies: Understand and configure the buffer pool replacement policies (e.g., LRU, MRU, FIFO) based on your workload characteristics. Different policies prioritize different data pages for retention in the buffer pool.
  • Maintenance: Regularly review and adjust buffer pool configurations based on changing workloads, data growth, and hardware upgrades.

Also, Db2 for z/OS offers the following buffer pool tuning “knobs” that can be used to configure buffer pools to the type of processing they support:

  • DWQT (deferred write threshold) –  expressed as a percentage of the virtual buffer pool that might be occupied by unavailable pages. When this threshold is reached Db2 will start to schedule write I/Os to externalize data. The default is 30%, which may be too high for many shops.
  • VDWQT (vertical deferred write threshold) – basically the same as DWQT, but for individual data sets. The default is 10%, which once again is quite likely to be too high for many shops.
  • VPSEQT (sequential steal threshold) – expressed as a percentage of the buffer pool that can be occupied by sequentially accessed pages. Tune buffer pools for sequential access such as scans and sorting by modifying VPSEQT to a larger value. The default is 80%.
  • VPPSEQT (parallel sequential steal threshold) – the sequential steal threshold for parallel operations; the default value is 50%.

Additionally, adjusting parameters like page cleaning and prefetch size can further enhance buffer pool performance.

Remember that buffer pool configuration is not a one-size-fits-all approach. It requires a deep understanding of your specific workload, database design, and hardware capabilities. Regular monitoring and tuning are essential to maintain optimal performance over time.

There are other Memory settings that are nearly as critical as buffer pools, too. One example is the Db2 for z/OS EDM pool. EDM stands for Environmental Descriptor Manager. The EDM pool is used by Db2 to control programs as they execute. It will contain structures that house the access paths of the SQL statements for running programs. 

Actually, the EDM pool is a group that encompasses several other pools, all of which can be configured separately, including skeleton pools, DBD pool, sort pool, and RID pool. The RID pool is used by DB2 to sort RIDs (record identifiers) for List Prefetch, Multiple Index Access, and Hybrid Join access paths.  RID pool failures can cause performance degradation as alternate access paths are invoked, such as scans, and the CPU invested up to the point of the failure is wasted.

Db2 for z/OS also enables a Dynamic Statement Cache to be configured and tuned. It permits dynamic SQL prepare information to be cached in memory to optimize dynamic SQL. 

Another consideration to keep in mind is Query Parallelism, which refers to the ability of Db2 to execute multiple query tasks concurrently, leveraging multiple CPU cores. Enabling parallelism can significantly speed up query execution for resource-intensive workloads. The degree of parallelism, controlled by parameters like DFT_DEGREE and NUM_INIT_AGENTS, should be set based on the available hardware resources and workload characteristics. Careful tuning of these parameters ensures optimal parallel query execution without overloading the system.

Tuning Db2 Sort operations is also critical. During query execution sorting can consume significant memory resources. The sort heap is responsible for allocating memory for sort operations. Tuning the Db2 LUW SORTHEAP parameter to an appropriate size based on the complexity of sort operations and available memory helps avoid  excessive disk I/O and improves query performance. Regular monitoring and adjustment of SORTHEAP ensure efficient memory utilization for sort operations.

Statement Concentration is another configuration parameter to consider. It controls the consolidation of multiple SQL statements into a single unit of work. Enabling statement concentration reduces the overhead associated with parsing and optimizing individual statements, improving overall performance. By setting appropriate values for parameters like STMT_CONC and STMTHEAP, administrators can optimize statement concentration based on the workload and resource availability.

Connection and Maximum settings are also crucial to consider. For example, in Db2 for z/OS MAXDBAT sets the maximum number of database agents and Db2 LUW uses MAXAPPLS to define the maximum number of concurrent application connections. And an example of a setting that can control maximums is DSMAX (Db2 for z/OS) that can be set between 1 and 200000; it controls the maximum number of underlying data sets that Db2 can have open at any point.

It is important to note that tuning these configuration parameters should be done carefully and based on workload analysis and performance monitoring. The optimal settings may vary depending on the specific requirements and characteristics of the database environment. Regular monitoring of performance metrics, workload patterns, and system behavior is crucial to identify areas for tuning and ensure continued optimization.


In conclusion, configuration parameter tuning is a critical aspect of optimizing the performance of an IBM Db2 database. By understanding the impact of key parameters and adjusting them based on workload characteristics, administrators can fine-tune the system for optimal performance. 

We have only taken a high-level look at several configuration considerations and parameters in this blog post. But keep in mind that the configuration parameters and their settings contribute to an efficient and high-performing Db2 environment. Regular monitoring and tuning of these parameters help achieve optimal performance and responsiveness in the database system.