Frequently asked questions about Db2 workload management

On which Db2 platforms can I use Db2 workload management?

Db2 workload management is available on all platforms supported by Db2 9.5 for Linux, UNIX, and Windows or later. The optional tight integration-offered between Db2 workload management at the service class level and operating system workload management capabilities-is available on AIX platforms and any Linux platform based on the 2.6.26 kernel or higher.

Do I need to use workload management dispatcher?

Most workload management configurations begin with concurrency thresholds, which affect the consumption of all resources by controlling how much work can begin executing at any one time. In some situations, however, a concurrency threshold is not able to effectively limit the total amount of processing resource that is consumed, and high priority work is affected; for example, a scenario in which complex work is restricted to one running query that nevertheless consumes enough resource to disrupt higher priority work. In such cases, the workload management dispatcher is used to explicitly control CPU consumption and protect the higher priority work.

How does this new functionality affect Query Patroller and Db2 Governor?

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. Query Patroller has been discontinued starting with the Version 10.1 release. Db2 Governor was deprecated in the Version 9.7 release and, although still functional, it is no longer central to the workload management strategy. No further investment is planned for Db2 Governor in future releases.

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. Although Db2 Governor can watch agents in any service class, it is permitted to adjust the agent priority only for agents in the default user service class.

I am not on AIX. Does this mean I do not have any control over processor resources or I/O activity?

Users on all platforms have the same ability to control processor resources and I/O activity between service classes using SQL, such as the CREATE and ALTER SERVICE CLASS statements, for example.

To control CPU usage when the workload management dispatcher is enabled, use the CPU limit attribute of the Db2 service class to limit the amount of CPU resources a service class can consume. If the workload management dispatcher CPU shares (wlm_disp_cpu_shares) database manager configuration parameter is also enabled, you can use the CPU shares attribute of the Db2 service class to specify the share of CPU resources that a service class can consume relative to the CPU consumption of other service classes. On AIX and some Linux platforms, you can supplement (or replace) these approaches by taking advantage of the workload management capabilities that are offered by those operating systems to control CPU consumption.

For I/O activity, users on all platforms can set the buffer pool or prefetcher priority attribute of a Db2 service class to a value of high, medium, or low. All service classes run with a medium priority by default.

Can I use AIX or Linux WLM or the Db2 workload management dispatcher to manage I/O activity?

Currently, neither AIX WLM nor Linux WLM support direct I/O activity controls at the thread level. However, it is possible to indirectly control I/O activity by means of concurrency thresholds, or to use the Db2 workload management dispatcher, AIX WLM, or Linux WLM to manipulate CPU resources. The more CPU resource that is available to an executing thread, the less frequently that thread will request I/O resources.

You can influence buffer pool behavior by using the BUFFERPOOL PRIORITY attribute of any Db2 service class. You can also control Db2 prefetcher I/O activity by using the PREFETCH PRIORITY attribute of any Db2 service class.

Can I use AIX or Linux WLM to manage memory use?

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.

Memory (such as sortheap) that is consumed during the execution of an SQL statement can be indirectly influenced through the use of concurrency thresholds, because consumption does not begin until the statement is allowed to execute. However, unlike I/O activity, restricting CPU consumption does not affect the amount of memory that is consumed. In fact, restricting CPU consumption can exacerbate the memory situation, because queries will be running more slowly and holding onto their allocated memory longer.

Is there a way for WebSphere Application Server to pass the client information fields used by the Db2 workload?

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).

Can I create multiple CONCURRENTDBCOORDACTIVITIES concurrency thresholds for the same set of work?

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. Verify that you really need more than one concurrency threshold level.

Why is my work not assigned to the correct workload?

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:

• Issue a query against the system using the WLM_GET_SERVICE_CLASS_WORKLOAD_OCCURRENCES table function while the connection is active
• Open a cursor on a connection and use the WLM_CAPTURE_ACTIVITY_IN_PROGRESS stored procedure against that cursor to have the activity information captured to the activities event monitor (Hint: do not forget to create and activate the activities information event monitor)
• Turn on the collection of detailed activity information for the workload being used by the connection, issue one statement in order to capture the activity information, and then turn off the collection.

Why does Db2 workload manager affect REORGCHK, IMPORT, EXPORT and other CLP commands?

These CLP commands are affected by Db2 workload management 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.

Is there a way to change the service class to which an activity is assigned while it is executing?

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, DATATAGINSC, or SQLROWSINSC threshold with the REMAP ACTIVITY action on the original service subclass. Initially, Db2 workload management 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.

Much of my batch work is done using CLP scripts under the same ID, how can I go about uniquely identifying these so I can manage them differently?

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 class1

To 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 class2

Since 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.

When should I use the COLLECT AGGREGATE ACTIVITY DATA clause versus the COLLECT ACTIVITY DATA clause?

The answer depends on why the monitoring is desired and what is to be done with the information.

Aggregate activity information spans the entire set of work that has executed within the scope covered by the clause, and it captures summary characteristics of this set; it does not capture specific details about individual activities. The COLLECT AGGREGATE ACTIVITY DATA clause can be specified on Db2 workloads, Db2 service classes, and Db2 work action sets. For normal operational monitoring, use the COLLECT AGGREGATE ACTIVITY DATA clause, because it is very lightweight, it can be gathered automatically by the statistics event monitor for a historical record, and it provides important information on overall response time patterns. If further insight is required on a specific type of work, use the COUNT ACTIVITY or COLLECT AGGREGATE ACTIVITY DATA actions within a Db2 work action set to gather more granular information (with minimal overhead) about different types of work executing in a workload, service class, or database.

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.

How does Db2 workload management work with the new AIX WPAR feature?

All aspects of Db2 workload management 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.

What is the relationship between the DB2_OPT_MAX_TEMP_SIZE registry variable and Db2 thresholds based on SQLTEMPSPACE?

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 member and generates a threshold violation if the stated limit is exceeded during normal processing.

Now that Query Patroller is discontinued and Db2 Governor is deprecated, how do I migrate to Db2 workload manager?

Following the introduction of Db2 workload manager as the strategic workload management solution in Db2 Version 9.5, Query Patroller has been discontinued in the Version 10.1 release and the Db2 Governor has been deprecated since the Db2 Version 9.7 release and might be removed in a future release.

Although Db2 Governor is still supported in this release, you should begin adopting the new features and capabilities of the workload manager, including those introduced in this release. Note that with the 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 Db2 best practices article Implementing Db2 workload management in a data warehouse contains a supplement that is specifically designed for those who are migrating from Query Patroller. Pertinent task topics are also available in the Related tasks section.

What are the licensing requirements for Db2 workload manager?

What information do you get from the different event monitors that are associated with workload management?

The threshold violations, statistics, and activities event monitors capture information about threshold violations, operational statistics and aggregate activity data, and individual 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).

How do I determine which activities are queued by a workload management threshold and the order of the activities in the queue?

You can do this by first creating a view using the WLM_GET_SERVICE_CLASS_AGENTS 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.

What changes in behavior might I see when I turn ON the workload management dispatcher?

If you enable CPU shares via the wlm_disp_cpu_shares database manager configuration parameter and do not specify CPU shares or CPU limits for your service classes, all service classes receive an equal soft share of the CPU resources on your system. The effect of all service classes receiving an equal soft share of the CPU resources might result in a different allocation of CPU resources to services classes than in previous Db2 releases. As a result, you should consider setting CPU shares or CPU limit values appropriate for your workload. For more information about how to determine values for CPU shares and CPU limits, see: Workload management dispatcher.

With the introduction of the workload management dispatcher, are concurrency thresholds such as CONCURRENTDBCOORDACTIVITIES no longer needed or useful?

The Db2 workload management dispatcher and concurrency thresholds can be used together. Concurrency thresholds are still very useful for controlling how much work is running. For each activity that starts running, the Db2 database manager provides other resources to that activity, in addition to CPU resources, which the activity usually retains for as long as it is running. Such non-CPU resources include (among others) the Db2 agent, sort memory, temporary table space, locks, and I/O. By preventing an activity from starting to run, those additional non-CPU resources are not consumed and are available for other activities.

In addition, concurrency thresholds can be applied at different points within the Db2 database manager to determine the origin of the work that is running. For example, putting a concurrency threshold on large queries coming from a specific workload limits the consumption or share of the resources available to that particular workload in a service class, as compared to other workloads contributing to the same service class.

In summary, concurrency thresholds can be used to control when activities start to run and consume the CPU and non-CPU resources on the system. The workload management dispatcher can be used to control how much of the CPU resources such activities get to consume once they start running.

Why would I ever want to use AIX WLM or Linux WLM?