Automating index design has been an active area of research for decades due to the significant impact that indexes have on query performance and database efficiency. Existing approaches range from brute-force search to cost-based optimizations and, more recently, machine learning techniques. However, many suffer from high computational costs, reliance on inaccurate cost models, or the need for deep integration with database internals. We introduce SQL:Trek, a time-efficient tool for automated index design that operates entirely as an external utility. SQL:Trek leverages query compiler cost models to identify effective indexes while mitigating false positives through execution on a lightweight simulation database. This approach enables fast, iterative index selection without modifying database internals, making it broadly applicable across relational databases, including most MySQL ® and PostgreSQL ® derivative databases. Our evaluation demonstrates that SQL:Trek delivers significant query performance improvements while keeping index selection computationally efficient, with most workloads analyzed in under five minutes. Unlike many cost-based what-if analysis methods, SQL:Trek significantly improved performance of many production workloads while avoiding the majority of detrimental index recommendations caused by optimizer misestimates. These results highlight SQL:Trek as a practical, scalable solution for automated index tuning in modern database environments.

In this paper we introduce dashDB Local, a new Big Data & SQL warehousing technology from IBM designed to provide dramatic simplification over traditional data warehousing deployments, while offering next generation query performance and the analytic richness of the Apache Spark ecosystem. Designed as a Software Defined Technology, dashDB Local automatically adapts to hardware platforms to provide simplified deployments. In experiments one multiple workloads, we have achieved deployment times for large clusters in <; 30 minutes while providing dramatic workload performance speedups of several factors on multiple workloads under study compared to industry leading appliance technology and market leading data warehouses. We see dashDB Local providing five novel value propositions: 1. Improved deployment with automatic adaptation to the target hardware enabling fast deployment in minutes of fully configured data warehouse and Big Data clusters from gigabytes to petabytes. 2. Superior workload performance through advances in in-memory columnar algorithms. 3. Rich polyglot language support, supporting many dialects of Big Data, SQL languages. 4. Full stack integration of Apache Spark into the processing engine, co-existing with the rich SQL engine. 5. Compatibility with dashDB as a service in the cloud, providing on-premises and cloud flexibility to develop and run applications and analytics where desired.

Although the DRAM for main memories of systems continues to grow exponentially according to Moore's Law and to become less expensive, we argue that memory hierarchies will always exist for many reasons, both economic and practical, and in particular due to concurrent users competing for working memory to perform joins and grouping. We present the in-memory BLU Acceleration used in IBM's DB2 for Linux, UNIX, and Windows, and now also the dashDB cloud offering, which was designed and implemented from the ground up to exploit main memory but is not limited to what fits in memory and does not require manual management of what to retain in memory, as its competitors do. In fact, BLU Acceleration views memory as too slow, and is carefully engineered to work in higher levels of the system cache by keeping the data encoded and packed densely into bit-aligned vectors that can exploit SIMD instructions in processing queries. To achieve scalable multi-core parallelism, BLU assigns to each thread independent data structures, or partitions thereof, designed to have low synchronization costs, and doles out batches of values to threads. On customer workloads, BLU has improved performance on complex analytics queries by 10 to 50 times, compared to the legacy row-organized run-time, while also significantly simplifying database administration, shortening time to value, and improving data compression. UPDATE and DELETE performance was improved by up to 112 times with the new Cancun release of DB2 with BLU Acceleration, which also added Shadow Tables for high performance on mixed OLTP and BI analytics workloads, and extended DB2's High Availability Disaster Recovery (HADR) and SQL compatibility features to BLU's column-organized tables.

We present new hash tables for joins, and a hash join based on them, that consumes far less memory and is usually faster than recently published in-memory joins. Our hash join is not restricted to outer tables that fit wholly in memory. Key to this hash join is a new concise hash table (CHT), a linear probing hash table that has 100% fill factor, and uses a sparse bitmap with embedded population counts to almost entirely avoid collisions. This bitmap also serves as a Bloom filter for use in multi-table joins. We study the random access characteristics of hash joins, and renew the case for non-partitioned hash joins. We introduce a variant of partitioned joins in which only the build is partitioned, but the probe is not, as this is more efficient for large outer tables than traditional partitioned joins. This also avoids partitioning costs during the probe, while at the same time allowing parallel build without latching overheads. Additionally, we present a variant of CHT, called a concise array table (CAT), that can be used when the key domain is moderately dense. CAT is collision-free and avoids storing join keys in the hash table. We perform a detailed comparison of CHT and CAT against leading in-memory hash joins. Our experiments show that we can reduce the memory usage by one to three orders of magnitude, while also being competitive in performance.

Compression has historically been used to reduce the cost of storage, I/Os from that storage, and buffer pool utilization, at the expense of the CPU required to decompress data every time it is queried. However, significant additional CPU efficiencies can be achieved by deferring decompression as late in query processing as possible and performing query processing operations directly on the still-compressed data. In this paper, we investigate the benefits and challenges of performing joins on compressed (or encoded) data. We demonstrate the benefit of independently optimizing the compression scheme of each join column, even though join predicates relating values from multiple columns may require translation of the encoding of one join column into the encoding of the other. We also show the benefit of compressing "payload" data other than the join columns "on the fly," to minimize the size of hash tables used in the join. By partitioning the domain of each column and defining separate dictionaries for each partition, we can achieve even better overall compression as well as increased flexibility in dealing with new values introduced by updates. Instead of decompressing both join columns participating in a join to resolve their different compression schemes, our system performs a light-weight mapping of only qualifying rows from one of the join columns to the encoding space of the other at run time. Consequently, join predicates can be applied directly on the compressed data. We call this procedure encoding translation. Two alternatives of encoding translation are developed and compared in the paper. We provide a comprehensive evaluation of these alternatives using product implementations of each on the TPC-H data set, and demonstrate that performing joins on encoded and partitioned data achieves both superior performance and excellent compression.

DB2 with BLU Acceleration deeply integrates innovative new techniques for defining and processing column-organized tables that speed read-mostly Business Intelligence queries by 10 to 50 times and improve compression by 3 to 10 times, compared to traditional row-organized tables, without the complexity of defining indexes or materialized views on those tables. But DB2 BLU is much more than just a column store. Exploiting frequency-based dictionary compression and main-memory query processing technology from the Blink project at IBM Research - Almaden, DB2 BLU performs most SQL operations - predicate application (even range predicates and IN-lists), joins, and grouping - on the compressed values, which can be packed bit-aligned so densely that multiple values fit in a register and can be processed simultaneously via SIMD (single-instruction, multipledata) instructions. Designed and built from the ground up to exploit modern multi-core processors, DB2 BLU's hardware-conscious algorithms are carefully engineered to maximize parallelism by using novel data structures that need little latching, and to minimize data-cache and instruction-cache misses. Though DB2 BLU is optimized for in-memory processing, database size is not limited by the size of main memory. Fine-grained synopses, late materialization, and a new probabilistic buffer pool protocol for scans minimize disk I/Os, while aggressive prefetching reduces I/O stalls. Full integration with DB2 ensures that DB2 with BLU Acceleration benefits from the full functionality and robust utilities of a mature product, while still enjoying order-of-magnitude performance gains from revolutionary technology without even having to change the SQL, and can mix column-organized and row-organized tables in the same tablespace and even within the same query.

Commercial databases compete for market share, which is composed of not only net-new sales to those purchasing a database for the first time, but also competitive "win-backs" and migrations. Database migration, or the act of moving both application code and its underlying database platform from one database to another, presents a serious administrative and application development challenge fraught with large manual costs. Migration is typically a high cost effort due to incompatibilities between database platforms. Incompatibilities are caused most often by product specific extensions to language support, procedural logic, DDL, and administrative interfaces. The migration evaluation is the first step in any competitive database migration process. Historically this has been a manual process, with the high costs and subjective results. This has led us to reexamine traditional practices and explore an automatic, innovative solution. We have designed and implemented the Migration Evaluation and Enablement Tool for DB2 for Linux Unix and Windows, or MEET DB2, a tool for automatically evaluating database migration projects. Encapsulated in a simple one-click interface, MEET DB2 is able to provide detailed evaluation of migration complexity based on its deep analysis on the source database. In this paper, we present MEET DB2, and discuss many aspects of our design, and report measurements from real-world use cases. In particular, we show a novel way to use XML and XQuery in this domain for better extensibility and interoperability. We have evaluated MEET DB2 on 18 source code samples, covering nearly 1 million lines of code. The utility has provided benefits in several dimensions including: dramatically reduced time for evaluation, consistency, improved accuracy over human analysis, improved reporting, reduced skill requirements for migration analysis, and clear analytics for product planning.

Lock memory consumption can be difficult to project and can vary rapidly in short amounts of time. This volatility makes lock memory tuning difficult and can result in either significant memory waste if systems are configured for peak requirements, or lock escalation and lock wait if under configured; either of which can cause significant performance penalties. This paper describes an algorithm for adaptive tuning of database lock memory. The DB2 technique adapts the locking memory in real time to mitigate the occurrence of lock escalations. The technique uses a combination of synchronous and asynchronous modification to the locking structures so that it can respond well to rapid immediate growth in locking requirements. The adaptive algorithm also relaxes the locking memory over time so that peak requirements in lock memory will not result in a permanently large allocation of memory to locks. Experimental tests have shown this technique to work well in a number of benchmark and adaptive workloads, converging almost immediately to optimal settings which avoid lock escalations and achieve optimal throughput. The solution has been implemented in DB2 9.

Thevast majority of the world's structured data are now stored and managed by relational database management systems (RDBMSs). These systems provide powerful data management capabilities. However, as the power and functionality of these systems has grown, so has the complexity of their administration. In this paper, we show how the IBM DB2 Universal Database for Linux, UNIX, and Windows (DB2 UDB) product has exploited autonomic computing to reduce this administration complexity and become more self-managing. We survey the major autonomic computing features in the DB2 UDB product and describe the benefits, with experimental data in some cases

Load balancing is widely used in computing systems as a way to optimize performance by reducing bottleneck utilizations, such as adjusting the size of buffer pools to balance resource demands in a database management system. Load balancing is generally approached as a constrained optimization problem in which only the benefits of load balancing are considered. However, the costs of control are important as well. Herein, we study the value of including in controller design the trade-off between the cost of transient imbalances in resource utilizations and the cost of changing resource allocations. An example of the latter are actions such as resizing buffer pools that can reduce throughputs. This is because requests for data in pools whose memory is reduced immediately have longer access times whereas requests for data in pools whose memory is increased must fill this memory with data from disk before accessed times are reduced. We frame our study of control costs in terms of the widely used linear quadratic regulator (LQR). We develop a cost model that allows us to specify the LQR Q and R matrices based on the impact on system performance of changing resource allocations and transient load imbalances. Our studies of a DB2 universal database server using benchmarks for online transaction processing and decision support workloads show that incorporating our cost model into the MIMO LQR controller results in a 14% improvement in performance beyond that achieved by dynamically allocating the size of buffers without properly considering the cost of control.

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