Migrating from Db2 Governor to Db2 workload manager
Migrating from Db2 Governor to Db2 workload manager (WLM) requires that you set up your database for coexistence of Db2 Governor and Db2 WLM, re-examine your goals, and implement a workload management solution.
Before you begin
- Review your overall approach to workload management in light of the Db2 WLM capabilities
provided to determine the best implementation. Refer to Workload
management roadmap for a number of
resources that are available to get you started with Db2 WLM, including
Best Practices: Db2 Workload Management.
- Review the Chapter 11. Db2 Governor in Db2 Workload Manager for Linux, UNIX, and Windows available at http://www.redbooks.ibm.com/redpieces/abstracts/sg247524.html for details about migration from Db2 Governor to Db2 WLM.
If your existing workload management solution includes Query Patroller, also review Migrating from Query Patroller to Db2 workload manager. Query Patroller has been discontinued in Db2.
About this task
There is no tool to automatically migrate your Governor configuration to Db2 WLM because the type of controls and mechanisms available are different between the two. When a query is running, the Governor watches for certain thresholds during the query execution which can trigger certain events. In Db2 WLM, a number of control mechanisms are available, in addition to the control of thresholds, which enable you to approach the same workload management problems in different but more effective ways.
This task provides guidelines to implement an efficient workload management solution and assist users migrating from Db2 Governor to Db2 WLM.
To migrate from Db2 Governor to Db2 WLM:
Upgrade the data server where the Governor is installed to Db2
version 11.5 so that you have an environment where Db2 WLM and the Governor
can coexist. Use one of the following tasks:
After the upgrade, there is a default workload created to identify all the user database activities and the workload is mapped to the default user service class which defines an execution environment. The Governor ACTION NICE rule clause is managed in only the default user service class. You cannot use the Governor to alter the priority of agents in user-defined service superclasses and subclasses. However, all other governor rules are enforced for all user-defined service classes.
- Limit the use of Db2 WLM to control work in the default user service class to avoid potential conflicts between the Governor and Db2 WLM.
- Re-examine your workload management goals. Understanding them is critical to implement a workload management solution.
Identify the work that runs on the data server and maps to your goals.
Take advantage of the additional identification options at your disposal in Db2 WLM.
- Manage the work that you identified
by assigning resources and imposing controls to meet your goal metrics.
Using any of the following approaches might result in a more simple and effective implementation:
- Use Db2 service classes to separate and isolate competing workloads from each other or group database activities. Then change the agent, buffer pool, and prefetch priority options each service class receives to affect their individual response times. Try this approach first instead of creating concurrency thresholds.
- Take note of the AUTHID and APPLNAME parameter values in the Governor control file and create a workload specifying the SESSION_USER and APPLNAME connection attributes using the AUTHID and APPLNAME parameter values.
- If you cannot separate work by its source using workloads, 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, manipulate the resources available to each service class to achieve your goals.
- If you do not achieve the desired results by setting the priority options each service class receives alone, selectively apply other features of Db2 WLM as needed until you achieve your goals, such as the application of Db2 thresholds.
- When you use Db2 thresholds, ensure that the threshold violations event monitor is created and activated; otherwise, you will not know when and what thresholds are being violated.
- If you create thresholds to map to the same workloads the Governor was watching, consider all the thresholds available in Db2 WLM. Some of the Db2 Governor reactive rules will find a direct functional equivalent in Db2 workload management thresholds, like those controlling maximum execution time, the maximum number of rows returned, or the maximum connection idle time.
- Other rules are unique to workload management or to the Db2 Governor and require
you to rethink your approach to controlling work in current workload management terms. Note that
rules can apply to already running queries, whereas changes to Db2 WLM thresholds apply
only to new queries.
Consider all the different threshold actions available in Db2 WLM. You can choose a more forgiving action when a resource threshold is exceeded than ending the activity, such as letting the threshold continue execution or remapping it to a service subclass with different resource controls, and you can use the information logged in the threshold violations event monitor to further investigate the activity.
- For the rowssel limit, you can create a threshold using the SQLROWSRETURNED condition to indicate what action should be taken when the limit of number of data rows returned to the application is exceeded.
- For the rowsread limit, you can create a threshold using the SQLROWSREAD or SQLROWSREADINSC condition to indicate what action should be taken when the limit of number of data rows read during query evaluation is exceeded.
- For the cpu limit, you can create a threshold using the CPUTIME or CPUTIMEINSC condition to indicate what action should be taken when the limit for the amount of combined user and system CPU time consumed by an activity is exceeded.
- For the idle limit, you can create a threshold using the CONNECTIONIDLETIME condition to indicate what action should be taken when the maximum connection idle time is exceeded.
- For the uowtime limit, you can create a threshold using the UOWTOTALTIME condition to indicate the length of time a unit of work is allowed to run.
- If you are using connection pooling, Db2 WLM has the client attributes available for proper identification and management of queries. The application at the middle tier could either call the sqleseti API or WLM_SET_CLIENT_INFO procedure to set one of the client attributes before it issues the SQL.
- If your data server runs on the AIX® operating system, consider using AIX WLM for a more granular control of processor resource.
- Monitor options to ensure that you are meeting your goals.