The portable Global Positioning System (GPS) is a great resource. It can help you navigate streets, get turn-by-turn directions, and guide you around road construction. It will even tell you which lane you need to be in.
It would be nice to have a GPS equivalent for DB2 definitions when dealing with the physical nature of objects. One of the first things we need to consider when working on a project is what the table space and index space should look like. Understanding the myriad options is critical, but daunting. Some initial decisions to consider are:
• What table space and index types should we use and should the table space and index space have the same attributes?
• How large will the objects become?
• Should sliding secondary be used or specific space amounts?
• Should FREEPAGE or PCTFREE be used?
• Should we use partitions?
• Is data set parallelism important?
• Should the data be compressed?
• Which buffer pools should the data sets reside in?
Unfortunately, there’s no one-size-fits-all answer. Getting the options right the first time is critical to avoid costly rework and possible downtime. The familiar GPS equivalent of “recalculating” when you take a wrong turn isn’t available. DB2 10 provides many great alternatives to dropping and re-creating objects, but a REORG is still required to implement many new options. Here, we will discuss some of the physical options.
Among the first considerations is what type of table space to allocate. The choices are:
• Simple, which can be multiple tables that are interleaved; a page can contain rows from different tables. The maximum table size is 64GB (2GB per data set * 32 data sets). Simple tables have simple space maps that don’t allow for mass delete operations.
• Segmented, which can be multi-table, but only one table can occupy a specific page; there’s no interleaving. The maximum table size is 64GB (2GB per data set * 32 data sets). Segmented tables have sophisticated space maps that allow for mass delete operations.
• Classic partitioned, which is a single table only. The maximum table size depends on the page size, with the maximum of 128TB for a 32K page. Classic partitioned tables have simple space maps that don’t allow for mass delete operations.
• Universal Table Space (UTS) – Partition By Growth (PBG), or Partition By Range (PBR) are single table only. The maximum table size depends on the page size, with the maximum being 128TB for a 32K page. UTS combines segmented and classic partitioned with sophisticated space maps that allow for mass delete operations.
With DB2 V8 out of service, simple has been deprecated and isn’t an option for new table spaces. Classic partitioned may one day be deprecated. In DB2 10, the workaround to creating a classic partitioned table space is to specify SEGSIZE 0. An alternative is to change the ZPARM value for DPSEGSZ. Before doing so, carefully review the Installation and Migration Guide.
Specifying 0 segments for classic partitioned objects is required because, unlike UTS objects, they have no segments. UTS is a hybrid approach that takes the best from segmented and classic partitioned and provides a table space that can be partitioned and have segments. Benefits of UTS include faster insert and mass delete processing.
Most organizations still use classic partitioned and most still have some index-controlled partitions, properly called Partitioning Indexes (PIs), instead of table-controlled partitions. Starting in DB2 10, the default for creating partitioned table spaces with ranges is PBR, which can be defined only as table-controlled. Trying to create a PBR that’s index-controlled will fail with SQL code -662. Consider converting PIs to table-controlled.
UTS is the wave of the future. Starting in DB2 10: