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.

A Note on the Data Capacity of a DB2 for z/OS LOB Column

Last week, a DBA asked me a few questions about LOBs (large objects) in a DB2 for z/OS context. Most of you probably know that a LOB is a DB2 data type. There are actually two LOB data types: BLOB (binary large object -- a string of bits) and CLOB (character large object -- a string of characters). One key difference between LOB and non-LOB data types is capacity with respect to the size of individual data values: a table column defined with the VARBINARY or VARCHAR attribute (these being the highest-capacity non-LOB data types) can accommodate individual values with a length of up to 32,704 bytes, whereas one can store up to 2 gigabytes of data in one LOB column of one row in a table; thus, a BLOB column might hold movies or high-resolution photographs or digital audio recordings, and a CLOB column might hold books or lengthy legal documents. Another distinguishing characteristic of LOB columns is their physical separation, storage-wise, from non-LOB columns and from other LOB columns (if any) that are part of the same table: a table's non-LOB data values will be stored in a "base" table space, while each LOB column will have its own LOB table space (and there will be one LOB table space per LOB column and per partition of the base table space, so that a table with two LOB columns in a base table space with four partitions will have eight associated LOB table spaces). Of course, application programs don't "see" table spaces -- they see tables, and to a DB2-accessing program it appears that LOB values are side-by-side with non-LOB values in a table's rows (in a DB2 10 for z/OS system running in new-function mode, a table's LOB values -- or a portion of each LOB value -- can in fact be physically adjacent to non-LOB values in the base table space thanks to a new feature called LOB in-lining, but that's a subject for another blog entry).

Anyway, the aforementioned DBA was looking for answers to these questions:

1. How much data can be stored in one LOB column?
2. What is the relationship between a base table space and a LOB table space?
3. How can one control the amount of disk space that will be occupied by data stored in a LOB column?

I provided the DBA with the information he was seeking, and, in the interest of further disseminating that information, I'm providing it in this blog post.


Maximum amount of data that can be stored in a single LOB column

The short answer: 66,584,576 gigabytes. That's slightly less than 64 petabytes, which would be 67,108,864 GB. It's a little less than 64 petabytes because a LOB table space can have a maximum of 254 data sets, and you'd need a 256-data set LOB table space to get to the precise 64 petabyte figure.

Here's how this maximum amount of data could be stored in a single LOB column:

• A base table space could have up to 4096 partitions.
• As previously mentioned, there will be a LOB table space for each partition of an associated base table space (actually, there is one LOB table space per partition per LOB column, but I'm assuming here that the table in question has a single LOB column); so, there would be 4096 LOB table spaces associated with a base table space with 4096 partitions.
• One LOB table space can be comprised of up to 254 data sets (as noted above), each of which can reach a size of up to 64 GB.

THEREFORE, the maximum amount of data that can be stored in one LOB column is:

(4096 base table space partitions)
X (1 LOB table space per partition for the column)
X (254 data sets per LOB table space)
X (64 GB per LOB table space data set)
= 66,584,576 GB


Relationship between a base table space and a LOB table space

First, it's important to keep in mind that a LOB table space is a unique DB2 for z/OS database object. I like the way that Jay Yothers, a longtime member of IBM's DB2 for z/OS development organization, put it (the acronyms PBG and PBR refer, respectively, to partition-by-growth and partition-by-range universal table spaces): "LOB table spaces are not PBG, PBR, classic partitioned, segmented, or simple. LOB table spaces are LOB table spaces."

Second, remember that the base table space / LOB table space relationship is all about the partitions in the base table space. So, if the base table space is non-partitioned or is partition-by-growth with MAXPARTITIONS 1, there will be one LOB table space associated with the base table space (assuming, as before, that the table in the base table space has one LOB column).

For a range-partitioned base table space, the number of associated LOB table spaces (per LOB column in the base table) will be equal to the NUMPARTS value for the base table space.

For a partition-by-growth base table space, if MAXPARTITIONS is greater than 1 and a new partition is automatically added to the table space by DB2, a new LOB table space for the partition (per LOB column in the base table) will be automatically created by DB2.


Controlling the space occupied by data in a LOB column

I pointed out that one LOB column can hold up to 66,584,576 GB of data. What if you don't want that much data to be stored in a LOB column? No problem -- you have several means of controlling the amount of space that will used to store LOB data in a table. Among these are the following:

• Limit the number of base table space partitions. The fewer the number of base table space partitions, the fewer the number of associated LOB table spaces.
• Limit the size of the data sets comprising the LOB table space(s). The DSSIZE specification for a LOB table space controls the size of the data sets that comprise the LOB table space. DSSIZE can be nG, where G represents gigabytes and n is 1 or a multiple of 2 between 2 and 64, inclusive.
• Limit the length of a value that can be placed in a LOB column. When a base table is created, a LOB column can be defined as (for example) BLOB(100K). In that case, no value larger than 100 KB could be inserted into the LOB column.

Another LOB space consumption control capability is LOB inlining, introduced (as I pointed out in the opening paragraph of this entry) with DB2 10 for z/OS. The significance of that and other LOB-related features of DB2 10 is such that they deserve coverage in a separate blog entry. I'll try to get that entry posted within the next couple of weeks.

One more thing: you can find a lot of very good information about LOBs in a DB2 9 environment in an IBM "red book" titled "LOBs with DB2 for z/OS: Stronger and Faster." It's available on IBM's Web site at http://www.redbooks.ibm.com/abstracts/sg247270.html?Open.

I hope that the information in this blog entry will be useful to you. Check back in a week or two for an overview of LOB-related enhancements delivered in DB2 10 for z/OS.

A MIN, a VIEW, a UNION ALL, and a DB2 SQLSTATE Mystery Solved

Last month, a DBA at an organization with which I work sent me a query that was getting a puzzling result. The query executed successfully in their DB2 for z/OS environment, generating the correct and desired result set; however, it was also getting an SQL warning code on execution. The returned SQLSTATE, 01003, indicated that "null values were eliminated from the argument of an aggregate function." Indeed, the query did contain, in a subquery predicate, an aggregate function (MIN). Here, though, was the puzzling part: the argument of the MIN was a column of a view (I'll refer to it as COL_X), and it didn't contain any NULL values. It COULDN'T contain any null values, as the view was defined on a UNION ALL of two very simple, single-table SELECT statements, and the column in question was defined as NOT NULL in both of the tables involved in the UNION ALL. Just to make sure, the DBA executed a SELECT COUNT(*) with a WHERE(COL_X) IS NULL predicate against the view, and got the expected result: 0. She even added the predicate AND COL_X IS NOT NULL to the subquery with the MIN and STILL got the SQLSTATE warning about null values being eliminated from the argument of the aggregate function.

How could it be that DB2 was telling us that null values were being eliminated from the argument of the MIN function, when that argument contained no null values? The DBA and I racked our brains over this, but could not come up with an answer. I turned for help to a friend in IBM's DB2 for z/OS development organization, and he got us onto the path toward resolution by asking one simple question: "Is it possible that one of the legs [i.e., one of the SELECT statements] of the UNION ALL returns no rows?" Here's why that matters: as it turns out, when the MIN aggregate function is applied to a UNION ALL, it will split into a MIN for each leg of the UNION ALL, and a "parent" MIN to generate the final result. If a leg of the UNION ALL generates an empty result set, the "split" MIN applied to that leg will return a null value (from the DB2 for z/OS SQL Reference: "If the [MIN] function is applied to an empty set, the result is the null value"). In that case, the "parent" MIN will have a null value to evaluate, and as "the function is applied to the set of values derived from the argument values by the elimination of null values" (again quoting from the SQL Reference, with emphasis added), that null value will be eliminated from consideration and the 01003 SQLSTATE code will be returned.

I posed to the DBA the question about the possibility of a leg of the UNION ALL view returning an empty set, and she found that, sure enough, given the predicates applied to the view in the subquery containing the MIN, one leg would in fact return no rows.

Mystery solved. Still, I was curious about the broader question: would the same SQLSTATE warning code be returned for other aggregate functions applied to a UNION ALL with an empty-set leg (and keep in mind: the 01003 SQLSTATE is FYI information -- not an indication of an error -- and the result set returned from the query generating this code is as it should be)? And what if the aggregate function were applied to a UNION DISTINCT with an empty-set leg (and DISTINCT, of course, is the default for UNION, and is in effect unless UNION ALL is specified)? On DB2 9 and DB2 10 systems, I checked this out and found that the 01003 SQLSTATE warning code (along with the correct result) was returned when MAX, AVG, SUM, STDDEV, and VARIANCE were applied to a UNION ALL with an empty-set leg. The 01003 was not received when the COUNT, COUNT_BIG, CORRELATION, and COVARIANCE aggregate functions were applied to the UNION ALL with the empty-set leg. When any of these aggregate functions were applied to a UNION DISTINCT view, the 01003 SQLSTATE warning code was not received (I did not test this with the XMLAGG aggregate function).

Note that even when applying an aggregate function such as MIN or AVG to a UNION ALL that has an empty-set leg, you might not see the 01003 SQLSTATE code, depending on the tool you're using to issue the query. That's because some tools, such as the DB2-supplied SPUFI, report warning as well as error SQLSTATE codes associated with statement execution, while some others, including some PC-based tools, report (by default) only error SQLSTATE codes, and as I mentioned previously, the 01003 SQLSTATE is FYI information, not error information. 

So, there you have it. Thanks to an inquiring DBA, I learned something new. I hope that you've learned something via this blog entry. Happy New Year!