To understand the UTS concept it helps to look back and see what we, at least in the Db2 world, started with!
In the beginning it was simple
In the beginning were simple tablespaces with the possibility of multiple tables all mixing rows with each other that meant performance got very bad after a very short period of time. We also got single table partitioned tablespaces that required a Partitioning Index (PI) to be created before you could use the table at all. You could also create non-partitioned indexes (NPIs) that were Unique or Non-Unique and were based on the data from all partitions. You could, sort of, manage the size of these using the PIECESIZE parameter, but most shops just took the default and never used it.
Something new in V2R1
Then in DB2 V2R1, we got segmented spaces where all of any given segment only held rows for one table. This improved performance a lot, especially for insert and mass delete processing. It stayed like this right up until DB2 V8.1 when the requirement for a PI was dropped as now you could “partition by table”. This was a leap forward and also brought in the new Data-Partitioned Secondary Index (DPSI) to allow a different partitioning scheme as opposed to the actual partitioning scheme. First, only non-unique DPSIs were allowed (Just think about up to 4096 index checks for breaking a unique rule!) Unique was then finally actually allowed in DB2 V9.1 as long as the DPSI columns were a superset of the partitioning columns.
DB2 V8.1 the *big* release!
DB2 V8.1 was also the release that renamed our good old NPIs to be non-partitioned secondary indexes (NPSIs) The rules for “When is an index a partitioned index (PI or DPSI)?” or “When is it a non-partitioned secondary index (NPSI)?” are still pretty hard to explain – and most users just seem to guess!
Death of a PI
The drop of the PI was very good but most shops kept them hanging around as the application code was using them all over the place. DPSIs became a problem right out of the get go causing massive index probes and performance headaches right up to this day. Nowadays, the vast majority of indexes are NPSIs with a few, hopefully well chosen, DPSIs and any required PIs.
DB2 V9.1 new things appearing
Then in DB2 V9.1 along came the new kid on the block – UTS.
IBM has mentioned on multiple occasions that any new Db2 features will only be developed for UTS usage, which has been seen with the developments of CLONE spaces, HASH spaces, Currently committed locking behavior, Pending DDL, Inline LOBs, XML multi-versioning, and ALTER TABLE with DROP COLUMN. This list just keeps on growing.
The Universal TableSpace was born!
The UTS was created with two distinct and very different flavors.
Partition-By-Range (PBR) is a single-table, segmented tablespace that is partitioned on data values that are given at the table level. Partitions can be from 1 GB DSSIZE up to 64 GB DSSIZE in DB2 V9.1 (In DB2 10 it rose to 256 GB DSSIZE.) The maximum physical size of the partitions and the corresponding partitioned and non-partitioned indexes are all, relative to each other, the same. This was a modernization of the original partitioned table space with its index-based partitioning scheme.
PBRs got used straightaway as the benefits of the segmented definition were quickly realized. They gave fast spacemap usage for variable length rows and much faster mass delete processing. Changing the maximum possible size, the DSSIZE, of a partition or even of a partitioned index was still very nasty as it required a complete tablespace REORG to action. For NPSIs there was no real change – still just PIECESIZE here to, sort of, manage the maximum size of the datasets.
Calculating the maximum possible sizes is also “non-trivial”. For the data partition it is relatively straightforward but for the PI, the DPSI, and the NPSI it is a pretty nasty set of calculations you must do – and any mistake can quickly lead to an SQLCODE -904!
Partition-By-Growth (PBG) is a single-table, segmented tablespace that can have from 1 to 4096 partitions, each of which can be from 1 GB DSSIZE up to 64 GB DSSIZE in DB2 V9.1 (In DB2 10 it rose to 256 GB DSSIZE.) The PBG cannot have a partitioning key but can have multiple NPSIs, if desired. The PBG was basically viewed as just one huge container where you could just insert rows forever. This has benefits and drawbacks!
Benefits are: No worries about PI and DPSI definitions as you can only have NPSIs. No worries about running out of space – I mean 4096 huge partitions is a vast amount of space!
Drawbacks are: No PI and so no way to balance the partitions, a terrible space map search algorithm that simply kills mass insert processes and a huge problem with utilities as it is “one huge space” – and reorganizing one part in the middle of 100’s just causes grief. Plus the inherent problems in copying or cloning data are more complicated, especially if the number of parts on source and target are not the same.
It all started so easily…
In the beginning people used MAXPARTITIONS 4096 thinking “I will never need to change this” but very very quickly it turned out that this was a disastrous idea! The problem being that *all* of the partitions required some control block storage in memory, even if not physically defined or even in use! This caused massive problems and the recommendation to drop down a *lot* in the usage of MAXPARTITIONS. Over the years it has dropped down even more and so now, as of Db2 12, the IBM recommendation for a PBG definition is:
MAXPARTITIONS 1 DSSIZE 64 GB
For the older readers amongst us it should be apparent that this is the good old Linear Dataset (LDS) limit for simple and segmented spaces but rolled out in a new way! For the younger people out there, when DB2 began you could only have 2GB in one VSAM dataset, and so simple and segmented datasets got the ability to allocate up to 32 VSAM datasets, thus giving us the *massive* amount of 64GB space! Coincidence? I think not!
That’s enough about UTS for this month, come back next month for a wrap up about how Db2 12 has changed this picture and all the ways to handle these spaces in the best possible way!
TTFN,
Roy Boxwell