Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Apr 13, 2004

Tackling Slowdowns: Utility Throttling in IBM DB2 UDB V8.1.2 and Beyond - Page 2

By DatabaseJournal.com Staff

How Does Throttling Work?

To control throttling, there is an algorithm that is busy ensuring that DB2 UDB is aware of the utility's load on the system and adjusts its consumption accordingly. A simplified representation of this algorithm is shown below:

The DBA ellipse represents the setting of the UTIL_IMPACT_LIM parameter that is determined by the DBA and the business policy. The DB2 ellipse encapsulates the utilities that are running. During utility operations, a sensor interacts with the Adaptive Controller box (which is actually inside DB2 UDB), and it runs algorithms that determine a metric for computing degradation. It compares that returned value with a baseline estimation for the workload and the "impact allowances," as defined by the DBA. If there is a problem, a separate process will cause the utility to "sleep" until it is in compliance with the set policy.

The graph on the right plots a sample of the output for the aforementioned OLTP workload with the BACKUP utility running. In this test, the UTIL_IMPACT_LIM parameter was set to 50 (represented by the red line). The actual performance numbers are plotted in blue. You can see that DB2 UDB was able to keep the workload constant by throttling the utility after it exceeded the target threshold and the throttling algorithm took effect.

More representative of the real world would be a scenario involving multiple concurrent workloads on the system.

The following test had a dual workload with the BACKUP utility running concurrently. On the left side of the diagram, you can see the workload distribution between the two applications, with the total system resource shown in blue. On the right side of the diagram, the BACKUP utility has been invoked, again with the UTIL_IMPACT_LIM = 50. If you look at the right side of the diagram, you can see that the "meat" of the workloads' impact on the system is now at 900 seconds, compared to 600 seconds on the left - this implies that the workloads were impacted by roughly 50%. Note that the distribution of the workloads in both diagrams has a similar shape.

Using DB2 UDB Throttling

In DB2 UDB V8.1.2, throttling is mainly confined to the BACKUP utility; throttling the REBALANCE utility requires special handling.

Utilities can be run in either throttled or unthrottled mode. In unthrottled mode, the utility would not be under the throttling system and does not obey the UTIL_IMPACT_LIM parameter. In throttled mode, you can actually assign different priorities.

In most cases, DBAs will just run a utility in throttled mode without any special priority. Although the ability to specify a priority is not very important when there are not a lot of utilities that can be throttled, this method created an infrastructure that is scalable and extensible to support other utilities in future releases (see the DB2 UDB Throttling in "Stinger" section later in this article).

For example, the following syntax was added to the BACKUP DATABASE command:

On the BACKUP DATABASE command, priority is an optional flag that represents a number in the range of 1 to 100, with 100 representing the highest priority, and 1 representing the lowest priority. The priority specifies the amount of throttling to which the utility is subjected. All utilities at the same priority undergo the same amount of throttling, and utilities at lower priorities are throttled more than those at higher priorities. If you do not specify this option, the default priority is 50. If you invoke the BACKUP utility without specifying the UTIL_IMPACT_PRIORITY keyword, the utility will execute in unthrottled mode (of course, if the UTIL_IMPACT_PRIORITY keyword is specified, but UTIL_IMPACT_LIM is set to 100, the utility will run unthrottled).

The REBALANCE utility is invoked indirectly from an ALTER TABLESPACE operation. By default, the REBALANCE utility is started in unthrottled mode, and a mechanism is provided whereby a DBA can throttle this utility after it has been started.

The ability to use the command line processor (CLP) to enable or disable throttling, as well as reprioritize a running utility, is also provided through the set command, as follows:

set UTIL_IMPACT_PRIORITY for <utility_id> to <priority>

For the set command, the priority flag performs the same function as detailed above; however, if you set it to 0, it will force a throttled utility to continue unthrottled.

In the DB2 UDB V8.1.2 implementation, running multiple throttled utilities at the same time is not supported.

DB2 UDB Throttling in "Stinger"

DB2 UDB "Stinger" has a number of enhancements to the throttling capabilities of DB2 UDB (check out www.ibm.com/db2/stinger for more information):

  • Some of the utilities and policy settings have been encapsulated in the Control Center and Wizards.
  • There is an active utilities monitor to track the status of utilities.
  • Snapshots and SQL support for monitoring the progress of executing utilities have been added.
  • Multiple concurrently running utilities can be throttled.
  • The UTIL_IMPACT_PRIORITY clause has been added to the RUNSTATS utility.

About the Authors

Paul C. Zikopoulos, BA, MBA, is with IBM Canada Ltd. Paul has written numerous magazine articles and books about DB2. Paul has co-authored the books: DB2 Version 8: The Official Guide, DB2: The Complete Reference, DB2 Fundamentals Certification for Dummies, DB2 for Dummies, and A DBA's Guide to Databases on Linux. Paul is a DB2 Certified Advanced Technical Expert (DRDA and Cluster/EEE) and a DB2 Certified Solutions Expert (Business Intelligence and Database Administration). You can reach him at: paulz_ibm at msn.com.

Roman B. Melnyk, PhD, is with IBM Canada Ltd., specializing in database administration, DB2 utilities, and SQL. Roman has written numerous DB2 books, articles, and other related materials. Roman co-authored DB2 Version 8: The Official Guide, DB2: The Complete Reference, DB2 Fundamentals Certification for Dummies, and DB2 for Dummies. You can reach him at roman_b_melnyk at hotmail.com.

IBM reserves the right to include or exclude any features advertised as part of the DB2 UDB "Stinger" release in the final version of this product.

DB2 Archives