DB2 Version 9.7 for Linux, UNIX, and Windows

Migrating from DB2 Query Patroller to DB2 workload manager using the sample script

The transition from DB2® Query Patroller (QP) to DB2 workload manager (WLM) is facilitated by the QP to WLM migration sample script. Use it to quickly migrate your database to WLM. You can continue to use the same approach that QP currently uses to control your system until you determine how best to use the new WLM capabilities.

About this task

Restrictions

This script generates a file which contains the DDL statements to create the WLM objects that most closely reflect the current QP setup. Some QP features do not have a direct WLM mapping. The following QP features will either not generate WLM DDL statements or generate WLM DDL statements that are commented out in the output scripts to control their use, because there are some differences in the behavior:

min_cost_to_manage setting in QP submitter profiles: This setting has no equivalent WLM setting. This setting will be ignored and no WLM DDL statements will be generated for it.
max_cost_allowed setting for a QP submitter profile: If the qpwlmmig.pl script is run on DB2 V9.7 or later, this setting will cause an ESTIMATEDSQLCOST threshold DDL statement to be added for the associated WLM workload object. If the script is run on DB2 V9.5, this setting will be ignored and no WLM DDL statements will be generated for it.
max_queries_allowed setting in QP submitter profiles: In WLM, to restrict the number of activities that can be run in a workload occurrence, you can use the CONCURRENTWORKLOADACTIVITIES threshold. However, this is not a queuing threshold. In addition, this threshold controls the number of activities that can run concurrently in an occurrence of a workload, while the max_queries_allowed setting in QP controls the number of DML statements that can be run by a specific submitter profile. Therefore, if this setting is used in QP, then a CONCURRENTWORKLOADACTIVITIES threshold will be generated but will be commented out. You can uncomment it if required.
include_applications setting in QP system settings: This setting specifies which applications should be intercepted by QP. This setting will be ignored.
max_total_cost setting in the QP system settings: This setting has no equivalent WLM setting. This setting will be ignored and no WLM DDL statements will be generated for it.
max_total_queries setting in QP system settings: This setting controls the maximum number of DML statements that can run on the system at a time. It does not apply to any activities that are bypassing QP (either through the registry variables or through QP settings). WLM has a similar functionality in the CONCURRENTDBCOORDACTIVITIES threshold. You can use it with a DB2 Work Action Set to control the total number of DML activities running on your system. However, it applies to all DML activities, including those that were bypassing QP. Because of this, a work action set containing a CONCURRENTDBCOORDACTIVITIES threshold work action will be generated but it will be commented out. You can uncomment it and adjust its value if required.

Before you begin

The QP to WLM migration sample script files are located in the following directory:

Windows: install_path\sqllib\samples\perl
UNIX: install_path/sqllib/samples/perl

Copy the sample files from this directory to a working directory prior to running the sample programs. The sample program directories are typically read-only on most platforms and some samples produce output files that require write permission on the directory.

This sample tool must be executed by a user with DBADM authority.

The generated DDL scripts must be executed by a user with DBADM or WLMADM authority.

Procedure

Prepare your QP to WLM migration sample script environment.
1. Copy the files in one of the following directories to a working directory and ensure that the working directory has write permission (for example, working_qpwlmmig).
  Windows
  
  install_path\sqllib\samples\perl\qpwlmmig*
  
  UNIX
  
  install_path/sqllib/samples/perl/qpwlmmig*
2. Copy the files in one of the following directories to the working directory specified above.
  Windows
  
  install_path\sqllib\samples\perl\DB2WlmHist*
  
  UNIX
  
  install_path/sqllib/samples/perl/DB2WlmHist*
3. Start the Database Manager with the following command:
```
db2start
```
4. Test that you can connect to the database with the following command:
```
db2 connect to database
```
5. Navigate to the working directory containing the files copied in the first two steps. For example,
```
cd working_qpwlmmig
```
Create and review the generated DDL scripts.
1. Run the QP to WLM migration sample script qpwlmmig.pl. This generates two DDL script files:
  - outputfile contains the DDL statements to create the WLM objects that most closely reflect the current Query Patroller setup
  - outputfile.DROP contains the DDL statements to drop the WLM objects created by the first script
```
perl qpwlmmig.pl dbname user password outputfile
```
2. Open the DDL file outputfile to review the WLM objects that will be altered and created. Ensure that the names of the WLM objects are appropriate. If you change any of the WLM object names in the outputfile file, be sure to also make the appropriate name changes in the outputfile.DROP file. If you add or remove any of the DDL from the outputfile file, be sure to also make the appropriate changes in the outputfile.DROP file.
Run the generated DDL script files:
1. Connect to the database.
```
db2 connect to databasename
```
2. Run the generated outputfile file.
```
db2 -tf outputfile
```

What to do next

The generated output file contains the DDL that creates the WLM objects that are set up to collect either activity or aggregate information. In order to capture this information, create the WLM event monitors using the wlmevmon.ddl script contained in the sqllib/misc directory.

To undo the changes made from running the outputfile file, run the generated outputfile.DROP file:

	db2 -tf outputfile.DROP