Table Space Design for Your DB2 Data Warehouse
Last week we talked about the types of table spaces possible for our large temporal table. The new table partitioning by range (PBR) was chosen because of its many performance advantages. The ability to separate I/O performance and locking to separate partitions, the ability to improve backup and disaster recovery scenarios and the ability to better control placement of partitioning data sets to devices are all advantages of the new UTS PBR table space for our new temporal table.
Having the table space choice type in place, the analysis of the partitioning key or keys and the number of partitions that are needed are the next analysis points for the temporal table. Partitioning can be defined using a single column or multiple columns to provide the range limits that separate the data partitions. A single column as a partitioning key for the temporal table can work out as long as it’s unique enough to spread the data across the partitions. Single columns such as identity, date, ROWID, VARCHAR are all options to use, but many of these types run into restrictions. There are also restrictions around the date or timestamps used as the SYSTEM_TIME or BUSINESS_TIME within our temporal table. Usually the best columns for partitioning keys are the natural numeric keys within your business. Multiple numeric columns are usually a better choice for partitioning schemes because the various column values are more varied and provide better granularity for evenly spreading the data across the partitions.
How evenly the partitioning column or columns key values spread the data is an important factor that will minimize locking, spread the performance across multiple table space partition data sets and potentially a number of storage devices. Analysis of the key values and how the data falls into the partitioning definitions is critical for positioning the data across all the partitions of the temporal table in your DB2 data warehouse evenly.
Additionally, how many partitions are necessary for the temporal table is always an interesting debate. Given the improvements handling large numbers of data sets, DB2 can handle the maximum of 4096 partitions very easily. Partitioning schemes below 4,000 are recommended, but pick a number of partitions that are manageable, keep the data set size well below 4GB and a partitioning scheme that never needs to rotate, add or remove partitions for as long as the database table space exists. Partitioning for a temporal table with more than 100,000 rows or billions of rows can provide great performance with as few as hundred (100) or even a thousand (1,000) partitions.
Researching how the key partitioning columns are used in the application is also very important. The partitioning columns for the temporal table should be analyzed for their update frequency since it is also very expensive to update a partitioning key and move the row from one partition to another. Several batch and or even thousands of online application processes should be able to reference the database concurrently and potentially multiple partitions within their application processing for parallel processing. Parallel processing is extremely vital for a temporal table within a high performance DB2 data warehouse and dramatically reduces runtimes especially for daily ad hoc application processing and loading historical data.
These are only highlights of partitioning guidelines and practices that have worked for many of the high performance database table space I have implemented and witnessed over the years. Do the analysis of your DB2 data warehouse application and its data keys and design the temporal table space partitioning that is going to work best for your application performance.
Come to my presentation at International DB2 Users Group – IDUG Conference in Anaheim May 3-9, 2011.
If you are or someone at your company is attending the IDUG NA Conference in Anaheim, Dave will give his “Database Performance Discoveries and Recommendations” presentation on Tuesday, May 3rd at 4:30-5:30.
Come to my presentation at the Information on Demand – IOD Conference in Montpellier, France July 6-9, 2011.
If you are or someone at your company is attending the IOD-EU Conference in Montpellier, France Dave will give his “Database Performance Discoveries and Recommendations” presentation and maybe his “Java DB2 Developer Performance Best Practices Part 2” presentation.
Leave a Reply