Migrating from Query Patroller to DB2 workload manager

Migrating from Query Patroller to DB2® workload management (WLM) requires that you set up your database for coexistence of Query Patroller and DB2 WLM, re-examine your goals, and implement a workload management solution.

To migrate from Query Patroller to DB2 WLM:

1. Upgrade the data server where Query Patroller is installed to DB2 Version 9.7 so that you have an environment where DB2 WLM and Query Patroller can coexist so that you can reduce the migration impact and risk. Use one of the following tasks:
   • Upgrading DB2 servers (Windows)
   • Upgrading DB2 servers (Linux and UNIX)

   After the upgrade, there is a default workload created to identify all the user database activities and map them to a default user service class which is the execution environment. Query Patroller can only intercept and manage queries assigned and executing in the default user service class. If there are workloads defined to route user activities to service classes other than the default user service class, Query Patroller cannot be able to manage those activities.

2. Limit the use of DB2 WLM to control work in the default user service class to avoid potential conflicts between Query Patroller and DB2 WLM.
3. Re-examine your workload management goals. Understanding them is critical to implement a workload management solution.
4. 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.
5. Manage the work that you identified by assigning resources and imposing controls to meet your goal metrics. You can map Query Patroller management functions to a DB2 WLM equivalent using one of the following approaches:
   1. If you have an effective Query Patroller implementation, you can map Query Patroller management functions to WLM by using the qpwlmmig.pl script provided in the INSTHOME/sqllib/samples/perl (Linux and UNIX) or DB2PATH\samples\perl (Windows) directory. This Perl script allows you to generate a DB2 script containing DDL statements to create the database objects that best emulate the behavior of your Query Patroller implementation using WLM. Modify the script if necessary and run it to create an initial WLM setup.
   2. You can moderate the transition between Query Patroller and DB2 WLM by gradually creating service classes. Use any of the following approaches for a simple and effective implementation:
      • Use DB2 service classes to separate and isolate competing workloads from each other or to 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.
      • 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, including concurrency 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.
      • To implement query classes, you can use work classes and work action sets to identify the DML work and then set up thresholds on the work action set based on the range of query cost.
      • Identify large queries and consider the option of collecting detailed information, or remapping these queries to service subclasses with different resource controls, before taking the more severe action of stopping execution. When collecting information for later analysis, you can limit the scope of what you collect to a specific service class.
      • 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.
      • If your data server runs on the Linux operating system, consider using WLM for control of processor resource. Linux kernel version 2.6.26 or later on 64-bit systems is required.
6. If you used historical analysis functions in Query Patroller, Review Exercise 10: Generating historical data and reports to learn how to use the DB2 WLM Historical Analysis Tool sample. DB2 samples include a set of Perl scripts that provides functionality similar to the Query Patroller historical analysis functions using information captured by the DB2 WLM activity event monitor. You can modify the scripts to produce additional historical analysis reports to suit your needs.
7. Monitor options to ensure that you are meeting your goals.