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.
About this task
This task provides guidelines to implement an efficient
workload management solution and assist users migrating from Query
Patroller to DB2 WLM.
Restriction
- There is no equivalent in DB2 WLM
for the bypass options in Query Patroller.
Procedure
To migrate from Query Patroller to DB2 WLM:
- 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:
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.
- 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.
- 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. You
can map Query Patroller management functions to a DB2 WLM equivalent using one of the following
approaches:
- 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.
- 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.
- 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.
- Monitor options to ensure that you
are meeting your goals.