An Important DB2 10 for z/OS Stored Procedures Enhancement

I've said it before, and I'll say it again: DB2 10 for z/OS is packed with new functionality. Along with the really high-profile features of the latest release of DB2 for the mainframe platform (temporal data support, enhanced CPU efficiency, row and column access control, etc.), you'll find several goodies that will likely be very useful to your organization. One of my favorites of these is the ability to declare a cursor WITH RETURN TO CLIENT. Not familiar with this feature? Read on.

The discussion of cursors in this blog entry, by the way, is relevant to cursors declared and opened in a DB2 for z/OS stored procedure (any kind -- e.g., COBOL, external SQL, native SQL). Here's the deal: for a stored procedure in a DB2 9 or DB2 V8 environment, a cursor declared with the WITH RETURN option (indicating that the cursor's result set is to be retrieved by a program other than the stored procedure) is actually declared with the specification WITH RETURN TO CALLER (the TO CALLER part is the default -- and only -- behavior allowed for a cursor declared WITH RETURN). The result set of a cursor declared with the WITH RETURN TO CALLER specification can ONLY be retrieved by the program that invokes the stored procedure in which said cursor is declared. Another way to say the same thing: the result set of a cursor declared with the WITH RETURN TO CALLER specification can only be returned "one level up." In this context, "one level up" refers to the hierarchy of a chain of stored procedure calls. In the simplest case, what I'd call a "top-level caller" -- a non-stored procedure program -- invokes a stored procedure that does not call any other stored procedures. That would be a two-level call chain: level 1 is the top-level calling program, and level 2 is the called stored procedure.

You could also have a situation in which a top-level caller (which I'll refer to as PROG_TOP) calls stored procedure A (PROC_A), which in turn calls stored procedure B (PROC_B). That's a three-level call chain. In a DB2 9 (or V8) system, a result set generated by PROC_B could ONLY be returned (in a direct sense) to PROC_A, because PROC_A is "one level up" from PROC_B.  -- not to the PROG_TOP program. What if the PROG_TOP program needed the result set generated by PROC_B? You basically had three ways of addressing that requirement:

• You could change PROG_TOP to invoke PROC_B directly. That could be a non-trivial effort.

• You could have PROC_B insert the result set rows associated with the cursor in question into a temporary table. Then, PROC_A could declare and open a WITH RETURN TO CALLER cursor targeting the temporary table, and PROG_TOP, being one level up from PROC_A, could fetch the result set rows via the cursor declared and opened in PROC_A (by the way, created temporary tables, as opposed to declared temporary tables, tend to be recommended for this purpose).

• As noted above, you could have PROC_B insert the result set rows associated with the cursor in question into a temporary table. You could then modify the PROG_TOP program to directly access the rows in the temporary table via a cursor declared and opened in PROG_TOP.

All do-able, but wouldn't it be nice if the result set generated by PROC_B's cursor could be directly returned two levels up to the PROG_TOP program (or three levels up if PROG_TOP calls PROC_A which then calls PROC_A2 which then calls PROC_B, or four levels up if PROG_TOP calls PROC_A which then calls PROC_A1 which then calls PROC_A2 which then calls PROC_B, or...)? In a DB2 10 new-function-mode system, you can do just that.

The important DB2 10 change comes in the form of the new WITH RETURN TO CLIENT option of the DECLARE CURSOR statement. When a cursor in a DB2 10 stored procedure is declared WITH RETURN TO CLIENT, a result set generated by that cursor can be directly returned to the top-level program (the program that initiated a chain of stored procedure calls) no matter how many call chain levels separate the cursor-declaring stored procedure and the top-level program (and a chain of DB2 for z/OS stored procedure calls can go 16 levels deep); furthermore, within a call chain the result set of a cursor declared WITH RETURN TO CLIENT will be invisible to stored procedures between the top-level calling program and the cursor-declaring stored procedure.

The delivery of the WITH RETURN TO CLIENT option for DECLARE CURSOR in DB2 10 for z/OS is a very good thing, for two reasons:

• It provides stored procedure developers with an architecture option they didn't have before. That means more flexibility and an easier way to deliver required application functionality.

• It brings DB2 for z/OS into conformity with DB2 for LUW, which already had the RETURN TO CLIENT option for DECLARE CURSOR. This is a big deal in my eyes, because lots of organizations run both DB2 for z/OS and DB2 for LUW, and I believe that we'll see, to an increasing degree going forward, individuals engaged in developing stored procedures for both DB2 platforms. These people's lives are made easier by cross-platform conformity within the DB2 Family (this has been an IBM DB2 development priority for some years now, and SQL differences between DB2 platforms -- referring to SQL in application programs versus the DDL that is used primarily by DBAs -- have been reduced nearly to non-existence).

So, there you have it -- another reason to add to your long list of incentives to get to DB2 10. If your organization is already running DB2 10, check out the new RETURN TO CLIENT option of DECLARE CURSOR. Good stuff.  

Great DB2 Learning Opportunities in Prague this November

Greetings, folks. I know that an unusually long time has passed since I lasted posted an entry to this blog -- my apologies for that. I was out of commission during the second half of July and into early August, dealing with a medical issue. I'm much better now, and things are getting back to normal. I'm still on medical leave (until October 3), but I plan on posting several blog entries between now and then).

This first-in-a-month post is of the For Your Information variety. Many of you may already know that the International DB2 Users Group (IDUG) will be holding its 2011 DB2 Tech Conference EMEA (Europe/Middle East/Africa) on November 13-18 Prague, Czech Republic (one of my all-time favorite cities: modern conference and hotel facilities convenient to the subway, which can take you in minutes to the historic -- and large and beautiful -- city center). Attend the conference, and you'll learn a ton about DB2, whether you're new to the DBMS or you've been working with it for 20 years. In this blog entry, I want to let you know about two opportunities to pick up even more DB2 knowledge during IDUG EMEA week:

The one-day seminars offered on November 18

Most of the sessions comprising the agenda of the 2011 IDUG DB2 Tech Conference EMEA are 60 minutes in duration -- enough time for a high-level overview of a broad topic or an in-depth drill-down into a specific DB2 feature or function. Want to have breadth together with depth? If so, the one-day seminars are for you. This year's one-day seminar line-up, like those of previous IDUG EMEA conferences, features a who's who of top-shelf DB2 experts known for both their great technical expertise and their outstanding teaching skills. Check it out:

• DB2 10 for z/OS -- In Depth, delivered by Phil Grainger of Cogito and Grainger Database Solutions
• DB2 Intermediate and Advanced SQL, taught by Dan Luksetich of Yevich Lawson & Associates
• I Didn't Know DB2 did THAT!, presented by Bonnie Baker of Bonnie Baker Corporation
• Optimizing DB2 for z/OS System Performance Using DB2 Statistics Trace, presented by John Campbell of IBM's DB2 for z/OS development organization
• Rocket Science: DB2 for Linux/Unix/Windows Performance Analysis and Tuning Workshop, taught by Scott Hayes of DBI

You can register for one of these one-day seminars when you register for the 2011 DB2 Tech Conference EMEA (online registration is available). Seminar sign-up involves a fee that is in addition to the conference registration fee, but I will tell you that you won't find a better value in terms of in-depth DB2 technical knowledge gained relative to euros spent.


A free DB2 10 for z/OS Migration Planning Workshop on November 13

IBM will be providing a complimentary DB2 10 for z/OS Migration Planning Workshop on the Sunday of IDUG EMEA Conference week. This will be a great opportunity for you to pick up valuable information on migrating to the latest version of DB2 for z/OS, which delivers significant performance enhancements and lots of great new features (among my favorites are temporal data support, implicit data type casting, and the ability to store a time zone together with a timestamp in a DB2 table column). Register online for this seminar and enjoy learning about DB2 10 migration from IBM experts and leading DB2 consultants.


There you have it: two ways to get more DB2 knowledge in one of the world's most beautiful cities. Take advantage of these offerings and go to Prague. It will be a treat for the left and right sides of your brain.

A Point of Clarification about DB2 9 for z/OS and "Pure" Sequential Prefetch

Many of you have probably heard (I've said it a number of times in presentations myself) that starting with DB2 9 for z/OS "pure" sequential prefetch (what we just called sequential prefetch before dynamic prefetch came along) would only be used for table space scans. In all other situations if DB2 utilized prefetch to access pages of a database object it would be dynamic or list prefetch.

Well, I was out a site running DB2 9 for z/OS recently, and a DBA and I were looking at the output of the DB2 command -DISPLAY BUFFERPOOL(ACTIVE) DETAIL. We were surprised to see an indication of sequential prefetch activity for a buffer pool to which only indexes had been assigned. If pure sequential prefetch were used only for table space scans, how could this be?

I brought this to the attention of a friend of mine in the DB2 for z/OS development organization, and he told me that, in fact, pure sequential prefetch could be used for index access in a DB2 9 environment in a couple of situations: 1) for a multi-index access path (this is sometimes referred to as index ANDing or ORing), and 2) for index access by a utility (for example, RUNSTATS or CHECK INDEX).

So, it turns out that in a DB2 9 system, pure sequential prefetch is used ALMOST exclusively for table space scans. You could indeed see some pure sequential prefetch activity for indexes.

Speaking of indexes and prefetch, I'll remind you of a nice enhancement delivered with DB2 10 for z/OS: namely, the ability of DB2 to utilize list prefetch to access the leaf pages of an index when performing an index scan operation. This can deliver a major improvement in elapsed time for queries, particularly when the index in question is disorganized (in which case you'd see a lot of synchronous I/Os to retrieve leaf pages in a DB2 9 or DB2 V8 system). You shouldn't be surprised, then, to see, in -DISPLAY BUFFERPOOL output or via your DB2 monitor in a DB2 10 system, list prefetch activity for a buffer pool to which only indexes are assigned (for prior releases of DB2 list prefetch is associated with access to table space pages).

A Note on DB2 for z/OS "Conditional GETPAGE Failures"

Recently, a DB2 for z/OS professional asked me about something he'd occasionally seen in a DB2 monitor display: a non-zero value for CONDITIONAL GETPAGE FAILURES for a buffer pool. Now, his DB2 subsystem appeared to be running just fine, but we are rather conditioned (so to speak) to associate the word FAILURE with the meaning NOT GOOD, and he wanted to know if these FAILURES indicated something with which he should be concerned. The short answer to this question is, "No -- that's just an FYI number." In the remainder of this entry I'll provide some background on conditional GETPAGEs and explain why associated "failures" are nothing to lose sleep over.

I expect that you all know what a DB2 GETPAGE is: a request by DB2 for z/OS to access a particular page in a table or an index. For a "regular" GETPAGE (the vast majority are of this type), a "miss" (i.e., a situation in which the requested page is not found in the DB2 buffer pool) will result in DB2 issuing a synchronous read I/O to get the page into the buffer pool from disk storage. In essence, a DB2 synchronous read I/O is the result of a "regular" GETPAGE "failure." Are you concerned about those "failures?" Of course not -- that's just DB2 business as usual: "I (DB2) need to access page 123 of tablespace XYZ. If it's not in the buffer pool, suspend the application process on behalf of which I'm requesting this page, read the page into memory from disk right away, and resume the application process when the read I/O is completed."

So it is with conditional GETPAGE "failures" -- no worries (as the Aussies say -- I love that phrase). What's different about a conditional GETPAGE versus a "regular" GETPAGE: for the former, a "miss" (reported by a DB2 monitor as a "failure") will result in DB2 driving an asynchronous read I/O to get the page into memory and subsequently issuing a "regular" GETPAGE for the page. "Huh?" you might be thinking, "An asynchronous I/O for one page? I thought that asynchronous I/Os (generally associated with prefetch reads) were issued for multiple pages (typically, up to 32 at a time)." This all has to do with WHY DB2 issues conditional GETPAGE requests: it's for parallel I/O operations under a single task. Before DB2 10, conditional GETPAGEs were issued when query I/O parallelism was used by DB2 to access table or index pages (since DB2 for z/OS V4, most folks equate "query parallelism" with query CPU parallelism -- I/O parallelism is what we had before that). Since there's one task, you can have one synchronous I/O going. If DB2 needs a page and it's not in the buffer pool, it can issue an asynchronous I/O (performed under a DB2 task, versus the application process's task) and move on to the next page that it wants (from another tablespace partition, for example). When it's ready to work on that page for which the asynchronous I/O was driven, DB2 will issue a regular GETPAGE for it, and there's a good chance that it'll be in the buffer pool.

So, as I mentioned, I/O parallelism hasn't exactly been front and center in the minds of mainframe DB2 people since query CPU parallelism made the scene with DB2 V4. With DB2 10, there's something new: index I/O parallelism for inserts into a table in a non-segmented tablespace on which multiple indexes are defined. When there's an insert into a such a table (generally, one having three or more indexes), DB2 will of course issue "regular" GETPAGEs (for root, non-root, and leaf pages) for the table's clustering index, because that's how it identifies the target page in the table for the insert. When that's done and the row is inserted (into the target page of the table, or into a page near that one, if the target page is full or locked and space is available in a nearby page), it's time to update the table's indexes with the row's physical location. The clustering index leaf page needing updating is already in memory. DB2 goes to the next index and issues a conditional GETPAGE for the page it needs to access. If there's a "miss" (aka a "failure"), it moves on and issues a conditional GETPAGE for the next index page it needs. Thus you can have the inserting task stay busy while asynchronous read I/O requests are processed in the background. DB2 will issue "regular" GETPAGEs for the "last" of the table's indexes that it's updating, as it did for the clustering index, but again the objective is for DB2 to work in the background to bring pages from the other indexes on the table into memory asynchronously. When the inserting task is ready to update those index pages, the hope is that those pages will already be in memory, thanks to the asynchronous I/Os. Index I/O parallelism for inserts can result in significant reductions in elapsed time for insert operations into tables in non-segmented tablespaces in a DB2 10 environment.

Besides boosting performance, DB2 10 index I/O parallelism for inserts can result in some conditional GETPAGE "failures," as these are what trigger the asynchronous read I/Os for pages not found in the buffer pool. That, of course, is no sweat whatsoever, whether we're talking about DB2 10 or a prior release (in which case, as noted, conditional GETPAGEs are associated with query I/O parallelism). These "failures" are just buffer pool misses, and those happen all the time in a DB2 environment (unless you have a really big buffer pool configuration and a really small database). So when you're looking at a DB2 monitor display of activity for a buffer pool, and you see a non-zero value for conditional GETPAGE failures, think, "OK," and move on to other fields in the display. Hang loose, dude.

Application Programming Tip: Let DB2 do the Work for You

Back in the 1980s, when DB2 was relatively new, you had, of course, a lot of application developers who were learning how to program using SQL (IBM invented the relational database and Structured Query Language). Though SQL is pretty intuitive, mastery takes time and so it was understandable that some programmers early on did in their code things that would more effectively be done by DB2. Examples of what I'm talking about include joining tables in application code (open a cursor on table A, fetch a qualifying row, and look for a matching row in table A), retrieving unordered result sets and sorting the rows programmatically, and failing to leverage the set-oriented nature of SQL (too often retrieving result set rows via a series of singleton SELECTs versus DECLARE CURSOR / OPEN CURSOR / FETCH, FETCH, FETCH, FETCH...).

Interestingly, we're here now at 27 years after DB2's introduction, and there are STILL programmers who are doing in their applications what DB2 should be doing by way of properly coded SQL statements. In this blog entry I'll try to make the case for allowing DB2 to do what it can do in terms of retrieving and updating data. There are several motivating factors when it comes to exploiting the power of SQL as fully as possible. The ones that are most important to me include the following:

Enhance CPU efficiency. When your program issues an SQL statement, obviously that statement has to get from your program to DB2, and after statement execution has completed control has to return to your application code. This program-to-DB2 round trip is not free. The CPU cost of DB2-and-back is (usually) readily grasped in a client-server setting, what with network send and receive processing and all, but there is overhead associated with getting to and from DB2 even when the SQL-issuing program is running on the same server as DB2 (as is often the case in a mainframe system, where CICS and batch programs access a local DB2 database). Get what you want with fewer SQL statements, and you'll reduce the cumulative CPU cost of trips across the application program-DB2 boundary. The effect of getting the job done with fewer trips to DB2 can be quite significant, and here it's important to focus not on a single unit of work but on an overall workload. A developer might run a comparison test between a more-SQL-statements and a fewer-SQL-statements approach to retrieving a particular result set or accomplishing a data change operation, and he might conclude that it's a wash because the elapsed time is about the same either way. Elapsed time is not the issue here -- CPU time is. That's the money metric. And a seemingly small difference in CPU time per transaction (something that a DB2 monitor would show) can end up being a big deal when hundreds of transactions execute per second, or when a batch job has to work through hundreds of thousands or even millions of records in an input file.

Lest you think that this is just a matter of having DB2 and not your application code join tables, I'll tell you: it goes beyond that. To really get things tight from a CPU efficiency perspective, you need to keep up with, and be prepared to take advantage of, new DB2 features and functions that can enable you to reduce "chattiness" between your program and DB2. The MERGE statement (sometimes referred to as "upsert"), with which you can make changes to a table based on a set of input records, updating target table data when there is a match with an input record, and inserting a new row into the table when there isn't a match? That's a CPU saver compared to the old methodology of doing a SELECT against the target table to see if there is a row that matches an input record and then driving an UPDATE (if there is a match) or an INSERT (if there's no match). Multi-row FETCH and multi-row INSERT (sometimes called block FETCH and block INSERT), means of, respectively, getting several rows from DB2 with one FETCH and placing several new rows in a table with one INSERT? Those are CPU savers versus the one-at-a-time way of doing things. Look for ways to do more with less SQL, and you'll reduce the load on your DB2 server.

Application performance consistency. OK, back to joining tables. I like this example because when you do this in application code YOU'RE making the access path decision. Opening a cursor on table A, fetching a qualifying row, and looking for a match in table B is, in essence, a nested loop join. How do you know that nested loop is the right way to go? What about a merge join or a hybrid join (DB2 for z/OS) or a hash join (DB2 for Linux/UNIX/Windows)? When DB2 performs the table join, the SQL optimizer determines the low-cost means of accomplishing the join, based on statistical information stored in DB2 catalog tables. The optimizer is very good at what it does, having been continually enhanced for upwards of thirty years (in addition to inventing the relational database and SQL, IBM invented cost-based SQL statement optimization). Do you really think that you'd do a better job of choosing the most efficient join method for a query? And where does my point about consistency come in? Well, suppose that you do a programmatic join and choose a nested loop method to get the job done. That may in fact be the right choice early on in the life of the application, when target tables -- and the query result set -- are relatively small. What about later, when the database has grown substantially and the query result set is perhaps much larger than before? Would a merge join be the better choice under those circumstances? If it is, and if you let DB2 do the join, the optimizer will take care of that access path change automatically (in the case of dynamic SQL) or with a simple REBIND PACKAGE command (for static SQL) -- no program code changes are needed. Do the join in application code, and you either live with deteriorating performance (if the join method you chose is no longer appropriate), or you change the join logic in your program.

And what about the effect of physical database changes? Suppose that the nested loop join that you accomplish programmatically depends for good performance on the existence of a certain index on a target table? That same table may be over-indexed to the point that insert and delete operations are costing too much. It may be that if the index your programmatic join needs is removed, DB2 -- were it handling the join -- could switch to a merge join and deliver acceptable performance for your query. You can't switch from nested loop to merge join in your program (not without re-write effort), and so your programmatic join may stand in the way of DBAs making a physical database change that could reduce run times for critical data-change operations.

Oh, and things get way more complex when the number of tables joined in a given SELECT statement increases. In what order should the tables be joined? Should the same join method be used all the way through, or would it be best to join A and B via nested loop, and then join that composite table to C via a merge join? And what about tables D, and E, and so on? Do you really want to sort this out yourself?

Bottom line: when you let DB2 do as much as it can, you leave the access path selection process up to DB2. That's a very good move on your part.

Application program simplification. As with reducing program-to-DB2 "chattiness" for improved CPU efficiency, leveraging SQL for application program simplification goes beyond obvious things like having DB2 do things like table joins and result set row grouping and aggregation -- you do best when you learn about and exploit new features and functions that are delivered with every release of DB2. Do you need to assign a number -- perhaps a ranking number -- to each row in a result set based on some criterion of your choice? You could do that with your own program logic, or you could let DB2 do the work via its built-in RANK, DENSE_RANK, or ROW-NUMBER functions. Want to concatenate one character string value to another? Figure out the day of the week for a given date value? Get the number of seconds between midnight and a timestamp value? Overlay some portion of a character string with another string? Find a character string value with a pronunciation that is the same, or close to, that of another string? Serialize an XML value into a character string? All these capabilities, and MANY MORE, are built into DB2 (on all platforms -- mainframe and LUW). Have you checked out the built-in DB2 functions (scalar and aggregate) lately? If not, give 'em a look. Your best source for DB2 function information is the SQL Reference. You can find that manual, for DB2 for z/OS and for DB2 for LUW, at IBM's Web site (the built-in functions are documented in Volume 1 of the DB2 for LUW SQL Reference).

Beyond the built-in functions, you can move work into DB2 (and out of your application code) by way of CASE expressions, which provide for on-the-fly transformation of values returned by, or otherwise processed by, an SQL statements (e.g., "return X when the value in column COL1 is Y"), and with CAST specifications, which tell DB2 to change the data type of column values (e.g., from decimal to integer). Again, the SQL Reference is the place to go for more information.

I haven't even mentioned things like the INTERSECT and EXCEPT result-set-comparison operators, date/time arithmetic, and moving aggregates. DB2 SQL is very powerful and is becoming more so as time goes by. You can leverage that power to remove complexity that would otherwise be in your application program code.

Code re-use. This is a hot topic these days, and with good reason: to the extent that application logic can be encapsulated in a readily reusable form, programmer productivity increases (less reinvent-the-wheel work) and so does organizational agility (application creation and extension is accelerated). DB2 can be a big help here. There are all kinds of ways to push logic into the DB2 level of an application, and when that's done said logic is made available to any kind of program that accesses the database. A simple example of logic-in-DB2 is DB2-defined referential integrity. Why burden programmers with the task of ensuring that data values in a column of a "child" table always have a corresponding value in a "parent" table column, when DB2 can do that? And, when that's implemented at the DB2 level then it's there for the benefit of ALL DB2-accessing programs. Ditto table check constraints, which can ensure, among other things, that values to be inserted into a column must exist within a certain range of values.

Triggers are another way to push logic into the DB2 level of an application infrastructure. These can be used to enforce business rules for insert operations (rules that might be too complex to implement with table check constraints), to automatically maintain values in denormalized database tables ("if column X in table A is updated by a program, perform the same update for column Y in the matching row of table B"), to make otherwise read-only views updateable (this with INSTEAD OF triggers), and more. User-defined functions (UDFs) make certain routines (e.g., to perform certain data transformation operations) available to any program that can issue an SQL statement, and the same is true of DB2 stored procedures (these can be more sophisticated than user-defined functions, and they are invoked via the SQL statement CALL versus being referenced in SELECT statements as are UDFs).

The more data-centric logic is implemented in the DB2 database, the more you as a programmer can do what delivers the greatest value to your employer: writing code that directly addresses the business functionality needs of the organization. If you have some data-access logic that you think could be broadly applicable in your enterprise, talk to a DB2 DBA and see about getting that deployed in the database layer of the application system. A lot of your colleagues could end up benefiting from that move, as their programs will also be able to utilize the DB2-implemented capability.

Now, you've probably heard it said that there's an exception to every rule, and in closing I'll mention one such exception to my "let DB2 do what it can" rule. This exception has to do with sorting result set rows. Sometimes, in a decision support application environment, a user will want the capability to sort data rows returned from DB2 by whatever field he chooses. Having DB2 do that initial sort is the right move, but if the user wants to sort the same result set by a different field, you might consider doing that at the user workstation level (or maybe at the application server level). This might be good for efficiency if the result set is not particularly large (maybe a few hundred rows or less) but a lot of host resources are needed to generate the result set (sometimes a lot of data may be scanned and aggregated to build a small result set). In that case, you might not want to send the query back to DB2 with just a different ORDER BY specification -- maybe you just want to re-sort the 20 or 50 or 100 (or whatever) rows at the client end of things. The real work here was generating the result set. A simple re-sort might best be done locally with respect to the end user (on the other hand, if the query in question is very fast-running and the result set is rather small, having DB2 re-order the rows at the user's request is probably no big deal).

So, with the occasional exception here and there, you really are doing the right thing by letting DB2 do all that it can do with SQL (and remember that some of this logic- and functionality-implementing SQL -- examples include the creation of triggers and UDFs, and the altering of tables to include check constraints or referential integrity rules -- is in a DBA's domain, so get help there when you need it). Your programs are likely to be more efficient, performance will probably be more consistent over time, the code you have to write will be simplified, and your organization overall will benefit from the accessibility and re-usability of capabilities built into your DB2 database. A lot to like, there.  

Some Nuggets from IDUG in the OC, Part 2

Following up on the entry I posted last week, here are some more items of information picked up in sessions I attended during the IDUG 2011 North American DB2 Tech Conference, put on by the International DB2 Users Group earlier this month in Anaheim, California (in Orange County, aka "the OC"):

IBM Distinguished Engineer John Campbell delivered a presentation on DB2 10 for z/OS migration and early user experiences that was (as usual where John's concerned) full of useful, actionable content. One of the first points made during the session had to do with hash access, a new (with DB2 10) way of organizing rows in a table that can provide, under the right circumstances, super-efficient access to data. John told attendees that the "sweet spot" for hash-organization of data -- the set of data access scenarios in which hash organization would be the right choice for a table -- is smaller than he'd originally anticipated. I was pleased to hear that note of caution, as I feel that some folks have gotten a little carried away with the notion of accessing data rows via a hash key. It's something best used in a targeted way, versus generally. The hash route could be good for tables for which reads well outnumber inserts, and for which read access is dominated by single-row retrieval using a unique search argument (that would be the table's hash key column, or column group).

John also talked about the increased use of real storage (i.e., server memory) that should be expected in a DB2 10 environment relative to prior-release DB2 systems. He mentioned that DB2 10 could require 10-30% more real storage "to stand still," and more than that if people get aggressive with memory-using features such as high-performance DBATs (about which I blogged in an entry posted last month). In a lot of environments, this shouldn't be a concern, as I've seen plenty of production z/OS systems with tens of gigabytes of memory and demand paging rates in the low single digits per second; however, if you're running with DB2 V8 or DB2 9 and you're kind of tight on memory in your production z/OS LPAR (and I'd say that you are if the demand paging rate is near or more than 10 per second), consider adding memory to that LPAR prior to migrating to DB2 10 (and John pointed out that the cost of memory was substantially reduced when IBM rolled out the z196 mainframes).

John mentioned that while performance improvements (referring to CPU time) in the range of 5-10% should be expected for applications' in-DB2 processing in a DB2 10 environment (assuming that packages are rebound on the DB2 10 system), some "skinny packages" (packages with very short-running SQL statements and/or that issue very frequent commits) bound with RELEASE(COMMIT) were seen to have somewhat worse CPU efficiency in a DB2 10 environment as compared to prior-release systems. John pointed to a recently available fix (the one for APAR PM31614) that addresses this situation.

DB2 10's support of much larger numbers of concurrently active threads got prominent mention in John's presentation. Some threads, he mentioned, use more virtual storage than others, but John indicated that he's pretty confident that DB2 10 sites should be able to support at least 2500 to 3000 concurrently active threads per subsystem.

DB2 users should expect that exploitation of new features will increasingly require the use of universal table spaces, which were introduced with DB2 9. John pointed out that universal table spaces are a prerequisite for inline LOBs (a potentially major performance boost for applications that read and/or insert mostly small LOBs), the "currently committed" locking behavior (whereby data readers don't wait for the release of X-locks held for inserts or deletes of qualifying rows), and the aforementioned hash organization of tables. Fortunately, DB2 10 provides a means of migrating existing simple, segmented, and "classic" partitioned table spaces to universal table spaces without the need for an unload/drop/re-create/re-load sequence (this is done by way of ALTER TABLESPACE followed by an online REORG).

John brought up a change in the behavior of the CHAR function in a DB2 10 environment, when input to the function is decimal data (among other things, values returned by the function are not padded to the left with zeros). He then informed attendees that the fix for APAR PM29124 will restore pre-DB2 10 behavior for the CHAR function operating on decimal data.

Near the end of his presentation, John talked about preparing for DB2 10 in a DB2 Connect sense. He mentioned that DB2 10 requires that DB2 clients (referring to DB2 Connect or the IBM Data Server Drivers) be at least at the V9.1 fix pack 1 level. Several new DB2 10 functions require that DB2 clients be at the V9.7 fix pack 3A level or higher.

IBM's Beth Hamel delivered a session on data warehousing and business intelligence on the mainframe DB2 platform. She pointed out that two of the trends that are driving growth in data warehousing on System z are consolidation (mainframe systems are very highly scalable) and the rise of "transactional analytics" (these high-volume, quick-running queries can run concurrently with more complex, longer-running BI tasks in a mainframe DB2 system thanks to z/OS's advanced Workload Manager).

Beth also noted that a very large amount of the source data that goes into data warehouses comes from mainframe systems, and she said that organizations are looking to get closer to this source data by locating their data warehouses on mainframes. Also boosting System z activity in the BI space is the fact that data warehouses are increasingly seen by businesses as being mission critical. That leads to a greater emphasis on availability -- long a bedrock strength of the mainframe platform (and that high availability story gets even better when several DB2 for z/OS systems function as a data sharing group on a Parallel Sysplex shared-data mainframe cluster).

In addition to delivering new BI-enabling technology for the mainframe platform, IBM has made moves in the areas of product pricing and packaging that can help organizations to get up and running with DB2-based data warehouses on System z in less time and at a lower cost versus a piece-by-piece implementation. Beth pointed to the InfoSphere Warehouse on System z offering, which provides cubing services to accelerate OLAP applications, SQL-based ETL functionality, and more, all managed by way of an easy-to-use interface. Beth told attendees that the cost of InfoSphere Warehouse on System z is way below that of the offering's individual components, were these to be acquired separately.

In wrapping up her presentation, Beth talked about Version 2 of the IBM Smart Analytics Optimizer, a query accelerator that attaches to a mainframe DB2 system and can deliver eye-popping performance for formerly long-running data retrieval tasks. ISAO V2 will take advantage of Netezza technology (Netezza was acquired by IBM last year) to expand the range of queries that can be processed by the ISAO and to significantly boost the system's data capacity. Beth said that she expects the beta program for ISAO V2 to begin in the third quarter of this year.

That's it for this collection of IDUG nuggets. I'll wrap up this series of posts with a Part 3 entry in a few days.

Some Nuggets from IDUG in the OC, Part 1

That's OC as in Orange County, California, where you'll find Anaheim, site of the 2011 International DB2 Users Group North American Tech Conference. The conference wrapped up on May 6, and herein I have items of information gleaned from some of the sessions I attended (posting delayed by a crazy-busy week following the IDUG event). In a few days I'll provide more of these nuggets in a Part 2 entry.

Gerald Hodge of HLS Technologies delivered a presentation on the plan management feature introduced with DB2 9 for z/OS. This capability enables you to rebind a package whilst retaining the previous version (and, optionally, the "original" version), making it easy to switch to a saved version, in case of performance regression, by issuing a REBIND PACKAGE command with the SWITCH(PREVIOUS) option (or SWITCH(ORIGINAL), to restore the original version of the package). The purpose of the plan management feature is, as some have said, to "take the fear out of rebinding." Gerald pointed out that rebinding all packages is getting to be a more and more important part of the DB2 for z/OS migration process, as performance gains -- and this is particularly true of DB2 10 for z/OS -- depend increasingly on regeneration of packages. He stressed that realizing performance gains in a DB2 10 system (especially in the area of CPU efficiency) is NOT just a matter of getting a different access path. In fact, performance improvements are expected for most packages when rebound in a DB2 10 environment (and we're talking about Conversion Mode here) even when access paths for the packages' SQL statements don't change. The packages generated in a DB2 10 system via REBIND will feature internal enhancements (some pertaining to column handling, others to predicate evaluation) that should result in DB2 doing the same thing better (if access paths are as they were in the previous release). Package regeneration is also required to get the bulk of thread-related virtual storage up above the 2 GB "bar" in the DB2 database services address space (aka DBM1), and THAT is the key to DB2 10's ability to support 5 to 10 times the number of concurrently active threads versus previous releases of the product.

Gerald reminded session attendees that packages last bound prior to DB2 for z/OS Version 6 will be automatically rebound when you get to DB2 10 (that's if the ABIND parameter in ZPARM is set to the default value of YES or to COEXIST; otherwise, an attempt to execute such a package will result in a -908 SQL error code), and he recommended getting such old packages rebound in the "come from" DB2 environment (be that DB2 V8 or DB2 9) BEFORE going to DB2 10. Want to see if you have packages that were last bound prior to DB2 V6? Run the DB2 pre-migration "check-out" job on your system. This job, DSNTIJPM, ships with DB2 10. You can also get the DB2 V8 and DB2 9 versions of the job (DSNTIJP8 and DSNTIJP9, respectively) by way of the fix for APAR PM04968.

Bryan Paulsen of John Deere talked about his company's experience with DB2 10. Bryan told session attendees that Deere's DB2 10 migration and fallback testing went "flawlessly." He also spoke of old DB2 functionality that is going away with DB2 10 -- in particular, the private protocol used for mainframe-DB2-to-mainframe-DB2 client-server communication. DRDA has long been the preferred protocol for DB2 distributed database processing (for all platforms on which DB2 runs -- not just the mainframe), but at some shops there are still packages that utilize private protocol. Bryan pointed people to an APAR, PK64045, that provides, for DB2 V8 and V9 users, several tools that can facilitate the identification of private protocol-using packages and the conversion of these to use the DRDA protocol. He also mentioned APAR PK92339, which introduces a new ZPARM that can be used to disable private protocol at the DB2 subsystem level. Bryan said that Deere found this private protocol disablement capability to be a very useful means of "smoking out" private-protocol-using programs prior to the migration to DB2 10.

Bryan noted that DBRMs bound into plans (versus packages) is another old piece of functionality that is gone in a DB2 10 environment. If DB2 10 encounters a DBRM that is bound directly into a plan, it will create a corresponding package, but Bryan suggested that people do these conversions themselves before going to DB2 10. He briefly described APAR PK62876, which delivers a new REBIND PLAN option that can be used to convert DBRMs bound directly into a plan into packages, and then to convert the plan to use a PKLIST that will include the collection into which the new packages (corresponding to the DBRMs) were bound.

Moving on, Bryan said that Deere had successfully tested with 3000 concurrently active threads on a DB2 subsystem. He noted that Deere normally runs with 450 concurrently active threads for a subsystem, so this test result is in keeping with the expectation that DB2 10 will support 5 to 10 times more concurrently active threads versus a DB2 V8 or V9 system.

Bryan mentioned that he likes the new DB2 10 catalog table SYSPACKCOPY, which makes it easier to track the status of previous copies of a package that are maintained by way of the previously mentioned plan management functionality of DB2 (which allows retention of, and an easy "switch to," the immediate previous and -- optionally -- the "original" copy of a given package).

DB2 10 online schema enhancements -- a further expansion of changes that can be effected for a database object without the need to drop and recreate that object -- were another of the new release capabilities successfully tested by Deere. Bryan said that Deere changed the DSSIZE value for a partition-by-growth universal table space, and changed page sizes for table spaces and indexes, using the new ALTER-then-REORG process introduced with DB2 10.

Sometimes, people will see a DB2 online REORG job fail in the switch phase (the last phase before clean-up) because a thread holds a read claim on the table space or partition being REORGed. DB2 10 introduced a new ZPARM parameter, LRDRTHLD, that can help in identifying processes that hold read claims for extended periods of time (read claims are released at commit time, but sometimes read-only applications do not issue commits in a timely manner). Bryan said that Deere successfully tested this new functionality, which will cause DB2 to write a trace record when the threshold is hit, using the default LRDRTHLD value of 10 minutes.

Bryan concluded his presentation with brief descriptions of some relatively new DB2 10 APARs, including PM27811, which allows for inlining of LOB values in the skeleton package table (SPT01) in the DB2 10 directory (as part of the DB2 10 enable new function mode process, SPT01 is converted to a partition-by-growth universal table space, with package information stored as LOB values). LOB inlining should improve SPT01 access performance and reduce disk space requirements for the tablespace (the latter because a LOB tablespace cannot be compressed, but the LOB values inlined in a base table can be compressed when compression is used for the corresponding table space). [Note: APAR PM27073 enables one to change the LOB inline length used for SPT01.]

Terry Berman of DST Systems also discussed DB2 10 features from a user's perspective. He started out with a positive review of the catalog restructuring accomplished as part of the DB2 10 enable new function mode (ENFM) process. In particular, SYSDBASE undergoes big changes: each of the 14 tables formerly in that table space goes into a partition-by-growth table space (a PBG table space, being a universal table space, contains one and only one table). Tests run at DST showed that the catalog structure changes greatly improved concurrency for DDL and BIND operations (Terry said that they successfully tested 20 concurrent BINDs).

One of the nice features delivered with DB2 9 for z/OS was the LASTUSED column of the SYSINDEXSPACESTATS catalog table -- a BIG help when it comes to identifying indexes that are not helping performance and are candidates for dropping (fewer indexes means better performance for INSERT and DELETE operations, and for UPDATEs of indexed columns, as well as savings with respect to disk space consumption). Terry gave a thumbs up to the introduction, with DB2 10, of LASTUSED in the SYSPACKAGE and SYSPLAN catalog tables, saying that this information facilitates identification of obsolete plans and packages. The new LASTUSED column values are maintained in DB2 10 conversion mode and are updated once per day.

Also on the topic of new DB2 10 catalog columns, Terry said that he was pleased to see read-activity metrics introduced to the real-time statistics tables in the catalog. He specifically mentioned the usefulness of two new SYSTABLESPACESTATS columns: REORGSCANACCESS, which records data accesses for a table space since the last REORG or LOAD REPLACE of the object (or since the object was created, if it hasn't been subsequently REORGed or LOAD REPLACEd), and REORGCLUSTERSENS, which shows the number of times that data in a table space was read by SQL statements that are sensitive to the clustering sequence of data in the table space.

Terry told session attendees that their DB2 EXPLAIN really ought to be in Unicode format in the DB2 10 environment (APAR PK85068 can help with the conversion of EBCDIC EXPLAIN tables to Unicode). Terry also pointed out that the number of PLAN_TABLE columns continues to grow: the DB2 10-format PLAN_TABLE has 64 columns -- up from 59 columns for the DB2 9 format and 58 for the DB2 V8 format (PK85068 also helps in getting EXPLAIN tables into your current release format).

Terry talked up the access path repository introduced with DB2 10, pointing out that it can be used to (among other things) set various optimization options, such as REOPT, at an individual SQL statement level, versus the package-level granularity of previous DB2 releases.

Terry concluded his presentation with information related to DB2 instrumentation. He noted that compression of SMF trace records works very well: DST saw 75.8% compression with CPU overhead that did not exceed 1% (APAR PM27872 provides a sample SMF decompression program). Terry also said that he really likes the inclusion of statement ID information in DB2 10 messages, which -- thanks to the new STMT_ID columns in the SYSPACKSTMT catalog table and the DSN_STATEMENT_CACHE_TABLE, makes it much easier to tie error situations to SQL statements in a DB2 10 system. Also getting mention was the separation (Terry: "Finally") of lock and latch times in DB2 accounting trace data, the new IFCID 359 trace record (index page split activity), IFCID 361 (auditing the DB2 "superusers" in the system), and IFCID 401, which provides statement-level metrics with a lower CPU overhead versus previous DB2 releases (Terry pointed out that getting the IFCID 401 information requires that packages be bound or rebound in a DB2 10 new function mode environment).

That's all for now. As I mentioned up top, more to come in a few days.