DB2 Version 9.7 for Linux, UNIX, and Windows
DB2 Version 9.7 for Linux, UNIX, and Windows
This FAQ provides you with answers to common questions about DB2® workload manager (WLM) and about how it relates to existing Query Patroller and Governor functionality.
DB2 workload manager is available on all platforms supported by DB2 9.5 for Linux, UNIX, and Windows or later. The optional tight integration offered between DB2 service classes and operating system service classes is available with AIX and Linux WLM.
No. Although Query Patroller and DB2 workload manager are both part of the Performance Optimization feature, they are independent of each other. In other words, one does not require Query Patroller to be installed to use DB2 workload manager or vice versa.
DB2 workload manager represents a shift of emphasis in our workload management strategy to focus primarily on the ability to monitor and control active work once it has entered the DB2 execution environment. In order to provide the degree of control and monitoring desired by our customers for higher volumes of concurrent work requests while it is actually executing, yet with minimal overhead, we made the strategic decision to incorporate any new WLM technology directly into the DB2 engine infrastructure.
The approach of using a cooperative relationship with an auxiliary application to provide workload management for DB2 data server, such as that used with Query Patroller, simply would not be able to provide what was needed to satisfy customer requirements.
The DB2 workload manager introduces an independent approach to workload management and does not rely on or interact with Query Patroller or DB2 Governor in any way. While Query Patroller and DB2 Governor are still functional, they are deprecated and no longer central to DB2 workload management strategy and no further investment is planned for them in future releases.
To ensure an easy transition, DB2 data server enables Query Patroller and DB2 Governor to coexist with the facilities provided by DB2 workload manager while still providing separate scopes of control. If Query Patroller is present, any work submitted for execution in the default user service class are intercepted and sent to Query Patroller. Work submitted for execution in other service classes defined by the database administrator are not presented to Query Patroller.
When DB2 9.5 or later is first installed, the default user service class is automatically defined and all incoming work is sent to it for execution. This means that any existing Query Patroller installation will continue to function as it did before. It is only once the administrator introduces new DB2 service classes and begins to guide work away from the default user service class that the work seen by Query Patroller begins to change.
The story is essentially the same for DB2 Governor which, although it can watch agents in any service class, it is permitted to adjust the agent priority only for agents in the default user service class.
Note that DB2 workload manager is aware of and can control all work within DB2 including that within the default user service class. When Query Patroller is used, it is recommended to limit the use of DB2 workload manager to control work in the default user service class in order to avoid potential conflicts between Query Patroller and DB2 workload manager. It is always safe to use the monitoring features of DB2 workload manager.
While you can emulate the approach taken by Query Patroller by categorizing work by its estimated cost, mapping it to different service sub classes, and applying different concurrency thresholds on each service sub class, this is neither the recommended approach nor the best starting point. This approach does not deal with all the different types of work that execute within DB2 data server, only with DML SQL statements. Achieving a stable execution environment requires that all work executing within DB2 data server is controlled to one degree or another.
With DB2 workload manager, it is possible to separate and isolate competing workloads from each other and then affect their individual response times by changing the resources available to them. This is done by using DB2 service classes and manipulating the processor and prefetcher priorities each service class receives. It is recommended that you start here as this provides the groundwork for controlling all work executing within DB2 data server.
If you cannot separate work by its source (via a DB2 workload), then you can map all incoming work to a common service super class and use a DB2 work action set to separate work by different characteristics and assign it to different service sub classes. At this point, you can manipulate the resources available to each service class to achieve your objectives. Note that not all types of activities can be recognized within a work action set and any unrecognized ones will not be mapped to a different service class; they will remain in the one originally assigned to them.
If resource manipulation does not achieve the desired results, you can selectively apply other features of DB2 workload manager as needed until you achieve your objectives. This includes the application of DB2 thresholds, including concurrency thresholds. As most concurrency thresholds (such as the CONCURRENTDBCOORDACTIVITIES threshold, for example) coordinate activities across all database partitions, they impose a higher overhead on the activities that they manage. Introducing a concurrency threshold adds complexity to the execution environment; if care is not taken in the definition, unexpected or unintended results may be the consequence.
Users on all platforms have the ability to control processor resource and prefetcher I/O activity between service classes using SQL (for CREATE and ALTER SERVICE CLASS statements, for example). To control CPU usage, users can use the agent priority attribute of the DB2 service class to set a relative processor priority for all threads that run in that service class. On AIX and Linux platforms, users can also use this approach or they can choose to take advantage of AIX and Linux WLM, respectively, for more advanced processor usage management. For prefetcher I/O activity, users on all platforms can set the prefetcher priority attribute of a DB2 service class to a value of high, medium or low. All service classes run with a medium prefetch priority by default.
Currently, neither AIX nor Linux WLM supports I/O activity controls at the thread level. Because DB2 Version 9.5 and later use a threaded model, it is not possible to use either AIX or Linux WLM to control disk I/O activity. You can control DB2 prefetcher I/O activity by using the PREFETCH PRIORITY attribute of any DB2 service class.
DB2 data server uses primarily shared memory which is accessed by more than one agent from different service classes. For this reason, it is not possible to divide memory allocation between different service classes using either AIX or Linux WLM.
WebSphere Application Server Version 6.0 and Version 6.1 can set or pass in the CLIENT INFO fields to DB2 data server, either explicitly by your applications (see: Passing client information to a database) or implicitly by having WebSphere Application Server do it for you (see: Implicitly set client information).
The service class agent priority setting does not take effect until an agent begins work on activities in that service class. An idle agent keeps the priority of the service class it last worked for until it joins a different service class. Another reason may be that the AGENTPRI dbm config parameter is set. Even though this parameter is deprecated as of Version 9.5, it does take precedence over the WLM service class setting. To use the WLM setting, reset the AGENTPRI config parameter to its default value, which is -1. On AIX, the instance owner must have CAP_NUMA_ATTACH and CAP_PROPAGATE capabilities to set a higher relative priority for agents in a service class.
DB2 workload manager (WLM) does not assign service class agent priority to work being done within a fenced mode process (FMP). Fenced procedures do not run their logic within a service class. These fenced procedures run within the DB2 FMP and this work is not done by DB2 agents. As a reminder, DB2 WLM controls DB2 agents.
On Solaris 10, the instance owner must have the proc_priocntl privilege to set a higher relative priority for agents in a service class. If DB2 is run within a non-global zone of Solaris, the zone must have the proc_priocntl privilege in the limit privilege set. On Solaris 9, there is no facility for DB2 to set a higher relative priority for agents.
The simple answer to this question is yes. You can create one or more CONCURRENTDBCOORDACTIVITIES concurrency thresholds that apply to the same set of activities by defining them at the level of the database, the service class in which the work executes, or within a work action set applied at the database or workload level. Be aware that each new concurrency threshold that applies to an activity implies additional overhead to enforce that concurrency threshold.
The more complex answer includes the following caution: verify that you actually need to use concurrency thresholds at all, let alone multiple ones. There may be simpler ways to address the scenario you are facing by using one or more of the other mechanisms and controls provided by DB2 workload manager. If you find yourself introducing one or more concurrency thresholds, you may have bypassed a simpler approach to address the problem. In general, concurrency thresholds for activities should be used at the database level, via a work action set, for disruptive activities that affect the entire system or go across service class boundaries, while concurrency thresholds at the service class level can be used to ensure proper sharing of resources between one service class and another (although a more effective technique may be to use the CONCURRENTWORKLOADACTIVITIES threshold on the workloads that contribute to the service class). There should rarely, if ever, be a case where you need to define a concurrency threshold for CONCURRENTDBCOORDACTIVITIES at the database level by itself.
There are a number of reasons why a connection may not be mapped to the desired workload. The most common ones are the failure to grant USAGE privilege on the workload, incorrect spelling of the case sensitive connection attributes, or the existence of a matching workload definition that is positioned earlier in the evaluation order.
Before a connection can be assigned to a workload, the connection attributes must match those of the workload definition, and the session authorization ID must have USAGE privilege on the workload. A common omission is to create the workload but not to grant USAGE privilege on the workload to users (See GRANT (Workload Privileges) statement). Only users with ACCESSCTRL, SECADM, or WLMADM authority can grant workload usage privilege to other users. Users with ACCESSCTRL, DATAACCESS, DBADM, SECADM, or WLMADM authority have implicit usage privilege on all workloads.
Connection attributes for workloads are case sensitive. For example: If the system user ID is uppercased, then the SYSTEM_USER connection attribute you specify must be in uppercase as well.
To establish why a connection is not being mapped to the expected workload, you should gather some information. Which workload is the work being mapped to? Is that workload before or after the one that you thought would be used when you look at the workload definitions in the order of evaluation? (Hint: try selecting the workload definitions ordered in ascending order by the value of the EVALUATIONORDER column in SYSCAT.WORKLOADS).
If you do not know what the connection attributes are for the target connection, you can find out the values for the connection in a number of different ways:
While having the DB2 data server automatically create corresponding AIX or Linux WLM service classes when DB2 service classes are created might reduce administrative overhead for system administrators, this is not available for a number of reasons:
In the end, we decided that it is better for DB2 data servers not to create AIX or Linux WLM service classes when a DB2 service class is created. We believe that this gives the DB2 data server and our customers maximum flexibility.
These CLP commands are affected by DB2 workload manager thresholds, because the database engine cannot distinguish system requests originating with these utilities from other requests directly initiated by users within the CLP interactive front-end.
Yes, you can change the service subclass an activity is executing in to another service subclass within the same parent service superclass by defining a CPUTIMEINSC or SQLROWSINSC threshold with the REMAP ACTIVITY action on the original service subclass. Initially, DB2 workload manager maps an activity to a service class based on the relevant workload definition for the connection, modifies it as required if a work action set exists on that service class, and then sets up the DB2 agent to execute in the assigned service class. When an activity violates a threshold that has a REMAP ACTIVITY action defined, the agent remaps itself to the specified target service subclass (under the same superclass) once the threshold violation has been detected and the activity continues executing in the new service subclass.
You have a couple of options:
An enhancement has been added to CLP so that the client application name is automatically set to the CLP script filename, with a CLP prefix preceding it (the value of this field at the server can be seen in the CURRENT CLIENT_APPLNAME special register). For example, if the CLP script filename is batch.db2, the CURRENT CLIENT_APPLNAME special register value is set to CLP batch.db2 by CLP when that script is run. With this feature, it is possible for different CLP scripts to be associated with different workloads based on the client application name.
For example, to create a workload for CLP file batch1.db2, you can issue the following DDL statement:
CREATE WORKLOAD batch1 CURRENT CLIENT_APPLNAME ('CLP batch1.db2')
SERVICE CLASS class1To create a workload for CLP file batch2.db2, you can issue the following DDL statement:
CREATE WORKLOAD batch2 CURRENT CLIENT_APPLNAME ('CLP batch2.db2')
SERVICE CLASS class2Since these two batch files are associated with different workloads, they can be assigned to different service classes and managed differently.
Another option is the new stored procedure WLM_SET_CLIENT_INFO, which permits you to set the values of any of the client information fields at the server using a simple CALL SQL statement. By inserting a CALL statement into any of your existing CLP scripts, you can uniquely identify them using these fields and map them to different workload definitions.
For more information, see WLM_SET_CLIENT_INFO procedure.
The answer depends on why the monitoring is desired and what is to be done with the information.
Aggregate activity information is about the entire set of work that has executed within a service class and captures characteristics of this set; it does not capture details about individual activities. For normal operational monitoring, using the COLLECT AGGREGATE ACTIVITY DATA clause is preferred because it is very light-weight, can be gathered automatically by an event monitor for a historical record, and provides important information on overall response time patterns. If further insight is required on the type of work within a service class, it is also possible to use the COUNT ACTIVITY or COLLECT AGGREGATE ACTIVITY DATA actions within a DB2 work action set to gather more granular information about different types of work executing in a service class with minimal overhead.
In contrast, activity information contains detailed information about each and every activity that executes within the scope covered by the COLLECT ACTIVITY DATA clause. This clause can be specified on DB2 workloads, DB2 service classes, DB2 work action sets, and DB2 thresholds. It permits further in-depth analysis of the individual activities that are captured, in order to understand the flow and type of SQL statements submitted by a new application, for example, or to look into performance tuning opportunities with tools such as the Explain facility or the Design Advisor. Because it captures much more information for each activity affected by it, the impact of using this clause is higher on affected activities than other monitoring methods and it should be carefully controlled.
All aspects of the DB2 workload manager will work within an AIX WPAR but because AIX WPARs do not support the use of AIX WLM features, the option to tightly integrate DB2 service classes with AIX WLM service classes is of no benefit in this environment.
There is no direct relationship between these two things. The DB2_OPT_MAX_TEMP_SIZE registry variable is a directive to the query compiler to limit the amount of temporary table space that a query can use. This can cause the optimizer to choose a plan that is more expensive (potentially less efficient) but which uses less space in the system temporary table spaces. A DB2 threshold based on SQLTEMPSPACE does not affect the type of plan chosen by the optimizer. It simply causes DB2 data server to monitor the usage of system temporary table space by that query at each database partition and generates a threshold violation if the stated limit is exceeded during normal processing.
DB2 workload manager enables control of all queries regardless of origin and while it does offer queuing as a secondary control mechanism, the primary control mechanism is resource control and prioritization via a DB2 service class. This means that you can allocate processor resource between DB2 service classes and possibly avoid using a queue at all.
Following the introduction of DB2 workload manager as the strategic workload management solution in DB2 Version 9.5, Query Patroller and the DB2 Governor have been deprecated and might be removed in a future release.
Query Patroller and DB2 Governor are still supported in this release, but you should begin adopting the new features and capabilities of DB2 workload manager, including those introduced in this release. Note that with DB2 workload manager, you have many more options, and you should explore them, which might require you to rethink your approach to controlling work on your DB2 data server in current workload management terms. The article Best Practices: DB2 Workload Management contains a section on the approach you should take when adapting DB2 workload manager. To manually migrate from Query Patroller and Governor, task topics are also available in the Related tasks section.
To facilitate migration from DB2 Query Patroller to DB2 workload manager, a sample script (qpwlmmig.pl) has been included with DB2 V9.7 Fix Pack 1. For additional information, see "Query Patroller migration tool" in the Related concepts section.
DB2 workload manager is licensed as part of the Performance Management Feature, and use of the full set of workload management functionality requires that you obtain this additional license.
Regardless of licensing, some workload management functionality is always used by your DB2 data server. The following is always available:
The following functionality requires a license for the Performance Management Feature:
The threshold violations, statistics, and activities WLM event monitors capture information about threshold violations, operational statistics, and both individual and aggregate activity data (see: Historical monitoring with WLM event monitors).
Each event monitor collects one or more logical data groups (see: Event type mappings to logical data groups) and there are one or more monitoring elements in each logical data group (see: Event monitor logical data groups and monitor elements).
You can do this by first creating a view using the WLM_GET_SERVICE_CLASS_AGENTS_V97 table function and then running statements to list the queued activities in the order of the queue entry time. For examples describing how to do this, see: Example: Determining which activities are queued by a WLM threshold and their queue order.