DB2 Query Patroller and the DB2 Design Advisor

Wednesday Dec 21st 2005 by DatabaseJournal.com Staff

Learn how to pull a subset of the data from the DB2 QP historical information and pass it to the DB2 Design Advisor using the command-line interface to take advantage of this feature.

by Paul C. Zikopoulos

The IBM DB2 Universal Database product (DB2 UDB) comes with countless features and capabilities, among them the DB2 Query Patroller product.

For data warehousing, the DB2 Query Patroller (DB2 QP) product can be used to proactively manage users, groups, and queries bound for a DB2 UDB-managed data warehouse. Using the DB2 QP technology, you can specify that certain classes of users be permitted to run more resource-intensive queries than others, or that certain queries be automatically held and not run until less work-intensive windows in the business landscape.

DB2 UDB also comes with the DB2 Design Advisor. This must-have tool for database administrators (DBAs) can suggest up to four schema objects for a DB2 UDB database: Indexes, Materialized Query Tables (MQTs), Multidimensional Clustering (MDC) tables, and partitioning keys for DB2 UDB databases that leverage the database partitioning feature (DPF). With a query set, DB2 UDB can generate a list of recommended objects (or changes) that will evolve the schema of the database to match the query set.

These features are well integrated in DB2 UDB. In fact, you can import the DB2 QP workload into the DB2 Design Advisor for analysis. This gives DBAs a powerful opportunity to better understand their database environment, evolve their schema in a way that benefits the workload, and apply charge-back accounting metrics to different lines of business (LOBs) that leverage the data warehouse.

When you import the captured workload from DB2 QP using the DB2 Design Advisor, it is pretty much an all-or-nothing event. In other words, if you had two years of charge-back data that you wanted to load into the DB2 Design Advisor, you would have to take the entire work set. Depending on the activity levels of your data warehouse, you could spend a lot of time just loading in the data set.

In this article, I will share with you a little-known way that you can select the interval of data that you pass to the DB2 Design Advisor so that you can focus on specific intervals for schema evolution. Remember, however, that the DB2 Design Advisor can only make schema suggestions based on the workload you provide it – so limiting the workload could limit the applicability of the results to your system.

Historical Accounting in DB2 Query Patroller

DB2 QP also gives DBAs unique insight into the operational activities of their data warehouse using the historical analysis capabilities. For example, using this feature, a DBA can determine (across a sliding interval) the tables that are hit most often. For example, the following figure shows a DB2 QP-generated report showing the number of queries run across a two-month reporting window ending on September 16th, 2005 at 23:11:58:

Click for larger image

You can see in the preceding figure that there are a number of different views that you can have of this data.

You can also view this distribution table by table, and drill down to see the SQL statements each tool generated, whether the query ran successfully or not, the cost of the query, when it started, when it finished (if it did), what application generated the query, and more:

Click for larger image

DB2 QP also comes with many other preconfigured reports. You can see which users hit which tables, which columns they touched, which columns are not being used, which indexes were touched by the queries, which indexes are not being hit via the query workload, and more:

A Great Object Design Tool

DB2 UDB comes with the world's most comprehensive object design tool: the DB2 Design Advisor. The DB2 Design Advisor helps DBAs quickly adapt their schema to match their workloads. It provides a method by which the database schema can be modeled from a top-down approach when the bottom-up approach becomes impractical because of implementing third-party or home-grown applications. The DB2 Design Advisor can be used to suggest Indexes, Materialized Query Tables (MQTs), Multidimensional Clustering (MDC) tables, and partitioning keys for DB2 UDB databases that leverage the database partitioning feature (DPF), as shown below:

You can also instruct DB2 UDB to suggest only those schema changes that fall within specific business policies, such as the amount of storage space that would be consumed by the suggestion set, and even the amount of time DB2 UDB will spend analyzing the data and coming up with a suggestion set:

The DB2 Design Advisor is a powerful tool that all DBAs should get acquainted with. How powerful? In our labs, we took 1 GB of the TPC-H database warehousing benchmark specification and ran the 22-query stream against a DB2 UDB database that was actually decently tuned. (It had a respectable, though not optimal, partitioning key; the indexes created on this database were the same as the actual indexes we created for our world-record-breaking TPC-H benchmarks). We ran the query test to set the baseline for DB2 UDB performance. We then ran the DB2 Design Advisor and set the think-time limit for a suggested list of objects to 10 minutes. After implementing those results, performance increased by almost 650%, as shown below:

This performance was achieved after implementing the following changes as suggested by the DB2 Design Advisor:

This is a simple, but very compelling, example of how the tools in DB2 UDB can be used to provide compelling advantages without requiring the services of an expert DBA.

In fact, Merrill Lynch uses the DB2 Design Advisor quite extensively in their DB2 UDB data warehouse environment, as shown below. For more details on how Merrill Lynch benefits from the DB2 Design Advisor, check out DB2 Magazine (an excerpt is shown below):

by Paul C. Zikopoulos

Analyzing a Subset of the DB2 Query Patroller Historical Data

When you use the DB2 Design Advisor to import the DB2 QP workload into the tool for analysis, you may have noticed that there is no way to control the interval. In other words, you may only want to look at a specific month's query load since a new application was added, as opposed to analyzing and loading the whole year's historical tracking data that you've kept around for macro-level analysis.

The following figure shows how you import that DB2 QP workload into the DB2 Design Advisor for analysis:

Click for larger image

In DB2 UDB V8.1.5 (a.k.a. Fix Pack 5), a new feature was added to the db2advis command that allows you to pass a subset of the DB2 QP historical information to the DB2 Design Advisor. The db2advis command provides a non-graphical interface to the algorithms that power the DB2 Design Advisor. Some DBAs prefer to leverage this method for interaction with this tool. The algorithms and the result set from either method will always be the same – just the interface is different.

An example of using the command-line method to interact with this technology is shown below:

Click for larger image

The –qp flag can be used to specify start- and end-times (an interval) for which the corresponding historical data will be passed to the DB2 Design Advisor algorithms. (Note that this option is not available with the graphical version of this tool).

Essentially, you use the–qp flag to specify an historical interval as follows:

db2advis ... -qp [<starttime> [<endtime>]]

If you only use the [<starttime>] parameter, then the queries returned to the DB2 Design Advisor from the DB2 QP historical repository are only those that completed after that time. If you additionally specify the [<endtime>], you are specifying an upper bound by which queries would have had to be completed by.

For example, to pass only the queries that were managed by DB2 QP from September 1st, 2005 until September 15, 2005, you would enter a command similar to this:

    db2advis –d sample -qp 2005-09-01 2005-09-15

The following figure shows the passing of all the queries run on my system since September 1st, 2005 at 1:00 p.m. to the DB2 Design Advisor using the db2advis command:

You can see that DB2 QP captured a total of 71 statements for historical tracking and passed these to the DB2 Design Advisor. Note as well that the DB2 Design Advisor suggested that two indexes be created for these queries.

Wrapping It All Up

In this article, I described a method by which you can pull a subset of the data from the DB2 QP historical information and pass it to the DB2 Design Advisor. Although this feature is not available from the DB2 Design Advisor graphical interface, DBAs can leverage the command-line interface to take advantage of this feature. In a future article, I will detail another process to accomplish this task, whereby you generate a query workload file from the DB2 QP tables and pass the file to the DB2 Design Advisor.

About the Author


Paul C. Zikopoulos, BA, MBA, is an award-winning writer and speaker with the IBM Database Competitive Technology team. He has more than ten years of experience with DB2 UDB and has written over sixty magazine articles and several books about it. Paul has co-authored the books: DB2 Version 8: The Official Guide, DB2: The Complete Reference, DB2 Fundamentals Certification for Dummies, DB2 for Dummies, and A DBA's Guide to Databases on Linux. Paul is a DB2 Certified Advanced Technical Expert (DRDA and Cluster/EEE) and a DB2 Certified Solutions Expert (Business Intelligence and Database Administration). In his spare time, he enjoys all sorts of sporting activities, running with his dog Chachi, and trying to figure out the world according to Chloë – his new daughter. You can reach him at: paulz_ibm@msn.com.


IBM, DB2, and DB2 Universal Database are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both.

Linux is a trademark of Linus Torvalds in the United States, other countries, or both.

Other company, product, and service names may be trademarks or service marks of others.

Copyright International Business Machines Corporation, 2005. All rights reserved.


The opinions, solutions, and advice in this article are from the author's experiences and are not intended to represent official communication from IBM or an endorsement of any products listed within. Neither the author nor IBM is liable for any of the contents in this article. The accuracy of the information in this article is based on the author's knowledge at the time of writing.

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved