Daniel C. Zilio

DB2 advisor: an optimizer smart enough to recommend its own indexes

This paper introduces the concept of letting an RDBMS optimizer optimize its own environment. In our project, we have used the DB2 optimizer to tackle the index selection problem, a variation of the knapsack problem. This paper discusses our implementation of index recommendation, the user interface, and provide measurements on the quality of the recommended indexes.

Toward autonomic computing with DB2 universal database

As the cost of both hardware and software falls due to technological advancements and economies of scale, the cost of ownership for database applications is increasingly dominated by the cost of people to manage them. Databases are growing rapidly in scale and complexity, while skilled database administrators (DBAs) are becoming rarer and more expensive. This paper describes the self-managing or autonomic technology in IBMs DB2 Universal Database® for UNIX and Windows to illustrate how self-managing technology can reduce complexity, helping to reduce the total cost of ownership (TCO) of DBMSs and improve system performance.

Load Balancing for Multi-tiered Database Systems through Autonomic Placement of Materialized Views

A materialized view or Materialized Query Table (MQT) is an auxiliary table with precomputed data that can be used to significantly improve the performance of a database query. AMaterialized Query Table Advisor (MQTA) is often used to recommend and create MQTs. The state-of-the-art MQTA works in a standalone database server where MQTs are placed on the same server as that in which the base tables are located. The MQTA does not apply to a federated or scaleout scenario in which MQTs need to be placed on other servers close to applications (i.e. a frontend database server) for offloading the workload on the backend database server. In this paper, we propose a Data Placement Advisor (DPA) and load balancing strategies for multi-tiered database systems. Built on top of the MQTA, DPA recommends MQTs and advises placement strategies for minimizing the response time for a query workload. To demonstrate the benefit of the data placement advising, we implemented a prototype of DPA that works with theMQTA in the IBM® DB2® Universal Database^TM (DB2 UDB) and the IBM WebSphere® Information Integrator (WebSphere II). The evaluation results showed substantial improvements of workload response times when MQTs are intelligently recommended and placed at a frontend database server subject to space and load characteristics for TPC-H and OLAP type workloads.

Self-managing technology in IBM DB2 universal database

As the cost of both hardware and software falls due to technological advancements and economies of scale, the cost of ownership for database applications is increasingly dominated by the cost of people to manage them. Databases are growing rapidly in scale and complexity, while skilled database administrators (DBAs) are becoming rarer and more expensive. The scope of responsibility of DBAs is indeed daunting. This paper describes the self-managing technology in IBM DB2 Universal Database to illustrate how self-managing technology can enhance the usability of enterprise middleware and reduce the total cost of ownership (TCO).

An xml index advisor for DB2

XML database systems are expected to handle increasingly complex queries over increasingly large and highly structured XML databases. An important problem that needs to be solved for these systems is how to choose the best set of indexes for a given workload. We have developed an XML Index Advisor that solves this XML index recommendation problem and is tightly coupled with the query optimizer of the database system. We have implemented our XML Index Advisor for DB2. In this demonstration we showcase the new query optimizer modes that we added to DB2, the index recommendation process, and the effectiveness of the recommended indexes.

Data reorganization in parallel database systems

Parallel database systems are suitable for use in applications with high capacity and high performance and availability requirements. The trend in such systems is to provide efficient online capability for performing various system administration functions such as, index creation and maintenance, backup/restore, reorganization, and gathering of statistics. For some of these functions the online capability can be efficiently supported by the use of incremental algorithms, i.e., algorithms that achieve the function in several, relatively small (i.e., less time-consuming) steps, rather than in a single, large step. Incremental algorithms ensure that only small parts of the database become inaccessible for short durations as opposed to nonincremental algorithms which may lock large portions of the database or the entire database for a longer duration. The authors discuss issues in providing concurrent data reorganization capability using incremental algorithms in parallel database systems.

Recommending XML physical designs for XML databases

Database systems employ physical structures such as indexes and materialized views to improve query performance, potentially by orders of magnitude. It is therefore important for a database administrator to choose the appropriate configuration of these physical structures for a given database. XML database systems are increasingly being used to manage semi-structured data, and XML support has been added to commercial database systems. In this paper, we address the problem of automatic physical design for XML databases, which is the process of automatically selecting the best set of physical structures for a database and a query workload. We focus on recommending two types of physical structures: XML indexes and relational materialized views of XML data. We present a design advisor for recommending XML indexes, one for recommending materialized views, and an integrated design advisor that recommends both indexes and materialized views. A key characteristic of our advisors is that they are tightly coupled with the query optimizer of the database system, and they rely on the optimizer for enumerating and evaluating physical designs. We have implemented our advisors in a prototype version of IBM DB2 V9, and we experimentally demonstrate the effectiveness of their recommendations using this implementation.

Recommending XMLTable Views for XQuery Workloads

Physical structures, for example indexes and materialized views, can improve query execution performance by orders of magnitude. Hence, it is important to choose the right configuration of these physical structures for a given database. In this paper, we discuss the types of materialized views that are suitable for an XML database. We then focus on XMLTable materialized views and present a procedure to recommend them given an XML database and a workload of XQuery queries. We have implemented our XMLTable View Advisor in a prototype version based on IBM® DB2® V9.7, which supports both relational and XML data, and we experimentally demonstrate the effectiveness of our advisors recommendations.

XML Index Recommendation with Tight Optimizer Coupling

XML database systems are expected to handle increasingly complex queries over increasingly large and highly structured XML databases. An important problem that needs to be solved for these systems is how to choose the best set of indexes for a given workload. In this paper, we present an XML Index Advisor that solves this XML index recommendation problem and has the key characteristic of being tightly coupled with the query optimizer. We rely on the optimizer to enumerate index candidates and to estimate the benefit gained from potential index configurations. We expand the set of candidate indexes obtained from the query optimizer to include more general indexes that can be useful for queries other than those in the training workload. To recommend an index configuration, we introduce two new search algorithms. The first algorithm finds the best set of indexes for the specific training workload, and the second algorithm finds a general set of indexes that can benefit the training workload as well as other similar workloads. We have implemented our XML Index Advisor in a prototype version of IBMreg DB2reg 9, which supports both relational and XML data, and we experimentally demonstrate the effectiveness of our advisor using this implementation.

Load balancing and data placement for multi-tiered database systems

A materialized view or Materialized Query Table (MQT) is an auxiliary table with precomputed data that can be used to significantly improve the performance of a database query. A Materialized Query Table Advisor (MQTA) is often used to recommend and create MQTs. The state-of-the-art MQTA works in a standalone database server where MQTs are placed on the same server as that in which the base tables are located. The MQTA does not apply to a federated or scaleout scenario in which MQTs need to be placed on other servers close to applications (i.e. a frontend database server) for offloading the workload on the backend database server. In this paper, we propose a Data Placement Advisor (DPA) and load balancing strategies for multi-tiered database systems. Built on top of the MQTA, DPA recommends MQTs and advises placement strategies for minimizing the response time for a query workload. To demonstrate the benefit of the data placement advising, we implemented a prototype of DPA that works with the MQTA in the IBM^(R) DB2^(R) Universal Database(TM) (DB2 UDB) and the IBM WebSphere^(R) Information Integrator (WebSphere II). The evaluation results showed substantial improvements of workload response times when MQTs are intelligently recommended and placed on a frontend database server subject to space and load characteristics for TPC-H and OLAP type workloads.