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!

DB2 for z/OS: Driving Thread Reuse

I recently provided some assistance to an organization that was experiencing throughput issues in their CICS-DB2 online transaction environment. This company was migrating a large application from a non-IBM, non-relational DBMS to DB2 for z/OS, and the initial cut-over to DB2 placed a significant load on a mainframe system that didn't have a whole lot of spare cycles to begin with. The transaction slowdowns were most pronounced during periods of very high CPU utilization. In such situations, there are some mitigating actions that one can take if one has the time to see them through. For example, a business can add processing capacity to a CPU-constrained system, but the procurement cycle might be rather drawn out in the case of a large-scale, enterprise-class server. In the particular case I've described, application re-architecture work would likely yield major CPU savings, as phase one of the database migration project was not exploitative of the set-oriented nature of SQL (what we'd think of as a multi-row result set is presently being retrieved via a series of singleton SELECTs -- much more costly versus OPEN CURSOR/FETCH/FETCH/FETCH...). The application development leads at the company of which I'm writing are aware of this, but the SQL-exploiting phase of the application migration (as opposed to the "compatibility" phase) will take a while to implement.

In the meantime, there's this response time problem that needs attention now. What can be done quickly? A clue as to the appropriate performance tuning action came from side-by-side analysis of CICS and DB2 monitor information. The CICS monitor was reporting elevated wait-for-DB2 times during periods of degraded throughput, while at the same time the DB2 monitor was showing very good performance. Such a situation is often indicative of a problem in between CICS and DB2, and that is likely to have something to do with CICS transactions waiting to get the threads that are necessary for DB2 data access. The CICS monitor sees wait-for-thread time as wait-for-DB2 time, whereas a DB2 monitor doesn't "see" the CICS transaction until it gets a thread (and things might move along lickety-split once a thread is acquired -- thus the picture of good performance presented by the DB2 monitor).

Thankfully, for a problem of this nature there is a corrective action that both cuts down on DB2 thread create/terminate activity AND boosts CPU efficiency. It's called thread reuse. That's a relatively old concept, but it gets lost in the shuffle sometimes and isn't given much thought until a situation arises in which it's needed. Thread reuse has relevance in a transaction processing context, as it involves multiple transactions (i.e., multiple units of work, with the ratio of transactions to DB2 units of work typically being 1:1) accessing DB2 data, one after another, using the same DB2 thread; so, that thread which is reused persists across commits, as opposed to being terminated at end-of-transaction (the thread used by a batch job to access DB2 data is by its nature persistent until end-of-job: the batch job might commit ten thousand times in the course of its execution, but it will keep using the same thread).

When the DB2-accessing transactions are managed by CICS, there are basically two ways to drive up the rate of thread reuse. The first of these, which I don't much like, requires restricting the number of threads available between a CICS region and a DB2 subsystem. That restriction on the supply of threads is needed because this approach depends on transaction queuing to drive thread reuse. Here's what I mean by that: typically, when a CICS-DB2 transaction associated with DB2 plan ABC completes, the thread used for the execution of the transaction will be immediately terminated unless it is reused. The thread will be reused IF a transaction associated with the same plan is queued, waiting for a thread (that queued transaction will also have to be associated with the same CICS DB2ENTRY -- if any -- as the transaction that last used the thread). Wait-for-thread transaction queuing will only happen if the number of threads between the CICS region and the DB2 subsystem has reached its user-specified limit. In that case, new threads can't be created, so incoming transactions wait until in-use threads are freed up for reuse. [Note: I am using CICS-DB2 interface specification terms, such as DB2ENTRY, that are associated with CICS's Resource Definition Online (aka RDO) functionality. Some years ago, RDO replaced the old way of defining the interface between a CICS application-owning region (AOR) and a DB2 subsystem: a macro called the RCT (short for Resource Control Table). Information on DB2ENTRY, DB2CONN, and other DB2-related CICS resource definitions can be found in a manual called the CICS Transaction Server for z/OS DB2 Guide.]

Because I don't like to see CICS transactions queued up waiting for DB2 threads to come free, I favor an alternative means of promoting thread reuse: exploiting protected entry threads. I like this approach because it doesn't depend on forcing wait-for-thread transaction queuing. Unlike non-protected threads (those being pool threads or non-protected entry threads), a protected thread (a type of entry thread) will stick around for an average of 45 seconds following completion of the transaction that last used the thread (this time period is based on the setting of the CICS-DB2 thread purge cycle, which has a default value of 30 seconds -- a protected thread will be terminated if it is not reused within two of these purge cycles). If a transaction associated with the same DB2ENTRY and the same DB2 plan comes along in that 45-second period, the protected thread will be reused (keep in mind that several transactions can be defined for one DB2ENTRY by way of a wildcard character in the transaction name, and additional transactions can be associated with a DB2ENTRY via a DB2TRAN resource definition).

The approach to protected thread utilization that I recommend involves starting with the most frequently executed CICS-DB2 transactions. Set up a DB2ENTRY (or DB2ENTRYs) for these, with a THREADLIMIT value that is greater than zero and a PROTECTNUM value that is equal to the THREADLIMIT value (you could make PROTECTNUM smaller than THREADLIMIT, but I don't see much value in having entry threads that aren't protected). Also specify THREADWAIT(POOL) for the DB2ENTRY(s), so that transactions will overflow to the pool instead of waiting if the thread limit for the DB2ENTRY has been reached. Start with a moderate number of protected threads for an entry, and monitor the impact on thread reuse. If the thread reuse rate is not what you want it to be, increase the number of protected threads for one or more DB2ENTRYs. Note that in adding entry threads (whether protected or not) you may need to increase the value of TCBLIMIT for the CICS region's DB2CONN resource definition -- this to help ensure that you don't unintentionally hit the TCBLIMIT on the number of tasks using DB2 threads between the CICS region and the target DB2 subsystem (example: specifying 100 entry threads for a CICS region won't help you if the value of TCBLIMIT for the region is 50).

So, how do you monitor CICS-DB2 thread reuse in your DB2 environment? I like to use the accounting detail report generated by a DB2 monitor, with the data in the report grouped either by connection type (so that you'll see a report reflecting activity for your overall CICS-DB2 workload) or by connection ID (this would show activity at the CICS AOR level). [Be aware that, based on the particular DB2 monitor in use at your site, what I call an accounting detail report might be referred to as an accounting long report, and what I call data grouping (a SYSIN-specified option in the batch job that generates the report) may be termed data ordering.]

In the accounting detail report, look for a set of fields under the heading NORMAL TERM (or something like that -- headings and field names differ somewhat from one monitor to another). This is where thread reuse data is found. The field DEALLOCATION under the heading NORMAL TERM shows the number of times that a thread was deallocated (i.e., not reused). NEW USER indicates the number of times that a CICS-DB2 thread was reused with a different authorization ID. The RESIGNON field under the NORMAL TERM heading shows the number of times that a thread was reused without an authorization ID change (DB2CONN and DB2ENTRY resource definitions specify the DB2 authorization ID to be used for transactions, and this can optionally be set to a non-changing value, such as a character string or the ID of the CICS region). The rate of thread reuse is:

(NEW USER + RESIGNON) / (NEW USER + RESIGNON + DEALLOCATION)

At one DB2 for z/OS-using organization that I support, this rate is a little over 99%.

You can also use a DB2 monitor statistics detail report (your monitor might refer to this as a statistics long report) to check on the CPU consumption of the DB2 address spaces. A good bit of the work done by the DB2 system services address space (also known as MSTR) has to do with thread creation and termination. As your rate of thread reuse goes up, you might see MSTR CPU consumption go down (though this isn't a really big deal, as MSTR usually consumes only a small amount of a system's processing resource). Rising thread reuse also reduces the average CPU cost for CICS-DB2 transactions (this would be shown as average class 2 CPU time -- also known as in-DB2 CPU time -- per transaction in an accounting detail report of CICS-DB2 activity).

Want to really maximize the CPU savings achievable with greater reuse of CICS-DB2 threads? Bind DB2 packages executed frequently by thread-reusing transactions with RELEASE(DEALLOCATE). That will cause DB2 to retain table space-level locks (these are almost always IS or IX locks, which are not exclusive) and EDM pool entries needed for the execution of the packages until threads through which the packages are executed are deallocated, as opposed to releasing these resources at commit (i.e., at end of transaction) and then reacquiring them when the packages are executed again. The combination of thread reuse and RELEASE(DEALLOCATE), while good for CPU efficiency, will increase utilization of the part of the EDM pool in which package table (PT) sections go (a statistics detail report generated by your DB2 monitor might refer to this as the RDS pool, under the heading EDM POOL). Keep an eye on this, and if the number of free pages in the RDS part of the EDM pool drops below 10% of the total number of pages used for that part of the pool, add pages (you can up the value of the EDMPOOL parameter in ZPARM, and activate the change using the DB2 command -SET SYSPARM). DB2 10, by the way, moves PT sections above the 2 GB bar (for packages bound and rebound on the DB2 10 system), giving you much more room, virtual storage-wise, for enlarging the EDM pool.

Driving thread reuse is not just a CICS-DB2 thing. DB2 10 made it a DDF thing, too, thanks to high performance DBATs. I blogged about that DB2 10 enhancement a few months ago.

And what about that organization I mentioned at the start of this entry -- the one where CICS-DB2 transaction throughput degraded during times of very high CPU utilization? They set up some protected entry threads, and CICS-DB2 thread reuse went from zero to a much higher percentage, with attendant performance benefits. Look into this at your shop, if you're not already using protected entry threads. Your efforts could yield a nice performance payoff.

DB2 Data Sharing: What do you Know about Member Subsetting?

DB2 for z/OS data sharing is great technology. One of the things that makes it great is the ability of a data sharing system to automatically spread work across DB2 members so as to optimize load balancing, throughput, and availability. For this we can thank the z/OS workload manager (WLM), the DB2 group attach capability (for local-to-DB2 applications such as batch programs and utilities), and the Sysplex awareness built into DB2 Connect, the IBM Data Server Driver, and other DB2 clients (for remote data requesters).

Nice as it is to let the system spread work around the Parallel Sysplex, there are times when you'd like a particular workload to run on a subset of the members of the data sharing group. Here's a real-world example from a DB2-using organization that I support: the company has a business intelligence, or BI, workload (fairly complex data analysis queries) that targets tables also accessed by "run the business" OLTP and batch programs. To keep the BI queries from interfering with the highly performance-sensitive operational application programs, the organization restricted the analytic workload to a single member of their 6-way data sharing group. This workload isolation was easily implemented: the BI queries were issued by remote requesters, and the client systems connected to the one DB2 member to which the analytic SQL statements were restricted, versus connecting via the location name of the data sharing group (connecting to the group's location name causes requests to be spread across all of the group's DB2 members).

All well and good -- to a point. Yes, the high-volume online and batch programs with stringent performance requirements were insulated from the more dynamic BI application workload, but the analytics users were not protected from system outages as were the other users of the DB2 data sharing system. Here's what I mean by that statement: suppose that DBS1 (a made-up name) were the subsystem to which users of the BI application were required to connect. If that subsystem were to be unavailable for some reason, the BI users would get connection failures and might be out of luck for a while. In contrast to that situation, the operational application users (whether connecting locally via the DB2 group attach name, or remotely over the network using the group's location name) were assured of successful connections as long as any of the member DB2 subsystems were available (thanks, in the network-attached case, to the use of dynamic virtual IP addressing, which I briefly described in a blog entry from my independent DB2 consulting days, and about which you can read more in the IBM "red book" titled "DB2 9 for z/OS: Distributed Functions" -- most all of which is applicable to DB2 10, as well).

So, how to keep a given application workload from running on all members of a DB2 data sharing group without taking from that application's users the super-high-availability benefits conferred by DB2's shared-data technology? The solution to that challenge is something called member subsetting, and it was introduced -- for network-attached requesters -- with DB2 Version 8 (I'll cover member subsetting for locally-attached applications momentarily). Implementation of member subsetting for DRDA requesters involves creating one or more location aliases that map to subsets of the DB2 subsystems that comprise the data sharing group. For example, suppose that application ABC is to be restricted to members DBP1 and DBP2 of a 5-way data sharing group that has a location name of LOCDB2Y. In that case, a location alias associated only with members DBP1 and DBP2 could be created. Let's say that the location alias so defined is named LOCDB2X. If application ABC connects to location LOCDB2X, SQL statements issued through the application will execute only on subsystems DBP1 and DBP2 (there's your workload isolation); further, requests to connect to location alias LOCDB2X will succeed as long as one of the two associated subsystems is available -- so, if DBP1 is down due to a failure situation or a planned outage (perhaps a software maintenance upgrade), LOCDB2X connection requests will be handled by subsystem DBP2 (there's your high-availability set-up). Pretty sweet.

Setting up a location alias in a DB2 Version 8 or DB2 9 environment involves updating the communication record in the DB2 bootstrap data set (BSDS). That's done by executing the DB2 change log inventory utility (aka DSNJU003) with an input statement that would look something like this (referring to names I've used in my example, this statement would be executed via DSNJU003 on member DBP1 -- the statement for member DBP2 would look the same, but with a different RESPORT value):

DDF LOCATION=LOCDB2Y,PORT=1237,RESPORT=1238,ALIAS=LOCDB2X:8002

The location alias would have the same IP address as the group's location name. All members of the group listen on port 1237, but only members DBP1 and DBP2 would listen for requests directed to port 8002. Sysplex workload balancing for the subsystems to which location alias LOCDB2X works as it does for the overall group, except that the list of available subsystems returned to the DB2 client (e.g., the IBM Data Server or DB2 Connect), sorted in descending order of processing capacity as assessed by WLM, includes only DBP1 and DBP2.

Very good stuff, here, with one hitch: DSNJU003 can only be executed when DB2 is down. DB2 10 delivered an online means of defining a location alias via the new command -MODIFY DDF. Again using the names referred to previously, a location alias mapping to subsystem DBP1 would be dynamically created through the issuance of this command on DBP1 (the command would also be issued on DBP2 to map the alias to that subsystem):

-MODIFY DDF ALIAS(LOCDB2X) ADD

The -MODIFY DDF command would be executed again to specify a port number for LOCDB2X -- one option pertaining to a location alias can be specified per issuance of the command. Dynamically added location aliases are initially in a stopped state by default, so the last command issued to activate the fully-defined location alias would be (and again, this command would be issued on each subsystem associated with the alias):

-MODIFY DDF ALIAS(LOCDB2X) START

And, the story keeps getting better: DB2 10 provided a way to implement member subsetting for locally-attached applications (those being applications, such as batch jobs, that run on the same Parallel Sysplex as the DB2 data sharing members). This DB2 10 feature is called subgroup attach. A subgroup attach name for a DB2 10 subsystem can be specified via the SUBGRP ATTACH field of installation CLIST panel DSNTIPK. It can also be added by modifying the IEFSSNxx member of SYS1.PARMLIB and the IPLing the associated z/OS LPAR (details can be found in the DB2 10 Installation and Migration Guide, available online at http://www-01.ibm.com/support/docview.wss?uid=swg27019288#manuals).

So, if your organization has a DB2 data sharing group and you want to achieve both application workload isolation AND super-high availability for users of said application, implement member subsetting via a location alias or a subgroup attach. It's like having your cake and eating it, too.

IOD Dispatch (3) - Gleanings from the DB2 for z/OS "Coffee Track"

IBM's 2011 Information on Demand conference took place last week in Las Vegas. I attended a number of the event's technical sessions, and summarized what I heard in some of those sessions in a couple of entries posted to this blog. In this entry, my last focused on IOD 2011, I'll provide some of the more interesting items of information that I picked up in the "coffee track" -- that being the term often used to describe discussions that take place during coffee breaks and otherwise outside of the formal presentations. I don't know about you, but the "coffee track" has always been for me one of the most valuable aspects of conferences such as IOD. I really enjoy informal conversations with IBM DB2 developers and with professionals in the DB2 user community. Even when these talks are brief, they often result in my seeing a DB2 feature or function in a new light, and thus my understanding of DB2 technology is broadened. So, without further ado, here are some of the highlights from my "coffee track" time last week:

A lot of mainframe DB2 people still have a lot to learn about native SQL procedures. On Tuesday, I delivered a presentation on DB2 for z/OS stored procedures. During that hour I spent a lot of time talking about native SQL procedures, and some related questions that I got from a session attendee were illuminating. Native SQL procedures, by the way, were introduced with DB2 9 in new-function mode (I blogged about them a few years ago, when DB2 9 was new and I was working as an independent DB2 consultant). The functionality enables the development of stored procedures that are written entirely in SQL, and unlike external SQL procedures (introduced with DB2 Version 7), which are turned into C language programs that run in a stored procedure address space, native SQL procedures run in the DB2 database services address space and have no associated external-to-DB2 executable. Anyway, the aforementioned session attendee asked me about deployment of native SQL procedures from one DB2 environment to another (e.g., from test to production). My answer didn't quite hit the mark, and the question was asked again in a slightly different form. We went back and forth in this way for a while, until the session attendee finally asked, "With native SQL procedures, there's just one 'piece', right?" Right.

With the phrase "one piece," this person was contrasting native SQL procedures with traditional external stored procedures, which have two "pieces": an external-to-DB2 program written in a language such as COBOL or C or Java, and that program's DB2 package. In that application environment (one with which many mainframe DB2 people are very familiar), stored procedure deployment is mostly about deployment of the external-to-DB2 program, and the DB2 "piece" -- the package -- is dragged along in that process. In the case of COBOL stored procedures, for example, the external-to-DB2 executable exists in the form of a load module. Organizations have procedures, often involving the use of software tools, that are followed to get a load module deployed into a given system, and these procedures ensure that the associated DB2 package is also made available in the target system. But what if the only "piece" of a stored procedure is the DB2 package? If the DB2 package has previously been "along for the ride" as an external-to-DB2 program is deployed in, say, the production system, what's to be done when the package is in the driver's seat, so to speak, and the car (to continue the vehicular analogy) is otherwise empty (i.e., there's not a second "piece" to the stored procedure)? That is indeed something new for DB2 people, and dealing with that situation means learning about the new (with DB2 9) DEPLOY option of the BIND PACKAGE command, and about extensions to the ALTER PROCEDURE statement, such as the ACTIVATE VERSION option (useful for "backing out" a version of a native SQL procedure that is causing errors). So, if you're looking to take advantage of native SQL procedure functionality (and there can be multiple benefits of doing so), get familiar not only with development of these procedures, but with deployment, as well. I have some introductory information in an entry I posted a couple of years ago to the blog I maintained while working as an independent consultant, and you can find more detailed information in the DB2 Command Reference (for BIND PACKAGE) and the DB2 SQL Reference (for ALTER PROCEDURE) -- both the DB2 9 and the DB2 10 manuals are available online at IBM's Web site.

DB2 for z/OS for data warehousing -- when the application "cannot fail." I had a talk with a couple of IOD attendees from a large company. Their organization had recently spent months in developing go-forward plans for a mission-critical business intelligence application. The basic decision was this: leave the application's database on the mainframe DB2 platform, where it had been for some time, or move it to a non-mainframe, non-DB2 system. The company chose to stay with DB2 for z/OS, mainly for two reasons: 1) they are confident that DB2 on System z can scale to handle the big increase in database size and access activity expected over the coming years, and 2) they place a high value on the rock-solid reliability of the mainframe DB2 platform (as one of the people with whom I spoke put it: "The [decision support application] database cannot fail"). In expanding on that second point, these folks went beyond System z's and DB2's well-earned reputation for high availability, and talked up the excellence of their DB2 for z/OS support staff -- systems programmers and DBAs.

Sometimes, we take for granted the deep expertise possessed by a lot of companies' mainframe DB2 teams. DB2 for z/OS has been around for more than 25 years (IBM invented relational database technology), and many DB2 for z/OS professionals have two decades or more of experience in working with the product. They know how to configure DB2 systems and design DB2 databases for high availability, and they know what to do if a data recovery situation arises. On top of that, these teams tend to have have processes, developed over a number of years, around things such as change management, capacity planning, and performance monitoring and tuning, that are very robust and very effective. Combine a super-reliable hardware/software platform with a veteran and highly knowledgeable support staff, and you get a data-serving system that organizational leaders trust to deliver information as needed, whenever it's needed.

DB2 10 for z/OS: it's about time. There was a lot of buzz at IOD 2011 about the temporal data support provided by DB2 10 for z/OS, and with good reason: this is functionality that would be a bear to provide via your own application code (something I know from first-hand experience), and even if you went that route you couldn't match the performance of the built-in DB2 capability.

There are two flavors of temporal data support available in a DB2 10 new-function mode environment (and they can be employed separately or together for a given table): system time (this has to do with automatic saving of "before" images of rows targeted by UPDATE and DELETE operations in a history table, with information indicating the timestamps between which a row was in the "current" versus the "history" state) and business time (this enables, among other things, a "future insert" capability, whereby a row with, for example, next year's price for a product can be added to a table without affecting programs querying "currently valid" rows).

I appreciated the value of system time temporal data support as soon as I learned of it, having done, in the mid-1990s, some consulting work for an insurance company that had written "row change history" logic into their application code -- this to enable determination of a policy holder's coverage at the time that an automobile accident (for example) occurred. With regard to business time, however, I'll admit that I initially had some trouble envisioning use cases. I heard of an interesting one at the conference last week: a company is utilizing DB2 10 business time to enable analysts to do future forecasting of profit margins. Very cool. I expect that as time goes by, organizations will come up with new and innovative ways to derive value from the system time and business time capabilities of DB2 10.

I had a great time last week, and I'm already looking forward to IOD 2012 -- hope to see you there.