DB2: DPSI Do, or DPSI Don't?

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.

Mainframe DB2 Data Serving: Vision Becomes Reality

One of the things I really like about attending DB2 conferences is the face-to-face time I get with people who otherwise would be on the other side of e-mail exchanges. I get a whole lot more out of in-person communication versus the electronic variety. Case in point: at IBM's recent Information on Demand event in Las Vegas, I ran into a friend who is a DB2 for z/OS database engineering leader at a large financial services firm. He talked up a new mainframe DB2 data serving reference architecture recently implemented for one of his company's mission-critical applications, and did so with an enthusiasm that could not have been fully conveyed through a text message. I got pretty fired up listening to the story this DBA had to tell, in part because of the infectious excitement with which it was recounted, but also because the system described so closely matches a vision of a DB2 for z/OS data-serving architecture that I've had in mind -- and have advocated -- for years. To see that vision validated in the form of a real-world system that is delivering high performance and high availability in a demanding production environment really made my day. I am convinced that what the aforementioned financial services firm (hereinafter referred to as Company XYZ) is doing represents the future of mainframe DB2 as a world-class enterprise data-serving platform. Read on if you want to know more.

Three characteristics of the reference DB2 for z/OS data architecture (so called because it is seen as the go-forward model by the folks at Company XYZ) really stand out in my mind and make it an example to be emulated:

1. It is built on a DB2 data sharing / parallel sysplex foundation, for maximum availability and scalability (not only that -- these folks have done data sharing Really Right, as I'll explain).
2. It leverages Big Memory (aka 64-bit addressing) for enhanced performance.
3. The software stack on the mainframe servers is pretty short -- these are database machines, plain and simple.

A little elaboration now on these three key aspects of Company XYZ's DB2 for z/OS reference architecture:

The robust foundation: a DB2 data sharing group on a parallel sysplex mainframe cluster. It's well known that a standalone System z server running z/OS and DB2 can be counted on to provide very high levels of availability and scalability for a data-serving workload. These core strengths of the mainframe platform are further magnified when concurrent read/write access to the database is shared by multiple DB2 members of a data sharing group, running in the multiple z/OS LPARs (logical partitions) and multiple System z servers of a parallel sysplex. You're not going to beat the uptime delivered by that configuration: formerly planned outages for maintenance purposes are virtually eliminated (service levels of of DB2, z/OS, and other software components can be updated, and server hardware can be upgraded, with no -- I mean zero -- interruption of application access to the database), and the impact of an unplanned failure of a DB2 member or a z/OS LPAR or a server is greatly diminished (only data pages and/or rows that were in the process of being changed by programs running on a failed DB2 subsystem are temporarily unavailable following the failure, and those retained locks will be usually be freed up within a couple of minutes via automatic restart of the failed member). And scalability? Up to 32 DB2 subsystems (which could be running on 32 different mainframe servers) can be configured in one data sharing group.

Now, you can set up a DB2 data sharing group the right way, or the Really Right way. Company XYZ did it Really Right. Here's what I mean:

• More z/OS LPARs and DB2 members than mainframes in the sysplex. I like having more than one z/OS LPAR (and DB2 subsystem) per mainframe in a parallel sysplex, because 1) you can route work away from one of the LPARs for DB2 or z/OS maintenance purposes and still have access to that server's processing capacity, and 2) more DB2 members means fewer retained locks and quicker restart in the event of a DB2 subsystem failure.
• Dynamic VIPA network addressing. Availability and operational flexibility are optimized when remote DRDA clients use a dynamic VIPA (virtual IP address) to connect to the DB2 data sharing group (as long as at least one member of the data sharing group is up, a connection request specifying the group's VIPA can be successfully processed). A sysplex software component called the Sysplex Distributor handles load balancing across DB2 members for initial connection requests from remote systems (these will often be application servers), while load balancing for subsequent requests is managed at the DB2 member level.
  
• Internal coupling facilities. ICFs (basically, coupling facility control code running in an LPAR on a mainframe server) are less expensive than external coupling facilities, not only with respect to acquisition cost, but also in terms of environmental expenses (floor space, power, cooling). [It's true that if the mainframe containing the ICF holding the lock structure and the shared communications area (SCA) should fail, and if on that mainframe there is also a member of the DB2 data sharing group, the result will be a group-wide outage unless the lock structure and SCA are duplexed in the second ICF. Company XYZ went with system-managed duplexing of the lock structure and SCA (DB2 manages group buffer pool duplexing in a very low-overhead way). Some other organizations using ICFs exclusively (i.e., no external coupling facilities) decide not to pay the overhead of system-managed lock structure and SCA duplexing, on the ground that a) a mainframe server failure is exceedingly unlikely, b) the group-wide outage would only occur if a particular mainframe (the one with the ICF in which the lock structure and SCA are located) were to fail, and c) the group-restart following a group-wide outage should complete within a few minutes. The right way to go regarding the use or non-use of system-managed lock structure and SCA duplexing will vary according to the needs of a given organization.]

Taking advantage of 64-bit addressing. Each of the LPARs in the parallel sysplex on which Company XYZ's model DB2 for z/OS data-serving system is built has more than 20 GB of central storage, and each DB2 subsystem (there is one per LPAR) has a buffer pool configuration that exceeds 10 GB in size. In these days of Big Memory (versus the paltry 2 GB to which we were limited not long ago), I don't think of a production-environment DB2 buffer pool configuration as being large unless the aggregate size of all pools in the subsystem is at least 10 GB. The reduced level of disk I/O activity that generally comes with a large buffer pool configuration can have a significant and positive impact on both the elapsed time and CPU efficiency of data access operations.

Lean, mean, data-serving machines. A production instance of DB2 for Linux, UNIX, and Windows (LUW) usually runs on a machine that is a dedicated data server -- data access code executes there, and that's it. Business-logic programs? They run on application servers. Presentation-logic programs? They might run on yet another tier of servers. The DB2 for LUW server Just Does Data. When I started my IT career in the early 1980s, a mainframe-based application was almost always entirely mainframe-based, by which I mean that all application functionality -- data access logic, business logic, and presentation logic -- was implemented in programs that ran on a mainframe server. Nowadays, I believe that the unmatched availability, scalability, reliability, and security offered by the System z platform is put to most advantageous use in the servicing of data access requests. In other words, I feel that a DB2 for z/OS system should be thought of, in an architectural sense, as a dedicated data server, just as we tend to think of DB2 for LUW systems (and other database management systems that run on Linux, UNIX, and/or Windows platforms) as dedicated data servers.

That's how DB2 for z/OS functions in Company XYZ's reference architecture: it just does data. Consequently, the software stack on the data-serving mainframes is relatively short, consisting of z/OS, DB2, RACF (security management), a data replication tool, some system automation and management tools, some performance monitoring tools, and little else. Transaction management is handled on application servers. Database access requests come in via the DB2 Distributed Data Facility (DDF), and much of the access logic is packaged in stored procedures (the preference at Company XYZ is DB2 9 native SQL procedures, because they perform very well and -- when invoked through calls that come through DDF -- much of their processing can be handled by zIIP engines).

Does this system, which looks so good on paper, deliver the goods? Absolutely. Volume has been taken north of 1400 transactions per second with excellent response time, and my DBA friend is confident that crossing the 2000-trans-per-second threshold won't be a problem. On the availability side, Company XYZ is getting industry-leading uptime. The message: System z is more than just capable of functioning effectively as a dedicated data server -- it works exceptionally well when used in that way. This is a clean, modern architecture that leverages what mainframes do best -- scale, serve, protect, secure -- in a way that addresses a wide range of application design requirements.

Here's a good coda for you: still at IOD in Las Vegas, and shortly after my conversation with the DBA from Company XYZ, I encountered another friend -- a lead DB2 technical professional at another company. He told me about the new DB2 for z/OS reference architecture that had recently been approved by his organization's IT executive management. The pillars of that architecture are a DB2 data sharing / parallel sysplex mainframe cluster, z/OS systems functioning as dedicated data servers, data requests coming in via the DB2 DDF, and data access logic packaged in DB2 9 native SQL procedures. I told this friend that he and his colleagues are definitely on the right track. It's a track that more and more DB2 for z/OS-using companies are traveling, and it could well be the right one for your organization.

DB2 9 for z/OS: Converting from External to Native SQL Procedures

As some of you may know, I'm a big fan of the native SQL procedure functionality introduced with DB2 for z/OS Version 9 (I've written a number of blog entries on the subject, starting with one posted last year). Native SQL procedures offer a number of advantages versus external SQL procedures (formerly known simply as SQL procedures in pre-Version 9 DB2 environments), including (generally) better performance, zIIP engine eligibility when called from a remote client via DRDA, and simplified lifecycle processes (referring to development, deployment, and management). These advantages have plenty of folks looking to convert external SQL procedures to native SQL procedures, and that's fine, but some of these people are under the impression that the conversion process involves nothing more than dropping an external SQL procedure and re-issuing that routine's CREATE PROCEDURE statement, minus the EXTERNAL NAME and FENCED options (if either had been specified in creating the external SQL procedure). This may in fact do the trick for a very simple SQL procedure, but in many cases the external-to-native conversion will be a more involved process. In this post I'll provide some information as to why this is so, along with a link to a well-written "technote" on IBM's Web site that contains further details on the topic.

First, a little more on this drop-and-recreate-without-EXTERNAL-NAME-or-FENCED business. It is true that, in a DB2 9 New Function Mode system, a SQL procedure (i.e., a stored procedure for which the routine source is contained within the CREATE PROCEDURE statement) will be external if it is created with the EXTERNAL NAME and/or FENCED options specified, and native if created with neither EXTERNAL NAME nor FENCED specified; however, it is not necessarily the case that an external SQL procedure re-created sans EXTERNAL NAME and FENCED will behave as you want it to when executed as a native SQL procedure. Why is this so? Well, some of the reasons are kind of obvious when you think about it. Others are less so. On the obvious side, think about options that you'd specify for an external SQL procedure (which ends up becoming a C language program with embedded SQL) at precompile time (e.g., VERSION, DATE, DEC) and at bind time (e.g., QUALIFIER, CURRENTDATA, ISOLATION). For a native SQL procedure, there's nothing to precompile (as there is no associated external-to-DB2 program), and the package is generated as part of CREATE PROCEDURE execution (versus by way of a separate BIND PACKAGE step). That being the case, these options for a native SQL procedure are specified via CREATE PROCEDURE options (some of which have names that are slightly different from the corresponding precompile options, an example being the PACKAGE OWNER option of CREATE PROCEDURE, which corresponds to the OWNER option of the BIND PACKAGE command). Here's another reason to pay attention to these options of CREATE PROCEDURE when converting an external SQL procedure to a native SQL procedure: the default options for CURRENTDATA and ISOLATION changed to NO and CS, respectively, in the DB2 9 environment.

A less-obvious consideration when it comes to external-to-native conversion of SQL procedures has to do with condition handlers. These are statements in the SQL procedure that are executed in the event of an error or warning situation occurring. External SQL procedures do not allow for nested compound SQL statements (a compound SQL statement is a set of one or more statements, delimited by BEGIN and END, that is treated as a block of code); so, if you had within an external SQL procedure a compound SQL statement, and you wanted within that compound SQL statement a multi-statement condition handler, you couldn't do that by way of a nested compound statement. What people would often do instead in that case is code the condition handler in the form of an IF statement containing multiple SQL statements. In converting such an external SQL procedure to a native SQL procedure, the IF-coded condition handler should be changed to a nested compound SQL statement set off by BEGIN and END (in fact, it would be a good native SQL procedure coding practice to bracket even a single-statement condition handler with BEGIN and END). This change would be very much advised not just because nested compound statements are allowed in a native SQL procedure, but also because, in a native SQL procedure, an IF statement intended to ensure execution of multiple statements in an IF-based condition handler (e.g., IF 1=1 THEN...) would itself clear the diagnostics area, thereby preventing (most likely) the condition handler from functioning as desired (in an external SQL procedure, it so happens that a trivial IF statement such as IF 1=1 will not clear the diagnostics area).

Also in the not-so-obvious category of reasons to change the body of a SQL procedure when converting from external to internal: resolution of unqualified parameter, variable, and column names differs depending on whether a SQL procedure is external or native. Technically, there's nothing to prevent you from giving to a parameter or variable in a SQL procedure a name that's the same as one used for a column in a table that the SQL procedure references. If a statement in an external SQL procedure contains a name that could refer to a variable or a parameter or a column, DB2 will, in processing that statement, check to see if a variable of that name has been declared in the SQL procedure. If a matching variable name cannot be found, DB2 will check to see if the name is used for one of the procedure's parameters. If neither a matching variable nor a matching parameter name is found, DB2 will assume that the name refers to a column in a table referenced by the procedure. If the same statement is encountered in a native SQL procedure, DB2 will first check to see if the name is that of a column of a table referenced by the procedure. If a matching column name is not found, DB2 will then look for a matching variable name, and after that for a matching parameter name. If no match is found, DB2 will return an error if VALIDATE BIND was specified in the CREATE statement for the native SQL procedure (if VALIDATE RUN was specified, DB2 will assume that the name refers to a column, and will return an error if no such column is found at run time). Given this difference in parameter/variable/column name resolution, it would be a good idea to remove ambiguities with respect to these names in an external SQL procedure prior to converting the routine to a native SQL procedure. This could be done either through a naming convention that would distinguish variable and parameter names from column names (perhaps by prefixing variable and parameter names with v_ and p_, respectively) or by qualifying the names. Variable names are qualified by the label of the compound statement in which they are declared (so if you're going to go this route, put a label before the BEGIN and after the END that frame the compound statement), parameter names are qualified by the procedure name, and column names are qualified by the name of the associated table or view.

Then there's the matter of the package collection name that will be used when the SQL procedure is executed. For an external SQL procedure, this name can be specified via the COLLID option of the CREATE PROCEDURE statement. [If NO COLLID -- the default -- is specified, the name will be the same as the package collection of the calling program. If the calling program does not use a package, the SQL procedure's package will be resolved using the value of CURRENT PACKAGE PATH or CURRENT PACKAGESET, or the plan's PKLIST specification.] When a native SQL procedure is created, the name of the associated package's collection will be the same as the procedure's schema. In terms of external-to-native SQL procedure conversion, here's what that means:

• If external SQL procedures were created with a COLLID value equal to the procedure's schema, it's smooth sailing ahead.
• If external procedures were create with a COLLID value other than the procedure's schema, a relatively minor adjustment is in order. This adjustment could take one of two forms: a) go with the one "root" package for the native SQL procedure in the collection with the name matching the routine's schema, and ensure that this collection will be searched when the procedure is called, or b) add a SET CURRENT PACKAGESET statement to the body of the SQL procedure, specifying the collection name used for COLLID in creating the external SQL procedure, and (via BIND PACKAGE COPY) place a copy of the "root" package of the native SQL procedure in that collection.
• If external SQL procedures were bound with NO COLLID, there could be a good bit of related work in converting those routines to native SQL procedures, especially if a number of "variants" of an external SQL procedure's "root" package were generated and placed in different collections. The external SQL procedure package variants will have to be identified, SET CURRENT PACKAGESET will be needed in the native SQL procedure to navigate to the desired collection at run time (perhaps using a value passed by the caller as a parameter), and variants of the native SQL procedure's "root" package (again, that being the one in the collection with the name matching the procedure's schema) will need to be copied into the collections in which the external SQL procedure package variants had been placed.

The information I've provided in this blog entry is not exhaustive with respect to external-to-native SQL procedure conversion -- my intent was to cover the major issues that should be taken into consideration in planning your conversion process. For more details, check out the excellent "technote" document written by Tom Miller, a senior member of IBM's DB2 for z/OS development team and an authority on SQL procedures (both external and native). To access this document, go to the DB2 for z/OS Support page on IBM's Web site, enter "external native" as your search terms, and click on the search button -- Tom's document should be at the top of the search result list.

Native SQL procedures are the way of the future, and I encourage you to develop a plan for converting external SQL procedures to native SQL procedures (if you have any of the former). It's very do-able, and for some external SQL procedures the conversion will in fact be very straightforward. For others, more conversion effort will be required, but the payoff should make that extra effort worthwhile.

IBM IOD 2009 - Day 4 (Belated Re-Cap)

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.