For a number of organizations, a key issue related to DB2 for z/OS Version 8 to Version 9 migration has been the elimination, in a DB2 9 environment, of the BUILD2 phase of partition-level online REORG. The concern with the elimination of BUILD2 had to do with the attendant restriction on the ability to execute online REORGs of multiple non-contiguous partitions of a tablespace in a parallel fashion. The good news that I want to highlight here is the removal of this restriction via the recently released fix for APAR PK87762. In the remainder of this post I'll explain what the BUILD2 phase of online REORG is (or was, from a DB2 9 perspective), the benefit of eliminating this utility phase, and the challenge that BUILD2 elimination posed for some DB2 for z/OS users prior to the availability of the aforementioned fix (the PTF is UK59095).
With Version 8 (and previous versions) of DB2 for z/OS, BUILD2 is the name of one of the phases of an online REORG TABLESPACE executed at the partition level (versus an online REORG of a partitioned tablespace in its entirety). During an online REORG of a tablespace partition, rows in the shadow data set for the partition are relocated relative to their position in the partition's active data set (this, of course, to restore clustering sequence for the partition). If there are any non-partitioned indexes on the tablespace (these are also known as NPIs), the index entries in those NPIs that point to rows in the partition being reorganized have to be updated to reflect the location of the rows in the shadow data set (which will be the active data set when the REORG job completes). BUILD2 is the utility phase in which these index entry corrections are made, and during that phase the logical partition of the NPI(s) that corresponds to the tablespace partition being reorganized is unavailable to application program. This means that INSERT and DELETE operations targeting the partition being reorganized cannot run during BUILD2 (nor can UPDATEs that change the value of a column in a key of an NPI, and SELECTs that would use the NPI to access data in the partition).
If BUILD2 completes in, say, 60 seconds or less, this may not be such a big deal. Even if BUILD2 runs for several minutes because of the number of rows in the partition being reorganized, the situation can be dealt with by causing BUILD2 to execute during a maintenance window. People do this by submitting the partition-level online REORG with a specification of MAXRO DEFER and LONGLOG CONTINUE, to put off execution of the REORG's final phases (including BUILD2). Upon entering the maintenance window, an -ALTER UTILITY command is issued to change MAXRO to a smallish number of seconds (e.g., 300), and REORG soon performs the final "catch-up" application of logged data changes to the shadow data sets, switches the shadow and active data sets, and completes BUILD2 processing.
What if you very rarely have a maintenance window? What if the partition being reorganized holds over 100 million rows, and BUILD2 runs too long to complete during a (for example) one-hour maintenance window? What then? For some DB2 Version 8 users, this meant that online reorganization of partitioned tablespaces could only be run at the tablespace level -- kind of a bummer, as one of the key benefits of tablespace partitioning is the ability to run utilities at the partition level.
So, BUILD2 elimination in the DB2 9 environment is a good thing, but the WAY in which BUILD2 was eliminated created, for some organizations, a new challenge. When a partition-level online REORG is run in a DB2 9 for z/OS system, any NPIs on the partitioned table will be reorganized in their entirety. That means that they have their own shadow data sets to which logged changes to the corresponding active data sets are applied. This makes BUILD2 unnecessary, but it also means that only one partition-level online REORG job can be run against the tablespace at any one time. That posed a problem for users who liked to get parallel reorganization of different partitions of a tablespace by running multiple REORG jobs targeting different partitions of a tablespace at the same time. Suppose, for example, that you want to run parallel online REORGs of partitions 2, 9, and 22 of a partitioned tablespace. In a DB2 Version 8 system, you'd run three separate online REORG jobs concurrently: one for each partition to be reorganized. In a DB2 9 environment, this approach does not work because the first of these jobs to get ahold of the tablespace's NPIs (to REORG these in their entirety) would block out the other jobs. You could not get around this restriction by submitting one online REORG job with a specification of PART 2, 9, 22 because this syntax was invalid. You could specify PART 2:22 and that would result in parallel online REORGs of partitions 2, 9, and 22, but also of all the other tablespace partitions in that range. There is a way, using an undocumented syntax, to run a REPAIR utility job that will flag certain partitions of a tablespace (e.g., 2, 9, and 22) as being in advisory REORG-pending status, after which an online REORG of the tablespace with the SCOPE PENDING option will result in the desired behavior (REORG only partitions 2, 9, and 22, in parallel); however, many DB2 users are understandably reluctant to go with this approach, and it is not one that I'd recommend.
At the IDUG North American Conference this past May, one of the IBM DB2 for z/OS developers mentioned that DB2 10 (now in beta release) would enable parallel online reorganization of non-contiguous partitions of a tablespace (i.e., partitions that aren't next to each other with regard to their sequence in a partitioned tablespace) with a single execution of REORG. That was great news for the future. What's great news NOW is the fix for the DB2 9 APAR I mentioned, PK87762. With this fix applied, DB2 9 REORG will now accept this syntax: PART(2, 9, 22). Not only that, but you can specify both individual partitions and a partition range in the same PART clause, as in PART(2, 9, 20:22). And there's more: now, when a LISTDEF used in an online REORG job references multiple individual partitions belonging to one tablespace, DB2 will execute REORG once for all of the tablespace's partitions referenced in the LISTDEF, versus running one REORG job for each partition in a serial fashion.
BUILD2 made partition-level online REORG not online-enough for some DB2 users, and I was pleased to see it go away with DB2 9. The (now former) inability to get parallel online REORGs of multiple non-contiguous partitions of a tablespace in a DB2 9 environment was seen by more than a few folks as being an irritatingly high price to pay for BUILD2 elimination. That problem has now been addressed. So, if you're planning a migration to DB2 9 and this issue has been a concern for you, cross it off your list. If you're already on DB2 9 and you've been pining for the days of unrestricted parallel partition-level online REORGs, get UK59095 on your system and pine no more.