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.
Trademarks
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.
Disclaimer
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.
Prev [1] [2]
