If You've Got DB2 for z/OS Product Requirements, This is your Online Community

Who makes DB2 for z/OS a great database management system? Well, the IBM DB2 developers, of course. But who else? You, that's who. You, the users of DB2 for z/OS, have provided IBM with a steady stream of enhancement requests since the product was introduced in 1983. There have been various means by which DB2 requirements have been communicated by users to IBM, including an application called FITS. FITS served its purpose pretty well, but one aspect of the application came to be seen as a drawback in light of the growing popularity and importance of social media: FITS is an IBM-internal application. A product enhancement request might be raised by a DB2 user, but it had to be entered and monitored on the user's behalf by an IBMer. What was needed was a new application that would provide a direct link between DB2 users and IBM DB2 developers. That new application is here, and it's called RFE (short for Request for Enhancements).

With RFE, you can enter DB2 for z/OS product requirements yourself, and interact directly with IBMers who evaluate and act on submitted requests. You can track the status of requests you've entered, and you can view -- and vote on, and comment on -- requests submitted by others. RFE is going to be a win-win for the DB2 for z/OS user community and for IBM, spurring increased flow of ideas on how to make DB2 even better than it already is, and contributing to the strengthening of the DB2 user community, across industries and around the world.

I encourage you to learn more about RFE. Here are some links that can get you started:

• The home page for the RFE community for DB2 for z/OS. What you see at the top of this page is "Information Management." What makes it the RFE home page for DB2 for z/OS is "DB2 for z/OS" in the box near the top of the page, under the heading, "Customize this page for your favorite product."
• RFE FAQs. You'll find here a long list of questions and answers. Scroll through them if you'd like, or use the links at the top of the page to go directly to FAQs pertaining to a specific area of interest. I recommend checking out the information on RFE groups, a very useful feature of the application. You can create an invitation-only private group for you and your co-workers, or a public group to attract "birds of a feather" DB2 users who share an interest in a particular category of product enhancements.
• RFE video tutorials. Watch and learn.
• RFE status definitions. Entries on this page expand on the meaning of request status values such as "Under consideration" and "Need more information."
• Getting an IBM ID (required for the use of RFE). Lots of you already have an IBM ID. It's free, and if you don't have one you can get one by clicking on the "Need an IBM ID?" link on this page.

The information available on the above-listed Web pages is pretty extensive, but in case you need more help in getting started with RFE, you can send a note to my colleague Janet Figone, at jfigone@us.ibm.com.

I've been on the IBM and the user side of the DB2 community. When those sides come together to make DB2 for z/OS a better product, great things can happen. Use RFE, and become a part of that.

Migrating from DB2 for z/OS V8? DO THIS

A lot of organizations have already migrated from DB2 for z/OS Version 8 to a more-current release of DB2 -- either DB2 9 or (in the case of skip-level migrations) DB2 10. For the most part, those migrations from DB2 V8 went quite smoothly; however, some companies making the move from DB2 V8 to DB2 9 or DB2 10 have encountered performance problems related to one thing: the need in DB2 9 and DB2 10 environments for more resources -- disk space, table spaces, buffer pool space -- associated with work file table spaces that have a 32KB page size. A lot of my colleagues and I have been beating this drum for some time, reminding people of the jump in 32KB-page sorting (versus 4KB-page sorting) that is to be expected when going from DB2 V8 to DB2 9 or DB2 10, but the message has apparently not gotten through to everyone, as evidenced by the fact that organizations to this day get caught with too little in the way of 32KB-page sort resources when migrating from DB2 V8 to DB2 9 or DB2 10. So, for those of you who are in the process of migrating from DB2 V8 to a more-current DB2 release, or who are just now completing that process, or who have been on DB2 9 or DB2 10 for a while and have been living with sub-optimal performance caused by inadequate 32KB-page sort resources and perhaps have been unaware of this connection, I'm going to lay out the relevant facts and recommendations in this blog entry. Get this one thing right, and your transition from DB2 V8 will be a smoother one.

Fact: starting with Version 9, DB2 directs more -- MUCH more -- of its SQL sort work to work file table spaces that have 32KB (versus 4KB) pages. By "SQL sort work," I'm referring to sorts that are related to SQL statements (e.g., sorts associated with ORDER BY, GROUP BY, and DISTINCT specifications, and with merge scan join operations), as opposed to sorts associated with IBM DB2 utility execution (these are handled by DFSORT, or by DB2 Sort when that product is installed). In a DB2 9 or DB2 10 environment, if records to be sorted have a length of 100 bytes or more, a work file table space with 32KB pages will be used for the sort operation; otherwise, a 4KB-page table space will be used. This change was implemented for a reason: sorting of longer records is more efficient when a larger work file page size is used.

Fact: starting with DB2 9, work file table spaces are segmented, not simple, and the SEGSIZE (which can't be altered in a DB2 9 CM or DB2 10 CM8 environment) is 16. This can add to the demand for 32KB-page sort space in a DB2 9 or DB2 10 environment. Think about it: suppose that a 200 rows with a length of 200 bytes apiece are to be sorted to satisfy an ORDER BY specification. A couple of 32KB pages should suffice, right? Wrong. With a work file table space SEGSIZE of 16, DB2 is going to use sixteen 32KB pages for this sort. Now, if you're running DB2 9 NFM or DB2 10 CM9 or NFM, don't go rushing to make the SEGSIZE of your work file table spaces something smaller than 16 -- not without reading further. I'll get to this.

Recommendation: have a lot of 32KB-page work file space in your DB2 9 or DB2 10 system. What's "a lot," you ask? That's going to vary from site to site, depending on the usage of the DB2 subsystem (a business intelligence workload might be more sort-intensive than an operational transaction or batch workload) and the volume of sort-driving SQL statement execution. Here's what I tell people: if, in your DB2 V8 environment, you had X amount of 4KB-page work file space, have at least X amount of 32KB-page sort work space in your DB2 9 or DB2 10 system, and consider having 2X amount of 32KB-page sort work space, to be on the safer side. You will likely be able to reduce -- perhaps considerably -- your 4KB-page sort work space once you're running with DB2 9 or DB2 10. At some sites where DB2 9 or DB2 10 is in use, the ratio of 32KB-page work file space to 4KB-page space is 3:1 or 4:1.

Recommendation: have a pretty good number of 32KB-page and 4KB-page work file table spaces with a secondary space allocation quantity of zero. You're probably aware that, starting with DB2 9 for z/OS, the TEMP database is no more. Things that had used TEMP DB space in DB2 V8 (declared global temporary tables and the temporary tables that hold result sets of static scrollable cursors) use space in the work file database in a DB2 9 or DB2 10 environment. It's a good idea to physically separate the two categories of temporary space usage -- SQL-related sorts, view materialization, triggers, etc. on the one hand, and declared global temporary tables and static scrollable cursors on the other -- within the DB2 9 or DB2 10 work file database. This can be accomplished by having some work file table spaces that are DB2-managed with a non-zero secondary space allocation quantity, and some that are either DB2-managed with a secondary space allocation quantity of zero, or user-managed. Depending on the value of the ZPARM parameter WFDBSEP (built into the DB2 10 base code, and provided a couple of years ago for DB2 9 via the fix for APAR PM02528), DB2 will favor (WFDBSEP=NO) or require (WFDBSEP=YES) the use of work file table spaces that are DB2-managed with a non-zero SECQTY value for declared global temporary tables and static scrollable cursors, and will favor or require the use of work file table spaces that are DB2-managed with SECQTY 0 -- or user-managed -- for SQL-related sorts, view materialization, etc. Note that while a user-managed work file table space will be favored for SQL sorts regardless of the secondary space allocation quantity, if you have user-managed work file table spaces these should have a secondary space allocation quantity of zero -- this is better for SQL sort performance.

Obviously, having work file table spaces with a secondary space allocation quantity of zero will mean that these table spaces will not grow beyond "piece" one. That being the case, you should have a fair number of these, of both the 4KB-page and 32KB-page variety. There's not a one-size-fits-all quantity here, but you might want to start with 10-15 32KB-page work file table spaces (and a similar number of 4KB-page table spaces) with a secondary space allocation quantity of zero. How large should the primary space allocation quantity be for these table spaces? That might depend on the availability of disk space at your site. A primary space allocation quantity of just under 2 GB (as recommended in "info" APAR II14587) is good if your disk storage resources will accommodate that specification; otherwise, go for 400-500 cylinders (around 280-350 MB of DB2-usable space). Keep in mind that -- as previously mentioned -- you will likely be able to adjust the amount of 4KB-page work file space downward once you're on DB2 9 or DB2 10 long enough to be past the fallback-possibility stage of migration.

By the way, DB2 9 introduced a REXX exec, DSNTWFG, that can be used to create DB2-managed work file table spaces. The parameters that you supply for this REXX exec are explained in the text of APAR PM17336, the fix for which allowed DSNTWFG to be used for the creation of DB2-managed work file table spaces with SECQTY 0 (previously the REXX exec could only be used to create DB2-managed work file table spaces with a non-zero SECQTY).

Recommendation: start with the default value of NO for the WFDBSEP parameter of ZPARM. This setting establishes a "soft" physical separation of work file space use for SQL sorts (and view materialization and other things) and declared global temporary tables (and static scrollable cursors). With that set-up in place, if space in the work file table spaces that have a secondary quantity of zero is inadequate for the processing of SQL sorts, DB2 can use space in DB2-managed table spaces with a non-zero SECQTY for this purpose. This flexibility serves to provide a "safety valve" (or expansion reservoir, depending on your preferred analogy) for SQL sort work in the subsystem. If you have WFDBSEP=NO and 1) you see your non-zero SECQTY  work file table spaces going into multiple "pieces" (A001, A002, A003, etc. data sets) and 2) you don't have a lot of declared global temporary table usage that would drive this expansion into multiple pieces, chances are the expansion of these table spaces into multiple pieces is being driven by SQL sort "spillover" activity that is occurring because space for that purpose in SECQTY 0 work file table spaces is under-allocated. In that case, consider increasing the amount of space in SECQTY 0 table spaces (of the 4KB-page or 32KB-page variety, as needed). If you don't see evidence of SQL sort "spillover" (or if you have heavy declared global temporary table usage and you want to keep that from spilling over into work file table spaces used for SQL sorts) then you can consider -- carefully -- going with WFDBSEP=YES (understanding that with this specification you'll get negative SQL codes and/or error messages if the space needed for declared temporary tables or SQL sorts is not available in work file table spaces with non-zero SECQTY and SECQTY 0, respectively).

Recommendation: dedicate 4K and 32K buffer pools to your 4KB-page and 32KB-page work file table spaces, and make them relatively large. "Large" is going to vary from one site to another, but given an adequate central storage resource I'd go with at least 10,000 buffers for the buffer pool used for 4KB-page work file table spaces (20,000-30,000 buffers would be better still) and at least 5000 buffers for the pool used for 32KB-page work file table spaces (10,000 buffers would be nice for this pool, if you have the storage). Check out a blog entry I posted a few weeks ago for other recommendations concerning buffer pools dedicated to work file table spaces.

Recommendation: consider -- carefully -- changing the SEGSIZE for your work file table spaces, once you are in DB2 9 NFM or (or DB2 10 NFM if you're doing a skip-level migration from DB2 V8). As I mentioned previously, the default SEGSIZE for these table spaces in a DB2 9 or DB2 10 environment is 16. IF your environment is characterized by a large number of sorts of relatively small result sets (e.g., result sets that could fit into a few 4KB or 32KB pages), a SEGSIZE of 8 (or maybe even 4) could relieve pressure on your work file database, as DB2 would not need to use considerably more pages than needed to hold small sets of rows to be sorted (and keep in mind here that SQL sorts are not just for things like ORDER BY specifications in SELECT statements -- they are also needed for operations such as merge scan joins, and these can involve sorts of many records, depending on the filtering accomplished via application of predicates to outer or inner table rows). On the other hand, IF you are seeing a lot of suspend time due to latch contention on space map pages of work file table spaces (and DB2 traces can be used to check on this), a SEGSIZE of 32 (or even 64) could provide relief. If neither of these situations matches your environment, stay with SEGSIZE 16.

Wrap-up: I hope that the information in this entry will be useful to you. Going from DB2 V8 to DB2 9 or DB2 10 will provide your organization with lots of great new functionality. Along the way, don't stub your toe on the work file database. Understand what's changed in this area, and make the needed adjustments to your configuration.