Large data sets can outgrow a single partition and often require partitioning across two or more volumes. Depending upon the nature of your data set there are different partitioning schemes you can employ.
In version 5 of DB2, a table contained in a single partition was required in order to implement query parallelism, as parallelism could not be implemented on either segmented or simple table spaces. With a single partition when a table is used in a JOIN DB2 splits the query into a set of parallel queries. With version 6, this restriction was removed and any table can be used in parallel queries.
One common partitioning scheme is to create a set of partitions with a linear distribution of data. This scheme attempts to utilize a set of volumes and often makes use of different sized partitions. Linear distributions are best used with random keys, and have to be carefully considered in order to prevent the partition from running out of space. Although straightforward in construction, the use of a random key can be a performance problem for OLTP during data retrieval as it requires many rows to be evaluated. Thus for large and growing tables a linear distribution scheme is not the best choice. Another partitioning scheme is one where an ascending sequence is used across partitions. Most ascending schemes add data to the end of the sequence, and thus are well behaved in terms of data access (fewer hot spots) as well as being easier to maintain. While INSERTing data to an ascending sequence works well, performance issues arise with DELETEs and multiple updates, particularly when the rows are all in the same area.
Some people use what is called an archiving and rolling strategy to store their data. In this scheme a partition might contain one time period's data. As the next time period begins, the older partition is archived. The size of the table dictates the logical partitioning unit, be it month, quarter, year, or so forth.
About the Author
Barrie Sosinsky (barries@sosinsky-group) is president of consulting company Sosinsky and Associates (Medfield MA). He has written extensively on a variety of computer topics. His company specializes in custom software (database and Web related), training and technical documentation.
For More Information
- Feedback: E-mail the editor with your thoughts about this tip.
- More tips: Hundreds of free DB2 tips and scripts.
- Tip contest: Have a DB2 tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
- Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
- Forums: Ask your technical DB2 questions--or help out your peers by answering them--in our active forums.
- Best Web Links: DB2 tips, tutorials, and scripts from around the Web.
This was first published in August 2003