DB2 Performance Features in DB2 V9 for z/OS – Part 16

DB2 V9 Tablespace and Partitioning Choices

Some of the new tablespace partitioning choices and features in DB2 Version 8 and Version 9 for z/OS are making everyone realize there are a number of table design choices to fit any application access type.

In DB2 V8, the first of the tablespace definition partitioning options were enhanced with partitioning and clustering being separate options.  This enhancement brought out the options for the table clustering to be one way and partitioned in another direction.  For example, the separation of partitioning and clustering lets you quickly partition by time and have the clustering be by customer id within each partitioning.  This separation of clustering and partitioning provides a variety of choices and solutions to tough application access designs.

Now with the DB2 V9 for z/OS, the Universal tablespace and the partition by growth (PBG) tablespace provide even more design enhancements, flexibility and manageability.  The PBG and Universal tablespaces have become the defacto standards for software packages where the table population is unknown and the number of tables is too big for detailed analysis until the application usage trends are fully realized.  The PBG allows safety of operation since it can expand to match the application needs.  These options let the DBA define the tablespace for the variety of software packages, let them grow and provide good DB2 performance by separating the I/O into many partitions.

The partition-by-growth PBG tablespace provides even more flexibility by, as the name implies, growing a new partition as the number of rows increase in the table.  This is great for robust applications that are continuing to grow their rows.  The PBG tablespace is also great for segmenting these new rows away from the old rows within the tablespace.  This can be a tremendous benefit for focusing the  I/Os within the system into a portion of the tablespace and avoiding the scan of an entire tablespace.  The focusing of the I/OS also enhances the benefits from the DB2 buffer pool and allows more focused data and index entries to be cached within this vital memory resource.

Each tablespace type has different number of data sets and different size parts where DB2 I/O and extent management need analysis to be properly optimized for the applications.  With all these tablespace design choices, make sure to leave analysis time to pick the best configuration and optimize your tablespace choice for the best application and DB2 performance.

_______________________________________________________

Dave Beulke is an internationally recognized DB2 consultant, DB2 training and DB2 education instructor.  Dave helps his clients improve their strategic direction, dramatically improve DB2 performance and reduce their CPU demand, saving millions in their systems, databases and application areas within their mainframe, UNIX and Windows environments.

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>