Robert's DB2 blog

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

2012-02-11T10:52:00.000-08:00

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)

2012-01-31T00:48:00.000-08:00

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

2012-01-19T07:21:00.000-08:00

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:

How much data can be stored in one LOB column?
What is the relationship between a base table space and a LOB table space?
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

2012-01-04T15:22:00.000-08:00

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

2011-12-13T20:56:00.000-08:00

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?

2011-11-23T08:32:00.000-08:00

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"

2011-11-02T18:03:00.000-07:00

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.

IOD Dispatch (2) - DB2 9 and 10 for z/OS SQL, and DB2 Connect Set-Up

2011-10-28T00:52:00.000-07:00

In this, my second dispatch from the 2011 IBM Information on Demand conference, more items of information from sessions attended.

SQL enhancements delivered in DB2 for z/OS Versions 9 and 10 - This presentation was given by Chris Crone, a Distinguished Engineer with the DB2 for z/OS development organization at IBM's Silicon Valley Lab. Among the DB2 9 SQL enhancements covered by Chris in the session were the following:

INTERSECT and EXCEPT - Given two query result sets that line up in terms of number of columns and data types of columns (i.e., the data type of the nth column of result set 1 is compatible with the data type of the nth column of result set 2), the INTERSECT set operator makes it easy to write a SELECT statement that will return only those rows that appear in both result set 1 and result set 2. EXCEPT is the ticket when you want the SELECT statement to return rows from result set 1 that do not appear in result set 2 (or vice versa).

INSTEAD OF triggers - I liked Chris's succinct description of this DB2 9 enhancement: "These are triggers on views." Basically, an INSTEAD OF trigger can be used to enable the updating of data through a view, when the data-change operation in question (INSERT, UPDATE, or DELETE) would be technically impossible or practically unfeasible in the absence of the INSTEAD OF trigger. For example, an INSTEAD OF UPDATE trigger could be used to change an employee's last name through an UPDATE targeting a view that joins the EMPLOYEE table to the DEPARTMENT table (this to bring in from the DEPARTMENT table the name of a department referenced only by department number in the EMPLOYEE table). Such a view is read-only by definition, but the INSTEAD OF trigger enables the UPDATE targeting the view to execute successfully, by changing the action to an update of the EMPLOYEE table.

MERGE - Using this statement, one can change data in a target table based on a comparison of target table rows with "rows" in a virtual "input table" ("input table" content is provided via array variables -- one for each "column" of the "input table"). When an "input table" row matches one or more rows in the target table (per matching criteria specified in the MERGE statement), the target table row (or rows) is updated with values in the matching "input table" row. When there is no target table match for an "input table" row, that row is inserted into the target table.

SELECT FROM UPDATE (and FROM DELETE and FROM MERGE) - This enhancement rounds out the SELECT FROM INSERT capability introduced with DB2 Version 8. The same concept applies: with one statement (versus separate SELECT and data-change statements), change data in a table and retrieve information about the data change operation. Chris showed how an INCLUDE column (a column, defined and assigned values via a SELECT FROM UPDATE/DELETE/MERGE statement) could be used to determine whether rows in the "input table" of a MERGE statement (see my description of MERGE, above) were inserted into the target table or were used to update target table rows.

TRUNCATE - This is a statement that can be used to clear data out of a table. Why use TRUNCATE versus a mass delete (that being a DELETE statement with no WHERE clause)? Here's one reason: TRUNCATE can be used to empty a table without causing delete triggers to fire (if that's what you want).

New OLAP functions - These functions -- RANK, DENSE_RANK, and ROW_NUMBER -- make it easy for a person to code a SELECT statement that will a) sort a query result set, in ascending or descending sequence, according to a user-specified single- or multi-column value (and this sort takes place BEFORE the sort for an ORDER BY clause that might appear at the "end" of the query), and b) assign an integer value to each row in this sorted result set, starting with 1 and counting up (as Chris put it, one can think of the OLAP specifications "as counting functions that count in different ways." The difference referred to in Chris's statement has to do with how "ties" (referring to the OLAP specification sort key value) are handled: ROW_NUMBER assigns an integer value of n to the nth row in the sorted result set, regardless of whether or not the sort key value in that row is the same as that of the previous row in the sorted set (in other words, "tied" rows get different ROW_NUMBER values, so if the result set is sorted in descending SALES order, and if rows four and five in the set have the same SALES value, the rows will get ROW_NUMBER values of 4 an 5, respectively). RANK and DENSE_RANK assign equal integer values to "tied" rows, but differ in how a row after a set of "tied" rows is numbered: RANK will skip integer values so that next row after a set of "tied" rows will be assigned a RANK value of n if it is the nth row in the result set. DENSE_RANK, on the other hand, will assign a rank of n+1 to the first row after a set of "tied" rows if those "tied" rows were assigned a rank of n. So, if rows three, four, and five in a set sorted by SALES have the same SALES value, they will each get a rank value of 3, whether the OLAP specification is RANK or DENSE_RANK. The next row in the set (row six) will get a rank value of 6 if the RANK specification is used, and a rank value of 4 if DENSE_RANK is specified (i.e., there are no "gaps" in rank values used if DENSE_RANK is specified).

In the second part of his presentation, Chris described SQL enhancements delivered in DB2 10 for z/OS, including the following:

Richer OLAP specifications: moving aggregates (e.g., moving sum and moving average) - These specifications enable one to code a SELECT that will partition a result set by a user-specified single- or multi-column value, sort within result set partitions by a user-specified single- or multi-column value, and then aggregate column values within partitions in a "moving window" fashion. For example, SALES values in rows in a partition might be averaged in this way: take a row's SALES value and average it with the SALES values of the two preceding rows in the partition. Row 1 in a partition has no preceding rows, so its "moving average" value would be the SALES value in the row. The "moving average" value for row 2 in a partition would be the average of the row 2 and row 1 SALES values (there is only one preceding row in that case). The "moving average" values for rows 3 through n in a partition will be the average of a row's SALES value and the SALES values of the two preceding rows in the partition. You could also generate other types of "moving" aggregates, such as moving counts and moving sums.

TIMESTAMP enhancements - A DB2 10 timestamp value can have up to 12 digits of precision for the fractional second part of the value (that gets you to the picosecond level -- trillionths of a second). Additionally, the precision of the fractional second part of a timestamp value can be user-specified, with the range of the user-specified precision being 0 to 12 digits (6 digits is the default).

TIMESTAMP WITH TIME ZONE - This new optional specification for the TIMESTAMP data type allows a time zone value to be stored with a timestamp value (the time zone value is an offset from UTC, formerly known as Greenwich Mean Time). To go along with the new WITH TIME ZONE option for the TIMESTAMP data type, there are new special registers (e.g., SESSION TIME ZONE), and the built-in date/time functions (e.g., EXTRACT) have been extended to support TIME ZONE values.

Implicit data type casting - In a DB2 10 system, you can concatenate a character string value with a numeric value without having to first explicitly cast the numeric value as a character string -- that casting will be done for you by DB2. Similarly, DB2 10 will let you provide the character string representation of a numeric value as the argument for a built-in function such as FLOOR -- that character string will be implicitly cast as a numeric value by DB2.

Extended indicator variables - Use of extended indicator variables is limited to host variables used to input data to DB2 (i.e., they can be used with host variables specified in INSERT, UPDATE, and MERGE statements); furthermore, extended input variables have to be enabled via the package bind option EXTENDEDINDICATOR(YES) or via the WITH EXTENDED INDICATORS option of PREPARE. An extended indicator value of -5 means that the target column for the associated host variable is to be set to its default value. An extended indicator value of -7 means that the target column for the associated host variable is to be treated as if it had not been specified in the statement.

Row permissions and column masks - Defined via CREATE PERMISSION and CREATE MASK, respectively, these are essentially table-level rules that control access to data rows and columns. Row permissions and column masks are better than views when it comes to establishing security as it pertains to row and column access, because they become part of the associated table's definition.

Cursors declared WITH RETURN TO CLIENT - When a cursor is declared in a stored procedure with this new option, the result set of that cursor can be retrieved by the "top-level" program that initiated a set of nested stored procedure calls, even if the stored procedure in which the cursor is declared is several levels down from the top-level program (WITH RETURN TO CALLER, formerly the only form of WITH RETURN for a cursor declaration, allows a result set to be retrieved by a program that is "one level up" from the stored procedure in which the cursor is declared, referring to levels of nested stored procedure calls). In talking about the WITH RETURN TO CLIENT option of DECLARE CURSOR, Chris noted that DB2 10 supports nested stored procedure calls up to 64 levels deep (versus a limit of 16 levels in prior releases of DB2).

Optimizing DB2 Connect deployments - This session was delivered by Brent Gross, a member of the DB2 for Linux, UNIX, and Windows development team at IBM's Toronto Lab. Brent noted, among other things, that with respect to DB2 clients, you want to go with "the lightest, smallest package possible." He recommends the Data Server Driver Package (aka the DS Driver) in most cases.

Brent also mentioned that a direct DB2 client to DB2 for z/OS server connection is preferred, in most cases, to a set-up that has DB2 clients accessing DB2 for z/OS through a DB2 Connect gateway. The main benefits of direct-to-DB2 connections are 1) simplified network topology and 2) improved performance. Regarding that second point, Brent said that elapsed time improvements of 15-30% have been observed when direct connection of clients to DB2 for z/OS has replaced a set-up that had client-to-DB2 connections going through a DB2 Connect gateway (OLTP workloads see the greatest performance gains).

Brent pointed out that there are some situations in which client connections to a DB2 for z/OS server have to go through a DB2 Connect gateway:

When a client-side transaction manager is using two-phase commit implemented through a "dual transport" model (Tuxedo and Encina are two transaction managers that implement two-phase commit in this way).
If homogeneous federation is to be used (this refers to the ability to create nicknames across DB2 and Informix servers).
When DB2 Connect Enterprise Edition is licensed with the "concurrent users" option.

Brent explained that direct-to-DB2 versus DB2 Connect gateway is a configuration issue, and that you need to be licensed for DB2 Connect in either case. He noted that DB2 Connect Unlimited Edition licensing makes life easier for systems support people because license key information is maintained at the DB2 for z/OS end of things (in other words, the DB2 Connect entitlement check is performed at the mainframe DB2 host, so license keys don't have to be stored at the DB2 client end).

Also in support of the general recommendation that DB2 clients connect directly to a DB2 for z/OS server, Brent pointed out that this approach carries with it no penalties with regard to functionality versus the DB2 Connect gateway alternative (for example, sysplex workload balancing, available for years for Java applications directly connected to DB2, was made available for .NET and ODBC and CLI clients with version 9.5 Fix Pack 3 of the DB2 client code).

For organizations looking to migrate from a DB2 Connect gateway to a direct client-to-DB2 set-up, Brent recommended starting with application servers (versus "fat client" workstations).

A few other items of information provided by Brent during the session:

DB2 10 for z/OS enables maximum connection, thread, and timeout values to be specified at the application server level via server profiles.
Full support of DB2 10 functionality for DRDA requesters requires the use of Version 9.7 Fix Pack 3a (or above) of DB2 client code.
For maximum availability in a DB2 for z/OS data sharing environment, dynamic virtual IP addresses (DVIPAs) should be assigned to DB2 member subsystems, and clients should connect to the data sharing group using the distributed DVIPA that is assigned to the Sysplex Distributor component of z/OS.

Signing off now. I'll post one more IOD 2011 entry within a day or two (and I mean within a BUSINESS day or two -- gotta chill over the weekend).

IOD Dispatch (1) - Of IDAA and DB2 DRDA

2011-10-25T01:20:00.000-07:00

Greetings from Las Vegas, Nevada, site of the 2011 IBM Information on Demand conference (aka IOD). This is the first of several entries I'll post this week to my blog, with highlights from some of the DB2 sessions I've attended. Today I want to share with you good stuff that I picked up from two excellent presentations: one, delivered yesterday to a group of IBMers, on the new IBM DB2 Analytics Accelerator, and one about DRDA best practices in a DB2 for z/OS environment.

The IBM DB2 Analytics Accelerator: wow-level performance for complex, data-intensive business intelligence queries. Boy, did this session ever grab my attention. Big Blue recently announced a new product, the IBM DB2 Analytics Accelerator (IDAA), that is already delivering, at beta sites, eye-popping response times for some really hairy data warehouse queries. The IDAA is, in essence, a deep integration of DB2 for z/OS and IBM's Netezza data analytics server. Here's how it works: a user or application directs a report-generating or decision support query to a DB2 for z/OS system. DB2 then does what it always does -- it optimizes the query; BUT -- and this is the really cool part -- the optimizer has, in the form of the IDAA, a new access path that it can select (if the IDAA is added to an existing DB2 system, the extended optimizer functionality is added by way of a DB2 PTF). Here's the deal: some or all of the tables in the DB2-managed data warehouse are copied over to the IDAA, and if a query targets one (or more) of those tables, the DB2 optimizer can analyze the query and send it to the IDAA for processing if it determines that this would be the best-performing option for result set generation; otherwise, DB2 will execute the query locally (the IDAA is attached to the DB2 for z/OS system via a 10 gigabit Ethernet connection).

Here's why I like this solution a lot:

The performance results can be pretty astounding (more on this in a moment).
In an environment in which a DB2 for z/OS database has been serving business intelligence query requests, the IDAA is invisible to end users and applications -- they continue to direct queries to the DB2 system. DB2 makes the query routing decision, so users and application programmers don't have to.
The database is updated, backed up, and secured on the DB2 end of things (data on the IDAA can ONLY be accessed via the attached DB2 system). Those are things that seasoned DB2 for z/OS DBAs do very well, and full advantage is taken of the industry-leading security and availability benefits delivered by the mainframe DB2 platform.
It's a best-of-both-worlds solution for modern data warehouse applications that are characterized by high-volume OLTP-like queries (served efficiently by the DB2 system) AND complex, data-intensive SQL requests (processed very effectively by the IDAA).
The time from "un-boxing" of the IDAA to ready-for-use is very short. No database schema changes are required (the IDAA can deliver outstanding complex query performance for traditional relational database designs and for star schemas), and IDAA set-up is accomplished via an intuitive graphical interface.

To that last point in the list above: the IDAA administration interface is basically an extension of the IBM Data Studio tool (which is FREE and DOWNLOADABLE, folks). If Data Studio is not already installed on a PC that will be used for IDAA administration, the extended edition with IDAA support can be installed. If Data Studio is already there, a plug-in will provide the IDAA administration functionality. DBA-requested IDAA administration tasks are performed via DB2 stored procedures that are supplied with the solution, including one that copies data to the IDAA (and subsequently updates that data) by invoking the DB2 UNLOAD and LOAD utilities (both the REPLACE and RESUME options of LOAD are supported).

And about performance: I've mentioned several times that the IDAA can deliver huge improvements in response time for queries that are highly complex and/or which require very large-scale data scans when executed. Words are fine, you may be thinking. How about some numbers? Well, we heard from an IT manager with an organization (a big insurance company) that was a beta test site for the IDAA. To quote this gentleman's understated assessment, test results "were impressive." How impressive? How about 4566 seconds for execution of a certain query before IDAA implementation, and 3.7 seconds after. How about 9558 seconds for another query before, and 5 seconds after. We're talking orders of magnitude improvement in elapsed time. More from the beta testing organization:

The data warehouse used for IDAA testing is big, with one table having more than a billion rows and some 200 tables having more than 500 million rows apiece.
High throughput was seen for IDAA data load operations, with 590 million rows loaded in 9.5 minutes, and 1.5 billion rows loaded in 15 minutes.
The IDAA was attached to a DB2 for z/OS data sharing group (attachment to a standalone DB2 subsystem is, of course, supported as well).

I encourage you to check out the IDAA. In particular, if you're already doing data warehousing with DB2 on System z, let this accelerator burn some rubber in your shop.

DRDA best practices for DB2 for z/OS -- This session was delivered by Christian Molaro, a Belgium-based DB2 professional whose expertise has been recognized by Big Blue: Christian is an IBM Gold Consultant and an IBM Champion for Information Management. In his presentation, Christian made a number of very sound recommendations related to the processing of client-server workloads in DB2 a for z/OS environment (DRDA, or Distributed Relational Database Architecture, is the protocol used by DB2 servers and requesters for distributed database access). Among these recommendations were the following:

Keep your DB2 client code current. In working with his clients, Christian has encountered several situations in which DB2 distributed database access problems were traced to down-level DB2 client code. He recommended that client applications utilize -- at least -- the version 9.5, fix pack 3 level of DB2 client code, and pointed out that the 9.7, fix pack 3a level of client code is required for full support of DB2 for z/OS Version 10 functionality.
Choose the right client package. IBM provides a number of DB2 drivers and client packages (see http://www-01.ibm.com/support/docview.wss?uid=swg21385217). Christian recommended going with the package that has the smallest footprint while delivering the functionality required by the client application.
Using an IBM driver to go straight from the client application server to DB2 (versus going through a DB2 Connect gateway server) is usually the right choice -- but not always. The no-gateway approach generally delivers optimal performance and simplifies the application infrastructure. On the other hand, using a DB2 Connect gateway server can be a good approach if the environment is characterized by a large number of "fat" clients, as opposed to a relatively small number of application servers that might front numerous upstream end user workstations.
Speaking of DB2 Connect, this can be a very good initial workload for hosting on a Linux image on a System z server. A performance advantage can be realized through the use of HiperSockets for communication with a DB2 subsystem in a z/OS LPAR on the same mainframe server. On top of that, when folks see that Linux on System z provides a great environment for a DB2 Connect gateway, they might be inclined to follow up with the implementation of an application server (such as WebSphere) under Linux on z.
DB2 10 for z/OS does not support private protocol, so make sure that your programs aren't using it before you migrate to DB2 10. APAR PK64045 provides useful information in this area.
For most efficient use of mainframe memory and cycles, ensure that DB2 support for inactive threads is enabled. This is done via the CMTSTAT parameter of DSNZPARM.
Location aliases are a great way to restrict execution of certain client-server workloads to a subset of the members of a DB2 data sharing group. DB2 10 delivered a nice enhancement here, enabling dynamic addition, deletion, and modification of location aliases via the -MODIFY DDF command (with prior releases of DB2, location alias changes required execution of the change log inventory utility (aka DSNJU003), and that utility can can be executed when DB2 is down.
High-performance DBATs, a DB2 10 enhancement enabled through binding of DRDA-using packages with RELEASE(DEALLOCATE), can deliver significant improvements in CPU efficiency for client-server workloads. I blogged on this very topic earlier this year.

That's all for now. More to come tomorrow.

DB2 for z/OS and SMS: a DB2 10 Migration Step That You Can Take Ahead of Time

2011-10-21T12:17:00.000-07:00

Those of you looking to migrate your DB2 for z/OS Version 8 or Version 9 systems to DB2 10 may be aware of the fact that the DB2 catalog and directory data sets must be SMS-managed in a DB2 10 environment, and that SMS must be set up to manage these data sets before you actually perform the migration process (SMS, of course, refers to the Storage Management Subsystem component of the z/OS operating system).

At some sites, the DB2 catalog and directory data sets are already SMS-managed (DB2 9 provided a USE SMS option on panel DSNTIPA2 of the DB2 install CLIST, which if set to YES would cause DB2 catalog and directory data sets to be allocated on SMS-managed storage). For folks with DB2 catalog and directory data sets that are not SMS-managed, IBM provides a job, called DSNTIJSS, that can be used to create a stand-alone SMS environment for the DB2 catalog and directory (that's for sites that do not currently have an SMS environment -- DSNTIJSS can also be used as a guide for adapting an existing SMS environment to achieve SMS management of the DB2 catalog and directory data sets). DSNTIJSS ships with DB2 10 -- it's a member of the SDSNSAMP library.

Suppose you want to use DSNTIJSS to help you get set up for SMS management of the DB2 catalog and directory data sets, but your organization has not yet ordered DB2 10? The good news for you is that this job is available on the IBM developerWorks Web site. developerWorks, by the way, is a good site for you to get to know anyway. There is a lot of great stuff there, and contrary to what you might assume because of the site's name, the content is NOT limited to items that would only be of interest to developers -- DB2 DBAs and systems people will find many useful papers, files, etc.

Here's how you can get the DSNTIJSS job from developerWorks:

Go to the developerWorks home page (http://www.ibm.com/developerworks/).
Click on the "Community" link at the top of the home page. This will cause a box to open just below the top of the page. In that box, click on the "Files" link.
On the "Welcome to Files" page that is subsequently displayed, you'll see a search box on the left-hand side of the page, under the heading "Public tags." In that box, enter the search term dsntijss and click on the adjacent magnifying glass icon.
You'll then get a page containing a link to the file dsntijss.copy. Click on that link, and on the displayed page click on the "downloading this file" link to download a copy of the file.

That's it! Note that dsntijss.copy is a text file that can be opened using an editor such as Notepad.

A few more items of information for you:

A good source for more information about DSNTIJSS is the DB2 10 Installation and Migration Guide. You can get a copy of this manual in PDF form by going to the DB2 10 for z/OS manuals page on the Web (if you'd rather access this manual's content in the DB2 10 Information Center, just click on the HTML link next to the manual's title in the list of manuals).
There is also useful information about DSNTIJSS in the IBM "red book" titled "DB2 10 for z/OS Technical Overview" (see section 12.9, "SMS-managed DB2 catalog").
Although getting set up for SMS management of the DB2 catalog and directory data sets is required prior to migrating a subsystem to DB2 10, it is not necessary to convert existing non-SMS-managed catalog and directory data sets to the SMS environment -- any such data sets not changed to SMS management during the DB2 10 migration process will become SMS-managed the next time the associated table space is reorganized.

DB2 10 has a whole lot to offer to organizations now using DB2 Version 8 or Version 9. The sooner you get to DB2 10, the sooner your company can benefit from numerous functionality, performance, and high-availability enhancements. I hope that the information in this blog entry will provide a boost to your DB2 10 migration efforts.

Even a "One-Box" DB2 Data Sharing Group Can Boost Availability

2011-10-11T20:57:00.000-07:00

Back in the mid-1990s, when organizations were first implementing DB2 for z/OS data sharing groups on Parallel Sysplex mainframe clusters, there were ALWAYS multiple server "boxes" involved. For one thing, the only type of coupling facility (CF) available was the external type -- a box of its own. And, you'd have two of those, to allow for things like lock structure rebuild in CF2 in case of the failure of, or loss of connectivity to, CF1. So, that's two boxes already, and we haven't gotten to the mainframe servers yet. You'd almost certainly have at least two of these, as well, because the mainframes that IBM was selling at the time -- the first generation built with CMOS microprocessors versus bipolar chip sets -- maxed out at 50 MIPS of processing power (5 MIPS per engine -- I'm not kidding -- with up to 10 engines per box). Parallel Sysplexes with four or five or more boxes -- coupling facilities and mainframe servers -- were the norm.

Not too many years later, the number of boxes in Parallel Sysplex / DB2 data sharing configurations started going down. Two key factors were at play here. First, IBM came out with internal coupling facilities (ICFs) -- basically allowing an organization to dedicate one or more mainframe engines and some mainframe memory to one or more LPARs that would run Coupling Facility Control Code instead of z/OS. That reduced Parallel Sysplex server "footprints," and saved companies some money, to boot (internal CFs are less expensive than their external cousins). The other development that had a reducing effect on the number of boxes found in the typical Parallel Sysplex was the introduction of ever-more-powerful mainframe servers -- machines featuring more engines and more MIPS per processor. Organizations found that they no longer needed five or eight or ten System z servers to get whopping huge amounts of processing capacity. The current flagship of the System z line, the z196, can have up to 80 general purpose processors with an aggregate capacity of more than 52,000 MIPS in ONE footprint (up to 16 additional processors can be configured as specialty engines, examples of which include internal coupling facility engines and zIIPs). Lash just a couple of these bad boys together in a 'Plex, and you've got one very highly scalable computing platform.

With all this about internal coupling facilities and super-powerful mainframes said, plenty of organizations want a minimum of three boxes in a Parallel Sysplex / DB2 data sharing configuration. A three-box set-up does indeed provide the ultimate in high availability, as it enables you to avoid a scenario in which the failure of one box results in the simultaneous loss of 1) a DB2 data sharing member and 2) the coupling facility lock structure and/or the coupling facility shared communications area (that particular simultaneous loss would cause a data sharing group failure which would then require a group restart). This scenario, sometimes called the "double failure" scenario, can also be avoided in a two-box Parallel Sysplex if the coupling facility lock structure and the coupling facility shared communications area are duplexed. I wrote a good bit about the "double failure" scenario and the pros and cons of lock structure and SCA duplexing in an entry posted last year to the blog I maintained while working as an independent DB2 consultant.

Ultimate DB2 data sharing availability, then, is delivered by a multi-box Parallel Sysplex. Now, suppose your organization runs DB2 for z/OS on one System z server, and suppose that single-box situation is unlikely to change anytime soon. Could your company still realize value from the implementation of a DB2 data sharing group, even if that group were to run on a one-box Parallel Sysplex? YES. Here's why: even on a one-box Sysplex, DB2 data sharing can deliver a very important availability benefit: the ability to apply DB2 maintenance, and even to migrate to a new release of DB2 (as I pointed out in a previous post to this blog), without the need for a maintenance window (you apply fixes to the load library of a member of the DB2 data sharing group, quiesce application traffic to that member, stop and start the member to activate the maintenance, resume the flow of application requests to the member, then do the same for the other member -- or members -- of the group). To put it another way: even when the Parallel Sysplex infrastructure (at least two z/OS LPARs, and at least two internal coupling facilities) is configured on one mainframe, implementation of a DB2 data sharing group will enable you to apply DB2 maintenance (and to perform DB2 version upgrades) without having to stop the application workload. At a time when unplanned DB2 outages are more and more rare (thanks to ever-more-reliable hardware and software), the opportunity to virtually eliminate planned outages can be a very big deal for an organization.

And even though the frequency of unplanned DB2 outages is diminishing, in an increasingly online world the cost of unexpected downtime is higher than ever (i.e., tolerance for unplanned outages is lower than ever). A DB2 data sharing group running on a one-box Parallel Sysplex can greatly reduce the scope of an abnormal DB2 subsystem termination: if such a failure occurs in a data sharing system, only the data and index pages (or rows, if row-level locking is used) changed by units of work that were in-flight on the failing DB2 subsystem become unavailable until the failed subsystem is restarted (this as opposed to the whole database becoming inaccessible if a standalone DB2 subsystem terminates abnormally). On top of that, DB2 restart processing tends to complete more quickly in a data sharing versus a standalone DB2 environment (externalization of changed pages to group buffer pools at commit means that the roll-forward part of restart requires less time).

Of course, a data sharing group running on a one-box Parallel Sysplex can't provide data access if the one mainframe server fails. Maintaining application availability in the event of such a failure (or of a planned server outage) would require a multi-box Sysplex. The point I want to make is this: you can get a lot -- though not all -- of the availability benefits of DB2 data sharing even if your Parallel Sysplex is contained within one System z server (consider: mainframes are extremely reliable). There are organizations out there right now that have boosted uptime for DB2-accessing applications by implementing data sharing groups on single-box Parallel Sysplexes. Want ultimate data availability? Do data sharing on a multi-box Sysplex. If the multi-box route is not an option for your company, don't assume that DB2 data sharing can't work for you. It can.

How big can a DB2 for z/OS index be?

2011-09-26T14:50:00.000-07:00

That looks like a pretty simple question, doesn't it? I mean, if I were asking about maximum table space size, you'd be able to answer, really quickly, with "64 GB for a non-partitioned table space, 128 TB for a non-LOB partitioned table space," right? Asking about maximum size for an index doesn't make the question that much harder, does it? Or does it? Do you know the answer? Read on, and you will.

Just last week, I got a question from a DBA at one of the DB2-using organizations that I support. She had a non-partitioned index (NPI) defined on a table in a big partitioned table space. The index had grown to 63.9 GB, and that gave the DBA a nagging sense of unease. She was concerned that something bad might happen when the index size hit 64 GB. For lots of mainframe DB2 people, 64 GB does ring a bell as a size limit for a non-partitioned object. Does that limit apply to a non-partitioned index?

Looking for the answer to that question, you could check the "Limits in DB2 for z/OS" section of the appendix in the SQL Reference (this manual and others in the DB2 for z/OS library are available online at https://www-304.ibm.com/support/docview.wss?uid=swg27019288). There, in the "DB2 system limits" table, you'd see how large a table space can be, but you wouldn't find information on index size limits. Hmmmm....

Time to end the suspense. The aforementioned DBA can relax (and I told her so), because the non-partitioned index in question had been defined with a piece size of 64 GB, and a non-partitioned index can have, of course, more than one piece (i.e., more than one data set). In fact, any index (partitioned or non-partitioned) can have more than one data set in its page set (the data sets that comprise a DB2 table space or index space are collectively known as a page set). So, the maximum size of an index would depend on two factors: 1) the maximum number of data sets that can be in the index's page set, and 2) the maximum size of a data set in the index's page set.

Both of those index-max-size factors have some dependency on the nature of the table space holding the table on which an index is defined. If that table space is non-partitioned (i.e., a simple table space or a segmented table space that is not a universal table space), the index can have up to 32 data sets in its page set, and each of those data sets can be up to 2 GB in size -- so, in that case the maximum size of the index would be 64 GB (32 data sets times 2 GB per data set), UNLESS the index was defined with a PIECESIZE value smaller than 2G (if PIECESIZE 1G had been specified for the index, its maximum size would be 32 GB). Note that the default value for PIECESIZE is 2G if the index is defined on a table in a non-partitioned table space.

When the table on which an index is defined is in a partitioned table space, the index's maximum size depends on 1) whether or not that table space was defined with a DSSIZE specification (or with the LARGE attribute), and 2) whether the index is partitioned or non-partitioned (syntactically speaking, DSSIZE is preferred over LARGE for specifying partition size). We'll consider non-partitioned indexes first. If LARGE or DSSIZE was not specified for the associated table space, a non-partitioned index can have 32 data sets in its page set, and each of these data sets can be up to 2 GB in size. That makes the index's maximum size 64 GB (as noted above for an index associated with a non-partitioned table space, an explicitly specified PIECESIZE value smaller than 2G would reduce the index's maximum size accordingly).

If the partitioned table space was defined with the LARGE or DSSIZE options, an associated non-partitioned index can have as many data sets as the table space can have partitions. How many is that? Well, it depends on the DSSIZE value (and a LARGE specification equates to a DSSIZE value of 4G) and the page size of the table space. Details on the relationship between DSSIZE, page size, and maximum number of partitions can be found in the CREATE TABLESPACE section of the SQL Reference, but suffice it to say that the maximum size of a non-partitioned index is 128 TB (same as the maximum size of a non-LOB table space). This maximum size could be reached in one of two ways: with a PIECESIZE of 32G and 4096 data sets, or a PIECESIZE of 64G and 2048 data sets (a 64G PIECESIZE specification is possible only if the DSSIZE of the associated table space is 64G or larger, and with a DSSIZE specification of 64G and a page size of 32 KB, a table space can have a maximum of 2048 partitions). If the non-partitioned index was defined without a PIECESIZE specification, it will have a default PIECESIZE as determined by the following formula:

Default PIECESIZE = MIN(x, 2^32 /(MIN(4096, 2^32 / (x / y))) * z)

Where:

x is the DSSIZE of the associated table space
y is the page size of the table space
z is the page size of the index

For example, if you created a non-partitioned index without a PIECESIZE specification, and the associated table space had a DSSIZE of 32G (the "x" value in the above formula) and a page size of 32 KB (the "y" value in the formula), and the page size of your index is 4 KB (the "z" value), the default PIECESIZE value would be 4G.

How about the max size of a partitioned index? Because that index will have one data set for each of the associated table space's partitions, the answer to the index size limit question will (as for non-partitioned indexes) depend on the maximum number of partitions for the table space and the size of each index partition. Here's how the maximum number of partitions for a table space breaks down:

If the table space was defined without the LARGE or DSSIZE options, and with a NUMPARTS value of 64 or less, the maximum number of partitions is 64.
If the table space was defined with the LARGE option, it can have up to 4096 partitions.
If the table space was defined with a DSSIZE specification, the maximum number of partitions can be up to 4096, depending on the DSSIZE value and the table space's page size (information on maximum number of partitions for various combinations of DSSIZE and table space page size can be found in the CREATE TABLESPACE section of the SQL Reference).

So, the above maximum-number-of-partitions values indicate how many partitions a partitioned index can have (again, because you'll have one index partition per table space partition), but how large can each index partition be? You can't specify PIECESIZE for a partitioned index, but DB2 determines the index partition size via the formula referenced previously in this blog entry:

Index partition size = MIN(x, 2^32 /(MIN(4096, 2^32 / (x / y))) * z)

Where:

x is the DSSIZE of the associated table space
y is the page size of the table space
z is the page size of the index

To illustrate: if the table space's DSSIZE is 64G ("x" in the formula) and the table space's page size is 32 KB ("y" value) and the index's page size is 32 KB ("z" value), each index partition can be up to 64 GB in size, and the maximum size of the partitioned index is 128 TB (64 GB times 2048 partitions, which is the maximum number of partitions that a table space with a 64G DSSIZE and a 32 KB page size can have). In fact, 128 TB is as big as a partitioned index can get (an index could also reach this size if the underlying table space had a DSSIZE of 32G and a page size of 32 KB).

Can you use the above index partition-sizing formula when the partitioned table space associated with the index was defined without a DSSIZE specification? I believe that you can. For that purpose, note that a LARGE table space effectively has a DSSIZE of 4G. For a partitioned table space defined without the LARGE option and without a DSSIZE specification, the maximum size of a table space partition is:

4G, when NUMPARTS for the table space is between 1 and 16
2G, when NUMPARTS for the table space is between 17 and 32
1G, when NUMPARTS for the table space is between 33 and 64
4G, when NUMPARTS for the table space is between 65 and 254

You should now be able to respond to "How large can this index get to be?" questions. I hope that this information will be useful to you.

DB2 for z/OS Version 10: Moving Objects from One Buffer Pool to Another

2011-09-15T13:22:00.000-07:00

Over the past 15 years or so, I have written magazine articles, and presented, and blogged on the topic of DB2 for z/OS buffer pools and their impact on application performance (two of my more recent blog entries in this category were posted in September and October of last year). Often, the message boils down to this: bigger buffer pool configuration = good. That said, there are times when enlarging a DB2 buffer pool configuration is not feasible. There may already be some pressure on the memory resource of your z/OS LPAR (as indicated, in my book, by a demand paging rate in excess of 10 per second during busy processing times -- check your z/OS monitor). There could be an organizational impediment to making your buffer pool configuration larger, in that the approval process for such a system change takes so long as to make you reluctant to put in the request. Whatever the reason, when you need to (or want to) make do with the DB2 buffers you have, you can often improve application response time and throughput by moving table spaces and/or indexes from one buffer pool (a pool with a high rate of disk read I/Os per second) to another (the target pool presumably being one with a relatively low rate of read I/Os per second). There are other reasons, as well, for moving a database object to a different buffer pool: you might want to use a buffer pool to manage parallelism for dynamic queries (as mentioned in the blog entry I posted last week), or you might isolate an object in its own pool for monitoring purposes, or you might be changing an object's page size (more on this momentarily). Whatever your situation might be, I want to let you know via this blog entry that some of the rules pertaining to changing the buffer pool assignment for a table space or an index changed with DB2 10 for z/OS. I'll use the rest of this entry to describe these changes.

I'll start by informing you of one thing that hasn't changed: if your DB2 subsystem is running in standalone mode (i.e., it's not a member of a DB2 data sharing group), and the object being moved from one buffer pool to another is a table space, and the buffer pool to which the table space will be assigned has the same page size as the pool to which the table space is currently assigned, and there are no already-existing "pending" definition changes for the table space or any of its associated indexes (pending object definition changes were introduced with DB2 10 in new-function mode -- more on this later) then 1) the ALTER TABLESPACE statement with the BUFFERPOOL specification can be issued at any time, and 2) the change will take place when the table space's data sets are next opened after the ALTER TABLESPACE statement has been issued (a common means of closing and reopening a table space's data sets is to issue a -STOP DATABASE command for the table space, followed by a -START DATABASE command). This behavior is the same as that which you'd see in a DB2 9 environment (or in a DB2 V8 system).

OK, now for the DB2 10-introduced changes.

FOR TABLE SPACES AND INDEXES

More flexibility for DB2 data sharing sites. I like this change a lot. Prior to DB2 10, if one wanted to reassign a table space or an index to a different buffer pool, the target object had to be in the stopped state at the time of the issuance of the ALTER TABLESPACE or ALTER INDEX statement with the BUFFERPOOL specification. If you're a DBA, that's not so good from a convenience standpoint, as stopping a heavily-accessed table space or index may not be do-able in prime time, even if you issue the -STOP DATABASE command with the AT(COMMIT) option to utilize drain locking. You might have to do the -STOP at night and/or during a weekend. Yuck. Enter DB2 10 (in conversion mode, no less!), and that restriction is gone: you can issue the buffer pool reassigning ALTER TABLESPACE or ALTER INDEX statement at any time, even when DB2 is running in data sharing mode -- the target object does NOT have to be in the stopped state. The change will take effect, as before, when the object's data sets are next opened following the issuance of the ALTER statement.

FOR TABLE SPACES

Reassigning a table space to a buffer pool with a different page size. Before DB2 10, the only way to move a table space from a 4K buffer pool to (for example) an 8K pool (that is, to change the table space's page size) was to unload data from the table space, drop it, re-create it with the larger-page buffer pool specified, and re-load the previously unloaded data. Cumbersome. With DB2 10 (in new-function mode), you can effect this change for a universal table space (one that is not an XML table space) or a LOB table space via an ALTER TABLESPACE statement (specifying the buffer pool with the larger or smaller page size), and the change will take effect the next time the table space (in its entirety) is REORGed with SHRLEVEL CHANGE or REFERENCE (this kind of table space definition modification is known as a "pending" change -- something new introduced with DB2 10, and a continuation of the online schema change capabilities introduced with DB2 V8 and expanded with subsequent DB2 releases). By the way, DB2 10 (in new-function mode) also enables conversion of a simple, segmented, or traditional table-controlled range-partitioned table space to a universal table space via ALTER and REORG.

FOR INDEXES

Pending versus immediate definitional changes. For indexes, the DB2 10 change pertaining to buffer pool reassignment has to timing as it relates to the change actually taking effect. If the change is of the pending variety (new with DB2 10 in new-function mode), it will take effect when the index (in its entirety) is next reorganized via REORG INDEX with SHRLEVEL CHANGE (or via REORG TABLESPACE with SHRLEVEL CHANGE or REFERENCE -- and the REORG TABLESPACE route is required if there are also pending definition changes in existence for the table space associated with the index). Here is what would make an ALTER INDEX with a BUFFERPOOL specification a pending change (and ALL of the following conditions would have to be true):

The index's data sets have already been created.
The index is defined on a table in a universal table space, or on an XML table or an auxiliary table associated with a base table that is in a universal table space.
There are pending definition changes in existence for the index or the associated table space, or the buffer pool to which the index will be reassigned has a page size that is different from that of the index's current buffer pool (index page sizes other than 4K were introduced with DB2 9).

If any of the above conditions is not true, the index definition change is immediate.

For a pending change, buffer pool reassignment takes place the next time the entire index is reorganized via REORG INDEX with SHRLEVEL CHANGE or REORG TABLESPACE with SHRLEVEL CHANGE or REFERENCE.

If the change is of the immediate variety, and the page size of the "to" buffer pool is the same as that of the "from" buffer pool, reassignment of the index to the pool specified in the ALTER INDEX statement will take place when the index's data sets are next opened after the issuance of ALTER INDEX.

If it's an immediate change and the "to" buffer pool has a page size that is different from that of the "from" buffer pool, the index is placed in REBUILD-pending status and the buffer pool reassignment takes place when the index is rebuilt.

There you have it. I hope that this information will be useful for your buffer pool reassignment changes in a DB2 10 environment (or for DB2 10 migration planning, if you're currently using DB2 9 or DB2 V8).

Managing DB2 for z/OS CPU Parallelism for Dynamic SQL

2011-09-07T05:43:00.000-07:00

DB2 for z/OS query CPU parallelism (hereinafter referred to simply as "query parallelism"), introduced with DB2 Version 4 in the mid-1990s, enables DB2 to split a query into multiple tasks that can execute concurrently on a mainframe server. The primary benefit of query parallelism is improved performance from an elapsed time perspective (query parallelism tends to increase statement execution CPU time somewhat -- this because DB2 needs to evaluate a candidate statement to determine whether or not parallelism would improve the query's run time, and because DB2 has to manage a parallelized query's "child" tasks). That said, for locally-originating queries (i.e., those issued by programs running on the mainframe server on which the target DB2 subsystem is running), a sometimes equally-appealing benefit is redirection of work to the server's zIIP engines, if the server is configured with zIIPs (queries that come from remote DRDA requesters, processed through DB2's distributed data facility, already benefit, cost-wise, from substantial zIIP offload).

One matter that sometimes comes up when an organization is considering the use of query parallelism concerns management of the DB2 feature's scope-of-use. In other words, people may want some, but not all, queries executed in a given DB2 environment to be candidates for parallelism. This some-but-not-all approach makes sense in certain situations, particularly when there is a desire to utilize query parallelism in a system on which high-volume OLTP and/or batch applications are already running. Mainframe servers that handle such operational workloads often run at high levels of utilization during peak processing hours (sometimes 90% busy or more). On that type of system, the CPU cost of evaluation by DB2 of all queries for estimated parallelism performance benefit, and of management by DB2 of child tasks for queries that are parallelized, could push the server's utilization rate to a level that would negatively impact the performance of the existing operational applications. [Note: for a DB2 subsystem that is dedicated to business intelligence usage (an example being a data warehousing system), it might be appropriate to make all dynamic queries -- and dynamic SQL tends to dominate in BI environments -- candidates for parallelism by changing the value of the DB2 ZPARM parameter CDSSRDEF to ANY from its default value of 1.]

So, if you want some, but not all, of your queries to be candidates for parallelization, how do you go about putting that aim into action? For static SQL, it's easy: identify the program that issues the SELECTs that you'd like DB2 to parallelize, and rebind that package with a specification of DEGREE(ANY). For dynamic SQL, selective implementation of query parallelism might be a little less straightforward, depending on the particulars of the situation. If dynamic queries are issued by a program that an organization's developers have coded, making that program's queries candidates for parallelization would simply require that the program issue the SQL statement SET CURRENT DEGREE = 'ANY' prior to issuing the queries. In other cases, the SET CURRENT DEGREE mechanism may not be applicable. You might have a situation in which dynamic queries are generated and issued by an end-user data query tool, and that tool might not provide a means of setting the value of the CURRENT DEGREE special register to 'ANY'. Or, the dynamic queries you want DB2 to parallelize might be issued by a purchased application for which the vendor does not supply source code, thereby making it impossible to add a SET CURRENT DEGREE statement to a program (and even if you have the source code for a purchased application, modifying that source code could put you in violation of the vendor's support agreement).

What can you do if the SET CURRENT DEGREE = 'ANY' option is not available to you? Here, you have some options with respect to implementing limited-scope parallelism for dynamic queries (i.e., implementing parallelism for queries in a "some-but-not-all" fashion), as explained below. Note that for all three of the parallelism management approaches described below, the value of the CDSSRDEF parameter in ZPARM is assumed to be ANY (in other words, it's assumed that you've opened the door for all-dynamic-query parallelism on that subsystem, and you'll then take one or more steps to close that door for all but a portion of the dynamic query workload).

Limit query parallelism by table -- Suppose that you want to make dynamic queries targeting table ABC candidates for parallelism, while preventing parallelism for dynamic queries accessing other tables. This could be done pretty easily be assigning the table space associated with table ABC to its own buffer pool, and leaving the value of that pool's VPPSEQT parameter at the default of 50 (or specify a value greater than 50, if you want -- more on this momentarily). Set the value of VPPSEQT to zero for your other buffer pools, and parallelism for queries targeting tables in table spaces assigned to those pools will be disabled as a result.

Now, a bit more on VPPSEQT (short for Virtual Pool Parallel SEQuential Threshold): this is one of several DB2 buffer pool parameter specifications that can be altered for a particular pool via execution of an ALTER BUFFERPOOL command. The default value for VPPSEQT, as noted, is 50. This is actually a percentage of the VPSEQT (Virtual Pool SEQuential Threshold) value, which is 80 by default. That 80 is a percentage of the associated pool's buffers, so by default 50% X 80% = 40% of a pool's buffers are available to hold table and/or index pages that are read from disk as a result of parallel-mode query execution. If you change the VPPSEQT threshold for a pool to 60 and leave the VPSEQT threshold at 80 then, for that pool, 60% X 80% = 48% of the buffers will be available to hold pages read into memory in support of parallel-mode query processing. If VPPSEQT is set to zero, no buffers will be available to support parallel-mode query processing, so queries targeting tables in table spaces assigned to that pool will not be candidates for parallelization.

Limit query parallelism by package, collection, etc. -- A lot of people think of DB2's Resource Limit Facility (RLF) only as a means of limiting the amount of CPU time that a dynamic SQL statement can accumulate in execution before it is terminated. That is indeed the most common usage of RLF, and it is actuated through the use of what's called an RLMT table (the name of this table is actually of the form DSNRLMTxx, with xx being a user-specified two-character alphanumeric value). You could alternatively activate an RLST table (DSNRLSTxx), which would allow you to (among other things) deactivate query parallelism for dynamic queries associated with workload components qualified by certain identifiers (options include authorization ID, collection name, and package name -- these correspond to columns of the RLST table). Remember, your specification of ANY for CDSSRDEF in ZPARM opened the door for parallelism candidacy to all dynamic queries -- through the RLST table (via the value '4' in the RLFUNC column) you are closing that door for workloads OTHER than the one for which you want dynamic queries to be candidates for parallelism. Here's an interesting and easy-to-specify set-up: with one row in the RLST table, having '4' in column RLFUNC and no values in any other columns, parallelism will be disabled for dynamic queries that are local to the DB2 subsystem (e.g., issued via SPUFI or QMF or batch jobs), while allowing parallelism for queries issued from remote DRDA requesters (this thanks to a blank value in the LUNAME column of the RLST table, which represents the local location). You can find more information about the RLST table in the DB2 Version 8 Administration Guide, the DB2 9 Performance Monitoring and Tuning Guide, or the DB2 10 Managing Performance manual.

Limit query parallelism by DB2 data sharing group member -- I really like this option, but it does, of course, require that you have DB2 running in data sharing mode on a Parallel Sysplex. The concept here is pretty simple: you take one or more of the members of the data sharing group, and you set the value of the ZPARM parameter CDSSRDEF to ANY on those subsystems (leaving the value of CDSSRDEF at 1 on the other members of the group -- and it's assumed that you'll run your high-volume OLTP and batch processes on the CDSSRDEF = 1 subsystems). If you have "local" programs that issue dynamic queries that you want DB2 to parallelize, run those on the subsystem(s) that has CDSSRDEF = ANY. If you have users and/or applications that issue dynamic queries that access host data via DRDA and DB2's distributed data facility, have these users and apps connect to the CDSSRDEF = ANY subsystem(s). For maximum availability as it pertains to programs and users issuing dynamic queries that are to be candidates for parallelism, it's best to have at least two member DB2 subsystems set up for parallelism-by-default (i.e., with CDSSRDEF = ANY). That way, if one is down due to a planned (e.g., for maintenance) or unplanned outage, the other member(s) can continue to parallelize dynamic queries as desired. This kind of configuration for high availability was made MUCH easier for DRDA requesters when DB2 for z/OS Version 8 introduced a member-subsetting capability. Setting this up would involve creating an alias location name for the DB2 members for which you have CDSSRDEF = ANY, assigning a TCP/IP port to that alias, and updating the DDF record in the respective members' bootstrap data sets with this information (done via DSNJU003, the change log inventory utility). Combine a location alias with the use of dynamic virtual IP addresses (DVIPAs) for DB2 members, and you'll have a high-availability configuration that is simple from the perspective of users and applications connecting to the CDSSRDEF = ANY DB2 subsystems (from a CONNECT TO point of view, there is no need to know the names of individual DB2 members associated with the location alias). All of this is described quite clearly in an excellent IBM "red book" titled DB2 9 for z/OS: Distributed Functions.

And there you have it. You can implement parallelism for dynamic queries in a limited fashion, and you have options when it comes to setting this up. Assess your situation, and go the route that makes sense for your organization.

DB2 for z/OS and SNA: Breaking the Ties that (Used to) Bind

2011-08-30T09:39:00.000-07:00

I don't know if there are any industries in which things change as quickly (and constantly) as they do in the realm of information technology. Just a few years ago, clouds were what produced rain, streams were watercourses, and Ruby on Rails might have been a reference to your aunt taking the express train to New York City. Today, those are all IT terms. It can be kind of dizzying, keeping up with all this new stuff.

Fortunately (especially for us old-timers), even in IT there are those technology rocks that endure, providing a sense of stability amidst the swirl of change. Magnetic disk drives are still the dominant data storage media. Various server operating systems -- z/OS (descendant of MVS), UNIX variants, Linux, and Windows -- have stood the test of time. Relational database management systems are still very much mainstream, as challengers that have arisen over the years have faded away or become niche technologies. And then there's SNA, right? Good old Systems Network Architecture, that robust, feature-rich communications protocol that reliably gets data to and from mainframes. We'll always have SNA, won't we? Well, maybe not...

I remember when TCP/IP, the now-pervasive data communications protocol, was first getting a lot of attention in z/OS circles. It was the 1990s. Client-server computing was on the rise, and lots of organizations wanted their mainframes to be the servers on client-server configurations. With TCP/IP being the lingua franca of PCs and distributed systems servers, z/OS had to be able to support this protocol, and in time it did. Still, SNA remained heavily in use in mainframe environments, owing largely to the fact that TCP/IP, at the time, lacked some of the high-availability and diagnostic features of SNA -- features that were held in particularly high regard by the folks responsible for operating data centers that housed mainframe servers. Years passed, and TCP/IP became a much more robust communications protocol, to the extent that organizations could utilize TCP/IP instead of SNA without having to compromise on network robustness and availability. Thus, use of SNA, even in mainframe environments, steadily declined. But you still need SNA, and VTAM (the mainframe communications subsystem that enables the use of SNA), for some aspects of DB2 for z/OS processing, right?

WRONG. Starting with DB2 9 for z/OS running in New Function Mode (NFM), you can actually cut the cord between DB2 and VTAM (and SNA), and go 100% TCP/IP. Technically, this is accomplished by modifying the DDF record in the bootstrap data set (BSDS). How do you do this? The way you make any changes to the BSDS: via the change log inventory utility, also known as DSNJU003 (given what you can do with DSNJU003, it would be nice if it were rechristened as the "change BSDS" utility). Specifically, you submit a DSNJU003 job with a DDF statement. In that DDF statement, specify an IPNAME for the DB2 subsystem (or for the data sharing group, if the DB2 subsystem is a member of a group). IMPORTANT: do NOT add an IPNAME to the DDF record in the BSDS if you need your DB2 subsystem to process DRDA requests coming in over both TCP/IP and SNA. If you need to support both TCP/IP and SNA for DRDA requests, have an LUNAME in the DDF record in the BSDS and ensure that there is NOT an IPNAME in that record. Once the DDF record contains an IPNAME value, DB2 will not even try to activate its SNA communications support, even if some remote client sends a DRDA request to the subsystem using SNA. So, don't add an IPNAME to the DDF record in the BSDS until you are sure that you no longer need SNA support for a DB2 subsystem. Note that in a data sharing group, you could have one or more members supporting only TCP/IP communications, while other members support both TCP/IP and SNA. In that case, the IPNAME in the BSDS DDF record of the TCP/IP-only members must match the GENERIC LUNAME used by the members that are supporting both TCP/IP and SNA communications.

Once you have supplied an IPNAME for a subsystem in its BSDS DDF record (i.e., once you go to TCP/IP-only communications for DRDA requests), you can subsequently execute the DSNJU003 utility with the NOLUNAME option in the DDF statement. This will remove the LUNAME value from the DDF record in the BSDS (if you run DSNJU003 with NOLUNAME, no other DDF parameter specification is allowed -- NOLUNAME has to appear by itself in the DDF statement of the utility).

And there you have it: no LUNAME, no SNA, no association with VTAM. What'll change next? We'll see. At least the IBM logo still has those striped letters. Hold onto that, Big Blue!

An Important DB2 10 for z/OS Stored Procedures Enhancement

2011-08-25T10:09:00.000-07:00

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

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

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

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

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

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

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

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

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

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

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

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

Great DB2 Learning Opportunities in Prague this November

2011-08-16T15:27:00.000-07:00

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

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

The one-day seminars offered on November 18

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

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

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

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

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

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

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

2011-07-15T08:13:00.000-07:00

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

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

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

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

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

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

2011-06-26T10:02:00.000-07:00

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

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

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

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

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

Application Programming Tip: Let DB2 do the Work for You

2011-06-13T13:41:00.000-07:00

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Some Nuggets from IDUG in the OC, Part 2

2011-05-24T22:15:00.000-07:00

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

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

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

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

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

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

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

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

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

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

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

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

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

Some Nuggets from IDUG in the OC, Part 1

2011-05-16T12:02:00.000-07:00

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

You DO Let DB2 for z/OS Allocate Utility Sort Work Data Sets, Don't You?

2011-04-22T14:57:00.000-07:00

Some of my blog posts are about DB2 things that are very new (my previous entry described the high-performance DBATs introduced with DB2 10 for z/OS), and some cover DB2 stuff that's been out there for a while. This entry is an example of the latter. Dynamic allocation of the sort work data sets used in the execution of mainframe DB2 utilities is something that goes back at least to DB2 Version 8, and a lot if DBAs have taken advantage of this feature to improve the reliability and performance of their utility jobs. At the same time, it seems that there are a number of DB2 for z/OS people who are kind of confused about dynamic allocation of utility sort work data sets, and are uncertain as to how they can leverage this product capability. Seeing as how dynamic allocation of the data sets in question (and not just dynamic allocation, but DB2-directed allocation -- more on that distinction momentarily) is highly recommended by IBM, my aim today is to clear things up for folks who could use some clarification. By the way, I want to give props to Christian Michel, one of the utilities developers at the IBM lab in Boeblingen, Germany, who helped me get my arms around the topic.

So, a little background: a number of DB2 utilities -- including LOAD, REORG, REBUILD INDEX, and RUNSTATS -- use DFSORT (an IBM product that's a feature of z/OS) to handle required sorting of data records (typically index keys). DFSORT of course needs some disk space to do this work, and some time ago you had to allocate those data sets via DD statements in the JCL of your utility jobs. SORTWKnn is the DD name that generally comes to mind first when one thinks of these data sets, but there are others: SW01WKnn, DATAWKnn, DA01WKnn, etc.). The problem with the JCL-directed allocation of these data sets was that you could have a utility job fail if the space available in the DFSORT temporary data sets were insufficient, or the job might not perform optimally if the wrong number of data sets were allocated (this could impede parallelization of sort operations).

To address these challenges, IBM provided new keywords for DFSORT-using utilities (again, I'm not sure if this was introduced with DB2 V8 or a prior release) that would enable DFSORT to dynamically allocate the temporary data sets that it needed to do the sorting required by a utility job. The new keywords were SORTDEVT, which indicates the device type to be used for data sets dynamically allocated by DFSORT, and SORTNUM, which can be used to specify the number of temporary data sets to be allocated by DFSORT for each sort operation performed in the execution of a DB2 utility (the value of SORTDEVT is usually set to a so-called "esoteric," which is a z/OS installation-defined group of devices, and the common specification is SYSDA, because that means magnetic disk storage and it's an esoteric that is almost always defined on a z/OS system).

Dynamic allocation of utility sort work data sets by DFSORT was a step in the right direction, but still the situation was less than ideal (SORTNUM might be set to a sub-optimal value, potentially impeding utility sort parallelization, or the estimate of space needed for sort work data sets might be substantially off due to inaccurate statistics in the DB2 catalog). To address these challenges, DB2 utilities were enhanced via APARs PK45916 (for DB2 Version 8) and PK41899 (for DB2 9) to enable them to dynamically allocate sort work data sets before invoking DFSORT (the enhancements delivered via these APARs are part of the base functionality of DB2 10). In doing this, the utilities would optimize the number of sort work data sets dynamically allocated for a job (thereby removing the need for a user-specified SORTNUM value). On top of that, sort work space requirements would be more accurately estimated utilizing information in the real-time statistics tables.

That's all good (REAL good), but some people were (and still are) uncertain as to what had to be done to realize the benefits of these important DB2 utility enhancements. That, as much as anything, is what I want to address in this entry. Here's the deal:

Make sure that the value of the DB2 ZPARM parameter UTSORTAL is set to YES (note that YES is the default setting for UTSORTAL in a DB2 10 system).
Remove SORTNUM specifications from your utility control statements, or set the DB2 ZPARM parameter IGNSORTN to YES to have DB2 ignore any SORTNUM specifications found in utility control statements.
Remove ALL of the sort-related DD statements from the JCL of your DB2 utility jobs -- that's anything with a WKnn string in it (SORTWKnn, SW01WKnn, DATAWKnn, DA01WKnn, STATWKnn, and ST01WKnn), along with UTPRINnn and DTPRINnn (exception: keep the UTPRINT and SORTDIAG DD statements in your JCL).
Ensure that real-time statistics information is available to the DB2 utilities. If you are still on DB2 V8 or using DB2 9 in Conversion Mode, you need to create the real-time statistics tables if they have not already been defined on your system (starting with DB2 9 in New Function Mode, the real-time statistics tables are part of the DB2 catalog, and you don't have to be concerned with creating them). If you need to set up the real-time statistics tables in your DB2 environment, refer to Appendix I in the DB2 Version 8 Administration Guide (available in PDF form at this url: https://www-304.ibm.com/support/docview.wss?uid=swg27011659).

Another thing: leave SORTDEVT in your utility control statements, or include a SORTDEVT specification in those statements (referring to the utilities for which SORTDEVT is a valid option: CHECK DATA, CHECK INDEX, CHECK LOB, LOAD, REBUILD INDEX, REORG INDEX, REORG TABLESPACE, and RUNSTATS). You need this for dynamic allocation of sort work data sets, regardless of whether this dynamic allocation is done by DB2 (preferred) or DFSORT.

Finally: some people who have been accustomed to allocating sort work data sets via JCL DD statements might be concerned about loss of control over placement of these data sets in the disk subsystem if they go the dynamic allocation route. After all, SYSDA (again, the typical SORTDEVT specification) is usually very generic ("allocate these on magnetic disk devices"). What do people do to direct dynamically allocated sort work data sets to a particular set of disk volumes? My understanding is that the primary means to this end is the utilization of DFSMS Automatic Class Selection (ACS) routines. These routines would look for the DD names of the sort work data sets being dynamically allocated (as mentioned previously, these contain the string WKnn) and would direct those data sets to an SMS storage group that would be comprised of the volumes you want to be used for the "sort pool." Another option would be to define an esoteric that would include the sort pool volumes and then to use the name of this esoteric instead of SYSDA as the value of the SORTDEVT utility control card option.

OK, so all of this is old news to people who have been taking advantage of DB2-directed dynamic allocation of sort work data sets since the functionality was introduced back in 2008. I'm interested in reaching people who are still allocating DB2 utility sort work data sets the old way. If you're in that group, I'm telling you: the new way (let DB2 allocate the data sets) is the better way -- better for performance, and better for reliability. Give it a shot, if you haven't already.

DB2 10 for z/OS: What do You Know About High-Performance DBATs?

2011-04-01T14:59:00.000-07:00

DB2 10 for z/OS, which became generally available this past October, is loaded with features and functions that can reduce the CPU cost of applications. One of my favorites is high-performance DBATs (DBATs, or database access threads, are associated with SQL statements that are issued by remote requesters using the DRDA protocol and processed via DB2's Distributed Data Facility). I'm big on high-performance DBATs because I'm big on mainframe DB2's use as a super-scalable, super-available, super-secure data server for applications that run on Java, .NET, and other application servers. For DB2 for z/OS to realize its full value-delivery potential in client-server computing environments, it has to provide to DBAs and to systems administrators the same performance management and tuning options that are available for local-to-DB2 applications (such as those that run in CICS regions). This has been happening for a period that stretches back a good 20 years or so. First we got static SQL for client-server applications (back when the DRDA protocol and package bind were introduced with DB2 Version 2 Release 3). With DB2 Version 4 we got stored procedures, a critical enhancement with respect to driving up the scalability and manageability of DB2-based client-server computing (stored procedures themselves have been substantially enhanced since their introduction, with features such as WLM-managed stored procedure address spaces and, more recently, native SQL procedures). Management of DB2 DDF transaction priority through WLM service classes provided a means of setting performance objectives and report classes that could be very granular, with transaction classification possible by way of all kinds of attributes including collection, package, stored procedure, client application name, and user ID.

All this is great, and plenty of organizations are running very high-volume OLTP workloads in mainframe DB2 client-server environments today. That said, there was one thing annoyingly missing from the DDF transaction-tuning tool kit: the ability to leverage the CPU efficiency benefits of packages bound with RELEASE(DEALLOCATE), used in combination with threads that persist through multiple transaction executions. That gap was filled very nicely by the introduction, with DB2 10 for z/OS, of high-performance DBATs.

Before getting into the particulars of high-performance DBATs, I want to do a little illustration by comparison. High-performance DBATs are conceptually similar to CICS-DB2 protected entry threads (with some important differences, which I'll cover momentarily). Like CICS-DB2 protected entry threads, high-performance DBATs persist through multiple transaction executions (typically, a non-protected CICS-DB2 thread will terminate when the transaction using that thread completes). This persistence, in and of itself, is not such a big deal in the DB2 DDF world. For one thing, thread creation and termination doesn't consume a whole lot of resource (the DB2 systems services address space, aka MSTR, handles this function, and you can see in a DB2 monitor statistics report that MSTR consumes relatively little CPU). More importantly, if you have the DSNZPARM parameter CMTSTAT set to INACTIVE (the default), DB2 DDF connections are disassociated from DB2 DDF threads, and the latter can be pooled and used to support a very large number of connections, so that DBAT re-use will typically be very high and the incidence of DBAT creation will typically be very low.

So, if the re-usability of high-performance DBATs is not the big news, what is? The big news is that high-performance DBATs allow you to realize the CPU efficiency benefit of the RELEASE(DEALLOCATE) option of BIND PACKAGE -- a benefit previously available only for packages associated with local-to-DB2 programs. Back to the CICS-DB2 protected entry thread analogy. The real bang for the buck there is the use of such threads for the execution of packages bound with RELEASE(DEALLOCATE). Why? Because, for a package so bound, certain resources, such as table space locks and EDM pool elements, will be retained until the thread used in the execution of the package is deallocated -- this as opposed to being released and then reacquired, over and over again, as a high-volume CICS-DB2 transaction is executed (and retention of table space locks is generally not a big deal, as these are usually intent locks, and intent locks do not conflict with each other). Think about it: suppose a CICS-DB2 transaction accesses data in four table spaces, and the package has several sections that are stored in the EDM pool (the place where packages -- the run-time structures created from binding programs with embedded static SQL -- are cached in memory). If the transaction program's package is bound with RELEASE(COMMIT), when the transaction executes it will get locks (again, almost certainly intent -- versus exclusive -- locks) on the four table spaces and space in the EDM pool for the required package sections, and when it completes (driving a DB2 commit) those tablespace locks and EDM pool elements will be released. If the same transaction is executed right after that first one, those same tablespace locks and EDM pool elements will be reacquired and then released again at transaction completion. All that acquisition and release of the same stuff adds to the CPU cost of the transaction.

If the CICS-DB2 transaction program's package is bound with RELEASE(DEALLOCATE), and IF the thread used to execute the package can persist through executions of the program (that's where CICS-DB2 protected entry threads come in), the tablespace locks and EDM pool elements acquired for the program will be retained until the thread is deallocated, and that will likely not happen until the transaction has been executed a LOT of times; so, RELEASE(DEALLOCATE) plus protected entry threads together deliver a nice CPU efficiency benefit for high-volume CICS-DB2 transactions.

What kept that benefit from being available for transactions that execute in a client-server DB2 environment? It wasn't thread persistence -- as mentioned, DBATs, once created, can be reused many, many times. The problem was the RELEASE(DEALLOCATE) part of the equation. See, for a long time (since DB2 V6, I believe), although you could bind a package executed by remote DRDA requesters with RELEASE(DEALLOCATE), DB2 would not honor that bind specification in a DDF setting -- if an execution of the package were associated with a DBAT (versus a local-to-DB2 program's thread), DB2 would handle the package as though it had been bound with RELEASE(COMMIT). Why? Because, as noted, DBATs can stick around for a long time, and having tablespace locks -- even intent locks -- held by such long-duration threads could interfere with certain database maintenance operations (utilities, in particular). Now that high-performance DBATs have made the scene, RELEASE(DEALLOCATE) can be honored by DB2 even for packages executed on behalf of remote requesters via DBATs.

I mentioned that there are some important differences between CICS-DB2 protected entry threads and high-performance DBATs. One of those key differences concerns the actions required to bring the two different kinds of threads into existence. To have CICS-DB2 protected entry threads, one has to use CICS resource definition online functionality (aka RDO) to define a DB2ENTRY resource with a PROTECTNUM (and a THREADLIMIT) value that is greater than zero. A high-performance DBAT, on the other hand, is instantiated when it is used in the execution of a package bound with RELEASE(DEALLOCATE) -- there is no set-up, per se, required in order to have high-performance DBATs in your DB2 system (aside from the need to set the MAXDBAT and CONDBAT DSNZPARM parameters to values that are greater than zero, and to have CMTSTAT in DSNZPARM set to INACTIVE -- but that's what you've probably done anyway if you do any DDF processing on your system). Let's say that a remote requester issues an SQL statement that causes a DB2 package bound with RELEASE(DEALLOCATE) to be executed. Here's what will happen:

If there is already a high-performance DBAT in existence and associated with this particular connection (more on that in a moment), and it's available, it will be used in the execution of the package.
If there is not already a high-performance DBAT in existence for this connection, or if there is and it is tied up in the execution of another package, a "regular" DBAT in the pool will become a high-performance DBAT and will be used to execute the package (or a new DBAT will be created and will be a high-performance DBAT, if there are no available "regular" pooled DBATs).

Once the high-performance DBAT has been instantiated, it will be used to process 200 units of work and will then be terminated (if it is not terminated before as a result of hitting the idle thread timeout threshold). Auto-termination after 200 units of work is done to periodically release resources held by the high-performance DBAT. Even with that periodic break in the action, there will be times when you want resources acquired in the execution of packages to be released at commit, no matter how the packages are bound (perhaps during a period of time during which you want to run a number of utilities involving tablespaces targeted by SQL statements in the client-server packages). That can be easily accomplished thanks to a new command, -MODIFY DDF, that was introduced with DB2 10. If you issue -MODIFY DDF PKGREL(COMMIT), packages executed via DBATs will release resources at commit, no matter what RELEASE option was specified when the package was created (again, we're talking about tablespace locks and EDM pool elements -- retention of page and row locks is not affected by the RELEASE option of BIND PACKAGE). When this period of utility (for example processing is done, you can issue -MODIFY DDF PKGREL(BNDOPT) to have DB2 once again honor the RELEASE(DEALLOCATE) specification for packages so bound that are executed via DBATs.

Now, I implied previously that a high-performance DBAT, once instantiated, will be dedicated to the connection through which the request invoking the RELEASE(DEALLOCATE) package was issued. This is in fact the case. A high-performance DBAT does not go into the pool when it is freed up, to be usable by other connections to the DB2 subsystem. Instead, if the RELEASE(DEALLOCATE) package invocation came from a connection established by application server XYZ, the high-performance DBAT instantiated as a result of that request will persist to process 199 more units of work from application server XYZ; furthermore, there is no requirement that all -- or any -- of those 199 additional units of work be associated with packages bound with RELEASE(DEALLOCATE) -- a high-performance DBAT can be used for the execution of packages bound with RELEASE(COMMIT), too.

If you're using DB2 10 now, or you're planning your migration to this new DB2 release, I encourage you to think about taking advantage of high-performance DBATs to improve the CPU efficiency of your DDF workload. In other words, I want you to think about binding packages executed frequently via DBATs with RELEASE(DEALLOCATE); and, don't limit your consideration to packages associated with frequently executed programs that issue static SQL statements (though such packages, which could be related to DB2 stored procedures, can be excellent candidates for binding with RELEASE(DEALLOCATE)). You should also consider binding with RELEASE(DEALLOCATE) packages used by remote programs that issue dynamic SQL statements. Here, I have particularly in mind the packages used by the IBM Data Server Driver for JDBC and SQLJ. You can read more about binding these packages with RELEASE(DEALLOCATE) in the IBM "red book" titled "DB2 10 for z/OS Technical Overview" (see section 9.5).

The high-performance DBAT feature of DB2 10 is one of the latest in a long line of enhancements that have made DB2 for z/OS more and more valuable as a foundation for high-volume, transactional client-server applications. Go and rev up your DDF workload.

Monitoring DB2 for z/OS: What's in YOUR Subsystem?

2011-03-13T20:38:00.000-07:00

First of all, if your organization uses DB2 for z/OS, I hope that you have a DB2 monitor on the system. While several of the DB2 DISPLAY commands provide information that's useful for monitoring activity on a DB2 subsystem (a favorite of mine is -DISPLAY BUFFERPOOL(ACTIVE) DETAIL), for an in-depth view into what's going on you really want the capabilities of a monitor tool at your disposal. Multiple vendors provide these products -- IBM's offering is Tivoli OMEGAMON XE for DB2 Performance Monitor on z/OS (as that's a bit of a mouthful, I'll hereafter refer to it in this blog post as OMEGAMON for DB2).

I'll tell you something interesting that I've observed regarding the use of DB2 monitors out in the real world: LOTS of folks use only the online monitoring capability of whatever tool they have on their system. Online monitoring is definitely useful when it comes to checking out what's happening right now in a DB2 subsystem -- something you may need to do, and quickly, if a problem pops up. That said, when it comes to deep-dive analysis of the performance of a DB2 subsystem, nothing beats the REPORTS that a DB2 monitor can provide. Plenty of DB2 professionals have NEVER used the report-generation capabilities of their monitor product. That's a bummer, because there's so much good stuff in those reports, and they are fantastic for trend analysis. If you have never used your DB2 monitor to produce reports, do yourself a favor and figure out how to do that. It's not hard: check your monitor's report command reference (or batch reporting users guide -- different monitoring tools have differently-titled manuals), and you'll see JCL requirements (a key: point to the SMF data set that will provide the desired input to the monitor) and examples of SYSIN control statements (these specify report type, reporting time frame, and report interval, among other things). Run some of these reports, and check out the wealth of information therein.

I find that the two most useful DB2 monitor report types are the Accounting Report - Long and the Statistics Report - Long (that's what OMEGAMON for DB2 calls 'em -- for some other monitoring products these reports are referred to as Accounting Detail and Statistics Detail). Input to these reports are records generated by the standard DB2 accounting and statistics trace classes that most folks have running all the time (e.g., accounting trace classes 1, 2, and 3, plus classes 7 and 8 if you're interested in package-level accounting). I'll tell you about my favorite flavor of the Accounting Report - Long in just a moment, but first I want to mention a practice implemented at my shop when I worked in the IT department of a DB2-using organization: every day, an Accounting Report - Long and a Statistics Report - Long (each covering the previous days' 24 hours of activity) were "printed" to a data set on disk (more specifically, a GDG, which made it easy to keep a rolling X days of reports). 60 days of these reports were kept online, available for browsing via TSO/ISPF. Having these around was super for a couple of reasons: 1) if a problem situation started to crop up, we could look back over several weeks of data to see how things had been trending, and what might have changed; and 2) with the reports on-hand, we didn't have to keep 60 days of SMF records online (the reports summarize and greatly reduce these records).

Now, the Statistics Report - Long is great, and I may write more about that report in a future post, but for now I want to talk a little about the Accounting Report - Long (statistics reports show activity from the DB2 subsystem perspective, while accounting reports provide information from an application or workload point of view). An important specification when generating an Accounting Report - Long is the desired grouping of the accounting data. For an OMEGAMON for DB2 report, this data grouping is determined via the ORDER subcommand (this would be part of the SYSIN input to a report generation job). The default grouping for OMEGAMON for DB2 (and for other monitors I've seen) is primary authorization ID within plan name. I generally don't want data in the report grouped that way. What I most often like to see is a report generated with ORDER(CONNTYPE) (using OMEGAMON for DB2 lingo -- the terminology might be slightly different for other monitors). With ORDER(CONNTYPE), your Accounting Report - Long will have several sections, with one report component for each DB2 connection type. In other words, one part of the report will show all the CICS-DB2 activity on the subsystem, another part will have all the DRDA activity (that which comes through the DB2 DDF address space), another will show activity for all programs linked with the Call Attach Facility (typically, these are batch jobs), and so on.

If you do generate an Accounting Report - Long with data grouped by connection type, do a little exercise for me -- it could be something of an eye-opener for you. Take, for each part of the report (i.e., the CICS-DB2 part, the DRDA part, the CAF part, etc.), two fields (maybe three, as I'll explain), and get the product of these. The fields of interest are (and again, I'm using OMEGAMON for DB2 terminology -- this might vary somewhat from product to product) #OCCURRENCES in the HIGHLIGHTS section, and CP CPU TIME in the AVERAGE section (the latter under the "class 2" column in that report section, so called because it shows information from DB2 accounting trace class 2 records). Before getting the product of those two fields, check to see if the SE CPU TIME filed in the "class 2" column in the AVERAGE section contains a non-zero value (and note that depending on your monitor and your release of DB2, "SE CPU TIME" may be labeled "IIP CPU TIME). If it does, that's CPU time on a zIIP engine (an SE, or "specialty engine"), and it's NOT included in CP CPU TIME (which is just CPU time on general-purpose engines, or central processors -- CPs for short). Still with me? OK, so take the average class 2 CPU time per occurrence (that is, per accounting trace record), which is general-purpose CP CPU time plus zIIP, or SE, CPU time, and multiply that by the number of occurrences. What this gives you: the total application-chargeable CPU time consumed in SQL statement execution for the connection type. [I say "application chargeable" because some SQL execution-related CPU time is consumed by things such as prefetch reads and database writes, which are charged to DB2's address spaces and not to so-called allied address spaces -- but the "application chargeable" CPU time is almost always the large majority of total SQL statement-related CPU consumption.]

[If you have a DB2 data sharing group, you'll want to sum the SQL statement CPU consumption figures for each member of the group. In other words, add total CICS-DB2 class 2 CPU time for member DB2A to total CICS class 2 CPU time for member DB2B to the total for DB2C, and so on, for each connection type.]

To help clarify things, here is an excerpt from an OMEGAMON for DB2 Accounting Report - Long, with the fields I've mentioned highlighted:

   SUBSYSTEM: DB2A   ORDER: CONNTYPE INTERVAL FROM: 12/01/10 09:00:00.00
DB2 VERSION: V9     SCOPE: MEMBER    TO: 12/01/10 11:00:00.00

CONNTYPE: DRDA

AVERAGE       APPL(CL.1) DB2 (CL.2) ... HIGHLIGHTS
------------ ---------- ---------- ... --------------------------
ELAPSED TIME    0.031811    0.015938 ... #OCCURRENCES    : 2813092
NONNESTED      0.031361    0.015523 ... #ALLIEDS        :     7790
STORED PROC    0.000313    0.000277 ... #ALLIEDS DISTRIB:        0
UDF            0.000000    0.000000 ... #DBATS          : 2805272
TRIGGER        0.000138    0.000138 ... #DBATS DISTRIB. :       30
                                       ... #NO PROGRAM DATA:        0
CP CPU TIME     0.004754    0.004685 ... #NORMAL TERMINAT:    71402
AGENT          0.004754    0.004685 ... #DDFRRSAF ROLLUP:   276108
   NONNESTED     0.004614    0.004551 ... #ABNORMAL TERMIN:        0
   STORED PRC    0.000120    0.000114 ... #CP/X PARALLEL. :        0
   UDF           0.000000    0.000000 ... #IO PARALLELISM :        0
   TRIGGER       0.000020    0.000020 ... #INCREMENT. BIND:     1299
PAR.TASKS      0.000000    0.000000 ... #COMMITS        : 2823659
                                       ... #ROLLBACKS      :    74630
SECP CPU       0.000682         N/A ... #SVPT REQUESTS :        0
                                       ... #SVPT RELEASE   :        0
SE CPU TIME     0.003992    0.004067 ... #SVPT ROLLBACK :        0

I'll tell you why this information is of such interest to me: it shows the in-DB2 CPU cost (i.e., the CPU cost of SQL statement execution) of the DB2 workload by component, and that can be news to people. I could ask a DB2 person, "What's the largest component of your DB2 workload?" and that person might say, "Batch," or "CICS," because he or she thinks that's the case. Then we generate and take a look at a DB2 monitor Accounting Report - Long with data grouped by connection type, and we do the numbers in the aforementioned way (average class 2 CPU CPU time per occurrence -- ensuring that class 2 zIIP CPU time, if any, is added in -- times number of occurrences, for each connection type). People are sometimes surprised by what they see. Maybe the batch DB2 workload isn't king of the hill, after all. What often makes the biggest impression is the relative size of the DRDA workload (which I often refer to as the client-server DB2 workload). It's not unusual for this to be the fastest-growing part of an organization's overall DB2 for z/OS workload, and sometimes it's the largest component of the overall workload. One factor here: at more and more sites, the bulk of new DB2 for z/OS-related application development work involves applications running on off-mainframe application servers, directing SQL statements to a DB2 for z/OS database via DRDA and the DB2 DDF (these statements may take the form of JDBC or ODBC calls, and more and more frequently they include calls -- maybe LOTS of calls to DB2 stored procedures).

Another client-server DB2 workload growth factor: a growing number of organizations are providing users with query and reporting tools and allowing them to use these to access data in production, operational DB2 databases. That's right -- BI (business intelligence) work targeting production DB2 for z/OS tables. Yes, this can be done while OLTP and batch programs go against the same data. I've seen it, and it works (it can work particularly well when DB2 is running in data sharing mode on a Parallel Sysplex, and one or two members of the group are dedicated to decision support applications). The DDF connection? The query and reporting tools typically send SQL statements to DB2 using the DRDA protocol (done through DB2 Connect or via one of the IBM Data Server Driver packages).

Whatever the breakdown of your workload, knowing it can help you to see where the demand for SQL statement execution capacity is coming from, and that can help you to deliver support where its needed most (suggestion: try tracking the workload breakdown over time, perhaps presenting the trends graphically in a line chart, with different colored lines for the different components of the overall DB2 workload, or a series of pie charts, the latter with slices for the different workload components).

I'll try to post more entries in the future on other uses of the information that can be found in a DB2 monitor Accounting Report - Long and Statistics Report - Long. For now, look these reports over at your shop. Don't run 'em yet? Get started. You'll be glad you did.

DB2 for z/OS Roles: Trust Me on This One (Part 2)

2011-02-24T21:08:00.000-08:00

So, in part 1 of this two-part entry, which I posted a couple of weeks ago, I referred to a question someone had sent to the DB2-L discussion forum. The person asking the question wanted to know if the roles and trusted contexts functionality introduced with DB2 9 for z/OS could be used to provide DBAs in certain geographies with the privileges needed to get their work done, but in a way that would deny them access to data in user (versus system) tables. I wrote in my part 1 post that roles and trusted contexts could address part, but not all, of the requirement raised in the discussion forum question. In particular, a role could be used with a trusted context to limit the scope of the use a set of privileges to DBAs in one or more specific geographic locations -- this by associating the role with a trusted context having as attributes a set of IP addresses corresponding to servers located in the geographic area(s) in which the data-access-restricted DBAs work (alternatively, if the DBAs were restricted to using a certain batch job for issuing SQL statements, the trusted context could have as an attribute the name of that job).

What the role-and-trusted-context combination couldn't do, in and of itself, is satisfy the requirement that the DBAs in question be able to do their job without having -- just because they are DBAs -- access to data in user tables. More specifically, roles and trusted contexts wouldn't offer an easy way of addressing this need in a DB2 9 for z/OS environment. That's because restricting DBAs' access to user data is a privilege thing, not a role thing; thus, what you'd do to provide DBAs with the ability to do their job without having access to data in user tables would depend on the release of DB2 for z/OS you were running. If it were DB2 10, you could use a new option of the GRANT statement (I'm showing a GRANT to an auth ID -- you could also grant the privilege to a role):

GRANT DBADM WITHOUT DATAACCESS TO SMITH;

If you were running a DB2 release prior to DB2 10, accomplishing the same objective would require you to assign to the DBAs (directly or through a role) DBCTRL authority plus any additional privileges needed to do the job and not included with DBCTRL authority (e.g., ALTER), while withholding privileges such as SELECT. If you're wondering what privileges included with DBADM authority aren't included with DBCTRL, look in the DB2 Administration Guide (in the DB2 9 manual -- eighth edition, December 2010 -- the information is found in Figure 16, in the section titled "Administrative authorities" in Chapter 5).

Regarding the WITHOUT DATAACCESS clause that can be added to a GRANT DBADM statement in a DB2 10 for z/OS environment, note that this is applicable to the new (with DB2 10) "system" DBADM authority. System DBADM differs from "regular" DBADM authority in two important ways:

A system DBADM can manage ALL the databases in a DB2 10 subsystem, whereas the traditional DBADM authority (which is still available with DB2 10) is granted for a particular database or a list of databases.
The system DBADM authority, as previously noted, can be granted in a way that disallows access to data in user tables.

Speaking of restricting access to user data, DB2 10 also provides a new system privilege, EXPLAIN, that allows a user to issue EXPLAIN statements (and PREPARE and DESCRIBE statements) without having the authority to execute the statements being EXPLAINed. I like that a lot. It's a way to enable developers to check the access paths of statements in (for example) a production environment without having to first grant to those developers the SELECT and/or INSERT and/or UPDATE and/or DELETE privileges on target tables that they'd otherwise need in order to run EXPLAINs. Because the EXPLAIN system privilege enables you to expand the pool of people who can issue EXPLAINs without broadening access to data in tables, you can give more people that privilege without weakening data security, and with more people able to issue EXPLAINs you'll have a better chance of catching potentially poorly-performing access paths before programs issuing the SQL statements go "live."

Now, on top of the application performance benefits you've probably heard about, you have another incentive to get to DB2 10: better management of data access privileges. Lock that data down, folks!

DB2 for z/OS Roles: Trust Me on This One (Part 1)

2011-02-10T23:42:00.000-08:00

Although roles and trusted contexts have been on the mainframe DB2 scene for over three years (they were introduced with DB2 9 for z/OS), there is still some misunderstanding in the DB2 community regarding what these features are and how they can be used. That point was underscored by a question posted a few weeks ago to the DB2-L forum. The person asking the question had a need to provide DBAs in certain geographies with the privileges needed to get their work done, but in a way that would deny them access to data in user (versus system) tables. Could DB2 roles address this need? The answer to that question is, "Partially." That is to say, roles could provide part of the solution the questioner was seeking, though maybe not the part that she had in mind. In the rest of this entry I'll try to clear the air with respect to the meaning and utility of roles (and their relatives, trusted contexts). In a follow-on Part 2 entry, I'll address the challenge of enabling a DBA or system administrator to do his or her job whilst preventing that person from being able to access data in user tables.

First, it's important to understand that the introduction of roles and trusted contexts did not introduce any new DB2 privileges. Rather, this new security capability gives you a new way to assign privileges (via roles, instead of directly to users' IDs) and to limit the scope of the exercising of those privileges (by restricting their use to trusted connections that conform to defined trusted contexts). The ability to manage DB2 security in this way can be particularly welcome when you're faced with a common client-server computing scenario: an application, which may run on a Java or a .NET or some other such application server, issues SQL statements that are executed through DB2's Distributed Data Facility (DDF). Individual users authenticate at the app server, but the application itself presents to DB2 a generic authorization ID and password that are hard-coded in a program. If the SQL statements are dynamically prepared at the DB2 for z/OS server (as is typically the case for programs that use database interfaces such as JDBC or ODBC or ADO.NET), the application's generic authorization ID has to be granted table privileges (SELECT, INSERT, UPDATE, DELETE) on target objects to enable successful statement execution. Trouble is, any number of programmers might know the application's DB2 authorization ID and password (because, as mentioned, these are embedded in program code), and someone might use that ID and its privileges to access data in the database from outside the application. Security is weakened in that case, and the result could be unauthorized viewing and changing of data values in DB2 tables.

Enter roles and trusted contexts. In a DB2 9 (new function mode) or DB2 10 environment, instead of granting a set of privileges required to execute an application's dynamic SQL statements to a generic authorization ID, you could grant the privileges to a role. Now, here's another important point: granting privileges to a role and then stopping there accomplishes next to nothing. Why? Because DB2 has no way of knowing who can use the role's privileges and under what circumstances the role can be used. That's where a trusted context comes in. You could define a trusted context that would limit the exercise of a role's privileges to users connecting to DB2 from a particular application server (identified by an IP address), using an application that provides to DB2 a particular authorization ID (referred to as a "system" auth ID). Because the privileges needed to execute the dynamic SQL statements issued by the application are assigned to a role and not to an ID, the application's generic authorization ID is of no use unless that ID can have the privileges of the aforementioned role, and it can have those privileges only when the connection to DB2 is from the app server whose IP address is an attribute of the trusted context that specifies the conditions of the role's utilization; thus, security is way tighter than it would be if the application's generic ID had privileges that could be exercised regardless of the "come from" connection type.

That's pretty cool, but you can go further still in protecting your DB2 data from unauthorized access. You see, in addition to restricting the exercise of a role's privileges to (for example) connections from a particular application server through an application that provides a particular "system" auth ID to DB2, you can set things up so that only certain individual user IDs can use the role in the defined connection context. You might be wondering how that's done, given a situation in which an application provides a generic authorization ID to DB2. Actually, you have a couple of choices here:

If you use IBM's WebSphere Application Server, you can set the database property ‘propagateClientIdentityUsingTrustedContext’ to ‘true’.
There are APIs for JDBC (e.g., getDB2Connection), CLI (e.g., the SQL_ATTR_TRUSTED_CONTEXT_USERID attribute and the SQLSetConnectAddr function) and .NET (the connection string keyword UserID corresponds to the end user) that enable an application to establish a trusted connection to DB2 and to reuse a trusted connection with different end-user IDs.

Not only does this functionality enable you to restrict a role's privileges to particular users of a particular trusted connection, it also enables you to get DB2 (and RACF) audit information that contains end user's individual IDs, even when those users are connecting to DB2 through an application that itself provides a single generic authorization ID to DB2.

Now, a few additional items of information about trusted contexts:

I have been using the example of an off-mainframe application server that provides a generic authorization ID to DB2, but a trusted context can also be defined for a local connection to DB2 through a batch job or a started task.
Restricting the use of a role to individual IDs using a trusted connection is done through the WITH USE FOR clause of the CREATE TRUSTED CONTEXT statement (and the ADD USE FOR and DROP USE FOR and REPLACE USE FOR clauses of ALTER TRUSTED CONTEXT). In this way you can even assign different roles to different users of a defined trusted connection -- just keep in mind that your app server needs to be able to propagate individual user IDs to DB2, or your application needs to use the previously mentioned APIs to send individual user IDs to DB2.
When you omit the WITH USE FOR clause of CREATE TRUSTED CONTEXT, it is as though you'd specified WITH USE FOR PUBLIC WITHOUT AUTHENTICATION. This means that the privileges of the role associated with the trusted context can be used by any individual that (for example) connects to DB2 from a particular application server through an application that provides DB2 with a particular system auth ID. In some cases, this will be a plenty good set-up.
A trusted context can be set up so as to make the context's default role the owner of any object created using the role's privileges.
A DB2 for z/OS requester can use a trusted context (and can switch use of an existing trusted connection to different individual user IDs) based on entries in the requesting DB2's Communications Data Base.
When a user establishes a trusted connection with a DB2 subsystem (a connection that's in accordance with a defined trusted context), he or she has the privileges of the associated role PLUS any privileges granted directly to his or her ID. The point here is that roles and trusted contexts are effective in limiting the exercise of privileges if those privileges are not widely granted to users' DB2 auth IDs.

Recall that the DB2-L question-asker to whom I referred at the start of this entry was interested in restricting DBAs' access to data in user tables, based on the geographic location of said DBAs. The geography-specific aspect of this requirement could potentially be addressed through the use of roles and trusted contexts. The privileges that would let a DBA be a DBA -- but without access to data in user tables -- could be granted to a role, and that role could be associated with a trusted context that would have as attributes a set of IP addresses of servers located in the geographic area in which the data-access-restricted DBAs work. Alternatively, if these DBAs were restricted to using a certain batch job for issuing SQL statements, the trusted context could have as an attribute the associated job name.

But what about that restriction concerning the DBAs' access to data in user tables? How can that be implemented? With DB2 9 for z/OS, it would be a bit of a chore. In a DB2 10 environment, it would be easy. I'll fill you in on this and some other new DB2 10 security-related features (including some that pertain to trusted contexts) in my next entry. Look for that Part 2 post in a week or so.

A Note About IBM's DB2 .NET Data Provider

2011-01-26T15:54:00.000-08:00

A lot of people like to use DB2 (on whatever platform -- mainframe, Linux/UNIX/Windows, IBM i) as a data server for .NET applications running on Windows servers. IBM facilitates this architecture with the DB2 .NET Data Provider, which extends DB2 support for the ADO.NET interface. The DB2 .NET Data Provider is included with several of IBM's data server client and driver offerings (more on this momentarily). A DB2 for z/OS DBA friend of mine recently asked, "How can I tell if the DB2 .NET Data Provider is 'there' on an application server?" As it turns out, there's an app for that, and I'll tell you about it in this post (many thanks to Brent Gross, a senior member of IBM's DB2 development organization, who told ME about this).

The app to which I refer is called testconn. It's a .NET application that ships with all IBM client packages. testconn will actually drive a DB2 connection though the .NET layer. There are versions of testconn for each .NET Framework: 1.1, 2.0, and 4.0. To run testconn for a database (e.g., the sample database) that is local to the app server, you'd type the following (if you were looking to verify that the IBM DB2 Data Provider for .NET Framework 2.0 is installed on the server):

testconn20 database=sample

What you specify after testconn20 (or testconn40 or whatever) is a .NET connection string. The testconn tool will use that string to connect to the target database. If all you want to do is check to see if the driver is there, you can use any name for the database. If it's not a valid database name, testconn will report an error indicating that it cannot connect to the database, but it will first print out all of the driver information. Here is an example of a testconn execution for a remote database:

testconn20 database=robdb;server=myserver.com:50000;userid=robert;password=freddy

Here is some sample output from an execution of testconn:

E:\>testconn20 database=sample

Step 1: Printing version info
        .NET Framework version: 2.0.50727.3615
        DB2 .NET provider version: 9.0.0.2
        DB2 .NET file version: 9.7.3.2
        Capability bits: ALLDEFINED
        Build: 20100823
        Factory for invariant name IBM.Data.DB2 verified
        Factory for invariant name IBM.Data.Informix verified
        IDS.NET from DbFactory is Common IDS.NET
        VSAI assembly version: 9.1.0.0
        VSAI file version: 9.7.0.489
        Elapsed: 0.5

Step 2: Validating db2dsdriver.cfg against db2dsdriver.xsd schema file
        Elapsed: 0.015625

Step 3: Connecting using "database=sample"
        Server type and version: DB2/NT 09.07.0003
        Elapsed: 1.890625

Step 4: Selecting rows from SYSIBM.SYSTABLES to validate existance of packages
   SELECT * FROM SYSIBM.SYSTABLES FETCH FIRST 5 rows only
        Elapsed: 0.21875

Step 5: Calling GetSchema for tables to validate existance of schema functions
        Elapsed: 0.40625

Test passed.

And there you have it. So, if in doubt: testconn.

Now, I mentioned that the DB2 .NET Data Provider is included with a number of IBM's data server clients and drivers. You can get more information about these offerings at this url:

http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.swg.im.dbclient.install.doc/doc/c0023452.html

I'll tell you that the preferred client package for you is likely to be the IBM Data Server Driver Package (also known as the ds driver). It's lightweight and easy to distribute.

Got DB2? Got .NET apps? They go great together. Check it out, if you haven't already.

DB2 for z/OS: CATMAINT and Concurrency

2011-01-11T17:28:00.000-08:00

In the years since mainframe DB2 data sharing was introduced with DB2 for z/OS Version 4 (mid-1990s), I've done a lot of presenting and writing about the technology (e.g., a blog entry from a couple of years ago that provided an overview of the topic). From the get-go, one of the primary benefits of DB2 data sharing was the opportunity to achieve ultra-high availability. A lot of people have long understood that when you have DB2 operating in data sharing mode, planned outages for purposes such as software maintenance upgrades can be virtually eliminated: you apply fixes to your DB2 load library, then quiesce work running on member X of the data sharing group (allowing work to continue flowing to other members), then stop and restart member X to activate the maintenance, then resume the flow of application work to member X, then quiesce work on member Y, and repeat the preceding steps until all members are running the updated DB2 code (as this "round-robin" process progresses, the group runs fine with some members at maintenance level "n" and some at level "n+1").

Still, there was this widely accepted notion that DB2 data sharing wouldn't deliver 24X365 availability every year, because during some years (generally speaking, once every two or three years) you'd migrate the data sharing group to a new release of DB2, and you'd need a group-wide outage to do that -- right? One of the steps involved in migrating a DB2 system to a new release of the code is the running of an IBM-supplied job, after initially starting DB2 at the new release level, that executes the CATMAINT utility. CATMAINT effects some structural changes to the DB2 catalog and directory (some new tables, some new columns in existing tables, some new and/or altered indexes on tables), and you can't have DB2-accessing application work running while THAT'S going, can you? These concerns linger in the minds of plenty of mainframe DB2 people to this day, but I'm here to tell you that they shouldn't. You CAN keep your application workload running in a DB2 data sharing group, even through an upgrade to a new release of DB2.

Here's the deal: a long time ago (and I'm talking like early 1990s), it WAS recommended that you not run application work while running CATMAINT to update the DB2 catalog and directory to a new-release structure. That was OK with most folks. After all, you had to stop the application workload anyway during a DB2 migration, because you'd of course stop your DB2 Version N subsystem and then start DB2 at the Version N+1 release level. With the flow of DB2-accessing work temporarily stopped anyway, why not leave it stopped just a little longer while CATMAINT did its thing (typically well under an hour -- and CATMAINT elapsed time went down significantly starting with the DB2 V8 to V9 migration process).

Along came data sharing, and implementers of this technology by and large stayed with the old practice of not having application work running during CATMAINT execution (and throughout this entry, I'm referring to CATMAINT being used to effect catalog and directory changes as part of a DB2 release migration, as opposed to the other CATMAINT options introduced with DB2 9 to facilitate large-scale changes of VCAT or OWNER or SCHEMA name for objects in a DB2 database). Many people just didn't think about doing otherwise, but as the need for super-high availability became increasingly prevalent, more and more DB2 administrators started to explore the possibility of running DB2-accessing application programs during CATMAINT execution, and found that it was indeed technically possible. As time goes by, continuing the flow of application work during CATMAINT execution is becoming more common at DB2 sites, especially at sites running DB2 in data sharing mode. 24X365Xn (with "n" being greater than 1 and including years during which DB2 is migrated to a new release) really is possible with a DB2 data sharing group.

Now, is this deal completely catch-free? Not entirely. Because CATMAINT does change some objects in the DB2 catalog and directory, it can make these objects temporarily unavailable (the particular changes made by CATMAINT will vary, depending on the DB2 release to which you're migrating). The duration of that unavailability is likely to be pretty brief, particularly so since the big CATMAINT speed-up delivered with DB2 9, but if an application program happens to require access to one of these objects while it's being changed by CATMAINT, the program could fail with a timeout or "resource unavailable" error code. Similarly, the CATMAINT utility itself could fail due to contention with application work. If that happens, it's NOT a disaster: you'd terminate the job with the TERM UTILITY command and then re-execute CATMAINT from the beginning (you'd actually resubmit migration job DSNTIJTC, which executes CATMAINT).

So, some disruption is possible. To minimize conflict, consider the following:

Run CATMAINT during a period of relatively low application workload volume. At some sites, batch activity is halted during the time of CATMAINT execution, so that only online programs are accessing DB2 (this can be quite do-able, as the batch suspension may have to be in effect for only a few minutes).
Avoid executing DDL statements (e.g., CREATE TABLE) while CATMAINT is running.
Avoid package bind and rebind actions while CATMAINT is running.

These same guidelines apply with respect to running application work during the execution of the CATENFM utility, which makes catalog and directory changes necessary for the migration of a DB2 for z/OS subsystem from Conversion Mode to New Function Mode within a release level.

The bottom line: if you have a DB2 data sharing group and you're thinking that you'll have to stop application access to DB2 as part of the migration to a new release of the DBMS, think again. You CAN keep an application workload going while CATMAINT is running on one of the members of the data sharing group (and that includes running application work on the member on which CATMAINT is executing), and you can do the same with regard to CATENFM execution. If you want to take a brief application outage (referring to DB2-accessing programs) while CATMAINT is running, you can of course do that. Just know that you don't HAVE to. Figure out what's appropriate for your organization, and proceed accordingly.

DB2 for z/OS: KEYCARD Gets its Due

2010-12-30T13:25:00.000-08:00

By now, you've probably seen and/or heard a good deal of information about DB2 10 for z/OS, which was announced and became generally available this past October. There is indeed a lot of big news associated with this latest release of IBM's mainframe relational database management system: reduced CPU costs, support for temporal data (tables with system and/or business time dimensions), a huge increase in the number of threads that can be concurrently active, a migration path to universal tablespaces, SQL Procedure Language enhancements (better performance, plus the ability to write user-defined functions using SQLPL), table-based data access control policies, and lots more (and I do mean "lots" -- check out the DB2 10 "What's New" manual). Amongst all the "wow" features of DB2 10 are some nice little nuggets of functionality that will positively impact system performance and administration. One of these -- and the subject of this blog entry -- concerns the KEYCARD option of the RUNSTATS utility.

Here's the story in a nutshell: with DB2 10, the KEYCARD option is no longer optional. That is to say, if you run the RUNSTATS utility with an INDEX specification in a DB2 10 environment, and you have indexes with more than two key columns defined on the table (or tables) in the target tablespace, you will get the catalog statistics associated with KEYCARD, regardless of what you specify on the utility control statement. You can still put KEYCARD in this statement, but the keyword will be ignored because it "is now built into the normal execution of the RUNSTATS INDEX utility and cannot be disabled." Trust me: this is a good thing. It's a change that leaders on the optimizer team in IBM's DB2 development organization have lobbied for for quite some time. I'll explain why, and I'll tell you what this means for you if you're not yet running DB2 10 (and that's most of you, as, again, DB2 10 has only been generally available for a couple of months).

First, a little background. KEYCARD (which is only valid in the context of an INDEX specification on a RUNSTATS utility control statement) goes way back. I'm not sure when it was introduced as a RUNSTATS option, but I think that it might have been delivered with DB2 for z/OS Version 4 (mid-1990s). What does it do? Pretty simple: it causes RUNSTATS to determine the number of distinct values of combinations of an index's first "n" key columns, where "n" is greater than 1 and less than the total number of the index's key columns (I say this because the cardinality -- the number of distinct values -- of the index's first key column and of the full key are gathered anyway and placed in the FIRSTKEYCARDF and FULLKEYCARDF columns, respectively, of the SYSIBM.SYSINDEXES catalog table). The values obtained via the KEYCARD specification are placed in the SYSIBM.SYSCOLDIST table.

An example might be helpful here. Suppose you have a 10,000-row table containing data about customers in a particular country (one row per customer), and on that table you have an index with a key comprised of the columns STATE, CITY, and POSTAL_CODE. Suppose further that you have customers in fifty cities, with each city being in a different state (i.e., one city in each of 50 different states -- admittedly, this is a rather contrived example). Finally, assume that there are 200 different zip code values in the table, and that the duplicate values of STATE, CITY, and POSTAL_CODE are evenly spread across the table's rows (for information on non-uniform distribution of non-unique column values, check out an entry I posted last year on that topic in my old Catterall Consulting blog). Without KEYCARD specified, an execution of RUNSTATS will generate (as previously mentioned) FIRSTKEYCARDF (50) and FULLKEYCARDF (200) statistics for the index. With KEYCARD specified, RUNSTATS will also determine the number of distinct values of the index's first two columns: in this case, that's 50 -- the same as the FIRSTKEYCARDF value (if the index had 4 columns, KEYCARD would result in a determination of the number of distinct values of the combination of key columns 1, 2, and 3, as well as the number of distinct values of key columns 1 and 2).

Why is getting this statistical data important? Two words: column correlation. See, if you use what is a fairly common "base" RUNSTATS control statement, namely, TABLE(ALL) INDEX(ALL), DB2 will gather cardinality stats for every column of every table in the target tablespace. It will know, then, that there are 50 distinct values in the STATE column and 50 distinct values in the CITY column. What DB2 doesn't know is that the STATE and CITY values are highly correlated (CITY value 'Los Angeles' is paired with 'California', but not with any other value in the STATE column). Not knowing this, DB2 assumes that the values in the CITY column are independent of STATE values. Consider a query with the compound predicate below:

WHERE STATE = 'California'
AND CITY = 'Los Angeles'

Absent column correlation data, DB2 will estimate the number of qualifying rows by multiplying the cardinality of the STATE column by the cardinality of the CITY column; so, the estimate will be (1/50) * (1/50) * 10,000 rows = 4 rows. In fact, because STATE and CITY values are very highly correlated, the number of qualifying rows will be (1/50) * 10,000 = 200 (again, I'm assuming a uniform distribution of duplicate STATE and CITY values in the table's rows). Lack of correlation stats for the STATE and CITY columns results in a filtering estimate that is off by a factor of 50. When a filtering estimate is way off from reality, the access path chosen by the DB2 optimizer for a query could be sub-optimal, and that path may cause the query to run a lot longer than it needs to. KEYCARD gives DB2 more column correlation information to use in estimating the number of rows qualified by a query's predicates, and better information leads to better access path choices (and better query performance, which of course is the bottom line).

So, why not have RUNSTATS gather correlation stats for all combinations of all columns in a table? That could be done, using COLGROUP specifications for groups of non-indexed columns, but the CPU and run-time costs of doing this would be very high unless the table had very few columns and not many rows (plus, coding all the COLGROUP specifications for a table with a lot of columns would be extremely tedious). In most cases, it just isn't a practical option. Making KEYCARD automatic in DB2 10 is a good idea because it generates data that has significant query optimization value (additional column correlation information for indexes with keys comprised of more than two columns) and does so at a low cost in terms of RUNSTATS CPU and elapsed time (this because the column groups for which correlation data is obtained are "leading and contiguous" subsets of multi-column index keys, making the correlation data cheaply available by way of a table's indexes, in which key values are always strictly ordered).

I'll conclude with a word to the wise: don't wait for DB2 10 to make KEYCARD automatic in your environment. Go ahead and add this option to your RUNSTATS INDEX specifications. If, for example, your basic RUNSTATS control statement includes

TABLE(ALL) INDEX(ALL)

I'd recommend changing that to

TABLE(ALL) INDEX(ALL) KEYCARD

You'll enrich your catalog stats at a low incremental cost, and you'll have taken a step towards smoothing the path to DB2 10, as you'll have in your catalog the correlation stats that will be automatically generated in the DB2 10 environment.

REORG and DB2 for z/OS Partition-by-Growth Tablespaces

2010-12-02T14:26:00.000-08:00

A DB2 DBA friend of mine recently told me of a problem he'd encountered in reorganizing a single partition of a partition-by-growth (PBG) tablespace: the online REORG job failed with an out-of-space condition pertaining to the partition's shadow data set. The topic of partition-level REORGs of PBG tablespaces is one that I find interesting; thus, this post, in which I'll explain why my friend hit that out-of-space situation and how he resolved it. I'll also provide some additional items of related information that I hope you'll find to be useful.

I blogged a couple of times about PBG tablespaces (introduced with DB2 9 for z/OS) while I was working as an independent DB2 consultant, in one entry comparing them with partition-by-range (PBR) tablespaces and in another briefly mentioning them among several new physical database design options provided by DB2 9. One advantage of a PBG tablespace versus a traditional segmented tablespace (PBG tablespaces are also segmented) is the ability to run utilities at a partition level (the exception to this rule being LOAD, which has to run at the tablespace level for a PBG tablespace). On the surface, the option of REORGing a single partition of a PBG tablespace looks pretty attractive, but there is a catch, and it does have to do with space, as my DBA friend discovered.

Here's the deal: by definition, a new partition for a PBG tablespace won't be allocated unless the tablespace's existing partitions are full. So, if you run an online REORG for, say, partition 4 of a ten-partition PBG tablespace, that partition is likely to be quite full (depending on delete activity, which might have created some "holes" in pages of the partition, and on the table's clustering key, which would influence placement of newly inserted rows -- more on that momentarily). If the tablespace was created with non-zero values for PCTFREE and/or FREEPAGE, the REORG utility will attempt to reestablish that free space, and if the partition's data rows and the reestablished free space will not fit in the partition's shadow data set, the REORG job will fail. That was the situation that my friend brought to my attention. What did he do? He resolved the problem by setting the value of a relatively new ZPARM parameter, REORG_IGNORE_FREESPACE (introduced last fall via the fix for APAR PK83397), to YES. When the value of REORG_IGNORE_FREESPACE is YES (the default is NO), REORG TABLESPACE will ignore PCTFREE and FREEPAGE values when reloading data rows into a PBG tablespace if either of the following is true:

The utility is reorganizing a subset of the tablespace's partitions
The table in the PBG tablespace has one or more LOB columns

If free space is not reestablished, the rows unloaded from a partition will almost certainly fit into the shadow data set during a REORG. [I say "almost" because for a table with varying-length rows (and rows in a compressed tablespace are varying-length even if there are no VARCHAR columns), I can imagine a very unusual scenario in which rows unloaded from a partition might not fit back in after being sorted in clustering key sequence, even with free space parameter values ignored. So, REORG_IGNORE_FREESPACE = YES should take chances of an out-of-space condition on REORG or a partition of a PBG tablespace way down, if not all the way to zero.]

Now, you might be thinking, "Why is there even the possibility of this out-of-space condition when one partition of a PBG tablespace is being reorganized? Can't REORG take rows that won't fit back into the target partition after reestablishing free space and put them in other partitions that have unused space?" The fact of the matter is that REORG doesn't work that way -- it doesn't move rows from one PBG partition to another unless the whole PBG tablespace is being REORGed or a range of the tablespace's partitions are being reorganized. To expand on that italicized point: if partitions 4 through 8 of my example ten-partition tablespace are being reorganized (i.e., if the utility control statement has PART 4:8), REORG can freely move rows across partitions 4, 5, 6, 7, and 8 (unless the DB2 version is 9 and the table in the tablespace has one or more LOB columns, in which case a row has to be reloaded into the partition from which it was unloaded -- a restriction removed with DB2 10): after sorting the rows unloaded from the five partitions in clustering sequence, REORG will fill one partition before moving to the next.

What this means: if you want to REORG a subset of a PBG tablespace's partitions, and you want free space to be reestablished by the utility, it may be better to REORG a range of several partitions instead of targeting a single partition. That way, unused space in one or more partitions in the range can enable restoration of free space in other, more-full partitions with less of a chance of out-of-space being an issue.

As a coda, I'll tell you that the DBA to whom I've referred repeatedly in this entry ended up asking whether it was even worth it to reorganize partitions of the table in question. That is a very good question to ask. It's easy to get into a REORG-if-it's-unclustered mindset, but in fact some tablespaces don't need to be REORGed. The one with which the DBA was dealing gets plenty of inserts but is rarely read, and when it is read, performance just has to be OK -- it doesn't have to be great. CPU resources in such a case can be saved by cutting way back on REORG frequency (if not eliminating REORGs altogether) and optimizing insert performance either by switching to a continuously-ascending clustering key or by altering the table with the APPEND YES option introduced with DB2 9 (this causes DB2 to ignore clustering when inserting or loading data into the table). Bottom line: don't REORG a tablespace (or partitions thereof) "just because." Make sure that the benefits of reorganizing an object justify the CPU (and disk space) used during REORG processing. While keeping an object well organized is usually a good idea, in some situations disorganization really isn't a big deal. Think it through, and act accordingly.

An Update on the LASTUSED DB2 Catalog Column

2010-11-16T18:27:00.000-08:00

In a couple of entries that I posted to the blog I maintained while working as an independent DB2 consultant (one written this past summer, and the other in the spring of 2009), I mentioned the LASTUSED column of the DB2 for z/OS catalog table SYSIBM.SYSINDEXSPACESTATS. This column was introduced with DB2 9 for z/OS (indeed, the SYSINDEXSPACESTATS table itself was introduced with DB2 9 -- it's the moved-to-the-catalog version of the formerly user-defined INDEXSPACESTATS table, which holds index-related real-time statistics). LASTUSED provided -- finally -- an easy way to identify indexes that are just taking up space and which, therefore, should be candidates for being dropped (and potentially replaced with indexes that actually help query performance). You don't think that finding useless indexes was a challenging task before LASTUSED? It wasn't, if all of your SQL was static -- you'd just look at the SYSPACKDEP catalog table to see if any packages were dependent on a given index. That technique, however, didn't help in environments in which dynamic as well as static SQL statements were executed. How could you determine whether or not an index helped the performance of dynamic SQL statements? Believe me, this was no easy thing. LASTUSED changed that situation. It shows the last date on which an index was used by an SQL DML statement (a static or dynamic SELECT, FETCH or "searched" UPDATE or DELETE -- the latter term referring to UPDATE and DELETE statements containing one or more predicates) or for referential integrity constraint enforcement purposes (you wouldn't want to be searching for foreign key matches without an index on said foreign key). The column has a default value, and if you see that default value in a row for an index in SYSINDEXSPACESTATS, chances are that index is not boosting the performance of any SQL statements.

It's that default value that I want to talk about. See, a whole lot of mainframe DB2 people (including myself, up until about two days ago) think that the default value of the LASTUSED column is '0001-01-01' (i.e., January 1 of the year 1). Believe it or not, the default value of LASTUSED has NEVER been '0001-01-01'. It is, and always has been, NULL. So, if you're running DB2 9 in New Function Mode, and you do a SELECT on SYSIBM.SYSINDEXSPACESTATS, and you see '----------' in the LASTUSED column for an index (a string of dashes is the typical representation of the null value when you query the catalog using a tool such as SPUFI), that's an index that hasn't been used lately to performance-enhancing effect (but give it a little more time, and then do a little checking around before dropping the seemingly useless index, just to make sure that it really isn't needed).

I'll close this entry with an interesting piece of related information: DB2 9.7 for Linux, UNIX, and Windows delivered the LASTUSED catalog column for users of that DB2 platform (it's a column of several catalog views, including SYSCAT.INDEXES). The default value of the column? '0001-01-01'. Gotta love it.

DB2 Dispatch from Vegas: IOD, Day Four

2010-10-29T07:27:00.000-07:00

I only got to a couple of sessions on this, the last day of IBM's 2010 Information on Demand conference (held in Las Vegas), but they were good ones. Below are some of the highlights of each.

Back to the future: early experiences with temporal data processing in DB2 10 for z/OS. In this session, Shawn Gravelle, IT architect at State Farm (a large insurance company), talked about State Farm's work in testing the new temporal data support delivered by DB2 10. This feature enables DB2 to automatically manage data based on system time (basically, maintaining prior versions of a table's rows as well as the current version), and to facilitate the management of data with a business time perspective (referring to a time period during which data in a row is in effect -- a period that could be in the future). System time and business time can be combined for a table to provide bitemporal data management capabilities.

Among the items of information provided by Shawn during his session:

State farm has a LOT of data in their system. As in, more than 5000 terabytes on SAN storage. As in, more data than you'll find in the Library of Congress (for readers outside the US: that's a VERY big library).
Temporal data support is largely about productivity. It doesn't allow you to do what you couldn't do before. Instead, it allows you to maintain row history and to account for business dates (again, that's "in effect as of such-and-such a date" information) with a lot less SQL -- in application code and/or triggers and/or stored procedures -- than before. That means that functionality can be implemented more quickly than before -- and that's good for business. [Note that temporal data support simplifies SQL used for data retrieval, as well SQL used to change data values.]
Temporal data support can also be a performance-booster. Shawn mentioned that some processes ran quite a bit more quickly -- in one case, twice as fast -- when DB2 10 temporal data capabilities were used versus having the same functions handled by user code (implemented in the form of triggers and such).
Implementing system-time support is easy. That's true whether the table in question is new or an existing object.
Implementing business-time support can be a more challenging endeavor. It's a more complicated concept. As mentioned above, a row could be inserted into a table with an "in effect" business date that's sometime in the future. The row is in one sense current (it's the most recent version, from a system perspective), but in a business sense, it isn't (in other words, the information in the row is not yet active with respect to a policy or a promotion or whatever it represents). Business time can have implications for logical and physical database design, primary keys, and the organization's data archive strategy. With all this said, business time is a powerful capability -- you just want to put plenty of thought into using it. Another point: business time can be implemented for existing tables, but it can be easier to work it into a new application database design.

The IBM Smart Analytics Optimizer query engine. This session was delivered by IBM's Vijayshankar Raman. The Smart Analytics Optimizer (SAO) is, as Vijay put it, "a network-attached accelerator for DB2 for z/OS." What does it accelerate? Queries -- particularly OLAP-type queries (referring to online analytical processing) that involve aggregation and maybe roll-ups, and which scan a lot of data. It attaches to a mainframe system running DB2 for z/OS. A DB2 DBA defines a mart (a potion of the larger DB2 database), tables in the mart are copied to the SAO, and the accelerator does the rest.

Some of the points made by Vijay during his session:

The SAO can speed the execution of many queries by 10 to 100 times. This thing really screams. One of the keys to the SAO's ability to majorly accelerate "big query" execution is the fact that it operates on compressed data. I'm not talking about compressed as in DB2 compressed tablespaces -- rows from tables in such tablespaces are decompressed for query processing. Vijay noted that in the SAO, "most operations are performed on encoded [i.e., compressed] values." And the degree of compression achieved is enormous -- generally in the range of 8 to 40 times with respect to space reduction.
The SAO delivers consistent response times for complex, large-scale queries. Most queries directed to the system (and that's an automatic routing) will complete in 10-20 seconds.
The SAO is disruptive technology that is not disruptive to the DB2 query environment. In putting the SAO to use, there will often be no need to change SQL or applications currently running on the mainframe DB2 system.
Lots of engines, lots of memory. The SAO hardware is a set of blades installed in IBM blade centers. There can be up to 56 blades in one SAO, and each blade has 48 GB of main memory. In-memory processing (the SAO is a "cache-aware" system) is another part of the SAO performance picture.
The star schema angle: Generally speaking, the tables in the data mart copied to the SAO will be arranged in a so-called star schema. If the dimension tables in a star schema are particularly large relative to the associated fact table, query acceleration may be a little less dramatic than it would be for a star schema with dimension tables that are relatively small compared to the fact table.
Not all queries are supported. In many cases, well over 50% of the queries targeting the tables in a mart copied to the SAO will be supported for execution on the SAO (others would be executed by DB2 on the mainframe -- recall that the mart is copied to the SAO, as opposed to being relocated to the SAO). IBM has a tool that can be run on a mainframe DB2 system to estimate the percentage of existing queries that could execute on an SAO.
The SAO does its thing without the need for a "performance layer" over the tables in the mart. By "performance layer," Vijay meant indexes and materialized query tables. SAO just scans data in tables, but it does it very smartly (using techniques such as partition elimination and simultaneous application of all of a query's predicates), and in a highly parallel, in-memory-leveraging way. The SAO will rewrite a join, by the way, as multiple scans.

I'm certainly hoping to get the chance to work with a Smart Analytics Optimizer myself in the near future. And, I'm looking forward to next year's Information on Demand conference. I hope that a lot of you will be able to make it to that event.

DB2 Dispatch from Vegas: IOD, Day Three

2010-10-27T18:50:00.000-07:00

Today was a little different for me versus days one and two of IBM's Information on Demand conference in Las Vegas. I attended only one session -- the one in which I presented. Aside from that, my time was spent in meetings and in the Expo Hall. Tomorrow (the last day of the conference) it'll be back to regular session attendance for me.

This entry will be a little on the brief side. I'll provide some of the key points I made in my session, and I'll share information from a meeting I had that may be of interest to people. The overall theme of today's activity for me was business intelligence.

Mainframe DB2 data warehousing. I started my session by going over five factors that I believe are behind the increased incidence of organizations running data warehouses on a DB2 for z/OS platform:

Business intelligence applications have become mission-critical. Companies rely more and more on their data warehouse systems to fuel their decision making. These systems have to be highly available, and System z and z/OS set the standard for up time (the mainframe availability story is more impressive still when several systems are configured in a DB2 data sharing group on a parallel sysplex cluster.
"Operational" BI is on the rise. Here I'm referring to a data warehouse workload that is a mix of complex, long-running queries and simpler data requests that users expect to complete in a few seconds. Effectively managing performance for such a mixed workload is a challenge on some platforms, but not on the mainframe. The workload management capabilities of the z/OS operating system are second to none.
Data warehouses are being updated more frequently. The old pattern of query by day, ETL (extract/transform/load) by night no longer holds true at a number of sites. To an increasing extent, data warehouse users want source data changes to be more quickly reflected in the data warehouse database (sometimes the requirement is for data warehouse updates to be made in close to real time with respect to source data changes). When more-frequent updating is required, it can make sense to locate the data warehouse closer to the primary source system, and very often that primary source system is a mainframe.
Highly robust data access controls are a must. Data warehouses generally contain a lot of very sensitive data, and protecting this data from unauthorized access is an imperative. Take the security features of z/OS, the advanced capabilities of the RACF security management subsystem (or an equivalent third-party product), and DB2's own controls (made more comprehensive in DB2 9 with roles and trusted contexts, and with the new authority levels introduced with DB2 10), and put them together, and you have a platform that stands out from the crowd in terms of its data lock-down capabilities.
Mainframe people and processes complete the picture. Generally speaking, at sites around the world, the people who support mainframe systems are a highly experienced, "been there / done that" crowd. The processes they've developed over the years around change management, capacity planning, performance monitoring and tuning, and business continuity are rock-solid. Companies trust their mainframe support staffs with their most critical applications and databases, and that's driving BI work to the System z platform.

I then proceeded to a discussion of threes, covering my three favorite features of DB2 9 for z/OS (the release that most sites are running) in the areas of data warehouse performance, data warehouse cost efficiency, and data warehouse functionality. The performance list is as follows:

Global query optimization. This refers to DB2's ability to do some major transformation, under the covers, of queries that contain subquery predicates. To boost a query's performance, DB2 might change a correlated subquery to a non-correlated subquery (or vice versa). DB2 can also treat the result set of a subquery as a virtual table and join it to an "actual" table in the query's FROM list. The result: potentially large performance improvements with no need to alter the query's SQL code.
Indexes on column expressions. It used to be that a predicate containing a column expression such as WHERE UPPER(LAST_NAME) = 'JOHNSON' or WHERE SALARY + BONUS < 100000 were non-indexable. DB2 9 enables the creation of indexes on such expressions, resulting in sometimes-huge reductions in query run times.
Smarter sequential prefetch. Much of the prefetch activity in a DB2 9 environment shifts from "pure" sequential prefetch (now used only for tablespace scans) to dynamic sequential prefetch. Dynamic prefetch can be used for both forward and backward scans (pure sequential prefetch is forward-only), and it tends to make better use of buffer pool resources. With more dynamic prefetch, queries can be expected to run in less time and to consume less in the way of CPU resources.

My favorite DB2 9 features for data warehouse cost-efficiency are:

Universal tablespaces. Compared to the space map pages in a traditional partitioned tablespace, the space map pages in a universal tablespace (UTS) provide more information about "holes" in data pages that can accommodate to-be-inserted rows that are of varying length (and rows in compressed tablespaces are varying-length, regardless of whether or not they contain any VARCHAR columns) and which need to be inserted "in the middle" of the target table (versus at the end) to maintain clustering sequence. Because of this additional information about space in data pages, insert processes can execute with reduced CPU cost when populating tables in universal tablespaces.
Index compression. At some sites, indexes take up more disk space than tables. DB2 9 index compression can reduce disk space requirements for an index by 50-70%, with relatively little cost in the way of CPU overhead (often in the low single digits of percent).
Unused index detection. Dynamic SQL dominates in most DB2 for z/OS data warehouse systems, and it used to be very difficult to identify indexes that are not being used to improve the performance of any queries. DB2 9 introduced a new column, LASTUSED, in the real-time statistics table SYSIBM.SYSINDEXSPACESTATS. This column shows the last time that an index was used by a SELECT or FETCH statement, or by an UPDATE or a DELETE statement with predicates, or to check on a referential integrity constraint. Indexes that have not been used for any such purpose will have the default value or 1/1/0001 in the LASTUSED column. These are indexes that can probably be deleted to reduce disk space consumption and lower CPU costs (unneeded indexes drive up the cost of data change operations).

And, my top-three favorite DB2 9 data warehouse functionality features:

INSTEAD OF triggers. Many views, such as those on multi-table joins, are read-only. INSTEAD OF triggers can be used to enable updates through such views, allowing developers to reference the same object for both data retrieval and data change operations.
INTERSECT and EXCEPT. These two new set operators (added to the UNION operator, which has been around for a long time) make it much easier to get result sets based on the comparison of two different query result sets. INTERSECT instructs DB2 to retrieve rows from result set A that also appear in result set B. EXCEPT can be used to get rows from result set A that do not appear in result set B.
RANK, DENSE_RANK, and ROW_NUMBER. These so-called OLAP functions make it easy to assign numeric values to result set rows based on user-specified ordering criteria (which could be quite different from the ORDER BY sequence specified at the "end" of a query). Consider a set of rows to be numbered based on descending unit sales amounts. RANK could have gaps in the number values if there are "ties" (if row one has a unit sales figure of 1000 and both rows two and three have a unit sales figure of 900, rows two and three will both have a RANK or 2 and row four, with a unit sales figure of 800, will have a RANK of 4). DENSE_RANK leaves no gaps in the value of assigned numbers (given the example in the previous parenthetical phrase, rows two and three would have a DENSE_RANK value of 2 and row four would have a DENSE_RANK value of 3). ROW_NUMBER assigns numbers in sequence with no gaps and no repeats (so rows two and three as previously described with get ROW_NUMBER values of 2 and 3, respectively, even though they both have the same unit sales value of 900).

I concluded my presentation with some performance monitoring and tuning hints and tips:

Make DB2 buffer pools big if you have a server with a lot of memory. This may sound like a statement of the obvious, but a lot of folks have pretty small buffer pools in spite of having DB2 running on a mainframe server with tens of gigabytes of memory. I blogged on buffer pool sizing a few weeks ago.
Page-fix the buffers in heavily used buffer pools. This move can deliver some nice CPU savings. I blogged on this topic earlier this year.
Take advantage of tools for query performance analysis. I'm a big fan of IBM's free and downloadable Optimization Service Center for DB2 for z/OS.
Try executing a long-running, complex query in "chunks" to isolate a performance problem. Suppose that a query with a number of local (i.e., non-join) subquery predicates runs in 30 minutes. Try executing the query minus one of these subquery predicates. Then try it with that predicate back in the picture but without another of the subquery predicates. Keep doing this to see if the query's run time drops significantly after one of the subquery predicate has been removed. If that happens, focus your tuning efforts on that subquery predicate.

The strength of the System z platform, the convenience and simplicity of a BI "appliance." The key meeting on my schedule today involved a discussion of IBM's Smart Analytics System 9600. Demand is growing for preconfigured, optimized, full-function systems that accelerate data warehouse implementations by providing an end-to-end solution "out of the box." The Smart Analytics System 9600 brings this popular approach to the super-scalable, super-secure, super-highly-available System z platform. Everything you need to get productive with business intelligence -- the mainframe server, the z/OS operating system, DB2 for z/OS, InfoSphere Warehouse for DB2 for z/OS (providing, among other things, cubing services), Cognos BI software for advanced analytics, and implementation services -- comes with the System 9600, all at a price that is substantially lower than what you'd pay to get the components separately. Check it out -- it could be a good fit for your organization.

DB2 Dispatch from Vegas: IOD, Day Two

2010-10-27T00:07:00.000-07:00

Greetings again from the 2010 IBM Information on Demand conference (IOD) in Las Vegas. Lots of good DB2 10 for z/OS stuff today. Some highlights of sessions I attended:

Getting the most out of native SQL procedures. This presentation was delivered by Pallavi Priyadarshini and Maryela Weihrauch of IBM's Silicon Valley Lab (the home of DB2 for z/OS). I have a major interest in the topic, having blogged about native SQL procedures a number of times while working as an independent DB2 consultant (SQL procedures are stored procedures written entirely in SQL, and native SQL procedures, introduced with DB2 9 for z/OS, have no external-to-DB2 executable -- they execute as packages in the DB2 database services address space, aka DBM1):

http://catterallconsulting.blogspot.com/2007/12/what-are-your-plans-for-db2-for-zos.html
http://catterallconsulting.blogspot.com/2008/11/db2-9-for-zos-stored-procedure-game.html
http://catterallconsulting.blogspot.com/2009/02/another-advantage-of-db2-for-zos-v9.html
http://catterallconsulting.blogspot.com/2009/06/db2-stored-procedures-cobol-and-result.html
http://catterallconsulting.blogspot.com/2009/07/migrating-db2-9-for-zos-native-sql.html
http://catterallconsulting.blogspot.com/2009/08/db2-for-zos-stored-procedures-and-large.html
http://catterallconsulting.blogspot.com/2010/04/using-db2-stored-procedures-part-1.html
http://catterallconsulting.blogspot.com/2010/04/using-db2-stored-procedures-part-2.html
http://catterallconsulting.blogspot.com/2010/04/using-db2-stored-procedures-part-3.html

Among the many excellent nuggets of information delivered in Pallavi and Maryela's IOD session are the following:

How does the package of a native SQL procedure differ from that of an external stored procedure? The difference is basically the part that contains the SQL control language statements (these statements -- examples being IF, LOOP, and ITERATE -- provide logic flow control). These go into a part of the stored procedure package called Section 1. Almost all (about 90%) of the Section 1 part of a native SQL procedure package goes above the 2GB bar in the DB2 DBM1 address space, so you shouldn't be concerned about the virtual storage impact of native SQL procedure utilization.
Note that the value of ASUTIME (the limit on the mainframe service units that a stored procedure can consume before it is canceled) defaults to NO LIMIT. Specifying a value for ASUTIME, even if it is ten or more times greater than what the stored procedure is expected to consume, provides means of keeping a stored procedure from getting into an infinite loop. It might be a good idea to specify an ASUTIME value if the procedure contains a loop operation (maybe via IF, or LOOP, or REPEAT) -- just in case a logic error causes DB2 to fail to exit the loop.
External SQL procedures do not return unhandled warnings (i.e., warnings for which you don't have a condition handler) to the calling program. Native SQL procedures do.
Syntax changes might be required when converting an external SQL procedure to a native SQL procedure. To help identify such changes, IBM has provided a HOST(SQLPL) precompiler that can be used to perform a checkout inspection for an SQL procedure definition, validating that the source is properly coded to conform with native SQL PL syntax and behaviors. This function is built into DB2 10, and it will be made available for DB2 9 via the fix for APAR PM13844 (expected to be available sometime in December). Per the APAR text, the HOST(SQLPL) checkout "is a recommended step to perform for all native SQL procedure development. It is a vital step to perform prior to any migration of an external SQL procedure to a native SQL procedure."
When a stored procedure returns a result set to a caller, keep in mind that virtual storage will be used for that cursor and will not be released until commit. So, ensure that programs -- even read-only programs -- that fetch results from a cursor declared in a stored procedure issue a commit when done fetching rows (best practice is for the caller to also explicitly close the open cursor). Note that COMMIT ON RETURN in a stored procedure's definition is a way to ensure that a commit happens upon completion of the procedure's execution, but if you use that option for a stored procedure that returns a result set to the caller, ensure that the cursor opened in the stored procedure is declared WITH HOLD; otherwise, the automatic commit will close the cursor.
With DB2 10, the XML data type is valid for stored procedure parameters and variables (prior to DB2 10, you had to use -- depending on the size of the XML document -- a VARCHAR or a LOB data type to work with an XML document in a stored procedure.
DB2 10 enables the assignment of values to multiple parameters or variables in a stored procedure with a single SET statement. This "chained SET" capability can improve stored procedure performance.
Testing by DB2 development of native SQL procedures in a DB2 10 for z/OS environment has shown improvements in CPU efficiency of 10-20% versus a DB2 9 system.
SQL Procedure Language (aka SQLPL -- the SQL statements used to code a SQL stored procedure) can be used to create SQL user-defined functions (UDFs) in a DB2 10 environment. This is a very good thing, because the functionality of SQL UDFs is extremely limited in DB2 9 (and prior release) systems.

New DB2 10 for z/OS security features to help satisfy your auditor. Long title, yes, but a very good session delivered by DB2 developers Jim Pickel and Gayathiti Chandran. Jim started out by telling attendees that a goal in developing the security-related functionality introduced with DB2 10 was to enable organizations to meet compliance requirements with little or no application code changes. Some highlights of the session:

New authorities provided by DB2 10 facilitate minimization of the use of super-user authorization levels such as SYSADM. Specifically, the new System DBADM authority is likely to be used extensively in place of SYSADM authority. System DBADM authority can be granted with or without data access privileges, and with or without access control privileges (the latter referring to the ability to execute GRANT and REVOKE statements). Interestingly, System DBADM enables execution of most CREATE, ALTER, and DROP statements, but not those that would affect system or security objects. In addition, someone with System DBADM authority would need additional privileges to create objects such as views, functions, and triggers. That said, a System DBADM can execute what are called system-defined routines (such as stored procedures and UDFs), and routines created by someone with install SYSADM authority are considered to be system-defined routines. This, someone with a super-user authority can create administrative routines that could be executed -- but not dropped or modified -- by a person with System DBADM authority.
Another new authority, EXPLAIN, allows a person to EXPLAIN SQL statements (dynamic statements or statements embedded in programs) without having the authority to execute those statements. The new SQLADM authority adds privileges to those available via the EXPLAIN authority. For example, someone with SQLADM authority could issue an EXPLAIN for the dynamic statement cache, again without having the privileges needed to execute those statements.
Additionally, DB2 10 enables the separation of responsibility for managing access to restricted data from the owner of that data. A recommended best practice now is to have roles own objects, as opposed to having objects owned by individual authorization IDs. The CATMAINT utility can be used to switch ownership of an existing object to a role, and this can be done in such a way that programs with SQL statements referencing qualified object names do not have to be changed.
Auditing capability has been significantly enhanced with DB2 10. Now, all dynamic access to data (versus just the first access with the DB2 9 audit functionality) and any use of privileged authority can be included in the audit trail. More good news: this enhanced auditing capability can be utilized without having to specify AUDIT in the to-be-audited object's DDL. Once an audit policy has been defined, it is "turned on" via a -START TRACE command that names the audit policy (DB2 will turn on, under the covers, the trace record types, or IFCIDs, that are needed to get the information requested in the policy -- that's good for ease of use). Audit information is recorded in SMF records. The DB2 utility DSN1SMFP can be used to print formatted SMF records in a report.
Auditing of course involves some overhead, so it is recommended that audit policies be defined to track access to specific tables from specific programs. The fact that wildcarding can be used for schema and table names makes it easier to tailor audit policies.
Besides tracking access to data, an audit policy can be set up to record each use of an authority or authorities.
The temporal data capability built into DB2 10 (a first in the industry, by the way) makes it much easier to track changes to data records in DB2 tables (this by exploiting the new "system time" characteristic that can be built into a new table or easily added to an existing table).
You now how passwords have been 8 characters in the mainframe world, like, forever? DB2 10 supports the "password phrase" functionality introduced with z/OS V1R10. A password phrase can be up to 100 characters in length.
DB2 10 also supports the distributed identity functionality introduced with z/OS V1R11. This can make for a more-robust security set-up in a client-server environment.
My favorite part of the presentation was the material on the new table-access control features of DB2 10 for z/OS. Why did I so enjoy hearing about this? Because I believe that table access controls could take the place of the view-based security set-up that is often found in a data warehouse system. I've run up against "security views" on more than one occasion, and they can be a big headache, with views on top of views on top of other views, creating a security scheme that is highly complex and which can sometimes get in the way of good query performance (and try digging through these layers of views to see how the associated predicates figure into the performance characteristics of a query that targets one of the top-level views). In a DB2 10 system, table controls can be implemented via DDL with no requirement for overlying views. The controls, which -- after being defined via new SQL statements CREATE PERMISSION (for row-access restrictions) and CREATE MASK (for column-access protection) -- are activated with an ALTER TABLE statement, can determine access to data based on any of several ID types, such as primary authorization ID, SQL ID, and role. The controls affect static and dynamic SQL statements, and they can be used to return "mask" data values to querying users (a mask is defined by way of an SQL case expression). If you have security views implemented in your environment today, read up on CREATE PERMISSION and CREATE MASK, and start thinking about how you might be able to eliminate your security views in favor of DB2 10 table controls.

What's new in DB2 10 for z/OS for DBAs. Jim Teng, IBM Distinguished Engineer, longtime leading member of the DB2 development team, and a longtime friend, gave an excellent presentation on DB2 10 features that DBAs are going to like. These features include the following:

More online schema changes. The concept of "deferred" changes is introduced with DB2 10: you issue an ALTER TABLESPACE or ALTER INDEX statement to specify a change, and that change is put into effect by a subsequent online REORG of the target object. In this way, you can change the page size, DSSIZE, or SEGSIZE of a universal tablespace (UTS); the page size of an index; and the MAXPARTITIONS value for a partitioned tablespace. You can also convert a single-table simple or segmented tablespace to a UTS. MEMBER CLUSTER can be implemented for a partitioned-by-range (PBR) or partitioned-by-growth (PBG) UTS, or for a "classic" partitioned tablespace.
Note that a pending object change can be dropped via ALTER before being realized through a REORG -- kind of like an "I changed my mind" capability.
One DB2 10 ALTER TABLESPACE or ALTER INDEX statement cannot contain both deferred and immediate actions; furthermore, many immediate-effect DDL statements cannot be executed while there is pending DDL waiting to be put into effect via online REORG.
There are also some new immediate-effect online schema changes made possible by DB2 10. Among these changes are the addition of INCLUDE columns to an existing unique indexed key (though the benefit of increased incidence of index-only access a REBUILD INDEX and a rebind (if we're talking about static SQL). Also immediate is the change to HASH for a UTS (though the table's rows can't be accessed by way of the hash key until the tablespace has been REORGed. Similarly, a change to inline LOBs for a tablespace is immediate, but existing LOB values that can be in-lined won't be until the tablespace is REORGed.
With DB2 10, when a tablespace or index is altered to reassign it to a different buffer pool (with the same page size), that change takes effect immediately, with no need to stop and start the object (in a pre-DB2 10 data sharing environment, the object had to be stopped BEFORE the ALTER with the BUFFERPOOL specification was issued). Any application processes accessing the target object are quiesced with a DRAIN(ALL), and the new buffer pool is used following commit processing.
When data is inserted into a table with multiple indexes (two or more if a MEMBER CLUSTER tablespace or an APPEND table; otherwise, more than two), DB2 10 will update the table's indexes in parallel. For a table with quite a few indexes, this can have a big and positive effect on the performance of INSERT processes (some tests have shown an elapsed time improvement of 50% or more).
DB2 10 will cache (i.e., remember the address in the buffer pool of) the root page of an index when the index is opened. This will eliminate one GETPAGE/release page operation for every subsequent access to the index (good for performance).
DB2 10 can use list prefetch to efficiently retrieve entries from a disorganized index, thereby reducing the need to frequently reorganize indexes.
A new DB2 10 trace record, IFCID 359, can be used to monitor the performance (including the elapsed time) of index page split operations.
Avoidance of full buffer pool scans in a data sharing environment when, for example, a data set is closed, can significantly improve performance for large (i.e., 5 GB or more) buffer pools in a DB2 10 environment (and I'm talking here about the size of an individual pool, not the aggregate size of all pools in a configuration).
A new buffer pool parameter setting, PGSTEAL(NO), can be used to "pin" a table in memory in a DB2 10 system. With PGSTEAL(NO) in effect, all of a table's pages will be read into memory, using prefetch engines, when the underlying data set is opened. Thereafter, prefetch is turned off for the table (so, make sure that the pool has enough buffers to hold the pages of objects assigned to it; otherwise, you could see a lot of random reads).
The new "currently committed" locking option available with DB2 10 will enable an application to read previously committed data rows instead of waiting for inserting or deleting processes to release locks on pages or rows (this option can only be used with universal tablespaces). An application for which this option is in effect will still have to wait on locks held by data-updating processes.
The auto-compress feature of DB2 10 enables a compression dictionary to be built for a tablespace based on INSERT activity, versus having to wait for a LOAD or a REORG. The dictionary is built by an asynchronous DB2 task. The number of row inserts needed to build a dictionary will vary -- it's based on real-time statistics information.
DB2 10 supports the use of FlashCopy at the data set level.
The catalog and directory objects in a DB2 10 system will be DB2- and SMS-managed (meaning, among other things, that they are STOGROUP-defined -- a ZPARM lets you specify a default STOGROUP to be used for this purpose).

DB2 10 for z/OS performance improvement. Akiko Hoshikawa joked that her husband tells her that her voice puts him to sleep, then proceeded to get an hold our attention with a lot of good information pertaining to performance improvements delivered by DB2 10. Some notes from the session:

The virtual storage constraint relief provided by DB2 10 allows, among other things, a larger MAXKEEPD value (relevant for packages bound with KEEPDYNAMIC(YES)). This means more avoidance of "short prepares," and that means better performance. In one SAP test, CPU time was reduced by 26%, just by increasing the value of the MAXKEEPD parameter in ZPARM to 64,000 from 8,000.
The 1 MB real storage page frame size provided by the z10 and z196 (zEnterpise) servers (backed by 256 contiguous 4K real storage frames) boosts performance by reducing the incidence of "misses" versus "hits" in the translation lookaside buffer (this buffer is used to speed up the translation of virtual storage addresses to real storage addresses). Observed CPU savings tend to be in the 1-4% range. Utilization of 1 MB page frames requires a change to LFAREA in IEASYSxx, followed by an IPL.
Akiko pointed out that the z196 mainframe supports up to 3 TB of memory.
In a DB2 10 system, RUNSTATS will collect KEYCARD data by default.
Hash access to data -- a new DB2 10 feature that causes data rows in a table to be physically placed based on the hashing of a unique key value -- is great for single-row retrieval. The flip side? It can be pretty bad for sequential data retrieval. So, choose carefully when deciding on tables for which you want to use hash access.

Time to call it a day. Back with more tomorrow.

DB2 Dispatch from Vegas: IOD, Day One

2010-10-26T03:14:00.000-07:00

The sun is starting to set in a typically cloudless sky in Nevada's southern desert, and day one of IBM's annual Information on Demand conference (aka IOD) is just about done. Herewith are a few notes on today's goings-on in Las Vegas. I'll blog at the conclusion of days 2, 3, and 4, as well, and I hope that you'll check out those posts. On now to the rundown:

There are a LOT of people here. The conference got over 10,000 registrants this year. The arena-like events center in which this morning's grand opening session was held was pretty well packed. Fajitas for 10,000 (excellent guacamole) was an impressive operation.

A lot of information was packed into a fast-paced, 90-minute opening session. The emcee kicked things off with a nugget from a pre-conference survey: respondents indicated that the number one business challenge facing their organization is the need to cut costs and increase profits. I see the "cut costs" imperative as a lingering effect of the Great Recession out of which we're (thankfully) climbing. The "increase profits" goal is, I hope, indicative of a return to top-line growth as a focus of senior managements' attention -- companies are shifting, I think, from "survive" mode to a "drive to thrive." An overarching theme of this year's IOD event is the leveraging of information and analytics to "gain insight and optimize results."

Robert LeBlanc, IBM's Senior VP of Middleware Software, led most of the remainder of the opening session. Robert noted that a recent IBM survey of CEOs around the world uncovered three primary focus areas: 1) creative leadership, 2) reinvention of customer relationships, and 3) building operational dexterity. Taking advantage of data assets can lead to success in these areas, but first an organization has to get its data house in order, and that can be a tall task in light of the ongoing -- and accelerating -- information explosion: LeBlanc mentioned that in 2009, there were about 800,000 petabytes of information in computer systems around the world (80% of it unstructured). It's expected that by 2020 that number will be around 35 zettabytes (a zettabyte is 1 sextillion bytes, or a billion terabytes). That's a 44X increase in 11 years. Is the growing pile of data in organizations' systems effectively managed today? In many cases, the answer to that question is "no": 72% of senior executives indicated through a survey that they can get information more easily from the Internet than from their own applications.

Analytics, said LeBlanc, is all about using information. Information management, the foundation for successful analytics, has much to do with data governance (about which I wrote in a recent article published in IBM Data Management Magazine) and the establishment of a "single version of the truth" with regard to questions that might be answered based on the organization's data assets. Put the two together, and you can get some impressive business results:

Avis Europe realized a 50% decrease in marketing costs through better targeting.
The State of New York has avoided $1.2 billion in questionable tax refunds since 2004 through the use of analytics.
Newell Rubbermaid got business intelligence queries to run 30 to 50 times faster through reengineering of their decision support system.

Robert was joined on stage by IT executives from Visa International (Mike Dreyer) and CenterPoint Energy (Steve Pratt). Mike emphasized the importance of real-time analytics in detecting potential instances of fraudulent credit card use (Visa fraud-scores 10,000 transactions per second, with each result returned in less than a second, on average), and of getting to the aforementioned single-version-of-the-truth state (the question, "what is a customer?" at one time might have generated multiple different answers). Pratt spoke of CenterPoint's transformation "from an energy company that uses technology to a technology company that delivers energy," with developments such as smart grids and intelligent meters providing rich new flows of information that have enabled CenterPoint to boost customer satisfaction and organizational operating efficiency.

Next up was Arvind Krishna, IBM's General Manager of Information Management Software. Arvind spoke of how organizations are using modern information management technology to:

Reduce customer "churn" through enhanced customer understanding.
Slash loan origination times through collaborative decisioning.
Achieve tremendous ROI through improved supply chain visibility.

Arvind also described IBM's drive to build the most comprehensive business analytics and optimization portfolio in the industry. IBM has spent $14 billion on acquisitions just since 2005. It also has thousands of analytics and optimization consultants, and the largest mathematics research staff in private industry.

The opening session concluded with a panel discussion involving several IBM software executives, Arvind among them. Topics covered by the panel included:

The challenge posed by the information explosion on the one hand, and the need for a single version of the truth on the other (successfully dealing with this challenge often involves analysis of an organization's "information supply chain").
Workload-optimized systems (integrated hardware/software offerings that are optimized for a specific workload), which are delivering both improved performance and greater ease-of-use for a variety of companies.
The need to get started on building an analytics capability ("it's not a spectator sport"), with the right choice for a pilot being something that matters to the business.
The usefulness of an information agenda, the "external artifact" that can bring the IT and business parts of an organization together ("every organization has an IT strategy that is really an application agenda -- what's the information agenda?").

With the conclusion of the opening session, it was on to elective sessions.

DB2 for z/OS trends and directions. Rick Bowers, IBM Director of DB2 for z/OS Development, led this session. He reminded attendees of the recent announcement of DB2 10 for z/OS (October 19), and the very soon thereafter general availability date for the product (IBM started taking orders for DB2 10 on October 22). Interest in this new release of DB2 is very high in the user community, driven largely by the 5-10% CPU cost reduction delivered by DB2 10 "right out of the box" (and requiring only a rebind of existing DB2 packages). Improved scalability is another key DB2 10 benefit, with the expectation being that organizations will generally see a five- to ten-times increase in the number of users that can be concurrently connected to, and active on, a DB2 10 subsystem (it was mentioned that the number of concurrent DB2-accessing SAP application users would likely be five- to six-times greater on a DB2 10 subsystem versus previous product releases).

Rick talked about the "skip-release" migration path that would enable an organization running DB2 for z/OS V8 to go directly to DB2 10, without having to migrate to DB2 9 in-between. He said that skip-release migration had been successfully tested during the DB2 10 Beta program, and he suggested that the decision by a DB2 V8-using organization as to whether or not to go this route would depend on the organization's risk profile. "Early adopter" companies might opt to go straight from DB2 V8 to DB2 10, while late adopters would more likely migrate first to DB2 9 and then later to DB2 10.

Rick pointed out that 22 of the 25 DB2 10 Beta customers are planning to migrate DB2 10 to production (or at least to get the production migration process underway) during the 2011 calendar year.

Paul Gatan, Director of Systems Support at DST Systems, an early evaluator of DB2 10, joined Rick to talk about his company's DB2 10 experiences. DST has a large and very active (particularly from a data-change perspective) DB2 for z/OS database. One of the tables in that database has 61 billion rows and holds 9.5 terabytes of data. Paul highlighted the following in speaking of DST Systems' work with DB2 10:

They really liked the "full" 64-bit addressing capability of DB2 10 (64-bit addressing, introduced for DB2 for z/OS with Version 8 of the product, was further exploited with DB2 9 and even more fully utilized with DB2 10).
Use of the new z/OS 1 MB page size (DB2 page sizes remain as they were in previous releases -- DB2 10 maps multiple of its pages to one one-megabyte z/OS page) resulted in a 4% reduction in CPU utilization on top of the 5-10% CPU savings seen by DST for an insert heavy application process.
CPU consumption by one program dropped 60% after a rebind in the DB2 10 environment -- that was a surprise, and an "it depends" result that can't necessarily be predicted.
The restructured DB2 catalog (no links, universal tablespaces, row-level locking) greatly improved bind concurrency for DST.
DST liked the new security options, including System DBADM, which provides most of the capabilities of the SYSADM authority and can be granted without data access .
The new IFCID 401 trace record provides useful statement-level performance data for static SQL statements.

Rick wrapped up the session by urging attendees to check out IBM's new DB2 for z/OS Best Practices Web site (http://www.ibm.com/developerworks/data/bestpractices/db2zos/). It contains a lot of great stuff from IBM DB2 for z/OS gurus such as John Campbell.

DB2 10 for z/OS technical overview. Jeff Josten, an IBM Distinguished Engineer and a long-time member of the DB2 for z/OS development organization, delivered this presentation. Some highlights:

The use of z/OS 1 MB pages requires a z10 or a zEnterprise server.
The DB2 10 Beta program was the largest ever for a new DB2 release.
DB2 10 introduces High Performance DBATs (database access threads). Conceptually similar to CICS-DB2 protected entry threads, High Performance DBATs should be particularly beneficial for high-volume OLTP applications executed through DDF. They can be used by packages bound with RELEASE(DEALLOCATE) for improved CPU efficiency (formerly, only the RELEASE(COMMIT) bind option was honored for packages executed via DBATs). High Performance DBATs are released (for "clean-up" purposes) after every 200 uses.
Some INSERT jobs could see a 30-40% reduction in CPU consumption in a DB2 10 environment.
The movement of more thread-related storage above the 2 GB "bar" in the DB2 10 database services address space enables greater use of the RELEASE(DEALLOCATE) option of BIND (with DB2 10, only the working storage associated with a thread, and some of the thread/stack storage, goes below the 2 GB bar). This, in turn, can reduce the DB2 CPU time for an application process by 10-20%.
DB2 10 provides a new option that allows data readers to access the previously committed version of a row, rather than waiting for an inserting or deleting process to commit.
LRSN "spin avoidance," introduced with DB2 9 to reduce bottlenecking related to generation of log record sequence numbers in a data sharing environment, is further exploited in a DB2 10 data sharing group.
The new hash access capability enables one-GETPAGE direct access to a data row based on the hash of a key value (versus the multiple GETPAGEs needed to navigate through an index tree). Hash access might not be a good choice when program performance depends on data clustering, as it randomizes data row placement in a table.
DB2 10 uses 64-bit common storage to avoid constraints related to the use of below-the-bar extended common storage.
Data access for large (greater than 3-4 TB) buffer pools is more efficient in a DB2 10 environment.
More database schema changes can be effected online through ALTER (these become "pending alters" which are put into effect by an online REORG). Included among such alters are changes in page size, DSSIZE, and SEGSIZE, and a change in tablespace type to universal trablespace.
DDF location alias names can also be changed online.
Active log data sets can be dynamically added to a configuration via the DB2 command -SET LOG NEWLOG.
The catalog and directory objects can be reorganized with SHRLEVEL(CHANGE).
The new FORCE option of REORG can be used to cancel threads that are preventing the data set switch phase of online REORG from completing.
The BACKOUT YES option of RECOVER can speed point-in-time object recovery if the recovery point is a little before one image copy and way after another.
DBADM authority can be granted for all databases in a DB2 subsystem, removing the requirement that the authority be granted on a database-by-database basis.
New stored procedures are supplied to enable automatic generation of statistics by the DB2 RUNSTATS utility
Instead of having to specify one subsystem-wide value for the maximum number of active distributed threads (MAXDBAT), this limit can be specified in a more granular way (e.g., on an application basis).
Timestamp accuracy can go to the picosecond level in a DB2 10 environment.
XML schema validation is done "in the engine" in a DB2 10 system.
New moving sum and moving average functions can enhance OLAP capabilities.

DB2 10 for z/OS migration planning. This presentation was delivered in tag-team style by IBM's Roger Miller and Terrie Jacopi, and by Dil Sasidharan of Morgan Stanley. Some notes:

Roger started out by calling DB2 10 "the hottest release we've ever had."
Dil noted a major performance improvement (30%) seen when a DB2 stored procedure was converted from a C program to a native SQL procedure (the performance of SQL procedure language, aka SQLPL, is improved with DB2 10 -- realizing this improvement for an existing native SQL procedure will require a rebind of the associated DB2 package).
Dil also reported that a major improvement in DB2 elapsed time (50%) was seen for a certain application that Morgan Stanley tested with DB2 10. Lock/latch suspend, page latch suspend, and log I/O suspend times for the application all decreased markedly in the DB2 10 environment.
Morgan Stanley also saw a 15% improvement in elapsed time for concurrently executed LOAD REPLACE jobs in the DB2 10 system.
Terrie mentioned that some high-volume insert programs could consume up to 40% less CPU in a DB2 10 environment versus DB2 9. Certain complex queries could consume 60% less CPU time with DB2 10.
Roger recommended that people check out chapter 4 of the "Packages Revisited" IBM redbook for information on converting DBRMs bound directly into plans to packages (DB2 10 does not support DBRMs bound directly into plans).
The DB2 10 catalog and directory objects must be SMS-controlled, with the extended addressability (EA) attribute (the tablespaces will be universal with a DSSIZE of 64 GB).
Single-row result sets can be more efficiently retrieved with DB2 10, thanks to OPEN/FETCH/CLOSE chaining.
With the improved performance of SQLPL, native SQL procedures could be even more CPU-efficient than COBOL stored procedures in a DB2 10 environment, and that's before factoring in the use of less-expensive zIIP engines by native SQL procedures called through DDF.
DB2 10 provides support for "include" columns in an index key. These are columns that can be added to a unique key's columns (to get index-only access for more queries) without affecting the related unique constraint (i.e., the included key column values are not considered for key uniqueness). Organizations might be able to drop indexes that are redundant in light of this feature.
Roger recommended that people get their DB2 objects into reordered row format (RRF).
Roger urged people to take advantage of IBM's Consolidated Service Test program as part of their DB2 10 migration plan.

That's all for now. Stay tuned for notes from Day 2.

When do you Add a New DB2 Buffer Pool?

2010-10-17T19:36:00.000-07:00

I've written a number of entries, here and in my old Catterall Consulting blog, on various topics related to DB2 buffer pools (I posted the most recent of these just a few weeks ago). Often, when I write about DB2 buffer pools (or talk about them in presentations or when working with DB2 users), I have sizing in mind -- this because 1) undersized buffer pools are the DB2 performance inhibitor that I most frequently encounter, and 2) many DB2 people haven't yet thought about leveraging 64-bit addressing and the large amounts of memory commonly available on DB2 servers. What I'm thinking about today is related to buffer pool sizing, but is more of a layout consideration -- namely, when should you think about adding a new pool to your configuration?

There is an interesting history behind this question. Way back in the 1980s, when DB2 for the mainframe was new and DB2 for Linux, UNIX, and Windows wasn't yet on the scene, I and other DB2 specialists regularly counseled users to "put everything in BP0" (speaking, obviously, of objects with 4K pages). You didn't have a lot of choices back then (besides BP0, there were BP1, BP2, and BP3 for data sets with 4K pages, and BP32K for objects with larger pages), and we thought early on that it was best to just let DB2 figure out how to best manage the caching of pages for all tablespaces of all types (and associated indexes). In the 1990s we got a DB2 release (I'm thinking that it might have been Version 4) that gave us a LOT more choices with respect to the buffer pool configuration: you could have (and it's the case today) up to FIFTY different pools for objects with 4K pages, and ten different buffer pools each for data sets with 8K, 16K, and 32K pages. Around that time, we DB2 specialists started to sing a different buffer pool configuration tune: reserve BP0 for the catalog and directory objects, and assign the other tablespaces and indexes in your database to several other buffer pools. Aside from the obvious need to assign objects with different page sizes to pools with corresponding buffer sizes, we felt that assigning different categories of objects to different pools provided advantages in terms of performance and monitoring. I'll elaborate on this as I go along.

By the late 1990s, what I'd call the basic buffer pool configuration became pretty well established. That configuration tends to look pretty much like this (I'm talking about 4K buffer pools here, but the same categories could apply to 8K, 16K, and 32K pools -- except that the catalog and directory have to go in BP0, and work file tablespaces can have 4K or 16K pages, but not 8K or 16K pages):

Catalog and directory: BP0
User tablespaces: BP1 (doesn't have to be BP1 -- could be any 4K pool other than BP0)
Indexes on user tablespaces: BP2 (again, doesn't have to be BP2 -- just something other than BP0 and the pool to which user tablespaces are assigned).
Work file tablespaces: BP7 (doesn't have to be BP7, but that's a popular choice because the name of the work file database in a non-data sharing environment is DSNDBB07).

The size of these pools will vary from one installation to another, but often BP0 will have between 5000 and 10,000 buffers, and BP7 will have between 10,000 and 20,000 buffers (maybe more). BP1 and BP2 should probably have -- depending on the server memory resource available to the operating system -- at least 40,000 buffers each (it's not uncommon for these pools to have in excess of 100,000 buffers apiece). In addition to size differences, there can be differences in parameter specifications for the various pools. For example, the virtual pool sequential threshold (VPSEQT) for BP7 (the work file buffer pool) might be set to 90 or 95 versus the default setting of 80. This parameter indicates the percentage of the pool's buffers that can hold pages brought into memory from disk via prefetch reads. Because almost all DSNDB07 reads should be of the prefetch variety, it makes sense to allow a higher percentage of the buffers in the associated pool to hold prefetched pages.

Another fairly common BP7 parameter specification difference is a higher value for the vertical and horizontal deferred write thresholds (VDWQT and DWQT). The default values for VDWQT and DWQT are 5 and 30, respectively. For BP7, they might both be set to 50 or even higher. Why? Because for the pools that hold the catalog and directory and user tablespaces and indexes, lower deferred write thresholds trigger more-frequent externalization of changed pages to disk, and that leads to faster restart times in the event of an abnormal DB2 subsystem termination (there will be fewer pending writes to re-create during the roll-forward phase of subsystem restart). For the work file tablespaces, we don't care about recovery in case of a DB2 failure -- they just hold temporary work files used for sorts and other query-processing operations; therefore, the BP7 deferred write thresholds just need to be low enough to prevent a thrashing situation. [Note: use caution in setting there thresholds above 50 -- if they are too high, you could have a situation in which the data manager critical threshold (DMTH) is hit, and that is really bad for performance (in one situation, I saw a BP7 for which VDWQT and DWQT were both above 90, and DMTH was hit frequently for that pool). If you go above 50 for VDWQT and DWQT for your BP7, ensure that DMTH is not being hit -- you can check this out using your DB2 monitor or the output of the DB2 command -DISPLAY BUFFERPOOL (BP7) DETAIL(*).]

Next on the list of specifications that might differ from one pool to another is the PGFIX option. PGFIX is the parameter that indicates whether or not z/OS will fix a pool's buffers in memory (I blogged on buffer pool page-fixing a few months ago). Because page-fixing tends to deliver the greatest benefit for pools that have the highest rates of disk I/O operations, I tend to see PGFIX(YES) specified for pools that hold user tablespaces and indexes (BP0 and BP7 usually have the default specification of PGFIX(NO)).

The basic buffer pool configuration I've just described has been extended at many DB2 sites to include additional pools beyond BP1 and BP2 for user tablespaces and indexes. Why might you allocate an additional pool or pools for user objects? A couple of scenarios come immediately to my mind:

As part of a plan for growing the overall size of the buffer pool configuration. Suppose you have the basic configuration, with user tablespaces and associated indexes assigned to buffer pools BP1 and BP2 (or whatever). You check the total read I/O rate for these pools and find that it's on the high side -- over 1000 per second, let's say (see my blog entry on buffer pool sizing, posted a few weeks ago, for information on performing this read I/O rate analysis). You make the pools considerably larger -- maybe doubling them in size, to well over 100,000 buffers apiece -- and still the read I/O rate is higher than you'd like it to be. At this point, you could just keep growing these pools (assuming adequate real storage to back the larger pools), but you could also consider the option of enlarging the overall buffer pool configuration by creating a new pool for some of your tablespaces (and another new one for the indexes defined on tables in those tablespaces). Which objects might you reassign from BP1 to BP3 (if that's your choice for the new pool for tablespaces)? One alternative would be to reassign tablespaces of a certain category -- those holding "history-type" tables, for example, so as to separate these from "current-activity" tables from a page-caching perspective. Another possibility would be a reassignment of some tablespaces based on their I/O counts (some DB2 monitoring tools provide this information, and it's also available via the DB2 command -DISPLAY BUFFERPOOL with the LSTATS option specified). In any case, I'm talking about net new buffers in the new pool -- not a reduction in BP1 buffers to offset the buffers allocated to the new pool. How big might the new pool be? For starters, I might think in terms of BP3 being half the size of BP1, with the possibility of getting larger still based on observed I/O rates. What I've mentioned for BP3 applies also to BP4 (or whatever you decide to use for the indexes associated with the tablespaces reassigned from BP1 to BP3). When you've reassigned the tablespaces (and indexes), monitor I/O activity for the new and "legacy" pools, and grow them (or leave them alone) based on what you see.
To create a pool or pools to be used for "pinning" some objects in memory. This is a specialized case of overall buffer pool configuration size enlargement (again, I'm talking about new pools having net new buffers -- assuming, as always, that you have enough server memory to back the new buffers). I blogged about pinning objects in pools a couple of years ago. The concept is simple: you assign an object or objects (tablespaces and/or indexes) to a pool (or pools) that has enough buffers to hold all of the pages of the objects assigned to the pool. Once the objects are in the pool, there is no more disk read I/O activity associated with the pool. Generally speaking, the best candidates for pinning are objects that are a) relatively small (maybe a few thousand pages or less) and b) accessed very frequently. If you do decide to have a pool (or pools) for object-pinning, consider specifying PGSTEAL(FIFO) for the pool, instead of going with the default of PGSTEAL(LRU). This has the effect of making the buffer-stealing algorithm for the pool first in / first out (FIFO) instead of least-recently-used (LRU). The rationale: FIFO, being simpler, is more CPU-efficient than LRU. It's less sophisticated, but with no need for buffer stealing (remember, the pool has enough buffers to hold all pages of all objects assigned to the pool), why not go with the cheapest page stealing algorithm available?

If you do move some objects to a new pool, know that this is done by issuing an ALTER TABLESPACE (or ALTER INDEX) statement with the new buffer pool specified for the object, followed by a stop and a start of the object (via the commands -STOP DATABASE and -START DATABASE) to put the change into effect (in a data sharing environment, the object must be in the stopped state when the ALTER TABLESPACE or ALTER INDEX statement is executed). With the STOP and START being necessary, you'll want to make the change during a period when the object can very briefly be made unavailable to application processes. If you can temporarily shut off the flow of application requests for the target object, the stop and start should complete in a few seconds (issuing the STOP DATABASE command with the AT COMMIT option can enable you to "break in" on an application process bound with RELEASE(DEALLOCATE) to get the target object stopped).

As you consider adding more pools to your DB2 buffer pool configuration, keep these primary advantages in mind: 1) you can better fine-tune the configuration by setting buffer pool parameters in a way that boosts performance and efficiency for accesses to certain classes of database objects, and 2) you can get more fine-grained buffer pool monitoring data, enabling you to allocate additional buffer resources where they'll do the most good. This has worked well at a lot of DB2 sites. It could be a winning approach for yours.

Better DB2 for z/OS Query Performance through Statistics

2010-10-01T15:13:00.000-07:00

It's been longer than I like since my last post -- a reflection of a particularly busy schedule of late. Today I'd like to focus on catalog statistics as a means of improving query performance in a DB2 for z/OS environment.

When a DB2 query doesn't perform as you want it to (response time and/or CPU time is too high), your first move is often, I'm sure, a review of the query's access path information -- something you'd get either from EXPLAIN data in the PLAN_TABLE, or through a graphical display of the query's access plan (among the IBM tools that can generate such a display are Optimization Service Center, Data Studio, and Optim Query Tuner). Analysis of this information could lead you to think of two common remedial actions: add a new index (or add a column or columns to an existing index) on a table targeted by the query; or, modify the query text in order to (for example) make a stage 2, non-indexable predicate stage 1 and indexable. These steps do often yield very positive results, but sometimes neither one is feasible.

Take the add (or alter) index option. Indexes are never free -- they take up disk space (though less with the index compression capability of DB2 9), and they make row insert and row delete operations more CPU-costly (they also increase the cost of updates when those updates change the value of indexed columns). Sometimes, the query performance benefits offered by a new index are seen as outweighing the index's costs. In other cases, creation of a new index on a table (or even adding a column to an existing index) has little chance of approval. This could be the case if there are already a lot of indexes defined on the table in question (I've seen upwards of 20 on a single table). Even if a table has only a few indexes, the idea of adding another might get a lot of push-back if performance-critical, data-changing batch jobs or online transactions that access the table can't be made any more expensive, for fear of missing SLA targets (sometimes an issue for batch workloads) or bottlenecking throughput (a potential concern for a high-volume transactional application).

If adding an index to a table is not do-able, what about modifying the query statement text to get a better-performing access path? That, too, can be a choice that's not available to you. Why? Two words: query tool. If the poorly performing query gets generated by a query tool on a user's PC, you may have to deal with the SQL statement as-is. Even if the query is in an application program that was developed in-house at your organization, changing the statement text may be more easily said than done. The developer who could make the change might be swamped with other work, and/or you might need to get a change request approved and that could take a good bit of time (depending on what else is on development's plate).

When a new index is unlikely and statement modification is not possible, are you at a dead end in terms of improving a query's performance? NO. Instead of giving up, try the catalog statistics route. A little clarification here: I'm not talking so much about making sure that statistics for the tables referenced by the query (and indexes on these tables) are accurate -- keeping those current through regular execution of the RUNSTATS utility is DB2 101. What I'm referring to is the richness of the statistics in your DB2 catalog. See, those statistics are the primary input to the DB2 optimizer when it is generating an access plan for an SQL statement. With more comprehensive information, the optimizer is more likely to choose the access path for a query that actually IS the best-performing path, versus one it THOUGHT was lowest-cost but which turned out to be high-cost.

So, what constitutes "more comprehensive" statistics? What I have in mind are value distribution statistics for columns that have skewed duplicate values. Here's what I mean by that: with just basic catalog statistics in place for a table (more on this momentarily), DB2 has two items of statistical information pertaining to the values stored in a column of the table: the column cardinality (i.e., the number of distinct data values in the column) and the number of rows in the table. For a unique column, cardinality will be equal to the number of rows in the table. For a non-unique column, cardinality will be less than the number of rows in the table, because duplicate values of the column will appear in some rows. Absent additional statistical information about the column, DB2 will assume that duplicate column values are evenly spread across the table's rows. For example, suppose that table ABC has 1 million rows, and column XYZ of that table has a cardinality of 1000. If that's all the statistical information that DB2 has for the column, it will assume that the 1000 distinct values of column XYZ are evenly spread across the 1 million rows of table ABC. In other words DB2 will assume that each of the 1000 values of column XYZ appears in 1000 of table ABC's rows. What if the spread of values in column XYZ is very much skewed? What if one value of the column appears in 900,000 of the 1 million rows in table ABC? And suppose that the next-most-frequently occurring value of column XYZ appears in 90,000 of the remaining 100,000 rows in table ABC? That disconnect between DB2's assumption (even spread of duplicate column values) and the reality of the situation (highly skewed column value distribution) can lead to poor-performing access path choices.

Fortunately, that "reality gap" can be filled via a couple of options that can be specified on a DB2 RUNSTATS utility control statement: FREQVAL and HISTOGRAM (the latter was introduced for mainframe DB2 via DB2 9 for z/OS -- I blogged about it last year when I was working as an independent DB2 consultant). With FREQVAL, you can tell the RUNSTATS utility to gather information about the top "n" most frequently occurring (or least frequently occurring, or both) values in a column (10 is a popular choice for "n" here, but you can go with another integer value if you'd like). This information (for each of the top "n" values, the value itself and the percentage of table rows containing that value in the specified column) is placed in the catalog table SYSIBM.SYSCOLDIST (or SYSCOLDISTSTATS in the case of a partitioned table). When the HISTOGRAM option is used, DB2 divides the columns values into "quantiles" (each a range of column values) and determines the number of unique column values within each quantile (the different quantiles cover about the same number of table rows). Note that while I've referred to individual columns, you can optionally have RUNSTATS generate FREQVAL or HISTOGRAM stats for a group of columns. In any case, with the value-frequency-distribution information gathered via FREQVAL, the DB2 query optimizer can much more accurately estimate the cost of candidate access paths for queries that have "equal" or range-type predicates (>, <, >=, <=) that reference non-unique columns. HISTOGRAM statistics are particularly useful for queries that have BETWEEN predicates that reference non-unique columns. More accurate access path cost estimation is the key to choosing the path that will actually deliver the best performance for a query.

OK, so armed with this information, what do you do? Should you have RUNSTATS generate FREQVAL and/or HISTOGRAM statistics for every non-unique column of every table in your database (and all the combinations thereof, while you're at it)? Sure, if your company gets mainframe cycles for free. A better approach is to get a good baseline of statistics in the catalog, and then to enrich them further on an as-needed basis. In my opinion, a good "base" RUNSTATS control statement for a RUNSTATS TABLESPACE utility would include these options:

TABLE(ALL) INDEX(ALL) KEYCARD

Note that this will get you, by default, "top 10" FREQVAL stats for the first key column of each index on every table in the tablespace.

So, you have your base of statistics (which you keep up-to-date, right?) in the catalog, and you have a query for which DB2 seems to have chosen the wrong access path. Now what? Well, if you're interested in productivity, take that query, plug it into the Optimization Service Center for DB2 for z/OS (free and downloadable) or Data Studio or Optim Query Tuner, and run the Statistics Advisor function. You'll get recommended RUNSTATS control statement specifications for the query, and these could well include the FREQVAL or HISTOGRAM options. Run RUNSTATS as recommended by the Statistics Advisor (hint: the recommendations labeled as "high priority" tend to give you the biggest bang for the buck), re-run the query (rebind the query-issuing program first, if it's a static SQL statement), and see if you get the better-performing access path that you think should have been chosen by DB2 in the first place. Armed with richer statistics, the optimizer can be expected to make better access path choices.

I took the approach described in the preceding paragraph a few months ago when I was helping an organization with some DB2 query tuning work: we had a query that was running long and chewing up a lot of CPU time, and our analysis of the access plan chosen for the query (we used Optimization Service Center for this purpose) showed that DB2 was significantly under-estimating the number of result set rows that would be qualified by a certain predicate (OSC and the other tools I've mentioned are great for this purpose -- they show you DB2's estimates of result set row filtering at each step of a query's access path). This, in turn, appeared to cause the optimizer to choose what turned out to be a high-cost access path for the query. We ran the Statistics Advisor for the query, and saw that the recommended RUNSTATS control statement included the FREQVAL option for some of the columns referenced in the query's predicates. We executed RUNSTATS as recommended, reran the query (it was a dynamic SQL statement), and saw that elapsed time decreased by about 50% (we did in fact get a different access path). That was great: a major query performance enhancement, without a new index and without query statement text modification. We just gave DB2 more information about the data in the target table, and the optimizer took it from there. Give this approach a try sometime, and write your own success story.

Of DB2 for z/OS Clustering Indexes, Implicit and Otherwise

2010-09-15T20:54:00.000-07:00

Do all of your DB2 for z/OS tables have clustering indexes? Unless you have one or more tables with no indexes at all, the answer to this question is, "yes." How's that? Read on...

About three years ago, I posted an entry to my "old" blog (the one I maintained during my years as an independent DB2 consultant) in which I explained the importance of data clustering in a DB2 environment. A lot of you probably know that an index defined on a table will be used by DB2 to physically sequence that table's rows if it (the index) was created with the CLUSTER attribute (or if, sometime after being created, it was altered to have this attribute). The index with the CLUSTER attribute (and there can be only one such index for a given table) is referred to as the associated table's explicit clustering index (or, more commonly, as just the clustering index). Suppose, however, that none of a table's indexes has the CLUSTER attribute? What then? In that case, the table will have an implicit clustering index. That will be the index that was the first one defined on the table.

The effect of an implicit clustering index on DB2 processing is the same as that of an explicit clustering index:

When a new row is to be added to a table that lacks an explicit clustering index, the target data page for the row (i.e., the page into which it should go if optimal data clustering is to be maintained) will be determined via the table's implicit clustering index.
When a tablespace is reorganized using the IBM DB2 REORG utility, rows unloaded from the tablespace will be sorted based on the key of the implicit clustering index of the table (or tables) stored in the tablespace.

Note that the statement above pertaining to REORG TABLESPACE has not always been true. There was a time when the IBM DB2 REORG utility would re-sequence rows in a table only if the table had an explicit clustering index. I'm not sure when this behavior changed, but I know that at least since DB2 for z/OS Version 8, REORG will sort data rows according to a table's clustering key, regardless of whether the clustering index is explicit or implicit.

Given that a table with an index will always have a clustering index, and that an implicit clustering index works as does an explicit clustering index, you might think that defining an explicit clustering index on a table is no big deal. I'd disagree with that assessment, and here's why: relying on the status of an index as the "oldest" one on a table to ensure its use as the table's clustering index (if you go the implicit route) could lead to an unexpected situation if that index were to be accidentally dropped. See, if you re-create the dropped index according to the original DDL (which lacked the CLUSTER attribute), it will no longer be the table's implicit clustering index if there are other indexes defined on the table. Why? Because the re-created index will no longer be the oldest one on the table (it will instead be the newest). One of the other indexes on the table will be the "new oldest" one, and that one will be the table's new implicit clustering index. In my opinion, having an explicit clustering index on each of your tables that has an index is a best practice.

I'll conclude this entry with a look at two interesting (to me, anyway) scenarios. First, consider a situation in which a table's implicit clustering index is altered with the addition of a new column (an extension of ALTER INDEX functionality introduced with DB2 for z/OS Version 8). Will it still be the table's implicit clustering index? Yes. The clustering key may have changed by way of the ALTER INDEX... ADD COLUMN operation, but the index is still the oldest one defined on the table.

Scenario two: suppose that index ABC, the first one created on table XYZ, has the CLUSTER attribute. If an ALTER INDEX statement with a NOT CLUSTER specification is subsequently executed for index ABC, does that mean that the index is no longer the table's clustering index? No, that's not what it means. Until such time as an ALTER INDEX statement with a CLUSTER specification is executed for some other index on table XYZ (or until a new index with the CLUSTER attribute is defined on table XYZ), index ABC will remain the table's clustering index (albeit an implicit clustering index) by way of its status as the oldest index defined on the table.

Take out the guesswork, OK? If you are going to have a clustering index on a table (and remember, you will if there are any indexes on the table), label it as such.

Thoughts on DB2 for z/OS Buffer Pool Sizing

2010-09-03T18:19:00.000-07:00

A couple of years ago, on my Catterall Consulting blog, I posted an entry in which I urged mainframe DB2 people to take advantage of 64-bit addressing, a capability that debuted with DB2 for z/OS Version 8 and which allowed, among other things, the allocation of very large buffer pools. Now, I'm singing the second verse of that same song (actually, about the twentieth verse -- I have to keep repeating it): IF YOU HAVE BIG MEMORY, YOU SHOULD HAVE BIG DB2 BUFFER POOLS.

Again and again I see it: a mainframe server (or logical partition thereof) that has a dozen or more gigabytes of central storage, and a production DB2 subsystem -- the only one on that z/OS instance -- that has a a buffer pool configuration with an aggregate size of a a gigabyte or less. Sometimes, pages of large and very heavily accessed tables and/or indexes are cached in a pool that has 80,000 buffers, or 40,000, or 15,000, or maybe just 10,000 buffers. The result? Way high rates of I/O activity, as in thousands of disk reads per second. That's a big drag on application performance, negatively impacting both throughput and CPU efficiency. When you have the server memory available to eliminate this undesirable situation, USE IT.

So, first things first: get a handle on your buffer pool I/O situation. You can use a DB2 monitor to do this, but my preference is to use the output of the command -DISPLAY BUFFERPOOL(ACTIVE) DETAIL. More specifically, I like to issue this command once, and then again an hour later. That way I get, in the output of the second issuance of the command, one hour's worth of buffer pool activity data. For each pool referenced in the output of the second command, I sum the numbers in five fields:

SYNC READ I/O (R)
SYNC READ I/O (S)
PREFETCH I/O (under SEQUENTIAL PREFETCH)
PREFETCH I/O (under LIST PREFETCH)
PREFETCH I/O (under DYNAMIC PREFETCH)

I take that total figure and divide it by the number of seconds between the first and second -DISPLAY BUFFERPOOL(ACTIVE) DETAIL commands, and voila -- I have the rate of disk read I/Os per second for each active pool. If that number is less than 100 for a buffer pool, I'm not likely to have a performance concern. If it's between 100 and 1000, I'll probably want to see about increasing the size of the pool. If it's north of 1000, I will definitely want to make that pool larger -- maybe much larger (the largest number I've seen for disk read I/Os associated with a DB2 buffer pool is a little over 8000 per second -- I'm sure there are larger numbers out there).

If you have a really high rate of disk read I/O activity for a pool and you want to make it larger, can you? That depends on whether or not the z/OS system in question has enough memory to accommodate a buffer pool size increase without negatively impacting other work on the server. How do you know that? A good way is to check the demand paging rate on the system. This figure indicates the number of times per second that a page, previously moved from central storage to auxiliary storage, is brought back into central storage to satisfy a program request. It's available by way of a z/OS monitor. The lower the demand paging rate, the less pressure there is on the system's central storage resource. What you want is for the demand paging rate to be less than 10 per second. You also want it to be more than zero, because a super-low demand paging rate means that the mainframe memory your organization has paid for is probably not being leveraged as it should be for system performance. If the demand paging rate is less than one per second, memory on your system is likely being underutilized. Enlarging one or more of your DB2 buffer pools is often a very good way to put underutilized server memory to productive use. In my experience, if there is one production DB2 subsystem on a z/OS instance, and if the size of that DB2 subsystem's buffer pool configuration is less than 10% of the amount of memory available to the z/OS instance, it's almost certain that the demand paging rate is very low. If you make a DB2 buffer pool (or pools) larger, check the demand paging rate again after the fact and make sure that it's still less than 10 per second (and note that an occasional incident such as a dump can cause the demand paging rate to briefly spike -- that's not a big deal).

Something else: if a buffer pool's I/O rate is really high (as in a few thousand per second), and the pool is pretty small (e.g., 20,000 buffers or less for a 4K pool), and the system's demand paging rate is really low, don't just dink and dunk your way to a larger buffer pool configuration. In other words, don't add 1000 buffers to a 15,000-buffer pool. Go for way bigger. Think in terms of doubling the pool's current size, or tripling it, or even quadrupling it. Later, after it's bigger (like, 80,000 buffers or more), you can think about growing it in smaller chunks, percentage-wise (e.g., maybe make it 50% larger). Whenever you take any action to enlarge a buffer pool, follow that with the -DISPLAY BUFFERPOOL commands that I mentioned previously, to gauge the effect of the size increase on the pool's disk read I/O rate. When you do that, in addition to checking on read I/Os per second, look to see if the number of of synchronous reads associated with sequential access (SYNC READ I/O(S)) went down. When a buffer pool is really undersized relative to the volume of requests for pages in objects assigned to the pool, it may be that when a set of 32 pages is brought into the pool via a prefetch read I/O, some of those pages are flushed from the pool before the requesting application process can access them. That drives the number of synchronous reads related to sequential access higher (because those flushed-out pages, when requested, will be read into memory individually). With a larger pool, page residency time increases, and it's less likely that prefetched pages will get flushed before they are accessed for the requesting application process.

Another thing: once an individual buffer pool (as opposed to the whole buffer pool configuration) gets to be pretty big (say, a gigabyte in size), before making it larger still consider the possibility of creating a new pool (maybe 25% or half the size of the big one) and moving some of the highest-activity objects from the really big pool to the new one. This is NOT a technical requirement, as a single 4K buffer pool can grow to a terabyte in size; rather, it's an opportunity to split some objects out in a way that will provide you with more granular buffer pool statistics and a chance to more finely tune the overall buffer pool configuration. Just a thought.

Finally, if your DB2 environment operates in data sharing mode on a parallel sysplex mainframe cluster, keep in mind that an increase in the size of a buffer pool (and you usually want a given pool to be the same size on all members) may make an enlargement of the associated group buffer pool highly advisable -- this mainly to ensure that the group buffer pool will have enough directory entries to prevent directory entry reclaims, which get in the way of top performance. A long time ago, when the earliest users of data sharing asked for a formula to help with group buffer pool sizing, I came up with a pretty simple one: add up the size (in MB) of the local pools, and divide that sum by three (this assumes the default ratio of five directory entries for every data entry in a group buffer pool). Fifteen years later, that simple formula still works really well. So if, in a four-way data sharing group, you want to grow BP5 to 120,000 buffers (480 MB) on each member, a good size for GBP5 would be:

(480 MB X 4 members) / 3 = 1920 MB / 3 = 640 MB

If GBP5 is currently smaller than that, take it up to 640 MB (or more) before taking BP5 to 120,000 buffers.

I hope that this information will help you to assess your buffer pool configuration from a performance perspective, and I hope that you'll grow your DB2 buffer pools to improve throughput and CPU efficiency (assuming that you have enough memory for this on your system -- and I'm sure that a lot of you do).

DB2 9 for z/OS: an Important Enhancement of Partition-Level Online REORG Functionality

2010-08-25T08:53:00.000-07:00

For a number of organizations, a key issue related to DB2 for z/OS Version 8 to Version 9 migration has been the elimination, in a DB2 9 environment, of the BUILD2 phase of partition-level online REORG. The concern with the elimination of BUILD2 had to do with the attendant restriction on the ability to execute online REORGs of multiple non-contiguous partitions of a tablespace in a parallel fashion. The good news that I want to highlight here is the removal of this restriction via the recently released fix for APAR PK87762. In the remainder of this post I'll explain what the BUILD2 phase of online REORG is (or was, from a DB2 9 perspective), the benefit of eliminating this utility phase, and the challenge that BUILD2 elimination posed for some DB2 for z/OS users prior to the availability of the aforementioned fix (the PTF is UK59095).

With Version 8 (and previous versions) of DB2 for z/OS, BUILD2 is the name of one of the phases of an online REORG TABLESPACE executed at the partition level (versus an online REORG of a partitioned tablespace in its entirety). During an online REORG of a tablespace partition, rows in the shadow data set for the partition are relocated relative to their position in the partition's active data set (this, of course, to restore clustering sequence for the partition). If there are any non-partitioned indexes on the tablespace (these are also known as NPIs), the index entries in those NPIs that point to rows in the partition being reorganized have to be updated to reflect the location of the rows in the shadow data set (which will be the active data set when the REORG job completes). BUILD2 is the utility phase in which these index entry corrections are made, and during that phase the logical partition of the NPI(s) that corresponds to the tablespace partition being reorganized is unavailable to application program. This means that INSERT and DELETE operations targeting the partition being reorganized cannot run during BUILD2 (nor can UPDATEs that change the value of a column in a key of an NPI, and SELECTs that would use the NPI to access data in the partition).

If BUILD2 completes in, say, 60 seconds or less, this may not be such a big deal. Even if BUILD2 runs for several minutes because of the number of rows in the partition being reorganized, the situation can be dealt with by causing BUILD2 to execute during a maintenance window. People do this by submitting the partition-level online REORG with a specification of MAXRO DEFER and LONGLOG CONTINUE, to put off execution of the REORG's final phases (including BUILD2). Upon entering the maintenance window, an -ALTER UTILITY command is issued to change MAXRO to a smallish number of seconds (e.g., 300), and REORG soon performs the final "catch-up" application of logged data changes to the shadow data sets, switches the shadow and active data sets, and completes BUILD2 processing.

What if you very rarely have a maintenance window? What if the partition being reorganized holds over 100 million rows, and BUILD2 runs too long to complete during a (for example) one-hour maintenance window? What then? For some DB2 Version 8 users, this meant that online reorganization of partitioned tablespaces could only be run at the tablespace level -- kind of a bummer, as one of the key benefits of tablespace partitioning is the ability to run utilities at the partition level.

So, BUILD2 elimination in the DB2 9 environment is a good thing, but the WAY in which BUILD2 was eliminated created, for some organizations, a new challenge. When a partition-level online REORG is run in a DB2 9 for z/OS system, any NPIs on the partitioned table will be reorganized in their entirety. That means that they have their own shadow data sets to which logged changes to the corresponding active data sets are applied. This makes BUILD2 unnecessary, but it also means that only one partition-level online REORG job can be run against the tablespace at any one time. That posed a problem for users who liked to get parallel reorganization of different partitions of a tablespace by running multiple REORG jobs targeting different partitions of a tablespace at the same time. Suppose, for example, that you want to run parallel online REORGs of partitions 2, 9, and 22 of a partitioned tablespace. In a DB2 Version 8 system, you'd run three separate online REORG jobs concurrently: one for each partition to be reorganized. In a DB2 9 environment, this approach does not work because the first of these jobs to get ahold of the tablespace's NPIs (to REORG these in their entirety) would block out the other jobs. You could not get around this restriction by submitting one online REORG job with a specification of PART 2, 9, 22 because this syntax was invalid. You could specify PART 2:22 and that would result in parallel online REORGs of partitions 2, 9, and 22, but also of all the other tablespace partitions in that range. There is a way, using an undocumented syntax, to run a REPAIR utility job that will flag certain partitions of a tablespace (e.g., 2, 9, and 22) as being in advisory REORG-pending status, after which an online REORG of the tablespace with the SCOPE PENDING option will result in the desired behavior (REORG only partitions 2, 9, and 22, in parallel); however, many DB2 users are understandably reluctant to go with this approach, and it is not one that I'd recommend.

At the IDUG North American Conference this past May, one of the IBM DB2 for z/OS developers mentioned that DB2 10 (now in beta release) would enable parallel online reorganization of non-contiguous partitions of a tablespace (i.e., partitions that aren't next to each other with regard to their sequence in a partitioned tablespace) with a single execution of REORG. That was great news for the future. What's great news NOW is the fix for the DB2 9 APAR I mentioned, PK87762. With this fix applied, DB2 9 REORG will now accept this syntax: PART(2, 9, 22). Not only that, but you can specify both individual partitions and a partition range in the same PART clause, as in PART(2, 9, 20:22). And there's more: now, when a LISTDEF used in an online REORG job references multiple individual partitions belonging to one tablespace, DB2 will execute REORG once for all of the tablespace's partitions referenced in the LISTDEF, versus running one REORG job for each partition in a serial fashion.

BUILD2 made partition-level online REORG not online-enough for some DB2 users, and I was pleased to see it go away with DB2 9. The (now former) inability to get parallel online REORGs of multiple non-contiguous partitions of a tablespace in a DB2 9 environment was seen by more than a few folks as being an irritatingly high price to pay for BUILD2 elimination. That problem has now been addressed. So, if you're planning a migration to DB2 9 and this issue has been a concern for you, cross it off your list. If you're already on DB2 9 and you've been pining for the days of unrestricted parallel partition-level online REORGs, get UK59095 on your system and pine no more.

DB2 for z/OS Query Parallelism: It's About More Than Response Time

2010-08-19T20:45:00.000-07:00

Query CPU parallelism -- the ability to split a query into multiple parts that can be executed in parallel -- made its debut as a mainframe DB2 feature with Version 4 of the product, back in the mid-1990s. To this day, lots of mainframers think of query parallelism as they did when it was brand new: it's a nice way to reduce the elapsed time of what would otherwise be long-running queries in a data warehouse environment. That view is on the mark, but it's overly narrow. I'm here to tell you that query parallelism can be a means of significantly reducing the cost of computing for an operational DB2 workload (by which I mean one associated with run-the-business versus decision-support applications).

What I'm talking about became possible when IBM introduced zIIP processors for its System z servers. zIIPs -- more formally known as System z Integrated Information Processors -- are "specialty" engines that can be used in the execution of certain types of work on a mainframe system. System z users like zIIPs because they lower the cost of computing on the mainframe platform in a double-barreled way: first, they cost less than general-purpose mainframe processors; second, they do not factor into the pricing of software that runs on System z servers (add a general-purpose engine, and your software costs will go up; add a zIIP, and they won't).

At lots of mainframe sites, the utilization of zIIP engines (if they are configured on the System z servers) is driven almost entirely by client-server DB2 work (i.e., by SQL statements that are issued from off-mainframe application servers and routed to the DB2 for z/OS data server through the DB2 distributed data facility, aka DDF). These SQL statements execute under what are called enclave SRBs (SRBs -- short for service request blocks -- are a type of control block that can represent a dispatchable piece of work in a z/OS system). Enclave SRBs, in turn, can be dispatched to zIIP engines (and the zIIP-friendliness of DB2 client-server computing becomes even more pronounced in a DB2 9 environment, thanks to native SQL procedures, which can utilize zIIP resources when invoked through the DB2 DDF).

This is all well and good, but at more than a few sites the client-server DB2 for z/OS workload goes way down when the bulk of the online users go home at the end of the working day. What's left to do at night is a sometimes huge amount of batch work. These jobs may drive the general-purpose engines on a mainframe server to a utilization rate of 90% or more for hours at a time, while zIIP engines on the same box sit there are two or three percent busy. Wouldn't it be great if those zIIPs, with their large reserves of processing capacity, could be put to work running some of the nightly batch programs? WELL THEY CAN, and your ticket for doing that is -- you guessed it -- query parallelism.

You see, when DB2 splits a query, the different parts of the query execute under enclave SRBs, and that means that they can use zIIP MIPS. Queries in batch jobs can be good candidates for parallelization because they often scan lots of data. Making queries in a batch job eligible for parallelization is easy: assuming we're talking static SQL, you just rebind the program's package with a specification of DEGREE(ANY) versus the default of DEGREE(1) -- there's no need to change program code (for dynamic queries, parallelization is enabled by setting the value of the DB2 special register CURRENT DEGREE to 'ANY'). And, parallelization doesn't change the query's result: you'll get the same data back, at a reduced cost because lower-cost zIIP engines helped get the work done.

Want to get started with putting zIIPs to work with your nightly batch load? Here's what I'd do:

Identify your most expensive batch programs with respect to in-DB2 CPU time (also known as "class 2" CPU time). Tools from IBM and other vendors can help you here (an example of an IBM tool that could be used for this purpose is IBM Tivoli Omegamon XE Performance Monitor on DB2 for z/OS -- check out the accounting report and the TOP subcommand).
Pick one of these expensive programs, and run a test. First, see if DB2 would parallelize any of the program's queries if DEGREE(ANY) were in effect. One really easy way to do this is to use the free and downloadable IBM Optimization Service Center for DB2 for z/OS -- for a given query to be analyzed, you click on "Specify Query Context" and select ANY from the drop-down menu for "Current Degree" under "Application Environment." Keep this in mind: DB2 can parallelize parts of a query's execution even if no partitioned tables are accessed (while table partitioning does tend to be a driver of query parallelism, partitioning is not necessarily a prerequisite for parallelism).
If the access plan information obtained in step 2 above shows that DB2 would parallelize one or more queries in the batch program, run a test in a test environment (ideally one with data volumes approximating those in production, but if not that, at least one with catalog statistics that closely match those in production): execute the program as-is, then rebind it with DEGREE(ANY) and run it again. Use your DB2 performance monitor to see how much of the job's CPU time has shifted to a zIIP engine, and how much the job's general-purpose CPU consumption has decreased. Also see for yourself that the program's query results were not changed by parallelization (for example, FETCH counts should be identical if the program processes all rows in the query result sets).
Assuming a positive result from step 3 above, implement the change in production. Keep in mind that this may require modifying your standard program bind procedure, which may specify DEGREE(1) by default -- you don't want a subsequent rebind to inadvertently change DEGREE for the program's package back to 1.
Repeat steps 2 through 4 with another of the identified high-cost batch programs.

A few other things to keep in mind:

In a DB2 subsystem that is not dedicated to a data warehouse workload, you may want to set the value the PARAMDEG parameter in the DB2 ZPARM module to a value other than the default of 0, which lets DB2 choose the degree of parallelization for a queries (i.e., the number of pieces into which a given query can be split). A value in the low-to-mid single digits (e.g., 4 or 5) might be a good choice -- you don't want too large a number of split queries in the system when you're running a high-volume batch workload.
Keep an eye on the utilization of space in the DB2 EDM pool -- a package bound with DEGREE(ANY) will be larger than the same package bound with DEGREE(1).
DON'T rebind ALL batch packages with DEGREE(ANY) in one fell swoop. Be systematic about this. Do a few at a time, and maybe pause when you get the level of zIIP engine utilization on the system where you want it to be.

If you have zIIPs on your mainframe(s), I hope you'll look into the parallelization possibilities to get more out of those lower-cost engines. If you don't have zIIPs because you think you have a workload that can't exploit them -- think again.

People who think of query parallelization only in terms of its potential to reduce query run times are missing another important benefit of the technology: it's a zIIP-utilizer that can save your organization money, with no need to change program code. Gotta like that.

Wow II - DB2 Data Sharing Comes to the Linux/System x Platform

2010-08-10T19:10:00.000-07:00

Last year, when I was working as an independent DB2 consultant and trainer, I posted a blog entry on IBM's announcement of its DB2 pureScale technology. pureScale brought to DB2 for AIX, running on IBM's POWER6 and POWER7 servers, the advanced shared-data architecture technology that had delivered, for 15 years, unprecedented levels of availability and scalability for users of DB2 running on IBM System z servers. With pureScale, organizations could harness the processing resources of multiple DB2 for AIX servers sharing concurrent read/write access to one database in a clustered-server, shared-disk configuration. Though I had been around DB2 data sharing since its beta release in the mid-1990s, the pureScale announcement struck me as being really big news. "Wow" pretty much summed up my initial reaction, and that's how I opened the aforementioned blog entry.

Less than a year later, it's time for Wow II. A few days ago (August 5, to be precise), at an event in Beijing, IBM took the wraps off of DB2 pureScale for the Intel-based System x server platform running the SUSE Linux operating system (the supported servers are the System x3650 M3, the x3690 X5, and the x3850 X5). AIX and POWER are a great computing combination, delivering high availability, rock-solid reliability, and a particularly rich set of features and functions. That said, the combination of Linux and Intel-based servers continues to advance as a platform for enterprise data-serving. I remember thinking, as I considered the initial pureScale announcement (Wow I), that it would be great to see the technology extended to Linux and System x. What surprises me about the announcement of DB2 pureScale for System x is that it happened so soon after pureScale first made the scene. Yeah, I was hoping for this, but I thought I'd have to wait longer before seeing it.

Linux-on-Intel users, trust me on this one: you now have a data-serving solution that can take your business-critical database systems to heights of up-time and throughput that you may have thought were unattainable. Remember, this technology -- global lock and page cache structures housed in servers that act as shared-memory devices, forming the hub of a configuration that can scale up to dozens of nodes with remarkably low overhead -- implements the same shared-data architecture that has proven itself in IBM parallel sysplex mainframe clusters that have delivered the goods for years, in some of the most demanding high-availability and high-volume application environments in the world. Is it your only shared-data choice in the world of relational database and Linux/Intel servers? No. Is it your best choice? Absolutely. Hands-down. No doubt about it.

Check out DB2 pureScale on System x, and have some Wow time of your own.

Back to Blue

2010-08-05T19:02:00.000-07:00

Well, I've come full circle as a database professional. I was working for IBM in a branch office in Dallas when DB2 was announced back in 1983. A few years later, still in that branch office, I helped some organizations install DB2 Version 1 Release 2 (so, I wasn't quite on the ground floor, with respect to DB2 release experience). I wanted to do all DB2, all the time, and in 1990 that wish was granted when I became part of the mainframe DB2 national technical support team at IBM's Dallas Systems Center. The nineties was a really fun decade for me, work-wise, as I helped organizations all over the world succeed with DB2 -- being part of the roll-out of DB2 data sharing/parallel sysplex technology was especially gratifying.

Great as that situation was, I left IBM in 2000 to become part of the user segment of the DB2 community, joining Atlanta-based CheckFree Corporation (later acquired by Fiserv). As a DB2 user, I was eligible to serve on the Board of Directors of the International DB2 Users Group, and I was presented with that opportunity in 2001 (I was a member of the IDUG Board for seven years, and served as the Organization's President in 2006-2007). I was also invited to be a regular columnist for IBM Data Management Magazine (formerly DB2 Magazine), and I'm in my 11th year of that enjoyable extracurricular activity.

In mid-2007, I again shifted career gears, this time hanging out my own shingle as President of Catterall Consulting. Soon thereafter, I started blogging about DB2, sharing lessons learned in my work as a DB2 consultant and trainer. After a few years, I realized that being part of a larger organization was a better fit for me than going it alone, and when I had the chance to rejoin IBM in a DB2-related capacity, I took it. It feels good to be back at Big Blue. I plan on being here for a long time to come.

The 121 entries in my Catterall Consulting blog are still viewable at http://catterallconsulting.blogspot.com/, and I will continue to respond to comments left by readers of those entries. New posts will appear in this blog. As before, most of what I write will be about DB2 for z/OS, but I'm a big fan of DB2 for Linux, UNIX, and Windows, as well, and I'll occasionally have something to say about some aspect or another of DB2 for LUW.

In entries to come, I hope to provide DB2 information that will be useful to readers. Thanks for stopping by. Please visit again.