Some Information About DB2 10 for z/OS Hash-Organized Tables

A DB2 for z/OS DBA recently asked me about the pros and cons of hash-organized tables -- a physical database design option that is introduced with DB2 10 for z/OS running in new-function mode (NFM). Assuming that others in the mainframe DB2 community are also pondering this issue, I've decided to provide what I hope will be some useful information in this blog entry.

For as long as I can remember, there have been dichotomies with respect to DB2 for z/OS table spaces: partitioned and non-partitioned, single-table and multi-table, segmented and non-segmented, compressed and non-compressed, etc. In my mind, we really didn't have such dichotomies in the realm of tables: DB2 tables were just DB2 tables (with allowances for some specific and distinguishing usage scenarios such as clone tables and materialized query tables). Now, with DB2 10 NFM, we have what I see as a bona fide dichotomy in DB2 table-world: there are hash-organized tables and there are... Are what? "Non-hash-organized tables?" That seems to me to be a pretty clunky term. I prefer to describe a table that is not hash-organized as "cluster-organized," because that speaks to the gist of the difference between the two table types. Whereas rows in a cluster-organized table are arranged within the table according to a designated clustering key (with a requisite clustering index), rows in a hash-organized table will be physically placed according to the value generated when a row's hash key is run through a hashing algorithm. By way of the hashing algorithm, rows are mapped to the table's pages in such a way as to make the physical location of rows within a hash-organized table essentially random with respect to the unhashed key values; so, if an ORDER table has a unique key called ORDERNO (and a unique key can of course be comprised of more than one column), and if that table is clustered by the ORDERNO column, then the rows for ORDERNO 1122 and ORDERNO 1123 are likely to be on the same page (assuming a well-organized table). If, on the other hand, the same table is defined to be hash-organized with ORDERNO as the hash key (a hash key must be unique), it is very unlikely that the rows for ORDERNO 1122 and ORDERNO 1123 will be located on the same page.

Now, this idea of rows being purposely (and very) non-clustered might seem to fly in the face of traditional DB2 for z/OS physical database design wisdom, and indeed it does. Lots of us have known for years about the importance (in terms of query performance) of "locality of reference" -- the notion that rows in a set to be retrieved by a query should be located near each other in the target table. What, then, is the upside to hash-organization of rows in a table? Well, if the set of rows to be retrieved by a query in fact consists of only one row, locality of reference doesn't matter. What does matter? Getting to that one row as efficiently as possible. And how do we measure efficiency here? GETPAGEs, my friend. In mainframe DB2 parlance, a GETPAGE is a request by DB2 to examine a table or an index page. GETPAGEs are very important when it comes to the CPU cost of query execution (as I noted in a blog entry on the topic that I posted a few years ago while working as an independent DB2 consultant): enable generation of a query's result set with a smaller number of GETPAGEs, and it's a pretty safe bet that you've reduced the CPU cost of that query's execution. So, how low can you go when it comes to GETPAGE minimization? How about one GETPAGE? And how do you get to one GETPAGE for a query? With a hash-organized table, WHEN the query retrieves a single row that is qualified by an "equals" predicate that references the table's hash key. Using the example from the preceding paragraph, if you had a query like this one:

SELECT CUSTNAME, DATE, ...
FROM ORDER
WHERE ORDERNO = 1005;

You'd be likely to see one GETPAGE if the query targeted a hash-organized table (assuming that ORDERNO is the table's hash key), and maybe four GETPAGEs if the query targeted a cluster-organized table (3 index GETPAGEs if the table had a three-level index on ORDERNO, and 1 table space GETPAGE). How does DB2 get the row requested by the query with only one GETPAGE when the target table is hash-organized? Simple: it takes the hash key value referenced in the query's predicate, runs it through the hashing algorithm, and thereby obtains the row's location in the table. Due largely to the difference in GETPAGE activity, the query targeting the hash-organized table would consume less CPU time when executed. How much less will depend on several factors, but you can expect the CPU efficiency of a query with a 1-row result set with an "equals" predicate referencing a hash-organized table's hash key to be even better than retrieval of the same row from a cluster-organized table via index-only access (heretofore thought of as the cheapest way to get data from a table, and sometimes pursued in the extreme as an access path, with virtually every column in a table being included in an index key for this purpose). And, speaking of indexes, you don't even need to have an index on a hash-organized table's hash key, as I pointed out in an entry posted to this blog a couple of months ago.

Now, in the first line of the paragraph above this one, I mentioned that you're likely to see one GETPAGE for the query targeting the hash-organized table. That suggests that in some cases you could see more than one GETPAGE. When would that happen? When the target row is in fact not located in the page mapped to via the hash of the hash key value. That would be the case if on insert of the row the hash-mapped page turned out to be full. In such a situation the row is placed in the table's hash overflow area, and it would be located when needed via a special hash overflow index that contains entries only for rows located in the hash overflow area. Note that even retrieval of overflow rows should be quite efficient, as the overflow index should have a very small number of levels (since it should be pretty sparse).

This overflow business gets at one of the downsides of hash-organized tables: they'll generally take up 20-50% more disk space versus equivalent cluster-organized tables. This generosity of space is necessary to allow the large majority of rows to be located in pages identified via the hashing algorithm, and for the overflow area that accommodates rows that can't be so located. The DB2 REORG utility offers you an option -- a good one to take, by the way -- whereby DB2 will calculate, based on information in the real-time statistics tables in the catalog, the size of a hash-organized table's hash area (this is the fixed-size area into which row locations are mapped via the hashing algorithm). When REORG does this, it goes for a hash space size that will result in 5-10% of the table's rows going into the overflow area. If you want even fewer rows in the overflow area, you can increase the size of the table's hash space via ALTER TABLE (and a REORG to put the change into effect), but this will of course consume more disk space. You can use the real-time stats tables yourself to keep an eye on overflow space usage, and schedule REORGs accordingly (TOTALROWS in SYSTABLESPACESTATS tells you how many rows are in the hash-organized table, and TOTALENTRIES in SYSINDEXSPACESTATS tells you how many entries there are in the hash-organized table's overflow index).

The other downside to hash-organized tables is degraded performance for sequential data access. This is to be expected, since rows in the table are unclustered in the extreme if you think of them in terms of some clustering key. As efficient sequential access tends to be important for the performance of queries that have relatively large result sets, hash-organization of data is clearly not the best choice for all tables. In fact, my thinking at this early stage of the game is that cluster-organization of data will continue to be the right choice for most of your tables, with hash organization being appropriate for selected tables for which optimal performance for queries with single-row result sets qualified by "equals" predicates referencing a particular unique key is of prime importance (and this performance benefit extends to searched updates and deletes, as well). Hash organization of data in a table is not a DB2 physical database design panacea, but it sure is great to have in your tool box. Check it out and decide where (or if) this capability could be beneficially leveraged in your environment.