DB2 Table Space Design Choices
In the previous weeks we talked about getting started with a DB2 temporal table and the DB2 DSNZPARMs that will affect your DB2 data warehouse performance. This week we’ll start to design the implementation of the DB2 temporal table with a discussion of the type of table space and table we should have to maximize our DB2 data warehouse application performance.
Just like any table, a DB2 temporal table can have any table space definition. We want to avoid the obvious limiting table spaces of segmented unless the DB2 temporal table is going to be really small. The old school classic partitioning that is still available in DB2 10 should also be avoided in your DB2 data warehouse application since it is being phased out in the coming future releases. What we want is the latest and greatest type of table space a Universal Table Space (UTS).
Partitioning by growth (PBG) table spaces are good in the sense that you don’t need to worry about their space allocation. As their name implies, when the number of rows in the table grows, additional partitions are allocated and the table continues its operation. This is table space type is great for tables where the growth perspectives are unknown and especially for software packages where there are thousands of table spaces to manage.
The best table space definition for the DB2 temporal table in your DB2 data warehouse is the universal table space using the new table partitioning by range (PBR). This is the latest incarnation of the classic range partitioning where the partitioning range is set up using table values, not partitioning index values like the classic range partitioning. This UTS PBR provides all the standard partitioning by range advantages for separating out the data based on columns or column values and provides a way to quickly and easily separate data based on activity, across devices or for extra application parallelism. For high performance database tables like the DB2 temporal table, UTS PBR table partitioning is usually the best choice. The flexibility to perform on-line reorganizations against the individual partitions, separate application activity latches and locks into different partitions, and move table space data sets to optimize hardware I/O rates provide huge overall performance flexibility for your DB2 data warehouse.
The reason choosing the correct table space is critical is because redefining the DB2 temporal table is extra labor intensive once the temporal and its associated history table are both defined in your DB2 data warehouse. Getting the definition for the UTS partitioning by range table correct first can be difficult because of additional design decisions about the number of partitions and which columns need to be defined or what columns should be used for the range definitions.
These are only some of the table space design choices and I will cover those next week as the DB2 temporal table design for your DB2 data warehouse discussion continues.
Leave a Reply