In the summer of 2008, I posted a blog entry on page-fixing DB2 buffer pools, a feature introduced with DB2 for z/OS Version 8. A recent discussion I had with a client about buffer pool page-fixing brought to light two aspects of this performance tuning option that, I believe, are overlooked by some DB2 users. In this post I'll describe how you can make a quick initial assessment as to whether or not the memory resource of a mainframe system is sufficient to support buffer pool page-fixing, and I'll follow that with a look at the "bonus" performance impact that can be realized by buffer pool page-fixing in a DB2 data sharing environment.

Gauging the server memory situation. As pointed out in the aforementioned 2008 blog entry on the topic, page-fixing a buffer pool can reduce CPU consumption by eliminating the requests that DB2 would otherwise have to make of z/OS to fix in memory -- and to subsequently release -- a buffer for every read of a page from, or write of a page to, the disk subsystem. These page fix/page release operations are individually inexpensive, but the cumulative CPU cost can be significant when the I/Os associated with a pool number in the hundreds (or thousands) per second. The prospect of removing that portion of a DB2 workload's CPU utilization may have you thinking, "Why not?" Well, there's a reason why PGFIX(NO) is the default setting for a DB2 buffer pool, and it has to do with utilization of a mainframe server's (or z/OS LPAR's) memory resource.

With PGFIX(NO), the real storage page frames occupied by DB2 buffers are candidates for being stolen by z/OS, should the need arise. If something has to be read into memory from disk, and there is no available page frame to accommodate that read-in, z/OS will make one available by moving its contents to a page data set on auxiliary storage (if that relocated page is subsequently referenced by a process, it will be brought back into server memory from auxiliary storage -- this is known as demand paging). z/OS steals page frames according to a least-recently-used algorithm: the longer a page frame goes without being referenced, the closer it moves to the front of the steak queue. If a DB2 buffer goes a long time without being referenced, it could be paged out to auxiliary storage.

So, page-fixing a buffer pool in memory would preclude z/OS from considering the associated real storage page frames as candidates for stealing. The important question, then, is this: would some of those pages be stolen by z/OS if they weren't fixed in memory from the get-go? If so, then page-fixing that pool's buffers might not be such a great idea: in taking away some page frames that z/OS might otherwise steal, buffer pool page fixing could cause page-steal activity to increase for other subsystems and application processes in the z/OS LPAR. Not good.

Fortunately, there's a pretty easy way to get a feel for this: using either your DB2 monitor (an online display or a statistics report) or the output of the DB2 command -DISPLAY BUFFERPOOL DETAIL, look for fields labeled "PAGE-INS REQUIRED FOR READ" and "PAGE-INS REQUIRED FOR WRITE" (or something similar to that). What these fields mean: a page-in is required for a read if DB2 wants to read a page from disk into a particular buffer, and that buffer has been paged out to auxiliary storage (i.e., the page frame occupied by the buffer was stolen by z/OS). Similarly, a page-in is required for a write if DB2 needs to write the contents of a buffer to disk and the buffer is in auxiliary storage.

If, for a pool, the PAGE-INS REQUIRED FOR READ and PAGE-INS REQUIRED FOR WRITE fields both contain zeros, it is likely that the pool, from a memory perspective, is "V=R" anyway (that is to say, the amount of real storage occupied by the pool is probably very close to, if not the same as, its size in terms of virtual storage). In that case, going with PGFIX(YES) should deliver CPU savings without increasing pressure on the server memory resource, since the page frames being stolen are probably not those that are occupied by that pool's buffers. If you want an added measure of assurance on this score, issue a -DISPLAY BUFFERPOOL DETAIL(*) command. The (*) following the DETAIL keyword tells DB2 that you want statistics for the pool since the time it was last allocated. That might have been days, or even weeks, ago (the command output will tell you this), and if you see that the "PAGE-INS REQ" fields in the read and write parts of the command output contain zeros for that long period of time, it's a REALLY good bet that the pool's occupation of real storage won't increase appreciably if you go with PGFIX(YES). For even MORE assurance that the memory resource of the z/OS LPAR in which DB2 is running is not under a lot of pressure, check the "PAGE-INS REQUIRED" numbers for the lower-activity pools (those with fewer GETPAGE requests than others). If even these show zeros, you should be in really good shape, memory-wise.

With all this said, keep a couple of things in mind. First, even though your "PAGE-INS REQUIRED" numbers may give you a high degree of confidence that going to PGFIX(YES) for a buffer pool would be a good idea, make sure to coordinate this action with your z/OS systems programmer. That person has responsibility for seeing that z/OS system resources (such as server memory) are effectively managed and utilized, and you need to make sure that the two of you are on the same page (no pun intended) regarding buffer pool page-fixing. If you've done your homework, and you let the z/OS systems programmer do his (or her) homework (such as looking at z/OS monitor-generated system paging statistics), getting to agreement should not be a problem. Second, be selective in your use of the PGFIX(YES) buffer pool option. The greater the amount of I/O activity for a pool, the greater the benefit of PGFIX(YES). I'd recommend considering page-fixing for pools for which the rate of disk I/O activity is at least in the high double digits (writes plus reads) per second (and be sure to include prefetch reads when calculating the rate of disk I/O operations for a buffer pool). By staying with PGFIX(NO) for your lower-activity pools, you ensure that DB2 will make some buffer pool-associated page frames available to z/OS for page-out, should something cause the LPAR's memory resource to come under significant pressure.

And for you data sharing users... Just a couple of weeks ago, someone told me that he was under the impression that page-fixing buffer pools would have a negative performance impact in a DB2 data sharing environment. NOT SO. Assuming (as mentioned above) that your server memory resource is sufficient to accommodate page-fixing for one or more of your buffer pools, the resulting CPU efficiency benefit should be MORE pronounced for in a data sharing group versus a standalone DB2 system. How so? Simple: the buffer pool page fix/page release activity that occurs for DB2 reads to, and writes from, the disk subsystem with PGFIX(NO) in effect also occurs for writes of pages to, and reads of pages from, coupling facility group buffer pools. Like disk I/Os, page read and write actions involving a group buffer pool can number in the thousands per second. PGFIX(YES) eliminates the overhead of page fix/page release requests for disk I/Os AND for group buffer pool page reads and writes. So, if you're running DB2 in a data sharing configuration, you have another incentive to check out the page-fix option for your high-use buffer pools.

Last week, I attended a 1-day IBM System z "Technology Summit" education event in Atlanta. It was a multi-track program, and the DB2 for z/OS track ("Track 2") was excellent, in terms of both content and quality of presentation delivery (and it was FREE -- check out the remaining North American cities and dates for this event at http://www-01.ibm.com/software/os/systemz/summit/). The first presentation of the day, delivered by Jim Reed of IBM's Information Management software organization, focused on mainframe market trends and IBM's DB2 for z/OS product strategy. Jim's talk contained several nuggets of information that underscore the solid present and bright future of DB2 for z/OS as a platform for business intelligence applications. In this post, I'll share this information with you, along with some of my own observations on the topic.

BI important? How about most important? Jim started out with a reference to a recent IBM Global CIO survey which asked participants to identify their top priority. You know what's hot in IT circles these days: virtualization, mobility apps, regulatory compliance. So, what came out on top with regard to CIO priorities? Analytics and business intelligence. That's not very surprising, as far as I'm concerned. Having spent years on optimizing efficiency, squeezing costs out of every facet of their operations, organizations are increasingly focused on optimizing performance. Are they offering the right mix of products and services to their customers? Are they selling to the right people? Are they delivering value in a way that separates them, in the eyes of their customers, from their competitors? Data warehouse systems are key drivers of success here, enabling companies to generate actionable intelligence from their data assets (and the breadth of these data assets keeps expanding, including now not just traditional point-of-sale and other business transactions, but e-mails, customer care interactions -- even company- and product-related comments posted on external-to-the-enterprise Web sites).

Big iron has big mo. At the same time that BI is heating up as an area of corporate endeavor, the mainframe -- long seen as a workhorse for run-the-business OLTP and batch workloads -- is growing in popularity as a platform for BI applications. Jim spoke of several factors that are putting wind in System z's sails with regard to data warehousing. He cited a Gartner report that spotlighted key BI issues with which companies are grappling now. This list of front-burner concerns included:

• High availability. Data warehouses are more likely these days to get a "mission critical" designation. Many (including one I worked on just last month) are customer-facing systems, and a lot of those are the subject of service level agreements.
• Mixed workload performance. This was identified as the number one performance issue for data warehouses. Mixed BI workloads, in which fast-running, OLTP-like queries vie with complex, data-intensive analytic processes for system resources, are becoming common as so-called "operational BI" gains prominence.
  

Then, of course, there's the matter of data protection, on which so much else depends. Jim mentioned that 33% of people recently surveyed indicated that they would QUIT doing business with a company if that company experienced a data security or privacy breach and was seen as being responsible for the incident.

So, to address these key issues, you'd probably want to build your data warehouse on a hardware/software platform known for high availability, sophisticated workload management capabilities, and strong, multi-layered data protection and access control. Hmmm. Sounds like mainframe DB2 to me. Keep in mind, too, that the well-known availability and workload management strengths of System z and z/OS and DB2 are made even stronger when DB2 is deployed in data sharing mode on a parallel sysplex mainframe cluster configuration.

Oh, and let's not forget that the legendary reliability of mainframe systems is not just a matter of advanced hardware and software technology (good as that stuff is) -- it also reflects the deep skills and robust processes (around change management, performance monitoring and tuning, capacity planning, business continuity, etc.) that typify the teams of professionals that support organizations' mainframe computing environments. As BI applications continue to move from "nice to have" to "must have" in the eyes of corporate leaders, it stands to reason that IT executives would want to house these essential systems on the server platform that exemplifies "rock solid," and to assign their care to the people in whom they have the utmost trust and confidence -- mainframe people.

One more trend driving BI workloads to System z is the increased frequency with which data warehouse databases are being updated. Not long ago, the "query by day, update at night" model predominated. Now, many BI application users demand that updates of source data values be reflected more quickly in the data warehouse database -- sometimes in a near-real-time manner. A lot of the source data that supplies data warehouses comes from mainframe databases and files, and locating the data warehouse close to that source data can facilitate round-the-clock updating.

Let's make a deal. The technical arguments for building a data warehouse on a mainframe platform are many and strong, but what about the financial angle? IBM has been pretty busy in this area of late. I already knew of DB2 Value Unit Edition pricing, which makes DB2 for z/OS available for a one-time charge for net new workloads of certain types, including data warehousing. I'll admit to not having known about IBM's System z Solution Edition series (announced in August of last year) before Jim talked about them during his presentation. Included in this set of offerings is the System Z Solution Edition for Data Warehousing, a package of hardware, software (including DB2), and services that can help an organization to implement a mainframe-based data warehouse system in a cost-competitive way.

If your organization is serious about data warehousing, get serious about your data warehouse platform. Mainframes deliver the availability, mixed workload performance management, security, and -- yes -- total cost of ownership that can improve your chances of achieving BI success. Analyze that.

Some of the new performance features in DB2 Version 9 for z/OS are built into the software.  To leverage some of these functions and features, system maintenance DBAs and application DBAs need to coordinate their efforts to realize the benefits.

A couple of these enhancements that are related to new DB2 9 for z/OS zParms are the “support for optimistic locking” and “skipping locked rows” options.  These two features highlight the new enhancements that IBM is implementing in DB2 to address application locking issues. 

Locking always becomes a problem for databases that are extended for new applications other than their original purpose.  Also the latest .NET and Java application sometimes are challenging when they try to cache too much data.

The use of these two new performance enhancements built into DB2 9 for z/OS have some restrictions and considerations, but can be of some relief when applications locking issues are plaguing your system.  These new enhancements are detailed in the DB2 Version 9 manuals along with additional write-ups in section 5.6 of the new IBM Redbook, DB2 9 for z/OS Serialization and Concurrency Control (sg244725—available at the http://www.redbooks.ibm.com/abstracts/sg244725.html) This new Redbook discusses all the many parameter options for addressing locking issues like LOCKSIZE, LOCKMAX, MAXROWS, along with design guidelines for getting the best concurrency and serialization within your applications.

So if you want to understand your locking options better, research and leverage these new “support for optimistic locking” and “skipping locked rows” options within your DB2 9 for z/OS system and applications.

In part 1 of this two-part entry, posted last week, I wrote about some of the more interesting changes that I've seen in DB2 for z/OS data sharing technology over the years (about 15) since it was introduced through DB2 Version 4. More specifically, in that entry I highlighted the tremendous improvement in performance with regard to the servicing of coupling facility requests, and described some of the system software enhancements that have made data sharing a more CPU-efficient solution for organizations looking to maximize the availability and scalability of a mainframe DB2 data-serving system. In this post, I'll cover changes in the way that people configure data sharing groups, and provide a contemporary view of a once-popular -- and perhaps now unnecessary -- application tuning action.

Putting it all together. Of course, before you run a DB2 data sharing group, you have to set one up. Hardware-wise, the biggest change since the early years of data sharing has been the growing use of internal coupling facilities, versus the standalone boxes that were once the only option available. The primary advantage of internal coupling facilities (ICFs), which operate as logical partitions within a System z server, with dedicated processor and memory resources, is economic: they cost less than external, standalone coupling facilities. They also offer something of a performance benefit, as communication between a z/OS system on a mainframe and an ICF on the same mainframe is a memory-to-memory operation, with no requirement for the traversing of a physical coupling facility link.

When internal coupling facilities first came along in the late 1990s, organizations that acquired them tended to use only one in a given parallel sysplex (the mainframe cluster on which a DB2 data sharing group runs) -- the other coupling facility in the sysplex (you always want at least two, so as to avoid having a single point of failure) would be of the external variety. This was so because people wanted to avoid the effect of the so-called "double failure" scenario, in which a mainframe housing both an ICF and a z/OS system participating in the sysplex associated with the ICF goes down. Group buffer pool duplexing, delivered with DB2 Version 6 (with a subsequently retrofit to Version 5), allayed the double-failure concerns of those who would put the group buffer pools (GBPs) in an ICF on a server with a sysplex-associated z/OS system: if that server were to fail, taking the ICF down with it, the surviving DB2 subsystems (running on another server or servers) would simply use what had been the secondary group buffer pools in the other coupling facility as primary GBPs, and the application workload would continue to be processed by those subsystems (in the meantime, any DB2 group members on the failed server would be automatically restarted on other servers in the sysplex). Ah, but what of the lock structure and the shared communications area (SCA), the other coupling facility structures used by the members of a DB2 data sharing group? Companies generally wanted these in an external, standalone CF (or in an ICF on a server that did not also house a z/OS system participating in the sysplex associated with the ICF). Why? Because a double-failure scenario involving these structures would lead to a group-wide failure -- this because successful rebuild of the lock structure or SCA (which would prevent a group failure) requires information from ALL the members of a DB2 data sharing group. If a server has an ICF containing the lock structure and SCA (these are usually placed within the same coupling facility) and also houses a member of the associated DB2 data sharing group, and that server fails, the lock structure and SCA will not be rebuilt (because a DB2 member failed, too), and without those structures, the data sharing group will come down.

Nowadays, parallel sysplexes configured with ICFs and no external CFs are increasingly common. For one thing, the double-failure scenario is less scary to a lot of folks than it used to be, because a) actually losing a System z server is exceedingly unlikely (it's rare enough for a z/OS or a DB2 or an ICF to crash, and rarer still for a mainframe itself to fail), and b) even if a double-failure involving the lock structure and SCA were to occur, causing the DB2 data sharing group to go down, the subsequent group restart process that would restore availability would likely complete within a few minutes (with duplexed group buffer pools, there would be no data sets in group buffer pool recover pending status, and restart goes much more quickly when there's no GRECP). Secondly, organizations that want insurance against even a very rare outage situation that would probably not exceed a small number of minutes in duration can eliminate the possibility of an outage-causing double-failure by implementing system duplexing of the lock structure and the SCA. System duplexing of the lock structure and SCA increases the overhead of running DB2 in data sharing mode (more so than group buffer pool duplexing), and that's why some organizations use it and some don't -- it's a cost versus benefit decision.

Another relatively recent development with regard to data sharing set-up is the availability of 64-bit addressing in Coupling Facility Control Code, beginning with the CFLEVEL 12 release (Coupling Facility Control Code is the operating system of a coupling facility, and I believe that CFLEVEL 16 is the current release). So, how big can an individual coupling facility structure be? About 100 GB (actually, 99,999,999 KB -- the current maximum value that can be specified when defining a structure in the specification of a Coupling Facility Resource Management, or CFRM, policy). For the lock structure and the SCA, this structure size ceiling (obviously well below what's possible with 64-bit addressing) is totally a non-issue, as these structures are usually not larger than 128 MB each. Could someone, someday, want a group buffer pool to be larger than 100 GB? Maybe, but I think that we're a long way from that point, and I expect that the 100 GB limit on the size of a coupling facility structure will be increased well before then.

DEALLOCATE or COMMIT? DB2 data sharing is, for the most part, invisible to application programs, and organizations implementing data sharing groups often find that use of the technology necessitates little, if anything, in the way of application code changes. That said, people have done various things over the years to optimize the CPU efficiency of DB2-accessing programs running in a data sharing environment. For a long time, one of the more popular tuning actions was to bind programs executed via persistent threads (i.e., threads that persist across commits, such as those associated with batch jobs and with CICS-DB2 protected entry threads) with the RELEASE(DEALLOCATE) option. This was done to reduce tablespace lock activity: RELEASE(DEALLOCATE) causes tablespace locks (not page or row locks) acquired by an application process to be retained until thread deallocation, as opposed to being released (and reacquired, if necessary) at commit points. The reduced tablespace lock activity would in turn reduce the type of data sharing global lock contention called XES contention (about which I wrote in last week's part 1 of this two-part entry). There were some costs associated with the use of RELEASE(DEALLOCATE) for packages executed via persistent threads (the size of the DB2 EDM pool often had to be increased, and package rebind procedures sometimes had to be changed or rescheduled to reflect the fact that some packages would remain in an "in use" state for much longer periods of time than before), but these were typically seen as being outweighed by the gain in CPU efficiency related to the aforementioned reduction in the level of XES contention.

All well and good, but then (with DB2 Version 8) along came data sharing Locking Protocol 2 (also described in last week's part 1 post). Locking Protocol 2 drove XES contention way down, essentially eliminating XES contention reduction as a rational for pairing RELEASE(DEALLOCATE) with persistent threads. With this global locking protocol in effect, the RELEASE(DEALLOCATE) versus RELEASE(COMMIT) package bind decision is essentially unrelated to your use of data sharing. There are still benefits associated with the use of RELEASE(DEALLOCATE) for persistent-thread packages (e.g., a slight improvement in CPU efficiency due to reduced tablespace lock and EDM pool resource release and reacquisition, more-effective dynamic prefetch), but take away the data sharing effieicncy gain of old that is now provided by Locking Protocol 2, and you might decide to be more selective in your use of RELEASE(DEALLOCATE). If you implement a DB2 data sharing group, you may just leave your package bind RELEASE specifications as they were in your standalone DB2 environment.

What new advances in data sharing technology are on the way? We'll soon see: IBM is expected to formally announce DB2 Version "X," the successor to Version 9, later this year. I'll be looking for data sharing enhancements among the "What's New?" items delivered with Version X, and I'll likely report in this space on what I find. Stay tuned.

Post a comment below. Remember, you'll need to  log in or register if you don't see the Reply button.

With the end of the year in sight, it's a good time to tie up loose ends, as we say here in the USA. Thus it is that I've decided to focus, in this last post to my blog in 2009, on indexes as they pertain to DB2 for z/OS partitioned tables. That subject qualifies as a "loose end," because several years after the introduction of table-controlled partitioning with DB2 for z/OS V8, some folks are still not certain as to what can and cannot be done with indexes defined on partitioned tables. I'll try, in this entry, to clear things up.

When a table is partitioned by way of an index specification (the only way to partition a table prior to DB2 V8), index options for the table are pretty straightforward. The index that describes the partitioning scheme (i.e., the one with the PART integer VALUES (constant) clause in the CREATE INDEX statement) is called the partitioning index. No other index on that table is called a partitioning index, and no other index on the table is physically partitioned. Any index on a unique key can be defined as UNIQUE. Only the partitioning index can be the table's clustering index.

Starting with DB2 V8, table partitioning can be controlled by way of a table's definition (through the PARTITION BY and PARTITION integer ENDING AT (constant) clauses of CREATE TABLE). Table-controlled partitioning (enhanced in DB2 V9 via the partition-by-range universal tablespace) is way better than index-controlled partitioning, but this important DB2 advancement did change -- considerably -- the landscape as far as index options are concerned. First and foremost: for a table-controlled partitioned table, any index on a key that starts with the table's partition-by column or columns is called a partitioning index (and "starts with" means that the columns of a multi-column partitioning key appear in the order specified in the CREATE TABLE statement); so, if a table's partitioning key is COL_X, COL_Y then an index on COL_X, COL_Y, COL_A is a partitioning index, and so is an index on COL_X, COL_Y, COL_B (but an index on COL_Y, COL_X, COL_D would not be a partitioning index, because the order of the partition-by columns does not match the order specified in the table's definition). Among the implications of this rule: a table-controlled partitioned table may have several partitioning indexes, or it may not have any partitioning indexes. Furthermore, a partitioning index may or may not be physically partitioned. Continuing with the example of the table partitioned on COL_X, COL_Y, an index on COL_X, COL_Y, COL_D that does not have the PARTITIONED clause in its definition is partitioning (because its key begins with the table's partitioning key) but not partitioned (because it was not defined with the PARTITIONED clause). Possible, of course, doesn't necessarily mean advisable -- I don't see why you would have a partitioning index that is not also partitioned.

Next: for a table-controlled partitioned table, a secondary index (i.e., one that is not a partitioning index) can itself be partitioned -- you just have to specify PARTITIONED in the definition of the index (this will cause the index to be physically partitioned along the lines of the underlying table, so that partition 1 of the secondary index will contain the keys for rows in partition 1 of the table). A secondary index that is partitioned is called a data-partitioned secondary index, or DPSI (an index that is not partitioned is called a non-partitioned index, or NPI).

Third: there is a restriction on DPSIs with regard to uniqueness (a restriction that was loosened somewhat with DB2 V9). In a DB2 for z/OS V8 environment, no DPSI can be defined as unique (and remember: a DPSI is a partitioned index that is not a partitioning index -- a partitioning index can be unique). If you try to create a secondary index with both UNIQUE and PARTITIONED in the index definition, you'll get a -628 SQL code (and an accompanying error message indicating that "clauses are mutually exclusive"). In a DB2 V9 environment (and this was recently pointed out by DB2 consultant Peter Backlund in a thread on the DB2-L discussion forum), a DPSI can be defined as UNIQUE if the index key contains the table's partition by column or columns. Once again, consider the table partitioned on COL_X, COL_Y. A DPSI defined on COL_A, COL_Y, COL_B, COL_X could have the UNIQUE attribute because the index key contains all of the table's partition-by columns (and note that the partition-by columns do not have to be in any particular order within the DPSI's key -- they just have to be present within the key). Can there be multiple unique DPSIs defined on a DB2 V9 table-controlled partitioned table? Yes -- again, what's required is that the underlying table's partition-by columns be included in the key of a DPSI that is to be defined as UNIQUE.

Finally: with regard to the CLUSTER attribute, you have flexibility with a table-controlled partitioned table that you don't have with an index-controlled partitioned table. For an index-controlled partitioned table, the partitioning index will be the table's clustering index. For a table-controlled partitioned table, any one index can be the table's clustering index (and of course, a table can only have one index with the CLUSTER attribute). The clustering index could be a partitioning index or a secondary index (whether a DPSI or an NPI). The ability to cluster a table with one key and partition it by another key is, in my opinion, one of the key advantages of table-controlled partitioning over index-controlled partitioning (other pluses include the ability to add partitions to a table-controlled partitioned table, and the ability to rotate partitions in a "first to last" manner).

Is all that clear? I hope so. Table-controlled partitioning is a VERY good thing -- well worth the effort of getting your arms around the new rules regarding indexes on table-controlled partitioned tables.

Throughout my 27 years in IT, I've enjoyed the constancy of opportunities to learn new things. I look forward to more of the same in 2010. Have fun ringing in the new year!

One of the more interesting features delivered in recent years for DB2 for z/OS is the data-partitioned secondary index, or DPSI (pronounced "DIP-see"). Introduced with DB2 for z/OS Version 8, DPSIs are defined on table-controlled partitioned tablespaces (i.e., tablespaces for which the partitioning scheme is controlled via a table-level -- as opposed to an index-level -- specification), and are a) non-partitioning (meaning that the leading column or columns of the index key are not the same as the table's partitioning key), and b) physically partitioned along the lines of the table's partitions (meaning that partition 1 of a DPSI on partitioned table XYZ will contain entries for all rows in partition 1 of the underlying table -- and only for those rows).

[For more information about partitioned and partitioning indexes on table-controlled partitioned tablespaces, see my blog entry on the topic, posted a few weeks ago.]

Here's what makes DPSIs really interesting to me:

• They are a great idea in some situations.
• They are a bad idea in other situations.
• You may change the way you think about them in a DB2 9 context, versus a DB2 V8 environment.

DPSI do: a great example of very beneficial DPSI use showed up recently in the form of a question posted to the DB2-L discussion list (a great forum for technical questions related to DB2 for z/OS and DB2 for Linux, UNIX, and Windows -- visit the DB2-L page on the Web to join the list). The DB2 DBA who sent in the question was working on the design of a data purge process for a particular table in a database managed by DB2 for z/OS V8. The tablespace holding the data was partitioned (in the table-controlled way), with rows spread across seven partitions -- one for each day of the week. The sole purge criterion was date-based, and the DBA was thinking of using a partition-level LOAD utility, with REPLACE specified and an empty input data set, to clear out a partition's worth of data (a good thought, as purging via SQL DELETE can be pretty CPU-intensive if there are a lot of rows to be removed from the table at one time). He indicated that several non-partitioning indexes (or NPIs) were defined on the table, and he asked for input from "listers" (i.e., members of the DB2-L community) as to the impact that these NPIs might have on his proposed partition-level purge plan.

Isaac Yassin, a consultant and noted DB2 expert, was quick to respond to the question with a warning that the NPIs would have a very negative impact on the performance of the partition-level LOAD REPLACE operation. Isaac's answer was right on the money. It's true that the concept of a logical partition in an NPI (i.e., the index entries associated with rows located in a partition of the underlying table) makes a partition-level LOAD REPLACE compatible, from a concurrency perspective, with application access to other partitions in the target tablespace (a LOAD REPLACE job operating on partition X of table Y will get a so-called drain lock on logical partition X of each NPI defined on table Y). Still, technically feasible doesn't necessarily mean advisable. For a partitioned tablespace with no NPIs, a LOAD REPLACE with an empty input data set is indeed a very CPU- and time-efficient means of removing data from a partition, because it works at a data set level: the tablespace partition's data set (and the data set of the corresponding physical partition of each partitioned index) is either deleted and redefined (if its is a DB2-managed data set) or reset to empty (if it is a user-managed data set, or if it is DB2-managed and REUSE was specified on the utility control statement), and -- thanks to the empty input data set -- you have a purged partition at very little cost in terms of CPU consumption.

Put NPIs in that picture, and the CPU and elapsed time story changes for the worse. Because the NPIs are not physically partitioned, the index-related action of the partition-level LOAD REPLACE job (the deletion of index entries associated with rows in the target table partition) is a page-level operation. That means lots of GETPAGEs (CPU time!), potentially lots of I/Os (elapsed time!), and lots of index leaf page latch requests (potential contention issue with respect to application processes concurrently accessing other partitions, especially in a data sharing environment). Nick Cianci, an IBMer in Australia, suggested changing the NPIs to DPSIs, and that's what the DBA who started the DB2-L thread ended up doing (as I confirmed later -- he's a friend of mine). With the secondary indexes physically partitioned along the lines of the table partitions, the partition-level LOAD REPLACE with the empty input data set will be what the DBA wanted: a very fast, very efficient means of removing a partition's worth of data from the table without impacting application access to other partitions.

DPSI don't: there's a flip side to the partition-level utility benefits that can be achieved through the use of data partitioned (versus non-partitioned) secondary indexes, and it has to do with query performance. When one or more of a query's predicates match the columns of the key of a non-partitioned index, DB2 can quickly zero in on qualifying rows. If that same index is data-partitioned, and if none of the query's predicates reference the partitioning key of the underlying table, DB2 might not be able to determine that a partition of the table contains any qualifying rows without checking the corresponding partition of the DPSI (in other words, DB2 might not be able to utilize page range screening to limit the number of partitions that have to be searched for qualifying rows). If a table has just a few partitions, this may not be such a big deal. But if the table has hundreds or thousands of partitions, it could be a very big deal (imagine a situation in which DB2 has to search 1000 partitions of a DPSI in order to determine that the rows qualified by the query are located in just a few of the table's partitions -- maybe just one). In that case, degraded query performance might be too high of a price to pay for enhanced partition-level utility operations, and NPIs might be a better choice than DPSIs.

That's exactly what happened at a large regional retailer in the USA. A DBA from that organization was in a DB2 for z/OS database administration class that I taught recently, and he told me that his company, on migrating a while back to DB2 V8, went with DPSIs for one of their partitioned tablespaces in order to avoid the BUILD2 phase of partition-level online REORG. [With NPIs, an online REORG of a partition of a tablespace necessitates BUILD2, during which the row IDs of entries in the logical partition (corresponding to the target tablespace partition) of each NPI are corrected to reflect the new physical location of each row in the partition. For a very large partition containing tens of millions of rows, BUILD2 can take quite some time to complete, and during that time the logical partitions of the NPIs are unavailable to applications. This has the effect of making the corresponding tablespace partition unavailable for insert and delete activity. Updates of NPI index key values are also not possible during BUILD2.]

The DPSIs did indeed eliminate the need for BUILD2 during partition-level online REORGs of the tablespace (this because the physical DPSI partitions are reorganized in the same manner as the target tablespace partition), but they also caused response times for a number of queries that access the table to increase significantly -- this because the queries did not contain predicates that referenced the table's partitioning key, so DB2 could not narrow the search for qualifying rows to just one or a few of a DPSI's partitions. The query performance problems were such that the retailer decided to go back to NPIs on the partitioned table. To avoid the BUILD2-related data availability issue described above, the company REORGs the tablespace in its entirety (versus REORGing a single partition or a subset of partitions). [By the way, DPSI-related query performance problems were not an issue for the DBA who initiated the DB2-L thread referenced in the "DPSI do" part of this blog entry, because 1) the table in question is accessed almost exclusively for inserts, with only the occasional data-retrieval operation; and 2) the few queries that do target the table contain predicates that reference the table's partitioning key, so DB2 can use page-range screening to avoid searching DPSI partitions in which entries for qualifying rows cannot be present.]

DPSIs and DB2 9: the question as to whether or not you should use DPSIs is an interesting one, and it's made more so by a change introduced with DB2 9 for z/OS: the BUILD2 phase of partition-level online REORG has been eliminated (BUILD2 is no longer needed because the DB2 9 REORG TABLESPACE utility will reorganize NPIs in their entirety as part of a partition-level online REORG operation). Since some DB2 V8-using organizations went with DPSIs largely to avoid the data unavailability situation associated with BUILD2, they might opt to redefine DPSIs as NPIs after migrating to DB2 9, if DPSI usage has led to some degradation in query performance (as described above in the "DPSI don't" part of this entry). Of course BUILD2 avoidance (in a DB2 V8 system) is just one justification for DPSI usage. Even with DB2 9, using DPSIs (versus NPIs) is important if you need a partition-level LOAD REPLACE job to operate efficiently (see "DPSI do" above).

Whether you have a DB2 V8 or a DB2 V9 environment, DPSIs may or may not be a good choice for your company (and it may be that DPSIs could be used advantageously for some of your partitioned tables, while NPIs would be more appropriate for other tables). Understand the technology, understand your organization's requirements (and again, these could vary by table), and make the choice that's right in light of your situation.

Apologies for the delay in getting this entry posted to my blog -- the time since IBM's 2009 Information On Demand conference concluded on October 29 has been very busy for me. Now I have a little downtime, so I can share with you what I picked up on day 4 of the conference.

"Not Your Father's Database System," indeed - Guy Lohman, of IBM's Almaden (California) Research Center, delivered a very interesting presentation on the Smart Analytics Optimizer, a just-around-the-corner product (meaning, not yet formally announced) about which you'll be hearing a lot in the weeks and months to come. Developed jointly by the Almaden Research Center and IBM's Silicon Valley and Boeblingen (Germany) software labs, the IBM Smart Analytics Optimizer (ISAO) is a business intelligence query-acceleration system that network-attaches to a mainframe server running DB2 for z/OS. The way it works: using a GUI, a DBA copies a portion of a data warehouse (one or more star schemas -- fact tables and their associated dimension tables) to the ISAO (in effect, you set up a data mart on the ISAO). Thereafter, queries that are submitted to DB2 (the ISAO is transparent from a user perspective) will be routed by DB2 to the ISAO if 1) the queries reference tables that have been copied to the ISAO, and 2) DB2 determines that they will run faster if executed on the ISAO. Here's the interesting part: the longer a query would run if executed in the DB2 system, the greater the degree of acceleration you'll get if it runs on the ISAO.

When I say "acceleration," I mean big-time speed-up, as in ten to one hundred times improvement in query run times (the ISAO "sweet spot" is execution of queries that contain aggregation functions -- such as AVERAGE and SUM -- and a GROUP BY clause). How is this accomplished? The ISAO hardware is commodity stuff: multi-core microprocessors with a lot of server memory in a blade center configuration (and several of these blade centers can be tied together in one ISAO system). The query processing software that runs on the ISAO hardware is anything but commodity -- it's a built-from-the-ground-up application that implements a hybrid row-store/column-store in-memory data server. Want DBA ease-of-use? You've got it: there's no need to implement indexes or materialized query tables or any other physical database design extras in order to get great performance for otherwise long-running queries. This is so because the ISAO does a simple thing -- scan data in one or more tables -- in a very advanced, multi-threaded way to deliver consistently good response time (typically less than 10 seconds) for most any query sent its way by DB2. [Caveat: As the ISAO does no I/Os (all data that it accesses is always in memory), it runs its CPUs flat-out to get a single query done as quickly as possible before doing the same for the next query; thus, if queries are sent to the ISAO by DB2 at a rate that exceeds the rate at which the ISAO can process the queries, response times could increase to some degree -- this is just basic queuing theory.]

The ISAO is what's known as disruptive technology. As previously mentioned, you'll soon be hearing a lot more about it (the IOD session I attended was a "technology preview"). I'll be watching that space for sure.

A DB2 for z/OS data warehouse tune-up - Nin Lei, who works at IBM's System z benchmark center in Poughkeepsie (New York), delivered a presentation on performance management of a data warehouse mixed query workload ("mixed" referring to a combination of short- and long-running queries). A couple of the points made in the course of the session:

• You might want to cap the degree of query parallelization on the system - There is a DB2 for z/OS ZPARM parameter, PARAMDEG, that can be used to set an upper limit on the degree to which DB2 will split a query for parallelized execution. For some time now, I've advocated going with a PARAMDEG value of 0 (the default), which leaves the max degree of parallelization decision up to DB2. Nin made a good case for setting PARAMDEG to a value equal to twice the number of engines in the z/OS LPAR in which DB2 is running. I may rethink my PARAMDEG = 0 recommendation.
• The WLM_SET_CLIENT_INFO stored procedure is available on the DB2 for z/OS platform, too - This stored procedure, previously available only on the DB2 for Linux/UNIX/Windows and DB2 for System i platforms, was added to mainframe DB2 V8 and V9 environments via the fix for APAR PK74330. WLM_SET_CLIENT_INFO can be used to change the value of the so-called client special registers on a DB2 for z/OS server (CURRENT CLIENT_ACCTNG, CURRENT CLIENT_USERID, CURRENT CLIENT_WRKSTNNAME, and CURRENT CLIENT_APPLNAME). This capability provides greater flexibility in resource management and monitoring with respect to a query workload.

For fans of Big Memory - Chris Crone, Distinguished Engineer and member of the DB2 for z/OS team at IBM's Silicon Valley Lab, gave a presentation on 64-bit addressing in the mainframe DB2 environment. He said that development of this feature was motivated by a recognition that memory had become the key DB2 for z/OS system resource constraint as System z engines became faster and more numerous (referring to the ability to configure more central processors in a single z/OS image). Big DB2 buffer pools are needed these days because even a really fast I/O operation (involving a disk subsystem cache hit versus a read from spinning disk) can be painfully slow when a single mainframe engine can execute almost 1000 million instructions per second.

Here are a few of the many interesting items of information provided in Chris's session:

• You can currently get up to 1.5 TB of memory on a System z server. Expect memory sizes of 3 TB or more in the near future.
  
• The largest buffer pool configuration (aggregate size of all active buffer pools in a subsystem) that Chris has seen at a DB2 for z/OS site is 40 GB.
• It is expected that the default RID pool size will be 400 MB in the next release of DB2 for z/OS (the RID pool in the DB2 database services address space is used for RID sort operations related to things such as multi-index access, list prefetch, and hybrid join).
• The maximum size of the EDM pool components (EDM pool, skeleton pool, DBD pool, and statement pool) is expected to be much larger in the next release of DB2 (commonly referred to as DB2 X -- we'll get the actual version number at announcement time).
• In the DB2 X environment, it's expected that 80-90% of the virtual storage needed for DB2 threads will be above the 2 GB "bar" in the DB2 database services address space. As a result, the number of threads that can be concurrently active will go way up with DB2 X (expect an upper limit of 20,000 for a subsystem, versus 2000 today).
• DB2 data sharing groups (which run in a parallel sysplex mainframe cluster) could get really big -- IBM is looking at upping the limit on the number of DB2 subsystems in a group (currently 32).
• Solid state storage is going to be a big deal -- the DB2 development team is looking at how to best leverage this technology.

After Chris's session, it was off to the airport to catch the red-eye back to Atlanta. I had a great week at IOD, and I'm looking forward to another great conference next year.

Another day done at IBM's 2009 Information on Demand Conference - another day of learning more about DB2, and about technologies used at higher levels of the information transformation software stack. Some take-aways from today's sessions follow.

A good DB2 9 for z/OS  migration story- Maria McCoy of the UK Land Registry delivered a very good presentation on her organization's DB2 9 for z/OS migration experience. The Land Registry has one of the world's largest operational (versus decision support) databases, holding almost 40 TB of data. On top of that, the agency recently launched it's first public e-business application, a consequence being that downtime is even less well tolerated than before.

The Land Registry runs DB2 in data sharing mode on a parallel sysplex mainframe cluster. The number of DB2 subsystems across all of the Land Registry's environments (test, development, and production) is about 30.

The DB2 9 migration effort went off well, largely because the Land Registry stays pretty current on system maintenance, with quarterly upgrades of the DB2 service level (Maria confirmed what others have said, indicating that DB2 9 is very stable at the F906 maintenance level and beyond).

For the Land Registry, the primary DB2 9 migration drivers included:

• XML support
• Spatial data support (spatial awareness had historically been achieved by way of user-written code)
• Extensions to online schema changes
• Further exploitation of 64-bit addressing
• Improved utility CPU efficiency
• Indexes on column expressions
• Real-time statistics (especially the capability of identifying indexes that have gone a long time without being used for data access)
• Larger index page sizes (offering potentially reduced GETPAGE activity due to a  reduction in the  number of index levels)

An important part of the Land Registry's DB2 9 migration planning effort involved identification of third-party tools used with DB2. The agency identified 42 such products, among these being monitors, middleware, compilers, utilities, file management systems, and legacy software.

A dedicated test system proved to be very valuable. The LoadRunner tool was used to drive online transaction test scripts.

Following the migration to DB2 9, the Land Registry converted all existing simple tablespaces to segmented tablespaces (a good idea, as simple tablespaces can no longer be created in a DB2 9 environment). Maria and her colleagues thought that there were no simple tablespaces in their DB2 databases, but it turned out that 41 such tablespaces did exist.

Among the DB2 9 new features put to good use by the Land Registry are the following:

• Indexes on column expressions (thus was achieved a HUGE decrease in CPU time for a batch job containing a query with a predicate involving a column in a substring function)
• Clone tables (a table-data-change outage that formerly ran to 5 hours due to time needed to load new data and to inspect the newly loaded data for correctness went to 2 seconds)
• Rename column
• Rename index

The DB2 9 migration project went from beginning to end in about 12 months. The Land Registry ran with DB2 9 in Conversion Mode for about 2 months in each of their DB2 environments prior to moving to Enable New Function Mode and then to New Function Mode.

The current Information Management software scene - Arvind Krishna, General Manager of IBM's Information Management software business, spoke during a keynote presentation of the challenges faced by organizations dealing with explosive information growth (an estimated 15 petabytes of new data are generated daily - that's about 50 exabytes per year). He went on to talk about the benefits of "workload-optimized systems" being brought to market now by IBM - systems comprised of fully integrated hardware and software offerings that are optimized for specific workloads. An example of a workload-optimized system is IBM's Smart Analytics system, which provides hardware and a comprehensive software stack (with data management, warehousing, and analytics software) in one package that can be quickly and effectively deployed.

Ross Mauri, General Manager of IBM's Power Systems business (formerly called System p), provided information on the current state of the Power line (currently utilizing generation 6 of IBM's RISC-based microprocessor family, with generation 7 now in beta test mode). Ross said that "Power is everywhere," not only in IBM's Power servers but also in supercomputers, cars, all three of the major electronic game consoles, and the Mars Rover ("we have 100% market share on Mars"). From around 17% market share a few years ago, Power systems now has more than 40% of the market for RISC processor-based servers. Particular strengths of the server line include efficiency ("work per watt," as Ross put it), virtualization, management, and resiliency.

Arvind Krishna closed out the keynote session with remarks that spotlighted IBM's close partnership with SAP (the companies have joint development teams and tens of thousands of mutual customers).

DB2 9 for z/OS native SQL procedures are looking very good - Philip Czachorowski of Fidelity Investments presented information related to his company's early experiences with the native SQL procedures feature of DB2 9 for z/OS (I've blogged a number of times on this technology, beginning with an entry posted late last year). Philip talked about DDL extensions that help with the migration of native SQL procedures from development to test to production environments (statements such as ALTER PROCEDURE ADD VERSION and ALTER PROCEDURE ACTIVATE VERSION), and the new SET CURRENT ROUTINE VERSION statement that can facilitate the testing of a new native SQL procedure (Philip also stressed the importance of having a good naming convention for SQL procedure version identifiers, so you'll know what you're executing when running tests).

Performance data presented during the session was most interesting. Philip showed monitor data for one case in which total class 1 CPU time (from a DB2 monitor accounting report) for a native SQL procedure was only 4% greater than that of a comparable stored procedure written in COBOL.

Near the end of his presentation, Philip mentioned that the DB2_LINE_NUMBER clause of the GET DIAGNOSTICS statement could be very helpful in terms of resolving native SQL procedure code problems.

Stream analytics is way cool - Just before dinner, those of us participating in the IOD Blogger Program had an opportunity to spend an hour with IBMers who are working on the System S "stream analytics" technology on which IBM's InfoSphere Streams offering is based. This is cool stuff: stream analytics software, running under Linux on commodity hardware, can be used to analyze vast amounts of incoming data - often signal data produced by various sensors - to identify events or episodes as they occur, thereby enabling a very rapid response capability. The data could be structured or unstructured, and might consist of hydrophone-captured sounds (picking up, perhaps, the clicking of dolphins), radio astronomy signals, manufacturing data, vehicular traffic activity, weather data, telephone communications, or human-health indicators. Picking up on this latter stream category, a specialist in neonatology who has worked with the IBM System S team spoke of her work involving the monitoring of premature infants' vital signs. An electrocardiogram can generate 500 data signals per second, and there are other vital-sign streams that can be analyzed as well (e.g., blood flow data), and all this can be multiplied by several infants in one area being monitored concurrently (important, as an infection in one child could quickly spread to others). System S stream analytics technology is demonstrating the potential to save lives by taking anomaly detection time from 24 hours (using traditional monitoring methods) to seconds.

The IBM researchers then demonstrated the use of System S stream analytics software to analyze automobile traffic patterns in Stockholm, Sweden (500,000 pieces of GPS data per second).

The scalability of the System S technology is remarkable, the programming interface is surprisingly straightforward (people familiar with object-oriented programming languages tend to become proficient in a couple of weeks), and the GUI is pretty intuitive. Who knows how broadly applicable it might end up being (early adopters are largely in the government and health-care industries, but oil companies are also showing interest)? Watch this space, folks.

That's it for now. Tomorrow morning I'll deliver a presentation on DB2 for z/OS data warehouse performance, and tomorrow evening I'll try to post another blog entry.

Leave a comment below. If you don't see the Add a Comment button, please log in or register.