Data warehousing is again becoming a well-discussed hot topic in the mainframe world. Warehousing has always been popular on System z, though it seems few want to talk about it. With all the “green” talk about System z and the rising cost of power to run large server farms (assuming you can even get additional power where your data center is located), warehousing on the mainframe is becoming even more popular.
It’s not just hardware that’s intriguing; it’s everything that runs on that hardware. Having an Integrated Facility for Linux (IFL) lets you run a copy of Linux for System z stand-alone or multiple copies of Linux under z/VM. Linux for z opens the doors to numerous product offerings. You can easily have your warehouse solution, Business Intelligence (BI) solution, and all your System z solutions sharing a single, common platform.
At the heart of any data warehouse, before you can even consider running a BI product, you need a Database Management System (DBMS). Fortunately, System z provides DB2 for z/OS. In the beginning of data warehousing, DB2 was at the center of any decision support implementation. Throughout DB2’s long history, it has always managed product enhancements that championed decision support. Although many customers continue to use DB2 for z/OS today for their warehouses, some did drift away. In an answer to the changing database landscape in today’s challenging warehousing world, IBM has delivered significant DB2 for z/OS enhancements that provide direct support for data warehousing and BI. The last few DB2 releases have been rich with capabilities to make your warehouse and BI experience better.
This article focuses on one specific, significant set of DB2 improvements that address performance. Everyone likes to see their performance improve, and with DB2 9, you won’t be disappointed. Even though this article will focus on how these performance changes could help your warehousing and BI environments, you’ll find these features will significantly help anyone using DB2 for z/OS.
What will DB2 9 for z/OS do for you? Consider the features that will help yield a positive performance impact on your data warehouse project once you upgrade to DB2 9:
Randomized index key: You want your keys spread throughout the index with no hotspots, a task sometimes easier to explain than accomplish. Sometimes stuff just all ends up in an incorrect place, no matter how hard you try to pick a key that will spread the data around. Enter DB2 9 for z/OS with a new option for the CREATE and ALTER INDEX SQL statements. In the past, you could specify ascending for forward index scans or descending for backward index scans on each column in an index key. In DB2 9, you have the additional choice of specifying RANDOM for an index column. This will cause the index entries to be put in random order by that column. Randomly inserting keys could reduce the chances of contention caused by ascending index inserts or index hot spots. Indexes created with the RANDOM option don’t support range scans.
There are instances when you can’t use RANDOM:
• If the key column is VARCHAR and the index uses the NOT PADDED option
• The index was created with the GENERATE KEY USING clause
• The index is part of the partitioning key.