When IBM introduced us to the “Universal Table Space”, (UTS), they brought in two distinct flavors:
- Range-Partitioned – which finally enabled segmented, partitioned data (hoorah!)
- Partition-By-Growth (PBG)
The advantages of UTS are many, including: better space management for VARCHAR style data, improved MASS DELETE performance, TS scans limited to active segments, and immediate data reuse after delete.
Enabling the PBG option with the MAXPARTITIONS clause on the CREATE TABLESPACE was a wonderful addition to the world of DB2. Finally, you never had to “worry” about a data set running out of space. As soon as it got to, say, 2GB a new partition was simply “added”, and the world carried on spinning. What a great idea! No more 64GB limit for a segmented space since a PBG can get to be 128TB.
Then the doubts set in…
How do I REORG this?
How do I recover this?
How do I manage this?
How do I REORG PBG?
Now you may be wondering, “What’s the problem with REORG?” Well, take a second to reflect on what a good old partitioned space looked like at the physical level: lots of partitions with varying amounts of data in them and none of them going to a secondary linear data set of course.
Now in the bright new world of a PBG: lots of partitions absolutely choc full of data and none of them going to a secondary linear data set of course.
See the problem now?
If you REORG a partition of a PBG, you run the very real risk of not being able to fit your data back in the partition! This is known by the very technical term: “not a good thing”.
To try and get around the REORG problem, IBM then introduced an “opaque” ZPARM called REORG_IGNORE_FREESPACE. If set to YES, the REORG simply uses zero for the PCTFREE and FREEPAGE options when reorganizing a partition. The hope being that, without adding space around newly inserted records, it would “fit back in the box.” However, we are all aware that it is easy to unpack stuff, but pretty hard to pack stuff back into the original box.
How do I recover PBG?
The good news is: RECOVERY and COPY are no problem for PBGs. All you have to do is take care of your current REORG thresholds and *always* REORG a PBG at the table space level – time to check your “home-grown” or 3rd party DB2 database maintenance routines! – unless you are 101% sure that the data will actually fit back inside! Also bear in mind the DBA mantra: “Never REORG a table space unless you have sequential access, or you must resize it”.
How do I manage partitioned table spaces?
As with all partitioned table spaces, you must still manage the “limits of DB2”, but no longer the “am I reaching the maximum size of a partition?”. Instead, you must check “am I reaching the MAXPARTITIONS value?” I hope you have automatic DB2 space management software that does *both* of these checks for you now. As always with DB2 – old stuff and problems disappear and new stuff and problems come in!
What else is not allowed?
Restrictions to partition-by-growth table spaces
Well, a quick look in the IBM documentation provides the following restrictions that apply to partition-by-growth table spaces:
- The PART option of the LOAD utility is not supported.
- The REBALANCE option of the REORG utility is not supported.
- The default SEGSIZE value 32.
- Table spaces must be DB2-managed (not user-managed) so that DB2 has the “freedom” to create data sets as partitions become full.
- Table spaces cannot be created with the MEMBER CLUSTER option.
- Partitions cannot be explicitly added, rotated, or altered. Therefore, ALTER TABLE ADD PARTITION, ALTER TABLE ROTATE PARTITION, or ALTER TABLE ALTER PARTITION statements cannot target a partition partition-by-growth table space.
- XML spaces are always implicitly defined by DB2.
- If the partition-by-growth table space is explicitly defined, the LOB table space for the first partition of the partition-by-growth table space is defined based on the SQLRULES(DB2) or SQLRULES(STD). Any additional LOB table space for the newly-grown partition in the partition-by-growth table space is always implicitly defined by DB2, regardless of whether SQLRULES is in effect. Specification of the SQLRULES(DB2) or SQLRULES(STD) option does not affect the LOB table space for implicitly defined partition-by-growth table spaces.
- A non-partitioning index (NPI) always uses a 5 byte record identifier (RID).
- Partitioned indexes are not supported.
Quite a hefty list of restrictions there! But, if you can live with the above and you want exceptionally large segmented table spaces, then PBGs are the way to go. Remember the other DB2 DBA mantra: “It Depends”, since PBG spaces should not be used for every table space – at least not yet anyway!
Feel free to send me your comments and ask questions.
TTFN,
Roy Boxwell
Senior Architect