I blogged a couple of times about PBG tablespaces (introduced with DB2 9 for z/OS) while I was working as an independent DB2 consultant, in one entry comparing them with partition-by-range (PBR) tablespaces and in another briefly mentioning them among several new physical database design options provided by DB2 9. One advantage of a PBG tablespace versus a traditional segmented tablespace (PBG tablespaces are also segmented) is the ability to run utilities at a partition level (the exception to this rule being LOAD, which has to run at the tablespace level for a PBG tablespace). On the surface, the option of REORGing a single partition of a PBG tablespace looks pretty attractive, but there is a catch, and it does have to do with space, as my DBA friend discovered.
Here's the deal: by definition, a new partition for a PBG tablespace won't be allocated unless the tablespace's existing partitions are full. So, if you run an online REORG for, say, partition 4 of a ten-partition PBG tablespace, that partition is likely to be quite full (depending on delete activity, which might have created some "holes" in pages of the partition, and on the table's clustering key, which would influence placement of newly inserted rows -- more on that momentarily). If the tablespace was created with non-zero values for PCTFREE and/or FREEPAGE, the REORG utility will attempt to reestablish that free space, and if the partition's data rows and the reestablished free space will not fit in the partition's shadow data set, the REORG job will fail. That was the situation that my friend brought to my attention. What did he do? He resolved the problem by setting the value of a relatively new ZPARM parameter, REORG_IGNORE_FREESPACE (introduced last fall via the fix for APAR PK83397), to YES. When the value of REORG_IGNORE_FREESPACE is YES (the default is NO), REORG TABLESPACE will ignore PCTFREE and FREEPAGE values when reloading data rows into a PBG tablespace if either of the following is true:
- The utility is reorganizing a subset of the tablespace's partitions
- The table in the PBG tablespace has one or more LOB columns
Now, you might be thinking, "Why is there even the possibility of this out-of-space condition when one partition of a PBG tablespace is being reorganized? Can't REORG take rows that won't fit back into the target partition after reestablishing free space and put them in other partitions that have unused space?" The fact of the matter is that REORG doesn't work that way -- it doesn't move rows from one PBG partition to another unless the whole PBG tablespace is being REORGed or a range of the tablespace's partitions are being reorganized. To expand on that italicized point: if partitions 4 through 8 of my example ten-partition tablespace are being reorganized (i.e., if the utility control statement has PART 4:8), REORG can freely move rows across partitions 4, 5, 6, 7, and 8 (unless the DB2 version is 9 and the table in the tablespace has one or more LOB columns, in which case a row has to be reloaded into the partition from which it was unloaded -- a restriction removed with DB2 10): after sorting the rows unloaded from the five partitions in clustering sequence, REORG will fill one partition before moving to the next.
What this means: if you want to REORG a subset of a PBG tablespace's partitions, and you want free space to be reestablished by the utility, it may be better to REORG a range of several partitions instead of targeting a single partition. That way, unused space in one or more partitions in the range can enable restoration of free space in other, more-full partitions with less of a chance of out-of-space being an issue.
As a coda, I'll tell you that the DBA to whom I've referred repeatedly in this entry ended up asking whether it was even worth it to reorganize partitions of the table in question. That is a very good question to ask. It's easy to get into a REORG-if-it's-unclustered mindset, but in fact some tablespaces don't need to be REORGed. The one with which the DBA was dealing gets plenty of inserts but is rarely read, and when it is read, performance just has to be OK -- it doesn't have to be great. CPU resources in such a case can be saved by cutting way back on REORG frequency (if not eliminating REORGs altogether) and optimizing insert performance either by switching to a continuously-ascending clustering key or by altering the table with the APPEND YES option introduced with DB2 9 (this causes DB2 to ignore clustering when inserting or loading data into the table). Bottom line: don't REORG a tablespace (or partitions thereof) "just because." Make sure that the benefits of reorganizing an object justify the CPU (and disk space) used during REORG processing. While keeping an object well organized is usually a good idea, in some situations disorganization really isn't a big deal. Think it through, and act accordingly.