DB2 for z/OS: Trading Memory for MIPS (Part 3)

In part 1 of this three-part series on leveraging Big Memory (i.e., a large z/OS LPAR real storage resource) to enhance DB2 workload CPU efficiency, I focused on getting the most out of a DB2 buffer pool configuration without increasing its size (this by adjusting certain threshold settings and redistributing buffers between low-I/O and high-I/O pools). In part 2, I offered guidance on being bold -- but not reckless -- in asking for more memory for a DB2 subsystem's buffer pool configuration. In this, the concluding entry of the series, I'll look at memory-for-MIPS moves that involve DB2 pools other than the buffer pools -- starting with the EDM pool.

Actually, that "starting with the EDM pool" bit is an "it depends" thing, depending on the version of DB2 for z/OS you're using. What I want to talk about here is improving CPU efficiency via the use of the RELEASE(DEALLOCATE) parameter of the BIND (and REBIND) PACKAGE command. RELEASE(DEALLOCATE), when used for an SQL-issuing program that is executed via a thread that persists through commits, saves CPU cycles by causing DB2 to retain certain items associated with package execution, such as table space locks and thread copies of package sections, until thread deallocation, as opposed to releasing these resources at each commit (and very likely having to re-acquire them as the application process continues to execute). Threads that persist through commits include CICS-DB2 protected entry threads, threads used by batch jobs, and -- starting with DB2 10 -- high-performance database access threads used for DRDA clients (I blogged about high-performance DBATs last year). The less SQL work an application process performs between commits, the greater the proportional CPU cost of at-commit resource release and re-acquisition that occurs when RELEASE(COMMIT) is in effect. It is for such application processes -- high volume, relatively simple transactions, and batch jobs that issue lots of commits -- that RELEASE(DEALLOCATE) can deliver significant CPU savings (sometimes on the order of 10%). There are operational considerations associated with RELEASE(DEALLOCATE) and persistent threads. These include utility execution scheduling (a utility might not be able to execute if an application process holds a lock -- even a non-exclusive, intent-type lock -- on the target table space, and with RELEASE(DEALLOCATE) that table space -level lock will be held until thread deallocation) and package bind and DDL activity scheduling (same reason -- you could end up having to wait for deallocation of persistent threads used for the execution of packages bound with RELEASE(DEALLOCATE)). Keep these considerations in mind. It's likely that you can deal with them -- lots of folks have (and the PKGREL option of the -MODIFY DDF command can help in this regard when you use high-performance DBATs).

Another operational consideration associated with the use (or increased use) of the RELEASE(DEALLOCATE) bind option has to do with increased virtual and real storage utilization, and that's where the "it depends" at the start of the preceding paragraph comes in. There's no "it depends" with respect to increased memory utilization -- that WILL happen when you use RELEASE(DEALLOCATE) with persistent threads. The dependency is on WHERE that increased memory utilization happens in the DB2 database services address space (aka DBM1). See, one of the thread-allocated resources that DB2 will retain until thread deallocation when RELEASE(DEALLOCATE) is in effect for a package is the copies of package sections associated with the SQL statements issued by the program. Here's how that works: when a program issues an SQL statement, the section of the associated package containing the bound statement (if it's static) will be copied from the "skeleton" part of the EDM pool to another part of DBM1 (if it's a dynamic SQL statement, the relevant package could be, for example, one of the IBM Data Server Driver packages used for dynamic SQL statements issued by network-attached client-server applications via JDBC calls). WHERE in DBM1 will that thread copy of the package be stored? THAT'S the version-dependent part of this equation. Prior to DB2 10, the thread's copy of a package section (or a plan section, in the case of a DBRM bound directly into a plan -- something that is not supported in a DB2 10 environment) was stored in a part of the EDM pool. With DB2 10, the package section allocated to a thread is copied to an agent local pool that is exclusive to the thread in question (and there's some plan-related thread storage, too, even in a DB2 10 system -- it's mostly package storage, but not all). This DB2 10 change is a good thing, in that it eliminates latching that occurred when EDM pool space was used for copies of packages allocated to threads. It also moved this storage above the 2 GB bar for packages bound in a DB2 10 system, and THAT gives you a WHOLE lot more virtual storage head room for using RELEASE(DEALLOCATE) to reduce the CPU cost of DB2-accessing programs. [In a DB2 Version 8 system, none of this virtual storage used for thread copies of package and plan sections went above the 2 GB bar, and with DB2 9 only some of it went above the 2 GB bar.]

The bottom line, then, is that RELEASE(DEALLOCATE) can do you more good, in terms of boosting CPU efficiency, in a DB2 10 environment because you almost certainly will be able to use this bind option more extensively than before. Now, this doesn't mean that you can or should bind ALL of your packages with RELEASE(DEALLOCATE) in a DB2 10 system, because real storage resources, though potentially extensive, or nonetheless finite. If your DB2 monitor supports DB2 10, it will report to you (in a statistics detail report or an online display) the amount of storage -- above and below the 2 GB bar -- used for plans and packages (these are copies associated with threads -- storage used for skeleton package and cursor table purposes is still in the EDM pool, and is reported as such). Keep an eye on that storage utilization, and of course keep an eye on the demand paging rate for the z/OS LPAR in which the DB2 subsystem is running: if that's in the single digits or less per second, on average, during busy periods of the processing day, real storage is not being pressured to an undesirable extent. In a DB2 9 or Version 8 system, keep an eye on the EDM pool storage used for PT and CT, and consider enlarging that part of the pool (or cutting back on your use of RELEASE(DEALLOCATE)) if the number of free pages there is indicated to be less than 10% of the total number of pages in that part of the pool (and that's actually two parts in a DB2 9 environment -- one above the 2 GB bar and one below).

The other EDM-pool related memory-for-MIPS play has to do with dynamic statement caching. With respect to the global (versus the local) statement cache, this has been above-the-bar storage since DB2 V8 (the global statement cache, allocated when the ZPARM parameter CACHEDYN is set to YES, is very commonly used; local statement caching, associated with the KEEPDYAMIC(YES) package bind option, is less commonly used). Your DB2 monitor will show you the hit ratio for the dynamic statement cache. The larger this section of the EDM pool, the greater the hit ratio is likely to be, and the larger that hit ratio is the more you save through avoidance of full prepares of dynamic SQL statements. With this said, there's probably a point of diminishing returns that you could hit in your system with regard to enlarging the dynamic statement cache (accomplished by updating the EDMSTMTC parameter in ZPARM). If the hit ratio for the dynamic statement cache is north of 90% in your environment, making the cache larger may not move the needle much further to the right.

Turning now the RID pool, used by DB2 for RID list processing (done for things like index ANDing and ORing, hybrid join, and list prefetch): what you want is for as many of these operations as practically possible to complete in memory, and a larger RID pool will help in accomplishing that objective. Your DB2 monitor will show you the number of times that RID processing operations were terminated because of a lack of storage for such processing, and a RID pool enlargement can be accomplished by increasing the value of the MAXRBLK parameter in ZPARM (the RID pool has been allocated in above-the-bar virtual storage since DB2 V8). DB2 10 delivered a couple of important changes related to RID list processing: first, the default size of the RID pool in a DB2 10 system is 400 MB, versus 8 MB with DB2 9 and DB2 V8 (this is one of a number of reasons why you can expect DB2 real storage utilization to increase when you migrate to DB2 10). Important change number 2 concerns what happens when RID pool space is insufficient for the completion of a RID list processing operation. Prior to DB2 10, such a storage-related RID list processing failure would cause DB2 to abandon RID list processing for the SQL statement in question and go instead with a table space scan for the object being accessed. With DB2 10, insufficient RID pool space for a RID list processing operation causes DB2 to keep on trucking with the operation, using 32K work file space for the RID list. That's likely to be a performance-benefiting approach versus abandoning RID list processing for a table space scan, and even though processing a RID list using work file space is not quite as CPU-efficient as getting it all done in the RID pool, the overhead of overflow is mitigated if you have a large buffer pool dedicated to your 32K work file table spaces (fewer I/Os means less CPU consumption). Note that the new DB2 10 ZPARM parameter MAXTEMPS_RID can be used to limit the amount of space in the work file database that DB2 can use for a single RID list processing operation.

Lastly, a word about the DB2 sort pool. This is space in memory (above the bar since DB2 V8) that DB2 uses for SQL-related (versus utility-related) sort operations. The larger the sort pool, the more CPU-efficient DB2 SQL-related sorts tend to be. If you're considering a sort pool enlargement, keep a couple of things in mind: first, this pool (sized via the SRTPOOL parameter in ZPARM), is the maximum size of the sort work area that DB2 will allocate for each concurrent sort user (so a sort pool size of X MB could result in DB2 using 20X MB of virtual storage for sort pool space if there are 20 concurrent sort operations in progress). Second, the default size of the sort pool went from 2 MB in DB2 V8 and DB2 9 systems to 10 MB with DB2 10 (again, one of multiple reasons for DB2 10's greater use of memory versus DB2 9 and DB2 V8). If you want to increase the size of the sort pool on your system (the upper limit is 128 MB, but the largest value I've seen on a DB2 system is 30 MB), keep that per concurrent sort item in mind, and as always keep your eye (perhaps with the help of a z/OS systems programmer) on the z/OS LPAR's demand paging rate. I'm fine with a rate in the low single digits per second or less. A demand paging rate in the higher single digits per second is yellow-light territory in my book, and a rate in excess of 10 per second would tell me that the z/OS LPAR's memory resource may be over-taxed, and I might take steps to reduce DB2's use of that resource (via buffer pool, EDM pool, RID pool, or sort pool adjustments, or some combination of these).

Modern mainframe systems tend to have a lot of real storage, and the size of that resource for the average z/OS LPAR keeps getting larger, based on what I'm seeing. I'd like for you to use System z memory to make your DB2 workload more CPU-efficient, and I hope that the information in this three-part blog entry will help you to do that.

DB2 for z/OS: Trading Memory for MIPS (Part 2)

In part one of this three-part entry on leveraging System z memory to reduce CPU consumption for a DB2-accessing application workload, I wrote about getting your DB2 buffer pool configuration house in order by doing things to improve CPU efficiency without increasing the total amount of virtual storage allocated for page caching (e.g., adjusting certain thresholds for work file-dedicated pools, making appropriate use of buffer page-fixing, choosing the right buffer-steal algorithm for a pool, and judiciously shifting buffers from low-I/O to high-I/O pools). After taking these actions you might find that you still have high read I/O rates for some of your buffer pools. That's when you'd take the next step (if feasible, and I'll get to that momentarily) and increase the size of your buffer pool configuration to drive down disk read activity and associated CPU consumption. In a nutshell, that is the subject of this part two post. Within the next couple of weeks I'll post my part three entry, in which I'll look at memory-for-MIPS actions that involve DB2 virtual storage pools other than the buffer pools (e.g., the EDM pool, the RID pool, and the sort pool).

Before going further, I'd like to address a topic that's come up a number of times in conversations I've had with DB2 for z/OS DBAs and systems programmers -- that being the difference between GETPAGE requests and DB2 read I/O requests. Plenty of people have the idea that a bigger buffer pool means fewer GETPAGEs. In fact, that is generally NOT the case. Driving down GETPAGEs is a worthwhile objective, since GETPAGE requests -- more than anything else, in my experience -- are the key determinant of the CPU cost of SQL statement execution; however, growing your buffer pool configuration is not the way to reduce GETPAGE activity. Instead, you'd attack that objective through means such as physical database design changes (adjusting table clustering, adding or modifying indexes, etc.) and SQL statement coding (e.g., changing a non-indexable predicate to an indexable predicate). Enlarging your buffer pool configuration is about reducing I/O requests (primarily read requests, which tend to greatly outnumber write I/O requests) and the CPU cycles consumed in driving those requests. Is there a connection between GETPAGE requests and read I/O activity for a buffer pool? Of course there is: when DB2 needs to examine a page from a table space or an index -- usually on behalf of an application program -- it will issue a GETPAGE request for that page. If the page in question is NOT already in the buffer pool to which the table space or index is assigned, the GETPAGE request will result in DB2 initiating a read I/O operation to bring the page into memory (unless the page is to be read into memory via an already-underway prefetch I/O, in which case the requesting program will wait for the completion said prefetch I/O). A bigger buffer pool, then, doesn't mean fewer GETPAGEs, as DB2 will still need to look at the same number of table and/or index pages in executing the same SQL statement (all other things being equal). What a bigger buffer pool DOES mean is that fewer of the GETPAGE requests will result in read I/O operations, since the likelihood of a requested page already being in memory will tend to increase as a buffer pool's size increases.

There is an exception to this rule: if a buffer pool is way too small relative to the number of pages belonging to objects assigned to the pool and the volume of requests for those pages, DB2 could end up hitting the data manager threshold for the pool. That threshold, also known by the acronym DMTH, is reached when 95% of a pool's buffers are non-stealable (either currently in use or updated and not yet externalized to disk or to a coupling facility in a DB2 data sharing system). When that happens, DB2 will issue a GETPAGE for every row retrieved from a page, so that, for a page from which 20 rows are retrieved for a program, 20 GETPAGE requests will be issued, versus one GETPAGE had DMTH not been hit for the pool. Obviously, a big spike in GETPAGE activity will cause a spike in CPU consumption, so keep an eye on DMTH and make sure that you're not hitting that threshold for any of your buffer pools (your DB2 monitor will provide this information, as will the output of the DB2 command -DISPLAY BUFFERPOOL(xxx) DETAIL, where "xxx" could be the name of an individual pool, such as BP4, or the word ACTIVE if you want to see information for all active buffer pools). If DMTH is being hit for a pool, it's likely that the pool is significantly undersized.

On now to making a buffer pool configuration larger. While there's no need to apologize for that (I'll expand on this point in a moment), you want to do it for a reason, and the reason, as just pointed out, is to reduce disk read I/O activity (which in turn reduces CPU consumption). So, your first order of business is to check on the rate of read I/O activity for each of your buffer pools. As pointed out near the end of my part one memory-for-MIPS post, what you're looking for is the TOTAL rate of read I/O requests (synchronous and asynchronous) per second for your buffer pools. Also as pointed out in the part one post, you can get that figure from a DB2 monitor statistics detail report or online display of buffer pool activity, or from the output of the DB2 command -DISPLAY BUFFERPOOL DETAIL (and I describe in the part one post how to use -DISPLAY BUFFERPOOL DETAIL output to get the desired information). If the total read I/O rate for a pool is less than 100 per second, making that pool larger won't hurt anything (assuming the presence of sufficient real storage to back that enlargement, about which I'll have something to say in a bit), but it likely won't move the CPU consumption needle much (though you might want a zero-read-I/O situation associated with "pinning" a DB2 object in memory -- something made more straightforward and effective in a DB2 10 environment via the new PGSTEAL(NONE) buffer pool option, described in the part one post). What should draw your attention are any pools for which the read I/O rate is in the hundreds -- or even thousands -- per second (the highest I've seen with my own eyes at a DB2 for z/OS site is 9000 read I/Os per second for a pool). Taking a big chunk out of these numbers through buffer pool enlargement can result in a significant improvement in CPU efficiency for your DB2 workload.

Now, taking a "big chunk" out of a high rate of disk read I/Os is not likely to happen via itty-bitty steps, by which I mean that you probably won't see much of an impact from adding 1000 buffers to an 80,000-buffer pool. Big-step buffer pool enlargements will often involve increasing a pool's size by 25% or even 50% (a pool that's currently quite small -- say, 10,000 4K buffers in a system with a lot of real storage -- might be doubled in size -- or more -- in a single step, depending on the particular circumstances of the situation). I tend to think big, in terms of buffer pool enlargement, because I so often see buffer pools with high read I/O rates that are way smaller than they could be, given the abundance of memory available on the associated z/OS LPAR. Here's the deal: since 64-bit addressing was introduced in the System z environment some years ago, and the cost of memory was reduced for new mainframe models, it's become more and more common for production z/OS LPARs to be configured with 20 or 40 GB or more of real storage (and sometimes you see a z/OS LPAR with north of 100 GB of real storage -- a z196 server can be configured with up to 3 TB of memory); yet, in these big lakes of memory you find production DB2 subsystems with a total aggregate buffer pool size (meaning, the sum of the sizes of each allocated pool) of a measly 1 GB -- or even less. HELLO! IS ANYONE UNDER THE IMPRESSION THAT YOU GET BONUS POINTS FOR NOT USING A HARDWARE RESOURCE -- MAINFRAME MEMORY -- FOR WHICH YOUR ORGANIZATION PAID MONEY? Come on, folks. That stuff is there to enable your system to perform better, and no subsystem will use more memory to better effect than DB2.

That brings us to the "don't apologize" part. Some DB2 for z/OS people -- especially those who've worked with DB2 for 15 or 20 years or more -- seem to have some kind of a guilt complex over their subsystem's use of mainframe memory resources. "Golly, I'm using a GIGABYTE just for my buffer pools. The other subsystems on the production LPAR don't use that much memory. I should be satisfied with what I have." WRONG. Other z/OS subsystems that handle things like transaction management and security and networking don't NEED the memory that a relational database management system needs, and -- more importantly -- they don't benefit from getting lots more memory the way DB2 does. DB2 for z/OS was architected, from the get-go, to perform better with a large real storage resource. Give DB2 more memory, and -- unless your system is storage-constrained, and I'll get to that -- throughput goes up and CPU consumption (most likely) goes down (though if more memory for DB2 lets you get a lot more work done in a unit of time, CPU utilization could increase, but that's usually OK if it means that, for example, a critical batch run completes in much less time than before). Some DB2 sites -- and more as time goes on -- have figured this out. They've leveraged big mainframe memory resources to greatly increase DB2 buffer pool sizes, with very positive effects on application performance. The biggest buffer pool configuration I've seen for a single DB2 subsystem is approximately 42 GB. Are the people at that shop crazy? Of course not. They have a z/OS system with a large real storage resource (way bigger than 42 GB), and they are taking advantage of that to deliver excellent performance for a high-volume, mission-critical application that accesses a very large database. They key is to go big when there's big memory available, without going too big.

And that's the rub, you might think. How big is too big, in terms of mainframe memory allocated to a DB2 subsystem? Actually, that's not too hard to monitor. The main thing here is to avoid a situation in which mainframe memory is over-committed, and the metric to keep an eye on in that regard is the z/OS LPAR's demand paging rate. This figure, available via a z/OS monitor, is the rate (usually reported as a per-second number) at which z/OS has to bring into real storage, on-demand, pages that were paged out to auxiliary storage (often referred to as page data sets, on disk). What should that rate be? I'll tell you one thing: it shouldn't be zero -- not in my book. If the z/OS system's demand paging rate is zero, that tells me you have an underutilized resource that could be used to improve the performance of your DB2 workload, and you can change that "underutilized" situation by enlarging buffer pools that have high read I/O rates (or by pinning some really performance-critical DB2 objects in memory). Now, while I think that a demand paging rate that's greater than zero is generally a good thing, you don't want that number to go too high. I'm personally very comfortable with a demand paging rate that's between 1 and 10 per second during busy times (i.e., times of high volume workload processing). Low single-digits I like more than high-single digits, but I wouldn't be concerned about the memory resource being under more-than-desired pressure unless the demand paging rate gets into 10+ per second territory (and here I'm not counting the occasional memory-intensive event, such as a dump, that could cause the demand paging rate to temporarily spike to a higher level). Should your aim be a demand paging rate of 10 per second? No. I'm just saying that a rate of less than 1 per second (quite common these days) means that the system's real storage resource is not under any kind of significant pressure, and there is "head room" available for increasing virtual storage usage -- through enlarging buffer pools, for example -- without negatively impacting non-DB2 work on the system.

So, if the demand paging rate on your system is less than 1 per second and you have high read I/O buffer pools and/or you want to pin some DB2 objects in memory, what should you do? How big should you go, buffer pool-wise? Here's a rule of thumb that's worked for me: in my experience, when a single production DB2 subsystem is running on a z/OS LPAR, that LPAR's memory resource is very unlikely to be under an undesirable level of pressure if the DB2 subsystem's buffer pool configuration size is up to 25% of the z/OS LPAR's real storage size. So, if you have a z/OS LPAR with 20 GB of real storage, a production DB2 subsystem buffer pool configuration size of 5 GB (again, meaning the aggregate size of all of the subsystem's active buffer pools) should pose no problem in terms of leaving plenty of memory for address spaces other than DB2's, such as those associated with TSO, CICS, batch, RACF, etc. (keep in mind that when the maximum real storage size was 2 GB, not too many years ago, it was not uncommon for sites to use 50% or even 75% of that for DB2 buffer pools). If you're well south of that 25% level, you should be able to grow your DB2 buffer pool configuration considerably without hurting overall system performance -- just keep an eye, as I've said, on that demand paging rate, and see that it stays in the aforementioned single digits per second range. Already have a DB2 buffer pool configuration size that's 25% (or even more) of the z/OS LPAR's real storage size? That doesn't mean you have to stop there. Just be deliberate about further buffer pool size increases, and don't overextend the memory resource of the system. Also, don't over-do it with respect to the use of PGFIX(YES) for your buffer pools, as I noted in my part one memory-for-MIPS post. And one more thing: if you have more than one production DB2 subsystem on an LPAR (test and development subsystems usually have buffer pool configurations that are smaller than that of a production subsystem), be aware of the TOTAL buffer pool configuration size of ALL the subsystems. Taking this to an extreme, if you have 4 production DB2 subsystems on one z/OS LPAR, don't size the buffer pool configuration of each to be 25% of the LPAR's memory -- that would likely result in a demand paging problem. You might want the TOTAL size of ALL of the subsystem's buffer pool configurations to be no more than half of the amount of real storage assigned to the LPAR.

If you make a buffer pool larger, check that read I/O rate again. If it's gone down, good. If it hasn't, you might want to make the pool larger still, but if you do that and the read I/O rate still doesn't budge, you may want to think in terms of taking some of the more performance-critical objects in that pool and assigning them to a new pool.

Finally, if you use DB2 in data sharing mode, keep in mind that enlarging a local pool on the member subsystems may necessitate an enlargement of the corresponding group buffer pool (GBP) in the coupling facility. What you want to do here is keep the GBP large enough, relative to the aggregate size of the corresponding local pools, to prevent page invalidations due to directory entry reclaims, as such invalidations require member DB2 subsystems to re-read into memory pages that were likely "clean" when invalidated, and that's a drag on system performance (output from the DB2 command -DISPLAY GROUPBUFFERPOOL GDETAIL will show you whether such invalidations are occurring or not -- look for the field labeled "CROSS INVALIDATIONS DUE TO DIRECTORY RECLAIMS"). To properly size GBPs in this regard, you can make use of the IBM CFSizer tool, available on the Web at http://www-947.ibm.com/systems/support/z/cfsizer/index.html. There's also a quick sizing formula for 4K group buffer pools with the default 5:1 ratio of directory entries to data entries that has long worked well for me (I used it effectively at a DB2 data sharing site just last month): add up the sizes of the local pools and divide that by three, and you have a good GBP size. For example: if you have two member subsystems and BP4 has 15,000 buffers, or 60 MB, on each member, a GBP4 sized at (60 MB + 60 MB) / 3 = 40 MB should not cause page invalidations due to directory entry reclaims (I worked out that formula years ago for 4K GBPs, and haven't worked on a similar one for 8K or 16K or 32K GBPs, but I could do that for those GBP types without too much difficulty).

Whew. More words than usual for one of my blog entries, but I was on a roll and went with it. Hope the information is useful to you. As previously mentioned, I'll post part three of this three-part memory-for-MIPS entry within the next couple of weeks.

DB2 for z/OS: Trading Memory for MIPS (Part 1)

If you are a DB2 for z/OS DBA or systems programmer, chances are that a good bit of what you do is focused on improving the CPU efficiency of your organization's DB2-accessing application workload. There are various ways in which people go about reducing DB2-related CPU consumption, including SQL statement tuning, physical database design changes (e.g., adding or modifying indexes), shifting work to zIIP engines (for example, by going with native SQL procedures versus external stored procedures for data access via DDF), and exploiting new DB2 features and functions (such as hash organization of data in a table space -- a potential winner for tables for which single-row access via a unique key is the dominant data access mode). If you're doing these things, good -- keep doing them; but, let me tell you something: I've been working with DB2 on the mainframe platform since the late 1980s, and to this day I am still taken aback by the number of sites at which one of the best DB2 efficiency-boosters of all is not fully leveraged (and sometimes is hardly leveraged at all). I'm talking about z/OS system memory. You should really be looking to use this resource for all it's worth, because a) System z servers these days often are configured with LOTS of the stuff (you can get 3 terabytes on a z196), b) mainframe memory costs a lot less than it did a few years ago, c) adding memory to your system doesn't affect the cost of your System z software, and d) thoughtful memory utilization offers a variety of ways to enable DB2 to do more while consuming less in the way of processor cycles. In this, the first of a three-part entry, I'll look at maximizing the effectiveness of your DB2 buffer pools without making them larger. In part two, I'll examine the issue of configuring DB2 to use more memory for buffer pools in an unapologetic way -- meaning, when you want more memory resource for your DB2 subsystem, don't ask for it as though you're Oliver Twist ("Please, sir, I want some more"). In part three, I'll look at ways you can leverage mainframe memory for performance purposes via pools other than the buffer pools (e.g., the EDM pool, the sort pool, and the RID pool).

I want to start out this memory-for-MIPS series with information on using the buffer pool virtual storage resource that you already have more beneficially, from a performance perspective, for three reasons: 1) the buffer pool configuration is typically the primary DB2 consumer of a system's memory resource, 2) you can really do some good by tuning what you've got, and 3) before asking for more memory for DB2 buffers, you ought to be making good use of the memory you've got.

Following, then, are some of my favorite hints and tips for getting your DB2 buffer pool house in order:

A buffer pool dedicated to the work file table spaces is a different animal. Let me start by saying that it's a REALLY good idea to have buffer pools that are dedicated to your work file table spaces (one for the 4K table spaces, and one for the 32K table spaces -- the latter being, often, MUCH more heavily utilized in a DB2 9 or 10 environment, versus a DB2 V8 system). In configuring these buffer pools, keep a couple of things in mind:

• Almost all of the reads of data in work file table spaces will be of the prefetch variety. Why should you care about this? Because the default value for the virtual pool sequential steal threshold, VPSEQT, is 80. Basically, that setting means that only about 80% of the buffers in a pool dedicated to work file table spaces will get a lot of use, with the other 20% being reserved for relatively infrequent random (single-page) reads. Raise that threshold value significantly, and it's like getting more memory for free (you should see fewer prefetch read I/Os for the pool in question, and fewer read I/Os means less CPU spent in driving I/O operations). I've often seen a VPSEQT value of 95-99% for a work file-dedicated buffer pool used to good effect.
• The motivation for externalizing updated work file table space pages to disk is not the same as it is for application table spaces and indexes. For application data (in table spaces and indexes), you don't want too much time to pass between the updating of a page and the externalization of that changed page to disk. Why? Because if the DB2 subsystem should fail, restart processing will be elongated if a large number of pending writes have to be processed (part of restart following abnormal termination involves DB2 making sure that committed updates made to pages that were in memory and not yet externalized at the time of the failure are reflected in the data on disk; otherwise, the data on disk would be inconsistent). It's a different story for pages belonging to work file table spaces. Those table spaces are conceptually like scratch pads and don't have to be processed during restart as do application table spaces and indexes and the DB2 catalog. So, for the work file table spaces, page externalization is about ensuring that the associated buffer pools don't get too full of non-stealable pages. That being the case, the deferred write and vertical deferred write queue thresholds (DWQT and VDWQT) can be raised for work file-dedicated buffer pools without adversely affecting DB2 restart performance. Raising these thresholds for the pools used for work file table spaces reduces disk write activity and that reduces CPU consumption. Some folks specify 70 and 40 for DWQT and VDWQT, respectively, some go with 80 and 50, and some go with even higher values (the defaults are 30 for DWQT and 5 for VDWQT). A word of caution: at one site I saw that DWQT and VDWQT for a work file-dedicated buffer pool were both set to 90, and the data manager threshold (indicating that 95% of a pool's buffers are unavailable) was hit a great many times per hour for that pool. That is REALLY BAD for performance (big spike in GETPAGE activity), so watch that you don't go overboard with choosing elevated values for the deferred write thresholds for your work file buffer pools (a DB2 monitor will tell you if the data manager threshold has been reached for a buffer pool; so, too, will output of the DB2 command -DISPLAY BUFFERPOOL DETAIL).

Steal smart. By default, DB2 utilizes a least-recently-used (LRU) algorithm in identifying buffers that are to be stolen to accommodate new pages being brought into memory from disk. That's the right algorithm to use in most cases; however, if you have a DB2 9 (or earlier) system and you "pin" some objects in memory by assigning those objects to a pool that has as many or more buffers than the objects have pages (e.g., you assign index A, with 200 pages, and index B, with 300 pages, to a pool that has 500 or more buffers) then you should use the first-in, first-out (FIFO) page steal algorithm for the pin-pool. Why? Because FIFO is a simpler, and therefore less costly (CPU-wise), page steal algorithm versus LRU. For a pool used for object pinning, little to no buffer stealing will occur (depending on whether or not you actually have enough buffers in the pool to hold all the pages of the objects assigned to the pool), so why go with the more expensive page steal algorithm?

In a DB2 10 environment you have a new choice here: PGSTEAL(NONE). When that option is specified for a buffer pool, here's what happens:

1. When an object assigned to a PGSTEAL(NONE) pool is first accessed, DB2 will, in the background, prefetch every page of that object into the pool (the application process that first accessed the object doesn't have to wait for this prefetch-all process to finish -- it gets the pages that it needs and goes on from there).
2. For further access to the now-fully-in-memory object, the DB2 optimizer will assume that no I/Os will be required, and this assumption will factor into SQL statement access path selection.
3. If in fact the pool does not have enough buffers to hold all of the pages of an object assigned to the pool, buffers will be stolen as needed based on the FIFO page steal algorithm.

Use page-fixing of buffers appropriately. Since DB2 V8, you've had the option of fixing a pool's buffers in memory via the PGFIX(YES) option of the -ALTER BUFFERPOOL command. Page-fixing a pool's buffers (meaning that the page frames holding those buffers can't be stolen by z/OS) delivers CPU savings by eliminating the need to fix in memory and then release a buffer every time it receives a page read in from disk or provides a page to be written out to disk (those page fix and release actions become unnecessary when the buffers are always in a fixed-in-memory state). Page-fixing then, can boost a DB2 system's CPU efficiency when it's used for pools that have a high level of I/O activity (and the focus here is usually on read I/O activity). If there are 10 or 20 read I/O operations per second for a buffer pool, fixing that pool's buffers in memory won't have much of an impact on CPU consumption. If the rate of read I/O activity for a pool is hundreds or thousands per second, page-fixing buffers can deliver a nice CPU savings benefit. You might think that it would be easier to specify PGFIX(YES) for all of a DB2 subsystem's buffer pools, but unless you're really awash in mainframe memory I wouldn't recommend that approach. I think that it's a good idea to have some pools -- those with lower rates of read I/O activity -- for which buffers are not page-fixed. That approach provides z/OS with something of a safety valve, referring to stealable page frames holding DB2 buffers, that can be utilized if the system memory resource becomes overtaxed.

You may have heard that DB2 10 will use 1 MB page frames (if available) for page-fixed buffer pools. That's true, and this feature of DB2 10 further boosts the CPU savings effect of fixing buffers in memory by improving the efficiency of virtual-to-real storage address translation (versus a 4 KB page frame usage scenario). Keep this in mind, though: DB2 9 can't use 1 MB page frames for buffer pools, whether page-fixed or not. What this means is that you don't want to configure an overly large portion of your z/OS system's real storage resource to be managed using 1 MB page frames (that's done via the LFAREA parameter of the IEASYSxx member of PARMLIB) until you've migrated to DB2 10 and are likely past the point of needing to fall back to DB2 9. If you want to be conservative, migrate to DB2 10 and THEN configure your z/OS system for 1 MB page frames.

Consider robbing Peter to pay Paul, if Peter has more than he needs and Paul doesn't have enough. As I mentioned up front, this first of my three-part memory-for-MIPS blog entry is focused on making the best use of the memory already allocated for your DB2 buffer pools. That doesn't mean that you can't make a pool larger -- you can, if you decrease the size of another pool by the same amount. Suppose that buffer pools BP2 and BP3 are both sized at 40,000 buffers each in your system. If the read I/O rate for BP2 is 20 per second and the rate for BP3 is 2000 per second, I'd seriously consider taking a big chunk of BP2's buffers away and giving them to BP3 (for example, you might take BP2 from 40,000 down to 20,000 buffers, and take BP3 from 40,000 buffers to 60,000. Such a move might put a serious dent in the high read I/O rate seen for BP3 without substantially increasing the read I/O rate associated with BP2.

As for these read I/O rates I've mentioned, you can get those from a DB2 monitor statistics detail report, or perhaps from a monitor's online display of buffer pool activity. You can also get this information from the output of the DB2 command -DISPLAY BUFFERPOOL(ACTIVE) DETAIL (and I specify ACTIVE in the command to get information for pools that are actually being used). To get per-second figures using the command output, do the following:

1. Issue -DISPLAY BUFFERPOOL(ACTIVE) DETAIL at the beginning of some busy hour of the day (referring to workload being processed by the DB2 subsystem). The output of this issuance of the command does not have to be retained.
2. Issue -DISPLAY BUFFERPOOL(ACTIVE) DETAIL a second time, one hour after issuing the command the first time. Retain the output from this issuance of the command.
3. A few minutes after issuing the command the second time, issue it a third time. All you want from the output of this issuance of the command is the timestamp value that appears in the DSNB409I part of the output (e.g., "INCREMENTAL STATISTICS SINCE 10:00:48 MAR 19, 2012"). That's the "to" time for the interval of activity captured in the second issuance of the -DISPLAY BUFFERPOOL(ACTIVE) DETAIL command. The "from" time for this interval is the timestamp value found in the output of the second issuance of the command. Subtract the "from" timestamp value from the "to" value, and you have the number of seconds in the interval. Divide the read I/O figures in the output of the second issuance of the -DISPLAY BUFFERPOOL(ACTIVE) DETAIL command by this number of seconds, and you have per-second rates of activity.

Whatever the source of your numbers (DB2 monitor or DB2 command output), what you want, for each pool, is the TOTAL read I/O rate per second. That's the sum of synchronous read I/Os (random + sequential) and asynchronous read I/Os (sequential prefetch + list prefetch + dynamic prefetch). This rate is your guide to appropriate use of buffer page-fixing, and your measuring stick to gauge the impact of, for example, increasing VPSEQT for a work file-dedicated pool, or increasing the size of a high-read-I/O pool by adding buffers taken from a low-read-I/O pool. In part two of my three-part memory-for-MIPS blog entry, we'll revisit read I/O rates in the context of boosting the aggregate size of a buffer pool configuration. Look for that next entry to be posted in a week or two.

APAR PM29226 and DB2 for z/OS Native SQL Procedure Source Code Management

Sometimes an APAR (in IBM-ese, a description of a software functional enhancement or defect correction effected via application of a fix) is not what it -- at first -- seems. Take DB2 for z/OS APAR PM29226, for example. The opening sentence of the brief (just one paragraph) APAR text reads as follows: "This APAR provides sample job DSNTEJ67 which initiates the process of converting source for an external SQL procedure into source for a native SQL procedure." A mainframe DB2 person reading those words might understandably conclude that the APAR is not relevant to his environment if either a) his organization has not made use of external SQL procedure functionality (external SQL procedures being what stored procedures written in SQL had to be prior to the introduction of native SQL procedures with DB2 9 for z/OS), or b) external SQL procedures have been deployed, but converting those to native SQL procedures is not currently a priority. In other words, you might think that PM29226 is about external-to-native SQL procedure conversion, period.

Folks, I have news for you: REGARDLESS of whether or not your organization has developed external SQL procedures, if you are using -- or thinking about using -- native SQL procedures in your DB2 for z/OS system, you really ought to take a look at the changes wrought by PM29226. External-to-native SQL procedure conversion is NOT the primary raison d'etre of this APAR; rather, the process of converting an external SQL procedure to a native SQL procedure is used to ILLUSTRATE a more important aspect of PM29226 -- that being the introduction of a number of services intended to facilitate source code management (SCM) in a DB2 for z/OS SQL procedure context.

Some background here: native SQL procedures are a big deal -- for my money, easily the most important enhancement since stored procedure functionality was introduced in the mid-1990s with DB2 Version 4 (I've blogged a lot on the subject, starting with an entry posted to the blog I maintained while working as an independent DB2 consultant). Native SQL procedures are advantageous in a number of ways, but they also introduce some new issues regarding development and deployment. One of these has to do with source code management. Here's why: for years, SCM as it pertains to stored procedures was an external-to-DB2 matter because stored procedure programs were external-to-DB2 objects. People would develop stored procedure programs in languages such as COBOL and Java, and they would use their SCM tool of choice to manage the associated source code (even an external SQL procedure has an outside-of-DB2 source code component, as the procedure is turned into a C-language program with embedded SQL statements as part of the execution preparation process). Along came native SQL procedures, and for the first time (as one DBA I met put it), "there's only one piece." In other words, there's not the DB2 piece and its corresponding external-to-DB2 piece -- the latter being the COBOL or Java (or whatever) source module (or the load module if you're talking about the executable). Instead, for a native SQL procedure there's just the DB2 piece: the CREATE PROCEDURE statement is the source code, period (and the stored procedure's package is the executable, period). DB2 people (DBAs and developers of DB2-accessing programs) have been accustomed to dealing with programs, written in various languages, that contain SQL statements. When the SQL statement (in this case, CREATE PROCEDURE) is the program, that introduces a new wrinkle with respect to SCM.

As native SQL procedures are still somewhat new in the mainframe DB2 world (they are available in a DB2 9 new-function mode environment, or with DB2 10 in CM9 mode, or DB2 10 NFM for organizations migrating to DB2 10 from DB2 V8), leading SCM tools tend not to provide explicit support for the management of program source when the source language is SQL Procedure Language (aka SQL PL, the language used to create DB2 native SQL procedures). That situation will be addressed as more and more organizations utilize native SQL procedures and request support in this area from their SCM tool vendors. In the meantime, the fix for PM29226 (UK71501 for DB2 10, and UK71502 for DB2 9) provide a means of bridging the SQL PL SCM gap.

To specifics: as previously mentioned, PM29226 modifies DB2 sample job DSNTEJ67. With the application of the fix for the APAR, DSNTEJ67 makes available to you a number of services, in the form of sample REXX routines, that support important SCM tasks applicable to the development and use of native SQL procedures. These tasks include source extraction, source transformation, and source deployment. [The new services, initially packaged in a macro library, are much easier to read when they have been expanded into members of a PDS. Step three of the PM29226-modified DSNTEJ67 job does this, using a temporary PDS. You might want to expand the REXX services into your own PDS, outside of the DSNTEJ67 job stream.] Among the services provided by PM29226 are the following:

• SQLPLSRC extracts the SQL PL source of a procedure from the DB2 catalog. Two other services enable one to save extracted SQL PL source code to either a RECFM F data set (fixed-length records, one logical record per physical block on disk) or a RECFM V data set (one variable-length logical record, stored as one physical block). The choice of record format might depend on the type of input that is suitable for an existing SCM tool or process: in some cases that could be a file; in others, a string.
• CHKSQLPL inspects and validates SQL PL source code, using the precompiler with the HOST(SQLPL) option.
• SQLPLTRN transforms a procedure's SQL PL source -- changing, adding, or removing options (e.g., schema, QUALIFIER, SQL PATH, DEBUG MODE, etc.) as specified by a user.
• CRSQLPL deploys a SQL procedure, with the SQL PL input coming from a file or a string.

Check these services out, and see if they can be of use to you as an aid in performing source code management tasks for your DB2 native SQL procedures.

DB2 for z/OS: of Stored Procedures and the DB2 MQListener

As a mainframe DB2 guy, I'm bullish on stored procedures -- I've presented on the topic at international conferences and regional user group meetings, written about stored procedures for the old DB2 Magazine, and posted many a related entry to my DB2 blogs, both this blog (an example being an entry from August of last year) and the one I maintained while working as an independent DB2 consultant (included among these is part 1 of a 3-part entry on stored procedure usage from an application architecture perspective). I'm an advocate of DB2 stored procedure technology because I'm very big on the use of DB2 for z/OS as an enterprise data server for multi-tiered, client-server applications, and stored procedures have an important role to play there.

I'm also a proponent of asynchronous data processing, particularly as it pertains to DB2 data-changing operations (e.g., database inserts and updates). In this context, "asynchronous" means that the processing of a back-end data change is "unhooked," time-wise, from the client-side process that supplied the input to the data change operation (this input could come from an end-user clicking "submit" on a screen of a Web-based application). The means of accomplishing this "unhooking" of front-end input and back-end insert or update is often a message queue placed in-between the two processes. In many cases, that message queue will be managed by MQ. Asynchronous processing of DB2 data change operations can be attractive for several reasons, including better end-user response time (the data-input transaction is complete once the data is placed on the MQ queue) and improved application resiliency (if the back-end database is temporarily unavailable for some reason, input messages simply accumulate on the MQ queue, and are processed when the database is brought back online).

A handy piece of code called the DB2 MQListener enables the bringing together of these two favorite technologies of mine -- DB2 for z/OS stored procedures and asynchronous transaction processing -- and that's what this blog entry is about.

Here's the deal: if you want information in a message that an application process has placed on an MQ queue to be input to a DB2 INSERT or UPDATE (or, conceivably, a DELETE), something has to take that message off the queue and initiate the DB2 data change operation. That "something" can be the DB2 MQListener. It is provided with DB2 (starting, I believe, with DB2 for z/OS V8), and you can read about it in the DB2 for z/OS Application Programming and SQL Guide (this manual -- and other DB2 manuals -- are available in PDF and HTML form for DB2 Versions 8, 9, and 10 at http://www-01.ibm.com/support/docview.wss?rs=64&uid=swg27011656).

The DB2 MQListener runs as a daemon (a background process, in the parlance of Linux/UNIX) under the UNIX System Services component of z/OS. Conceptually, what it does is pretty simple: it calls a DB2 for z/OS stored procedure in response to a message arriving on an MQ queue. What stored procedure does it call? That's up to you. See, the DB2 MQListener is a multi-threaded process, and each thread is connected to one MQ queue. When a message arrives on a particular queue, the DB2 MQListener calls the stored procedure that is associated with the queue (this association between a queue and a stored procedure is established by way of MQListener commands, and the configuration information is stored in a DB2 table named SYSMQL.LISTENERS). If you want several different message types to be processed by different DB2 stored procedures (i.e., message type A drives an insert to DB2 table X, message type B is input to an update of table Y, etc.), set up several input queues, and associate with each the stored procedure that is to operate on a given message type. Then, have the input-providing application programs direct each message type to its designated queue.

What, exactly, is passed to a stored procedure by the DB2 MQListener when a message arrives on a queue? That's easy: the message itself -- all of it. Each arriving message generates one stored procedure call. A stored procedure will do with a message what you've programmed it to do. If you want to keep things really simple, you can have a stored procedure insert a message, in its entirety, into a column of a DB2 table. If the message contains several items of information and you want these various items to go into different columns of a table, you can write a stored procedure to do that. This would be pretty straightforward if the message parts were of a fixed and consistent length (e.g., positions 1 through 4 contain item A, positions 5 through 12 contain item B, etc.). Disassembling a message into its component parts would be a little more involved if the parts were of varying length, but this could certainly be done -- you just have to be able to find the end of one item in the message and the beginning of the next item (the parts of the message might be separated by commas, for example). Want to drive an update of information in a table, based on the content of a message? Write a stored procedure to do that. Have a maybe insert, maybe update situation? Let the message be input to a MERGE statement in a stored procedure. You get the picture: the DB2 MQListener is going to pass a message, in its entirety, to the stored procedure associated with an input queue. What the stored procedure does with that message is your call. [Note that, in addition to the one input parameter (a message taken from a queue), the DB2 MQListener will specify a single output parameter when calling a stored procedure. You can have your stored procedure assign a value to this output parameter, but you don't have to -- it can be NULL.]

Just as you decide what a stored procedure is to do with a message supplied as input by the DB2 MQListener, so, too, are you in control of the type of stored procedure that will be used with the MQListener. Want it to be an external stored procedure, perhaps one that's written in COBOL? That's fine. Want to go with a native SQL procedure (these were introduced with DB2 9 in new-function mode)? That's OK, too. The right choice is the one that make sense in your environment and provides the processing functionality that you need (though I will say that I'm a big proponent of native SQL procedures).

Herein I've provided just a brief overview of the DB2 MQListener and the ways in which you can use it. As I mentioned, you can get a lot more information from the DB2 for z/OS Application Programming and SQL Guide (including examples of MQListener set-up and operation and DB2 stored procedure coding). Check it out. DB2 and MQ are a great combination, and the DB2 MQListener can help you take asynchronous invocation of stored procedures from concept to reality.

Got LOBs? Get DB2 10 for z/OS (Part 2)

So, last week I posted a blog entry describing one of the two really important (in my opinion) DB2 10 for z/OS enhancements related to LOB (large object) data management -- that being the ability to "in-line" a portion (or even all) of a LOB column's data values in a base table space, alongside the associated table's non-LOB data values (this as opposed to having to store all of every LOB value in a LOB table space that -- while logically transparent -- is physically distinct from a table's base table space). This week, I'll cover the other of my two favorite DB2 10 LOB-related enhancements: support for the variable-blocked spanned record format for the SYSREC data set of LOAD and UNLOAD utility jobs. I'll also provide some information about other improvements in LOB data management delivered with DB2 10. [And before going further, I'll give props here to Jeffrey Berger, a member of IBM's DB2 for z/OS development team, who's helped me to build up my knowledge of LOBs.]

At first glance, being able to use the variable-blocked spanned (VBS) record format for the LOAD and UNLOAD SYSREC data set may not seem like a big deal. A little background, then: SYSREC is the default DD name for the data set into which data is unloaded from a table via the UNLOAD utility, and it's the default DD name for the data set that holds records that are loaded into a table space through the LOAD utility. Prior to DB2 10, the LOAD or UNLOAD SYSREC data set had to use the variable-blocked record format (RECFM=VB). That's fine in most cases, but often a problem when LOB data is involved. Why? Because the maximum record length for RECFM=VB is 32,760, and as we all know a LOB data value can exceed 32 KB in length. How, then, were LOB values handled with respect to UNLOAD output and LOAD input? Here's how: when a table with a LOB column is unloaded with the UNLOAD utility, the non-LOB data goes into the one SYSREC data set, but each individual LOB data value goes into a separate file (the same is true, in reverse, for the LOAD utility: individual LOB data values are loaded from separate files). The name of the file into which a LOB value is placed (for UNLOAD) or from which it is retrieved (for LOAD) goes into what's called a file reference variable, or FRV, and it's the FRV values that are found in the SYSREC data set, along with the non-LOB data processed by UNLOAD and LOAD.

With regard to the file type used for LOB data UNLOAD and LOAD in a pre-DB2 10 environment, you have choices, but these come with associated trade-offs. One option is to unload LOB data values into (or load LOB data from) members of a PDS or PDSE. The goodness in that alternative is the fact that most DB2 for z/OS people are very familiar with these data set types. The negatives are related to scalability (i.e., accommodating a large amount of LOB data). Specifically:

• A PDS or PDSE is limited to one disk volume.
• The size of a PDS is limited to 65,536 tracks.
• A PDSE can have no more than 524,236 members.

LOB data can also be unloaded to, or loaded from, HFS files (referring to a file system available via the UNIX System Services component of z/OS, also known as USS). This USS file system doesn't have the above-noted space limitations of PDS and PDSE data sets, and it delivers a performance advantage, to boot; however, a lot of mainframe DB2 people are not very familiar with HFS, so there can be a learning curve to deal with when this file system is used for DB2 LOB data. On top of that, for UNLOAD an FRV cannot refer to a sequential file (DSORG=PS), which is the type used for data sets on tape (this is true whether HFS files or members of a PDS or PDSE are used to hold unloaded LOB values). Bummer.

Enter DB2 10, and these limitations and hassles are removed because the variable-blocked spanned record format (RECFM=VBS) is supported for SYSREC data sets (use of such a data set is indicated via the new SPANNED option of the LOAD and UNLOAD utility control statements). Thanks to this enhancement,

• ...a table's LOB and non-LOB data can be unloaded to (or loaded from) the SAME data set! LOB values no longer have to be in separate files!
• ...the UNLOAD output file (or LOAD input file) can be on tape -- not just disk!
• ...the UNLOAD output file (or LOAD input file) can span multiple volumes (thus overcoming a PDS/PDSE restriction)!

Those exclamation points may come across as a little juvenile, but I really am psyched about this DB2 10 feature. Add to the benefits listed above a MAJOR performance boost, especially for smaller LOBs: an IBM test of an UNLOAD of 16,000-byte LOB values showed an 80% reduction in elapsed time when a VBS SYSREC data set was used versus HFS file reference variables, and a 99% elapsed time improvement for a VBS SYSREC data set as compared to PDSE FRVs.

Truly, if you are storing LOB values in a DB2 for z/OS database, or thinking of doing that, DB2 10 is the release for you. As if LOB in-lining (written about, as previously noted, in an entry I posted to this blog last week) and VBS SYSREC data sets weren't enough to convince you of that, consider these additional LOB-related goodies delivered with DB2 10:

• LOAD REPLACE of data in a table containing LOB values is substantially faster with DB2 10 than with prior DB2 releases, thanks to the use of a write operation called format write for LOB table spaces (LOAD previously used format writes only for non-LOB table spaces).
• INSERT of data from a DRDA client into a table with a LOB column can take considerably less time when LOB values are large, because DB2 10's distributed data facility (DDF) does not have to materialize the entire to-be-inserted LOB value before passing to to the database services address space (DDF will materialize up to 2 MB of an incoming LOB value before starting to pass the value to the database manager, and it will continue passing chunks of the value until it's all been received from the client). In addition to reducing elapsed time for network-driven inserts of larger LOBs, this feature can reduce CPU time and virtual storage consumption associated with such insert operations.
• DB2 10 supports online REORG of a LOB table space with SHRLEVEL CHANGE, and it will reclaim disk space (if less is needed, as would be the case if some LOB values had been remove via DELETE operations). Previously, SHRLEVEL CHANGE could not be specified for a REORG of a LOB table space, and the utility would not reclaim space occupied by the LOB table space.
• The new AUX YES option of REORG will cause the utility to reorganize LOB table spaces associated with partitions of a partitioned table space, as these base table space partitions are reorganized. Additionally, when a partitioned table space holding a table with LOB data is reorganized in a DB2 10 environment, REORG can move rows from one partition of the base table space to another -- something not done in prior releases of DB2. This functionality enables REBALANCE to be specified for a REORG of a range-partitioned table space holding a table that contains LOB data, and it also comes into play when a partition-by-growth table space associated with a LOB-containing table is reorganized (in that case, rows could be moved from one partition to another to reestablish the table's clustering sequence).
• DEFINE NO can be specified on the creation of a LOB table space, so that the table space's data set(s) will not be created until data is inserted into the table space (this option was previously ignored when specified for a LOB table space). DEFINE NO can be particularly useful when DB2 objects are being defined as part of the installation of a packaged software application that will not be immediately put to use.

All good stuff. Get to DB2 10 for z/OS (if you're not there already), and reap these LOB-related benefits. If you're not yet storing LOBs in DB2 tables, get to DB2 10 and re-think that situation. More than ever, it's LOB time in mainframe DB2 land.

Got LOBs? Get DB2 10 for z/OS (Part 1)

In my previous entry, I provided information pertaining to the amount of data that can be stored in a LOB column of a mainframe DB2 table (LOBs being DB2 data values that can exceed 32 KB in length). I made brief references in that entry to significant enhancements in LOB data management capabilities delivered via DB2 10 for z/OS. I'll describe those enhancements -- the most significant since LOB support was introduced with DB2 Version 6 -- in a multi-part entry, of which this is part 1. Herein I'll cover my favorite of the DB2 10 LOB features: LOB inlining. In part 2 (to be posted within the next week or so), I'll get to my second favorite item of DB2 10 LOB functionality, that being support for variable-blocked spanned records in the SYSREC data set used for LOAD and UNLOAD utility jobs (that may sound like a yawner, but it's a big deal -- trust me).

LOB inlining has a particular appeal for me because it provides in the DB2 for z/OS engine a capability that some colleagues of mine implemented in program code about eight years ago, when I was working in the IT department of a mainframe DB2-using company. Back then, we were planning for the deployment of a new application that would store e-mails and associated attachments (received from people who were consumers of our services) as LOBs in our DB2 database. [E-mails would be stored as CLOBs (character large objects), and attachments (which could be screen shots) as BLOBs (binary large objects).] We came up with a preliminary estimate of the disk space we'd need to hold the e-mail data, and it was rather alarming. The big storage requirement existed because of two factors. First, LOB table spaces can't be compressed as can base table spaces (prior to DB2 10, LOBs, while logically appearing to be stored alongside non-LOB data in a table, had to be physically stored in table spaces that were distinct from base table spaces). Second, while a LOB value can span multiple pages in a LOB table space, any one page in a LOB can hold only one LOB value (thus, if the last part of a LOB value occupies 1000 bytes of a 32 KB page in a LOB table space, the other 31,000 bytes of that page can't be used for another LOB value).

Fortunately, our DB2 DBA team came up with a clever idea that slashed our e-mail data storage space needs: a "switch" in the application code (akin to a switch on a train track) that sent e-mails with a length of 8 KB or less to a VARCHAR column in a table, and larger e-mails to a CLOB column in the same table (most e-mails we received did not have attachments, so we were fine with storing all attachments in a BLOB column). That 8 KB threshold was a "sweet spot" value for this particular application, in that the large majority of the e-mails we received back then could be stored in 8 KB or less. I'll return to this "sweet spot" notion later on.

Did the "switch on the track" solution increase application code complexity? Yes, but the disk space savings achieved justified the extra programming effort.

Along comes DB2 10 (running in new-function mode), and the coding of a switch on an application track is no longer needed for disk-efficient LOB storage. Why? Because DB2 takes care of that for you. How? By way of a feature called LOB inlining. This new capability allows you to specify, in a CREATE TABLE statement (or an ALTER TABLE), the portion of a LOB column value that is to be physically stored in the base table space along with the table's non-LOB data. Any part of a LOB value past the specified inline length is stored in a LOB table space.

Now, our coding of application-based LOB inlining functionality way back when was aimed at disk space savings, and DB2 10's inline LOB capability certainly gives you that (inlined LOB data in a base table space can be compressed, and a base table space page, unlike a LOB table space page, can hold more than one inlined LOB value), but storage efficiency isn't all you get: inlining LOBs can also boost -- sometimes dramatically -- the performance of application programs and utility jobs (e.g., LOAD) that process LOB data.

This potential for performance improvement doesn't come just from the fact that inlined LOB values can be retrieved from a base table (versus having to be accessed in a separate LOB table space). You could also get a nice program run-time reduction by inlining a portion of a LOB column and and building an index on an expression (a capability introduced with DB2 9 for z/OS) that would, for example, use the SUBSTR built-in scalar function to extract and index the characters in positions 10 through 20 at the beginning of each LOB value (that might be a contract number in a standard form). Think of the possibilities here.

To get the full benefit of LOB inlining, you need to make some smart decisions. Key among these is the decision on the amount of data in a LOB column that is to be stored in a table's base table space. You might think that this is just a matter of going with a 32 KB base table space page size and inlining the maximum amount of LOB data possible (that would be, for example, 31 KB of a row's LOB data if the row's non-LOB data took up 1000 bytes of space in a 32 KB base table space page). In fact, that would NOT be a good one-size-fits-all approach, because inlined LOB data will cause a base table space to get larger, and that will impact the read hit ratio in the buffer pool to which the base table space is assigned. Typically, the ideal, in terms of a LOB inline length specification, is what I mentioned before: a "sweet spot" threshold that's large enough to enable complete inlining of a majority of the values stored in a LOB column, and small enough to allow for the most effective use of the base table space's buffer pool resource. What would that value be for you? 7 KB? 12 KB? 22 KB? That depends on the characteristics of the LOB data with which you're dealing. If an inline length of 5K would result in the complete inlining of 55% of the values in a LOB column of a table, and a 7 KB inline length would get you to 80% with respect to complete inlining of LOB values, adding those extra 2000 bytes to the inline length specification for the table of interest would probably be a good move.

Speaking of LOB data characteristics, note that inlining is likely to have little, if any, beneficial effect on application and utility performance if most values in a LOB column can't be completely inlined (that is, if most values would be split across the base table space and the associated LOB table space). I will say, however, that in such a case it could be advantageous (performance-wise) to define an index on an expression built on the inlined portion of the values of a LOB column. Keep in mind, too, that even if a majority of the values that would go into a LOB column of a table could be completely inlined, programs might not run faster -- and, indeed, might even run a little slower -- if the LOB data is rarely retrieved (this because, as mentioned previously, inlined LOB data will make a base table space larger, perhaps negatively impacting the buffer pool hit ratio for programs accessing non-LOB data in the table).

Without a doubt, you should give serious thought to the use of DB2 10 inlining for your LOB data. If you're not yet storing LOB data in your DB2 for z/OS database, perhaps the inlining capability would make DB2 a good choice for LOB data storage in your enterprise. Remember, the key to optimal inlining is knowledge of your LOB data and of the applications (and utilities) that process that data.

Want more information about LOB inlining? Check out section 4.3, "Inline LOBs," in the IBM "red book" titled, "DB2 10 for z/OS Performance Topics" (downloadable from the Web -- see http://www.redbooks.ibm.com/abstracts/sg247942.html?Open).

Next up, within a week or so, part 2 of this multi-part entry, in which I'll focus on the support provided by DB2 10 for the variable-blocked spanned record format for the SYSREC data set of LOAD and UNLOAD utility jobs -- a great enhancement from a LOB data management perspective.