Thursday, December 2, 2010

REORG and DB2 for z/OS Partition-by-Growth Tablespaces

A DB2 DBA friend of mine recently told me of a problem he'd encountered in reorganizing a single partition of a partition-by-growth (PBG) tablespace: the online REORG job failed with an out-of-space condition pertaining to the partition's shadow data set. The topic of partition-level REORGs of PBG tablespaces is one that I find interesting; thus, this post, in which I'll explain why my friend hit that out-of-space situation and how he resolved it. I'll also provide some additional items of related information that I hope you'll find to be useful.

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
If free space is not reestablished, the rows unloaded from a partition will almost certainly fit into the shadow data set during a REORG. [I say "almost" because for a table with varying-length rows (and rows in a compressed tablespace are varying-length even if there are no VARCHAR columns), I can imagine a very unusual scenario in which rows unloaded from a partition might not fit back in after being sorted in clustering key sequence, even with free space parameter values ignored. So, REORG_IGNORE_FREESPACE = YES should take chances of an out-of-space condition on REORG or a partition of a PBG tablespace way down, if not all the way to zero.]

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.

10 comments:

  1. Excellent article, Robert! The staff at SSWUG.org has featured it on the site.

    We hope you continue to supply informative posts like this one!

    ReplyDelete
  2. Thanks for the positive feedback, Sam. I'll try to keep 'em coming.

    Robert

    ReplyDelete
  3. this was very useful since there isn't a lot of information on utilities and UTS.

    Dale

    ReplyDelete
  4. Glad it was a good read for you, Dale.

    Robert

    ReplyDelete
  5. Very useful article and it helped! Thank you

    ReplyDelete
  6. Happy to hear that the information was of use to you, Cuneyt.

    Robert

    ReplyDelete
  7. Robert,

    It would be very nice to be able to activate the APAR you mention above - REORG_IGNORE_FREESPACE (introduced last fall via the fix for APAR PK83397) as a PARM on the execute statement of the REORG.

    ReplyDelete
  8. That would be an attractive option. You might want to pass your functionality enhancement idea to your local IBM DB2 technical support rep. He or she could then enter the request into our product requirements application, and you'd get a response back from DB2 for z/OS development.

    Robert

    ReplyDelete
  9. Hi Robert,
    While performing reorg on one of our TS it failed with "RELOADING OF DATASET 'dsnxxx.xxxx.dbnam.tsnam.jooo1.a002' WILL EXCEED THE MAXIMUM SIZE OF ******* PAGES"

    and the control card it is using "PART 2".
    My observations are it is a PBG(Since Maxparts is there) and i can see currently 3 partitions for that TS.

    I reran the Reorg again with removing the "PART 2" from the control card and it was successful.
    Was this approach correct ?
    Please let me know why the Initial Reorg Failed.

    ReplyDelete
    Replies
    1. First of all, partition 3 would not have been added to the table space unless partition 2 had filled up. Assuming that there have not been many deletions of rows from partition 2, a partition-level REORG for that partition probably failed because with free space reestablished (assuming non-zero values for PCTFREE and/or FREEPAGE for the partition), the data would not fit back into the partition. When a partition-by-growth table space is REORGed in its entirety, DB2 can distribute rows across the table space's partitions so that all will fit, and if DB2 needs to add a partition to the table space to accommodate all rows plus reestablished free space, it will do so.

      Delete