Outer Join: Get the Predicates Right

A few days ago, I was working with a team of people from a large company, trying to improve the performance of some queries executing in a DB2 for z/OS-based data warehouse environment. One query in particular was running much longer than desired, and consuming a lot of CPU time, to boot. One of the team members noticed that the problem query, which involved several table-join operations, had a rather odd characteristic: no WHERE-clause predicates. All the predicates were in the ON clauses of the joins. In fact, there was even an inner join of table TAB_A (I won't use the real table names) with SYSIBM.SYSDUMMY1 (which of course contains nothing), with two ON predicates referencing columns in TAB_A, like this:

SELECT...
FROM TAB_A
INNER JOIN SYSIBM.SYSDUMMY1
ON TAB_A.COL1 = 12
AND TAB_A.COL2 = 'X'
...

One of the application developers (the queries we were analyzing are report-generating SELECT statements built and issued by application programs) removed this inner join to SYSDUMMY1 and changed the two ON-clause predicates to WHERE-clause predicates, and the query's elapsed and CPU times went way down.

We were left thinking that this Cartesian join (i.e., a join with no join columns specified) to SYSDUMMY1 might reflect someone's thinking that WHERE-clause predicates are not needed in table-join SELECT statements. In fact, the use of WHERE-clause predicates versus ON-clause predicates in table-join statements can have a very significant impact on query performance. We looked at another long-running query in the aforementioned data warehouse application, and this one also involved a join operation and also had no WHERE-clause predicates. Importantly, the join was a left outer join, and the ON clause included multiple predicates that referenced columns of the left-side table (the table from which we want rows for the result set, regardless of whether or not there are matching right-side table rows). A DBA took one of these left-side-table-referencing ON-clause predicates and made it a WHERE predicate, too. In other words, an ON-clause predicate like TAB_L.COL2 = 5 (with TAB_L being the left-side table in the left outer join operation) was added to the query in the form of a WHERE-clause predicate. The result? Response time for the query went from 10 minutes to less than 1 second.

Why did the query's performance improve so dramatically, when all the DBA did was make an ON-clause predicate a WHERE-clause predicate? Simple: for a left outer join, a WHERE-clause predicate that references a column of the left-side table will filter rows from that table. That same predicate, if coded in the ON-clause of the SELECT statement, will NOT filter left-side table rows. Instead, that predicate will just affect the matching of left-side table rows with right-side table rows. To illustrate this point, consider the predicate mentioned in the preceding paragraph:

TAB_L.COL2 = 5

Suppose this predicate is included in the query in the following way:

SELECT TAB_L.COL1, TAB_L.COL2, TAB_L.COL3, TAB_R.COL4
FROM TAB_L
LEFT OUTER JOIN TAB_R
ON
TAB_L.COL3 = TAB_R.COL3
AND TAB_L.COL2 = 5

With no WHERE-clause predicates in this query, all rows from TAB_L will qualify - none will be filtered out. What the TAB_L.COL2 = 5 predicate will do is affect row matching with TAB_R: if a row in TAB_R has a COL3 value that matches the value of COL3 in TAB_L, and if the value of COL2 in that row is 5, the TAB_R row (specifically, COL4 of that row, as specified in the query's SELECT-list) will be joined to the TAB_L row in the query result set; otherwise, DB2 will determine that the TAB_R row is not a match for any TAB_L rows (and the TAB_L rows without TAB_R matches will appear in the result set with the null value in the TAB_R.COL4 column).

Now, suppose that the same predicate is specified in a WHERE clause of the query, as follows:

SELECT TAB_L.COL1, TAB_L.COL2, TAB_L.COL3, TAB_R.COL4
FROM TAB_L
LEFT OUTER JOIN TAB_R
ON
TAB_L.COL3 = TAB_R.COL3
WHERE TAB_L.COL2 = 5

In this case, the predicate will be applied to TAB_R before the join operation, potentially filtering out a high percentage of TAB_R rows (as was the case for the query cited earlier that went from a 10-minute to a sub-second run time).

So, a person codes a predicate that references a column of the left-side table of a left outer join operation, and places that predicate in an ON clause of the query, versus a WHERE clause (and I'm not talking about a join predicate of the form TAB_L.COLn = TAB_R.COLn, which you expect to see in an ON clause). Is it possible that the query-writer actually wants the result described above, to wit: no filtering of left-side table rows, and a further condition as to what constitutes a right-side table match? Yes, that's possible, but there's a very good chance that this person mistakenly placed the predicate in an ON clause because he (or she) thought that this would have the same effect as coding the predicate in a WHERE clause. Mistakes of this type are fairly common because misunderstanding with respect to the effect of predicates in outer join queries is quite widespread. Patrick Bossman, a good friend who is a query optimization expert with IBM's DB2 for z/OS development organization, pointed out as much to me in a recent e-mail exchange. Patrick also sent me the links to two outstanding articles written by Terry Purcell, a leader on the IBM DB2 for z/OS optimizer team. These articles (actually, a part-one and part-two description of outer join predicates and their effects on query result sets) were written a few years ago, while Terry was with DB2 consultancy Yevich, Lawson, and Associates, but the content is still very much valid today (Patrick considers the articles to be "a must-read for folks writing outer joins"). Check 'em out.

Outer join is a powerful SQL capability that is widely used in DB2 environments. If you code outer join queries (or if you review such queries written by others), make sure that you use ON-clause and WHERE-clause predicates appropriately, so as to get the right result (job one) and the best performance (job two, right behind job one).

DB2 9 Native SQL Procedures: One Address Space is OK

I've been doing a lot of presenting lately on the topic of DB2 for z/OS stored procedures. In these presentations, I've emphasized the benefits of native SQL procedures, introduced for the mainframe platform via DB2 9 for z/OS (I blogged on the importance of this development in an entry I posted late last year). During two different sessions held recently in two different cities, two different people asked me the same question pertaining to native SQL procedures versus external stored procedures (the latter being what you might think of as "traditional" stored procedures in a DB2 for z/OS environment). In this entry, I'll share with you that question and my response.

First, the common question: "When DB2 for z/OS Version 5 provided support for stored procedure address spaces managed by the Workload Manager (WLM) component of z/OS, we were told that an advantage of this enhancement was the ability to have multiple stored procedure address spaces, versus the one DB2-managed stored procedures address space (SPAS). With different stored procedures assigned to different WLM application environments and their associated address spaces, if a stored procedure program misbehaved in such a way as to bring down the address space in which it was running, the other stored procedure address spaces would not be impacted. Now, with DB2 9 native SQL procedures, we're back to one address space for stored procedure execution (native SQL procedures execute in the DB2 database services address space, also known as DBM1). Doesn't that mean that we now have the same risk we faced when using the old DB2-managed SPAS, namely, that one errant stored procedure could take down the one stored procedure address space (and this time, we're talking about losing DBM1)?"

My response: It's true that having multiple WLM-managed stored procedure address spaces can reduce the impact of an address space failure caused by an external stored procedure program, but that kind of failure has to do with stored procedure program code executing outside of DB2. Multiple DB2-accessing stored procedures running in in multiple stored procedure address spaces are all executing code in DBM1 when they issue SQL statements (as is true of multiple DB2-accessing CICS transaction programs running in multiple CICS AORs), and that doesn't cause DBM1 to crash. Native SQL procedures running in DBM1 execute as packages. It's all DB2-generated and DB2-managed code. This means that the exposure mitigated by having multiple WLM-managed stored procedure address spaces - that user-written stored procedure program code running outside of DB2 could cause a problem that would lead to the failure of a WLM-managed address space - does not exist for native SQL procedures. To put it another way, having native SQL procedures executing in one address space - DBM1 - is no more risky than having multiple packages invoked by external callers all running in DBM1, and that's been standard operating procedure for DB2 since day one (execution of an embedded SQL statement involves, under the covers, a call to DB2 and a reference to a section of a package).

So, take advantage of the enhanced performance and simplified lifecycle management offered by DB2 9 native SQL procedures, and don't worry about not having multiple address spaces in which to run these stored procedures - you don't need them. Native SQL procedures are made up of SQL statements, and SQL statements - as always - run in DBM1.

DB2, Stored Procedures, COBOL, and Result Sets

Last week, I helped a DBA at a large financial services firm with a couple of questions related to DB2 for z/OS stored procedures and result sets (referring to the row and column information accessed via a query included in a DECLARE CURSOR statement). Both of the DBA's questions had to do with COBOL programs called by DB2 stored procedures. A lot of mainframe sites have COBOL programs that are invoked by stored procedures, and plenty of people have some misconceptions with regard to the accessibility of result sets generated through cursors in stored-procedure-called programs, so in this post I'll share the information that I provided to the aforementioned DBA.

When it comes to DB2 for z/OS stored procedures calling COBOL programs, the situation is most interesting when both the stored procedure program and the program called by the stored procedure are written in COBOL. In that case, you have a couple of viable options: the COBOL stored procedure program could invoke the target COBOL program by way of an SQL CALL statement (the target program would run as a nested stored procedure, assuming that it had been set up to execute that way through a CREATE PROCEDURE statement referencing the program name), or it could execute the target program through a COBOL CALL (so that the target would run as a COBOL subroutine). Either way, both the COBOL stored procedure program and the target COBOL program would run in a WLM-managed stored procedure address space - the same address space if the target program executes as a COBOL subroutine, and the same or a different address space if the target is invoked via SQL CALL, depending on whether or not the same or a different WLM application environment was specified in the target's CREATE PROCEDURE statement (if you are using the DB2-managed stored procedure address space, get away from that and into WLM-managed address spaces soon - the DB2-managed space is not supported in a DB2 9 environment).

One of the differences between the nested-procedure and COBOL subroutine scenarios has to to with task control blocks (TCBs). If the target COBOL program is invoked via SQL CALL, it will run under its own TCB. If the target is executed as a COBOL subroutine, it will run under the TCB of the stored procedure program that issued the COBOL CALL for the subroutine.

Another difference - the one with which the question-asking DBA was concerned - has to do with access to a result set defined by a cursor declared in the target COBOL program. The DBA first brought up a situation in which a program running on an off-mainframe application server was seemingly able to fetch rows from a cursor declared in a "two levels down" stored procedure: the mid-tier program calls DB2 for z/OS COBOL stored procedure A, and stored procedure A calls COBOL stored procedure B. Stored procedure B issues a DECLARE CURSOR statement (on which the WITH RETURN option is specified) and opens this cursor. The mid-tier program subsequently fetches the result set rows associated with the cursor declared and opened in stored procedure B. That was working, but it shouldn't have been, because a DB2 for z/OS stored procedure generating a result set can return that result set only one level up within a series of nested calls. In other words, if stored procedure B declares and opens a cursor, stored procedure A (which called B via SQL CALL) can fetch rows from that cursor-defined result set by issuing an ASSOCIATE LOCATOR statement to get the locator value for the result set, and an ALLOCATE CURSOR statement to define a cursor and associate it with the result set locator value. If the program that called stored procedure A wants to retrieve the result set generated by the cursor declared in stored procedure B, it cannot use this ASSOCIATE LOCATOR/ALLOCATE CURSOR mechanism, because that mechanism only works one level up in a nested SQL CALL structure (DB2 for Linux, UNIX, and Windows allows result-set retrieval at both the one-level-up level and at the top level of a nested SQL CALL structure - "top" referring to the program that issued the initial CALL to a stored procedure).

So, how was the mid-tier program mentioned by the DBA able to get the two-levels-down result set generated by stored procedure B? Upon further investigation, the DBA found that stored procedure B, in addition to declaring and opening a cursor defining a result set, inserted the result set rows into a global temporary table (these come in two flavors, declared temporary tables and created temporary tables, with the latter usually being the best choice in terms of performance). Stored procedure A then declared and opened a cursor (WITH RETURN) referencing this global temporary table, and the mid-tier program (caller of stored procedure A) could then access the result set because it (the mid-tier program) was only one level up from stored procedure A. That's in fact an excellent way to make a stored procedure-generated result set available to a program several levels up in the nested call structure: put the result set in a global temporary table.

So, we had one mystery solved. The DBA then pointed to another situation that had him scratching his head: a program (again running on an off-mainframe middle tier) called COBOL stored procedure X, stored procedure X invoked COBOL subroutine Y via COBOL CALL, and the middle tier program was subsequently able to access a result set generated through a cursor declared (WITH RETURN) and opened by COBOL subroutine Y.

This was actually a working-as-designed situation. The DBA was thinking that it shouldn't have worked, because he was under the impression that a result set generated by program Y could be returned if program Y were invoked via SQL CALL, and could not be returned if program Y executed as a COBOL-called subroutine. You can in fact find passages in DB2 manuals and "red books" that appear to confirm this understanding of result set processing. It's not that the documentation is wrong - it's just that it can be easily misinterpreted if you consider it from a different perspective versus that of the documentation authors. Here's what I mean by that: when you read in a DB2 book that a COBOL-called subroutine cannot return a cursor-defined result set, what's being communicated is the fact that the subroutine can't return a result set to the program that invoked it via COBOL call. A subroutine called via COBOL call from a COBOL DB2 stored procedure program can return a result set to the program that called the stored procedure. This is consistent with the result set processing mechanism I described above for nested stored procedures: a SQL-called stored procedure can pass a result set to a one-level-up program (i.e., to the program that called it). In the context of result set processing, a subroutine called via COBOL CALL from a stored procedure program runs at the same "level" as the calling stored procedure; therefore, a result set generated by that subroutine, while not accessible by the calling stored procedure, can be accessed by the caller of the stored procedure (i.e., by the "one level up" program). Just remember that the cursor declared in the subroutine has to include the WITH RETURN option, and the stored procedure invoking the subroutine has to be defined with DYNAMIC RESULT SETS 1 (or more than 1, if multiple result sets will be generated by the stored procedure program and/or by COBOL-called subroutines invoked by the stored procedure program).

Is that clear? I hope so. I'm very big on DB2 stored procedures, and I want people to know how they can use them.

Thoughts on DB2 Triggers

I was in the Upper Midwest of the USA for most of last week, presenting at three regional DB2 user group meetings - in Minneapolis, Chicago, and Milwaukee - on three consecutive days. One of the presentations I gave in each city covered DB2 for z/OS data warehouse performance. In that presentation, I provided some guidelines on the average number of indexes defined per table in a data warehouse database (I wrote of this in an entry posted to this blog last year). Following the meeting in Milwaukee, one of the attendees asked me if I had any recommendations pertaining to the number of triggers defined on a table. I don't, because trigger usage scenarios and environments vary so widely, but the question sparked an interesting discussion about DB2 triggers that covered a variety of sub-topics. By way of this entry, I'll commit these DB2 trigger thoughts of mine to paper (electronically speaking).

[Super-brief level-set: by way of a trigger defined on a DB2 table, one can cause an SQL-expressed action to be taken automatically in response to an update, delete, or insert targeting the base table. For example, one could use a trigger defined on table A to cause an insert into table A to drive an update of a column in table B.]

First, concerning that question on the number of triggers defined on a table, the answer is very much of the "it depends" variety. I recall a presentation, delivered at a DB2 user group meeting several years ago, in which a developer described a new application that his company had implemented entirely by way of triggers. The number of triggers created for that application was fairly large, and I'm thinking that quite a few triggers were defined on certain individual tables. The application was successfully put into production, and everything worked fine, so having a lot of triggers is not necessarily a bad thing. On the other hand, there are situations in which triggers can affect application performance in an undesirable way. In that regard, the story has gotten better in recent years, certainly on the mainframe platform. Triggers were introduced with DB2 Version 6 for z/OS (the functionality had previously been delivered for DB2 on Linux, UNIX, and Windows servers), and in that and the subsequent release the presence of a trigger defined with UPDATE OF COL5 on a table increased the CPU cost of any UPDATE statement targeting the table, even if the statement did not change data in column COL5. That trigger cost was eliminated in DB2 for z/OS Version 8, so that the aforementioned trigger would affect the performance only of UPDATE statements that changed data in COL5.

So, continuing with this example, how would the performance of a COL5-changing UPDATE statement be impacted by the UPDATE OF COL5 trigger? That would depend, of course, on the nature of the triggered action (i.e., the SQL statement executed as a result of the trigger being "fired" by the UPDATE). If the triggered action is an update of one row in one table, identified by a unique, indexed column referenced in a predicate, the impact of the trigger on the performance of COL5-changing UPDATE statements is likely to be minimal. If, on the other hand, the triggered action were more involved (and keep in mind that it could be a call to a stored procedure), the affect of the trigger on COL5-changing UPDATE statements would be more noticeable. The key here is to keep in mind that the action taken when a trigger is fired is synchronous with respect to an SQL statement that causes the trigger to fire. In other words, the trigger-firing SQL statement isn't finished until the triggered action is finished. This means that there are performance implications for "downstream" triggers that might be fired as a result of the initial trigger being fired (a trigger defined with UPDATE OF COL5 on table ABC could drive an update of COL7 on table XYZ, and that triggered action would fire a trigger if one were defined with UPDATE OF COL7 on table XYZ).

Does this synchronous business mean that triggers with more complex triggered actions are a performance no-no? Not necessarily. One way to have that cake and eat it, too, is to have the trigger place information of interest (e.g., certain column values) on a WebSphere MQ queue (a trigger can certainly do this - the triggered action has to be an SQL statement, and DB2 provides built-in functions, such as MQSEND, that can be used to send data to a designated MQ location). Once that's done, the statement that fired the trigger can complete execution. Asynchronously, with respect to the trigger-firing statement, the data sent to the MQ queue by the trigger can be processed as needed, perhaps by a DB2 stored procedure invoked by the MQ listener (the MQ listener function can automatically take an action, such as calling a stored procedure, when a message lands on a queue).

How do triggers stack up, in terms of CPU efficiency, with other means of getting database action X accomplished as a result of action Y being performed? Suppose that you have a need to capture "before" and "after" values in certain columns of table ABC when those columns are updated by programs. If program PROG1 updates the columns of interest in table ABC, you could request that the program be modified to insert into table XYZ "before" and "after" values following the table ABC updates. This approach might well be the most CPU-efficient way to address your requirement, but it could prove to be impractical for at least a couple of reasons. For one thing, who would code the requested PROG1 functional enhancement? Will that person - likely engaged now in some other high-priority application development effort - be available to change PROG1 to your liking within the next year? Maybe not. Then there's potential problem number two: what if the table ABC columns for which you want to capture changes are updated by multiple programs besides PROG1? Are you going to try to get change-capture functionality added to all of those programs? How long will that take? You could opt to use a vendor tool to detect and capture changes made to the specified columns of table ABC, but if such a tool isn't currently part of your IT infrastructure, how long will it take to acquire it and how much will it cost?

You could certainly determine that a trigger on table ABC defined with UPDATE OF [the columns of interest] would be the right way to go, offering a quickly implementable solution that would have a modest CPU cost and a very low dollar cost (or euro cost or whatever-currency cost ). And, consider this: if programs that update the table ABC columns in which you are interested are so response-time sensitive that even adding a fairly simple trigger to the mix raises performance concerns, having that trigger defined on a data warehouse table (or operational data store table) to which table ABC changes are propagated might do the trick for you.

Flexibility, agility, and economy - that's what DB2 triggers offer. They should definitely be solution candidates when you have a need for timely implementation of incremental database application functionality.

Much Ado About DB2 Indexes (Part 2)

Last week, I posted an entry in which I described the numerous index-related enhancements delivered via DB2 9 for z/OS. In this related "part 2" entry, I'll cover new features of DB2 9.7 for LUW (Linux, UNIX, and Windows) - announced a few weeks ago and available next month - that pertain to indexes. My thanks go out to Matt Huras, Mike Winer, Chris Eaton, and Matthias Nicola of IBM's DB2 development organization, who recently delivered presentations on DB2 9.7 features that provided very useful information to me and others in the DB2 community.

First up: index compression. DB2 9.7 for LUW index compression differs from the index compression feature of DB2 9 for z/OS in several ways. For one thing, leaf pages of DB2 9.7 compressed indexes are compressed in memory as well as on disk, whereas DB2 9 for z/OS compression is disk-level only. Secondly, while DB2 9 for z/OS compression is based on squeezing the contents of an 8K, 16K, or 32K index leaf page in memory onto a 4K page on disk, DB2 9.7 index compression is accomplished via three algorithms that are chosen automatically by the DBMS (these algorithms, which can be used in combination, are briefly described below). Additionally, DB2 9.7 index compression is activated automatically when row compression is activated for a table on which indexes are defined (DB2 9.7 index compression can also be activated independent of row compression by way of an ALTER INDEX or CREATE INDEX statement with the new COMPRESS YES option, with a REORG required after ALTER INDEX).

I expect that index compression will prove to be very popular among users of DB2 9.7, especially in large-database environments, as it offers substantial disk space savings (likely to be in the range of 35% to 55%), better buffer pool hit ratios (with correspondingly reduced I/O activity), fewer page requests (because index leaf pages will hold more key values, and index levels may be reduced), and fewer index page splits. There will be some CPU overhead cost associated with index compression, but this should be offset to some degree by the aforementioned reductions in I/O activity and page access requests.

Now, a little about the algorithms by which DB2 9.7 index compression is achieved (again, these are selected - and combined, if appropriate - automatically by DB2):

• RID list compression. In an index leaf page, each entry contains a key value and list of RID (row ID) values, the latter indicating the location of rows containing the key value. A RID value will take up 4, 6, or 8 bytes of space, depending on the base table's tablespace type (e.g., LARGE or REGULAR, partitioned or non-partitioned). For a LARGE non-partitioned tablespace, for example, a RID will occupy 6 bytes of space: 4 bytes for a page number and 2 for a slot number within the page. If an index on a table in that LARGE tablespace is compressed, and if, say, 10 rows within a given page contain a certain key value, the full RID value only has to be stored once in the RID list for those 10 rows. For the other 9 rows containing the key value, only the delta values between one row's RID value and the next have to be stored (RID values are always stored in ascending sequence). Because that delta value can be stored in as little as one byte of space, substantial savings can be achieved. RID list compression delivers maximum benefit for indexes that have relatively low cardinality (i.e., lots of duplicate key values and, therefore, relatively long RID lists) and a relatively high cluster ratio (making it likely that multiple rows with duplicate key values will be found on a given page).
• Prefix compression. Key values are stored in an index leaf page in ascending sequence. Sometimes, adjacent key values will be very similar (consider, for example, timestamp values that have year, month, day, and hour values in common; or a multi-column key for which leading columns have low cardinality). In such cases, DB2 9.7 can store the full key with the common prefix once in a page, with subsequent entries containing only the differentiated values that follow the common prefix.
• Slot directory compression. A certain amount of the space in an index leaf page is occupied by something called a slot directory. It used to be that the size of the slot directory - determined based on the maximum number of index entries that could be stored on the page - was fixed. For a compressed index, the size of the slot directory is variable and can be reduced based on factors such as common prefix entries, variable length key parts, and duplicate key values.

Note that a new DB2 9.7 table function, ADMIN_GET_INDEX_COMPRESS_INFO(), can be used to obtain an estimate of the space savings that would result from activating compression for a given non-compressed index. This same function can be used to get the actual space savings for an index after it has been compressed.

Note also that compression can't be used for all indexes in a DB2 9.7 environment. Compression is not available for indexes on catalog tables, block indexes (these enable multi-dimensional clustering), XML path and meta indexes, and index specifications.

Next up, partitioned indexes. These are indexes, defined on a range-partitioned table, that are themselves partitioned along the lines of the underlying table's partitions. In comparison with global (i.e., non-partitioned) indexes on range-partitioned tables, partitioned indexes will allow for more efficient partition roll-in and roll-out operations (i.e., ATTACH and DETACH of partitions), as they eliminate the global index maintenance (and associated logging) that would otherwise be required. Partitioned indexes will also enable users to run REORG at the partition level.

In a DB2 9.7 system, all indexes on range-partitioned tables will be created, by default, as partitioned indexes as long as this is possible. It is not possible for a unique index when the index key is not a superset of the underlying table's partitioning key.

And, last but not least, DB2 9.7 delivers online creation of XML indexes and online REORG of same. In both cases, use of the ALLOW WRITE ACCESS option will enable the XML index CREATE or REORG operation to proceed without blocking writers.

So, for both the mainframe and Linux/UNIX/Windows platforms, IBM DB2 development keeps delivering good news on the index front. I expect more of the same in the future.

Much Ado About DB2 Indexes (Part 1)

I was in Denver last week for the North American Conference of the International DB2 Users Group (IDUG). More so than in recent years past, plenty of the talk during this conference was about index enhancements. Several important index-related features have come out over the past several DB2 releases, notable examples being online index reorganization for DB2 for z/OS and DB2 for Linux, UNIX, and Windows (LUW); block indexes (these enabled multi-dimensional clustering) with DB2 8.1 for LUW; and data-partitioned secondary indexes (aka DPSIs, referred to as "dipsies") with DB2 8 for z/OS. That said, the current versions of the DBMS - DB2 9 for z/OS and DB2 9.7 for LUW (announced last month and available in June) - pack in more index goodies than I've seen since type 2 indexes were delivered with DB2 for z/OS Version 4 in the mid-1990s. In this blog entry, I'll summarize what's new in the world of indexes with DB2 9 for z/OS. Next week, I'll post a "part 2" entry that will describe index enhancements delivered with DB2 9.7 for LUW.

Here, then, is my list of DB2 9 for z/OS features that pertain to indexes (while not necessarily an exhaustive list, it's fairly comprehensive):

Index-on-expression: As I wrote in my previous blog entry, this enhancement provides quick relief to a headache-inducing situation with which many a DBA has dealt: you have a query with a predicate involving a column expression (e.g., WHERE SUBSTR(COL1, 1, 2) = 'AB' or WHERE COL1 + COL2 = 100). The column expression makes the predicate non-indexable, and if there are no other indexable predicates in the query you're looking at a tablespace scan. With DB2 9 for z/OS you can create an index on a column expression, enabling you to make previously non-indexable predicates indexable. The potential payoff: orders-of-magnitude improvement in query performance.

Larger index page sizes: DB2 users have long had a choice with respect to the size of a data page in a table. 4 KB and 32 KB page-size options have always been there, and 8 KB and 16 KB page sizes were added to the mix several years ago. Indexes were a different story, with 4 KB being the only page size supported. That changed with DB2 9 for z/OS and its support for 8KB, 16 KB, and 32 KB index page sizes. Some might think of larger index page sizes only as a means of achieving index compression (see the next item in this list), but they can deliver benefits outside of compression enablement. Consider index page splitting, which occurs when a key value has to be inserted into an already-full index leaf page (not uncommon when an index key is not a continuously-ascending value): a portion of the leaf page's entries (traditionally, half of the entries, but that's also changed with DB2 9, as you'll see when you read about "adaptive index page splitting" a little further down in this list) are moved to an empty page to make room for the new entry, and the whole index tree is latched while this occurs. Larger index page sizes mean less index page splitting. Another potential benefit of a larger index page size is a reduction in the number of levels for an index. Suppose, for example, that an index with 4 KB pages has four levels: a root page that points to level-2 non-leaf pages, which in turn point to level-3 non-leaf pages, which point to the leaf pages. That same index might require only three levels with a larger page size, and that would reduce the CPU cost of each index probe operation (from root-level down to leaf-level) by 25%.

Index compression: Mainframe DB2 users have enjoyed the benefits of tablespace compression since Version 3 (if memory serves me correctly). For some DB2 subsystems, particularly in data warehouse environments, in which the average number of indexes defined on a table tends to be higher than in online transactional systems, the disk space occupied by indexes can exceed the amount used for tablespaces (especially if the latter are compressed, as is very commonly the case). With DB2 9, index compression is an option. It's different from tablespace compression in that 1) it's not dictionary-based and 2) the compression is only at the disk level (index pages are uncompressed in server memory). To be compressed, an index has to use a page size greater than 4 KB (for existing indexes, this can be accomplished via an ALTER INDEX followed by a REBUILD - and go down further in the list to see a REBUILD INDEX enhancement). DB2 then takes that 8 KB, 16 KB, or 32 KB index leaf page (only leaf-level pages are compressed, but the vast majority of an index pages are on this level) and compresses the contents onto a 4 KB page on disk. You might be tempted to think that a 32 KB index page size if best for compression purposes, but you have to keep in mind that DB2 will stop putting entries in a leaf page in memory once it has determined that no more will fit onto the compressed 4 KB version of the page on disk; thus, the aim is to strike a balance between maximizing space savings on disk and minimizing wasted space on index pages in memory. Fortunately, the DSN1COMP utility provided with DB2 9 will give you information that will help you to choose the optimum page size for an index that you want to compress.

Adaptive index page splitting: As previously mentioned (see the "larger index page sizes" item in this list), when an index leaf page must be split in order to accommodate a new entry, DB2 for z/OS will - before DB2 9 - move half of the page's entries to an empty page. That was OK unless entries were inserted in a sequential fashion within ranges. For example, suppose that an index is defined on a column that contains ascending values within the ranges of A001 to A999, B001 to B999, C001 to C999, and so forth. If a leaf page with the highest Annn value - say, A227 - is full and must be split to accommodate a new Annn entry (e.g., A228), half the entries in that page will be moved to a new page. Trouble is, the resultant 50% free space on one of those two pages (the one that does not contain the new highest value in the Annn range) will not be reused because nothing lower than A228 (using my example) will be added to the index (more precisely, that space won't be reused until the index is reorganized). DB2 9 improves on that situation by tracking value-insert pattern for an index. If it detects a sequential-within-range pattern (versus continuously-ascending overall, such as a timestamp or sequence number, in which case no splits will occur because new entries will always be at the "end" of the index), it will change the split process so that fewer than 50% of the split page's entries will be moved to the new page (or, if the insert pattern is descending within ranges, more than 50% of the split page's entries will be moved to the new page). The result: fewer page splits, leading to reduced CPU and elapsed time for application processes.

Online index rebuild: What I'm specifically referring to here is the introduction in DB2 9 for z/OS of a SHRLEVEL CHANGE option for the REBUILD INDEX utility. Formerly, an index rebuild operation would cause the underlying table to be in a read-only state for the duration of the rebuild process. Now, a table can be updated while a REBUILD INDEX operation is underway - DB2 deals with these data-changing operations by using the associated log records to apply the corresponding changes in an iterative fashion as needed to the index being rebuilt (during a final "catch-up" phase of this log apply processing, write activity against the underlying table is drained, as is the case for an online REORG running with SHRLEVEL CHANGE). This utility enhancement is good news for organizations (and there are many) at which new indexes on existing tables are commonly created with the DEFER YES option with a follow-on execution of REBUILD INDEX to physically build the index, and it means better data accessibility when REBUILD INDEX is run for an index in rebuild-pending status. Note, however, that if REBUILD INDEX is run with SHRLEVEL CHANGE for a unique index, inserts and updates (if the latter target a column of the unique index key) will not be allowed for the underlying table, because uniqueness cannot be enforced while the index is being rebuilt.

RENAME INDEX: Online schema evolution - the process by which DB2, in succeeding releases, allows more and more database object change operations to be performed without the need for a drop and re-create of the target object - marches on. In DB2 9 for z/OS, the functionality of the RENAME statement has been extended to include indexes. Note that renaming an index will not cause invalidation of packages (or of DBRMs bound directly into plans), because static SQL statements reference indexes by their object identifier (aka OBID), not by name. Prepared dynamic SQL statements in the dynamic statement cache, on the other hand, reference indexes by name, so those that use a renamed index will be invalidated (they'll of course be re-prepared and re-cached at the next execution following invalidation).

Greater leveraging of index lookaside: Index lookaside, a feature that allows DB2 to repeatedly access entries on a leaf page (and on the associated parent non-leaf page) without having to do a full index probe (root-to-leaf level transit of the index tree) each time, was introduced way back in Version 2 Release 3. It greatly reduced GETPAGEs (and thus, CPU time) for many application processes that used a file of search values sorted according to an indexed table column to retrieve DB2 data. In DB2 for z/OS Version 8, the use of index lookaside was finally extended to data-changing processes, but only for INSERT, and only for the clustering index on a table. With Version 9, DB2 can use index lookaside for INSERT operations with indexes other than the clustering index (assuming that these indexes have an ascending - or, I believe, descending - key sequence), and can also use index lookaside for DELETE operations. IBM performance guru Akira Shibamiya noted in a presentation given at last year's IDUG North American Conference that a test involving heavy insert into a table with three ascending-key indexes showed a reduction in average GETPAGEs per INSERT to 2 in a DB2 9 environment versus 12 in a DB2 for z/OS Version 8 system.

Closing the DPSI performance gap: Data-partitioned secondary indexes (DPSIs), introduced with DB2 for z/OS V8, are indexes over range-partitioned tables (referring to table-controlled versus index-controlled partitioned tablespaces) that are themselves partitioned in accordance with the partitioning scheme of the underlying table. DPSIs are nice for improving performance and availability with respect to some partition-level utilities and for FIRST TO LAST partition-rotation operations, but restrictions on their use for SQL statement access path purposes meant that DPSIs had a "performance gap" versus non-partitioned indexes. In the DB2 9 environment, this gap is made considerably smaller, thanks to these enhancements:

• Enhanced page-range screening: Page-range screening refers to DB2's ability to avoid accessing table or index partitions in the course of executing an SQL statement, when it determines based on one or more predicates that qualifying rows or index entries cannot possibly be located within said partitions. Page-range screening can have a VERY beneficial impact on query performance, and in the Version 9 environment DB2 can apply page-range screening more broadly to DPSIs.
• More parallelism: There are more situations in a Version 9 system in which DB2 can parallelize data retrieval for a statement that uses a DPSI for data access.
• A DPSI can provide index-only access for a SELECT statement with an ORDER BY clause.
• A DPSI can be defined as UNIQUE in a DB2 9 environment, if the DPSI key columns are a super-set of the table's partitioning columns.
  

And a few more: Just to wrap up with a few quickies:

• DB2 9 can use a non-unique index to avoid a sort for a SELECT DISTINCT statement.
• The calculation of CLUSTERRATIO by the DB2 9 RUNSTATS utility provides the optimizer with a more accurate indication of a table's "clusteredness" with respect to an index, particularly if the indexed key has a significant number of duplicate values. This can enable the optimizer to make better decisions regarding the use of such indexes (if desired, the old CLUSTERRATIO calculation can be retained through the ZPARM parameter STATCLUS).
  
• The LASTUSED column in the real-time statistics table SYSINDEXSPACESTATS (part of the DB2 9 catalog) shows the last time that an index was used for data access (e.g., for SELECT, FETCH, searched UPDATE, or searched DELETE) or to enforce a referential integrity constraint. This should be VERY helpful when it comes to identifying indexes that are no longer used and which therefore would be candidates for dropping in order to reduce disk-space consumption and CPU costs for inserts and deletes and utilities.

That's a lot of good index stuff. As I mentioned up top, tune in next week for a look at some cool index-related enhancements delivered in DB2 9.7 for Linux, UNIX, and Windows.

In Denver: Mainframes, DB2, COBOL, and SOA

I'm in Denver, Colorado this week for the 2009 North American Conference of the International DB2 Users Group (IDUG). Yesterday, I moderated a Special Interest Group session (also known as a SIG - basically, a "birds of a feather" discussion group) on the topic of "mainframes, DB2, COBOL, and SOA." The conversation was interesting and lively, and I'll summarize it for you by way of this post, with key discussion threads highlighted.

Is Service-Oriented Architecture still relevant? Absolutely. Sure, the buzz around SOA has diminished lately, thanks to more recent arrivals on the "You've GOT to get into this!" scene (see: cloud computing), but that's probably a good thing, as SOA was being hyped to the point that unrealistic expectations were leading to disappointing results (see my recent blog entry on this subject). With SOA frenzy in abatement, organizations can get about the work of implementing SOA initiatives that have been well researched, properly scoped, and properly provisioned (referring to having the right tools on hand). One of the participants in our SIG session mentioned that SOA is a very high priority at his organization, a large department of the United States federal government (at which point two other SIG participants, Susan Lawson and Dan Luksetich of consulting firm YLA, spoke up about the SOA work that they've been doing at another large U.S. government department). And SOA is not just a big deal in public sector circles - we had people in our SIG group from a wide variety of industries.

What does COBOL have to do with it? The SIG was titled as it was because many people have this idea that SOA requires the use of "modern" programming languages such as Java, C#, Ruby, and Python. That, of course, is totally untrue. COBOL programs can be a very important part of an SOA-compliant application. One thing that's happening in a lot of mainframe DB2 shops is the implementation of data access logic in the form of COBOL stored procedures that are called by business-tier programs running in off-mainframe app servers such as WebSphere. As we discussed this particular subject, one participant noted that his company has hardly any COBOL programmers. No problem. DB2 for z/OS stored procedures, which are very well suited to the data tier of an SOA, can be written in a variety of languages, including SQL (I'm particularly bullish on DB2 for z/OS V9 native SQL procedures, about which I blogged a few weeks ago). Organizations are also exposing CICS-DB2 and IMS-DB2 transaction programs, written in COBOL, as Web services.

Don't get too caught up in the technology behind an SOA. One of our SIG participants made the very important point that a successful SOA project has more to do with process and governance (and, some would say, with cultural change) than with technology. There are all kinds of options with regard to tools, languages, platforms, and protocols, but getting SOA right depends largely on changing the way an IT organization works: more discipline, more standards, better business-IT alignment. Because change makes a lot of people uneasy, executive-level support is usually critical to the achievement of a positive SOA outcome.

Give plenty of thought to service granularity. Susan and Dan of YLA talked of a client company that ran into major performance problems with an SOA-oriented application, with the key factor being an inordinately high number of calls to the back-end DB2 database. Sometimes, a situation of that type can result when the services provided by application programs are too fine-grained. More coarsely-grained services can allow for greater back-end efficiencies, but they can also reduce flexibility when it comes to reusing blocks of code to build new services. There's no one-size-fits-all solution when it comes to determining the granularity of services that an SOA-compliant application should provide, but it's probably a good idea to avoid the extremes at either end of the spectrum. An application architect friend of mine liked to put it this way: "What do people [meaning the folks who write service-consuming programs] want? Do they want water, or do they want to be able to get an atom of oxygen and a couple of atoms of hydrogen?" The right answer is the one that makes sense in your environment.

Can you do SOA and still have good end-user response time? With its emphasis on abstraction and loose coupling of application system components (the better to achieve agility with respect to extending application capabilities, and flexibility in terms of mixing and matching computing platforms at various application tier levels), SOA tends to increase an application's instructional path length (meaning, the app will consume more CPU cycles than one architected along more monolithic lines). Thus, going the SOA route could lead to elongated end-user response times. This performance hit can be mitigated through several means, one being the use of message queuing software (such as IBM's WebSphere MQ, formerly known as MQSeries) to de-couple back-end database processing from the front-end response to the user (in other words, make back-end processing asynchronous from the perspective of the end user). Another SOA performance-boosting technique involves the use of cache servers to speed response for certain data-retrieval requests (you can read more about the use of message queues and cache servers to enhance SOA application performance in an article I wrote on the topic for IBM Database Magazine).

Dan Luksetich talked up another option for improving the performance of an SOA application: drive multitasking. If the back-end processing associated with a transaction involves the execution of, say, three discrete tasks, see if you can kick off three processes that can do the required work in parallel. This is where enterprise service bus (ESB) and workflow orchestration software (sometimes referred to as a "process engine") can really come in handy (read more about this in my IBM Database Magazine column titled "Get on the Enterprise Service Bus").

SOA can impact database design as well as application design. Often, an SOA project will result in a DB2 database design that is more highly normalized versus a database designed for an application with a monolithic architecture. This has to do with the goal of loose coupling (i.e., dependency reduction) that is a key aspect of an SOA. What you want is a database design that is driven by the nature of the data in the database, as opposed to a design that is aimed at optimizing the performance of a particular application (the latter approach sounds good until you start thinking about other applications that could be built on the same database foundation - it can be to an organization's advantage to trade some database processing efficiency for improved flexibility).

SOA can be an impetus for database consolidation on a mainframe server. As previously mentioned, an important aspect of SOA is abstraction of one application system tier (e.g., the data layer) from another (such as the business layer). Another key characteristic of an SOA is standardization with respect to interactions between programs running in different tiers of the application system. Once this abstraction and standardization has been achieved, the platform on which data and data access logic resides should not be a concern to a business-logic programmer. The data server of choice should be the one that can deliver the scalability, availability, and security needed by the organization, and a mainframe (or parallel sysplex mainframe cluster) running DB2 for z/OS is not going to be beat on that score. Indeed, several of the SIG participants spoke of the momentum behind consolidation of databases from distributed systems servers to mainframes that is due in part to the progress of SOA implementation efforts.

That's pretty cool: the mainframe, referred to by some as a "legacy" (read: old-fashioned) server platform, is shining anew as a primo foundation for leading-edge enterprise applications designed in accordance with SOA principles. A very satisfying SIG, indeed.