DB2 9 for z/OS has plenty of great features; everyone is bound to find something they like in the latest version. This article explores:

  • A feature that has generated considerable discussion—a new structure type called universal table space
  • An enhancement that you may not hear all that much about—a new storage format referred to as reordered row format.

Both of these will significantly improve your DB2 experience by making a couple aspects of DB2 easier to manage.

You may not have heard a lot of clamoring for universal table space, but it’s something many people have wanted for years. As you deploy DB2 9, you’ll find this to be one of the more useful features and I believe it will soon become the de facto standard for building DB2 objects.

With DB2 for z/OS V8, you have four types of table spaces to choose from:

  • Simple: multiple tables per table space and all rows from all tables can share the same page in that table space. Simple table spaces go back to the dawn of DB2 on the mainframe. A simple table space has a maximum size of 64GB.
  • Segmented: segmented table spaces, which didn’t arrive in DB2 until V2, let you create multiple tables in a single table space. However, each table is contained in its own segment. You get to pick the segment size, something between four pages and 64 pages in multiples of 4 (4, 8, 12, etc.). A segmented table space also has a maximum size of 64GB.
  • Partitioned: allows multiple partitions; each partition can have a possible maximum of 64GB and you can define from one to 4,096 partitions in current releases of DB2. However, only one table is allowed per partitioned table space.
  • Large Object (LOB): the last table space type is for LOBs. If an LOB table space is defined, the rows defining the LOB are contained in a separate table space called the base table space.

When you upgrade to DB2 9, you’ll gain two new table space types. However, you end up with only a total of five table space types. It’s the new math. In DB2 9, you can no longer create a simple table space. Although you can still use and alter existing simple table spaces, any newly created table space in DB2 9 can’t be simple. The five table space types available to you in DB2 9 still include segmented, partitioned, and LOB. The two new table spaces added in DB2 9 are:

  • Universal (the subject of this article)
  • XML, which holds the new XML data in DB2 9. An XML table space is a little like an LOB table space. If an XML table space is defined, the rows defining the XML are contained in a separate table space called the base table space.

What’s a universal table space? The simplest way to explain a universal table space is to say it’s a cross between a partitioned table space and a segmented table space, hopefully giving you many of both of its parent’s best features. When using a universal table space, you get the size and growth of partitioning while maintaining the space management, mass delete performance, and insert performance of a segmented table space. It’s kind of like having a segmented table space that can grow to a 128TB of data (assuming the right DSSIZE and right number of partitions are specified) that also gives you partition independence.

Another benefit of a universal table space is the ability to use cloned tables; a universal table space is a prerequisite to using CLONE tables in DB2 9. Of course, like the original partition table spaces, a universal table space can contain only one table per table space. A universal table space also must be managed by DB2 (using DB2 storage groups). A universal table space can’t be a user-managed table space. Reordered row format, the last subject of this article, is always used for universal table space. Finally, before you get too excited, realize you won’t be able to take advantage of universal table space until DB2 9 for z/OS New Function Mode (NFM).

Universal table spaces come in two flavors: partition-by-growth and range-partitioned (a.k.a. partition by range). Partition-by-growth universal table spaces have no partitioning columns and grow “on demand.” As data is inserted into a partition-by-growth universal table space, a new partition is automatically created when the current partition becomes full. A range-partitioned universal table space is similar to the partition table spaces we’ve all grown up with, with the exception that it’s segmented with all the advantages of a segmented table space.

Let’s take a closer look at partition-by- growth universal table spaces. When a partition of a partition-by-growth universal table space reaches its maximum size from the addition of new rows, a new partition is automatically added to the table space. The new partition takes on the characteristics of the previously filled partitions, including using the same dictionary if compression is turned on. In addition to copying over the dictionary, the new partition has the same FREESPACE, DEFINE, logging, and TRACKMOD attributes.

Partition size, and how much data a single partition of a partition-by-growth universal table space can hold is determined by the DSSIZE keyword, or DSSIZE default if the keyword isn’t specified. If DSSIZE is 2GB, a new partition is added when that 2GB partition is full. If a partition-by-growth universal table space is growing (adding partitions) on its own, what prevents a runaway application from taking over all your available disk space while building the maximum number of partitions?

2 Pages