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:
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:
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):
