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

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

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

[Note: the IBM developerWorks Web site has undergone some significant changes since this blog entry was originally posted in October of 2011. These changes, aimed largely at making the site more community-oriented (there are several DB2-related communities), are welcome, but as a result the path to some file is not the same as before. I have updated the "how to find" information in this blog post accordingly.]  

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.

Information on getting the DSNTIJSS job from developerWorks follows. First I'll provide "find it" instructions starting from the developerWorks home page -- this because it's my way of encouraging you to look around at what the site has to offer. I'll follow that with more of a short-cut path to the file.

The scenic route:

1. Go to the developerWorks home page (http://www.ibm.com/developerworks/)
2. On that page, click on "Community" in the black banner across the top of the page. A gray box will drop down. Click on the "Communities" link (that's "Communities" plural, not the bold-faced "Community" link).
3. After clicking the "Communities" link, you'll see a gray banner near the top of the page, just under the developerWorks black banner. In the search box in the gray "Communities" banner (NOT the search box in the black "developerWorks" banner), you'll see a search scope indicator (it might be pre-filled with "Public Communities"). Click on that part of the search box to get a drop-down menu, and select "developerWorks Community" from that menu.
4. After selecting "developerWorks Community" as the search scope, enter DSNTIJSS as the search term next to the magnifying glass icon, and hit Enter. [At this point you'll get a sign-in page, if you're not already signed into developerWorks. To sign in you need an IBM ID. Getting one of these is free and quick. Sign in with your IBM ID.]
5. Click on the returned dsntijss.copy link, and you're there.

The more direct route: go to the developerWorks "public files" page (https://www.ibm.com/developerworks/community/files/app#/), and enter DSNTIJSS in the search box in the gray banner across the top of the page (again, not the search box in the black banner above the gray banner). That will return the dsntijss.copy link. Click on that and download 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

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.