DB2 9 for z/OS has a new keyword on the CREATE/ALTER SQL statement: MAXPARTITIONS. As the parameter implies, MAXPARTITIONS tells DB2 how many partitions a partition-by-growth universal table space can use before reaching its maximum number of partitions, therefore, stopping its growth. If you don’t specify the MAXPARTITIONS keyword, it defaults to 256 partitions. You also should specify the segment size for the table space. If you don’t specify SEGSIZE, it defaults to four pages. A four-page segment size typically isn’t optimal for whatever use you have planned for the table space.

You can specify several combinations of NUMPARTS, MAXPARTITIONS, and SEGSIZE. If NUMPARTS and SEGSIZE are specified, you get a range-partitioned (partition by range) universal table space. If only the NUMPARTS keyword is used (no SEGSIZE specified), the result is a traditional partition table space that won’t take advantage of segmentation. If, however, only SEGSIZE is used (no NUMPARTS or MAXPARTITIONS keywords specified), you end up with a traditional segmented table space, with no partitioning. Remember, there’s no longer any combination of keywords that will let you create a simple table space.

DB2 9 for z/OS also lets you implicitly create a partition-by-growth universal table space using the CREATE TABLE statement. The new phrase PARTITION BY SIZE EVERY xxx G (where xxx is an integer less than or equal to 64) tells DB2 to create this table in a partition-by-growth universal table space. If a table space name is specified on the CREATE TABLE’s IN clause, it must point to a partition-by-growth table space. If a table space is specified, the EVERY xxx G must specify the same value as the table space’s DSSIZE. If the table space is implicitly created, it’s created with LOCKMAX set to SYSTEM, MAXPARTITIONS equal to 256, SEGSIZE equal to 4, and DSSIZE equal to 4G. Choose carefully before letting DB2 implicitly create a partition-by-growth table space. The only way to change the SEGSIZE or DSSIZE of an implicitly created partition-by-growth table space is to drop and re-create the table space. There’s no ALTER TABLESPACE option for DSSIZE and SEGSIZE.

The DB2 catalog also has had a few changes in support of universal table space. The column TYPE in the catalog table SYSIBM.SYSTABLESPACE has some new values:

  • K for a range-partitioned table space
  • G for a partition-by-growth table space
  • P for an implicit table space created for XML columns.

There’s also a new column in SYSIBM.SYSTABLESPACE. MAXPARTITIONS contains (yes, you guessed it) the maximum number of partitions for a partition-by-growth table space. The existing PARTITIONS column in SYSIBM.SYSTABLESPACE will contain the number of physical partitions that currently exist in a partition- by-growth table space.

The other significant change in DB2 9 for z/OS is how the order of a table’s columns is maintained when the underlying data is stored on disk. An ongoing challenge for DB2 professionals has been the placement of the fixed length (CHAR) columns vs. the variable length (VARCHAR) columns when defining a row for a newly created table. There have been many different opinions about what’s correct, efficient, and best. Most agreed that the VARCHAR columns should go at the end of the row. However, no matter in what order you place the column, once DB2 hits a VARCHAR column in a row, it has to scan the row to find the rest of the columns. Until DB2 reads the length on the VARCHAR column (the two-byte prefix on every VARCAHR column), it has no idea where the subsequent columns in that row begin.

DB2 9 has a strong solution to this quandary. Once you get to DB2 9 for z/OS NFM, all VARCHAR columns on newly created table spaces will be placed at the end of the row. DB2 9 can make this type of decision because the row format in DB2 9 has changed. DB2 9 introduces “reordered row format,” which is a straightforward, simple concept. A DB2 9 NFM row will have all the fixed length columns first, followed by pointers to the beginning of each VARCHAR row. The pointers are followed by the actual variable length data. Rather than scan what could be some lengthy columns just to find the beginning of the column you’re looking for, DB2 needs to scan only the list of pointers to find the location for the beginning of the column you want.

As stated earlier, get reordered row format for any table space created in DB2 9 NFM. You’ll also have your table spaces, or table space partitions, converted to reordered row format when a REORG or LOAD REPLACE is run against a table space or table space partition. Watch out for the partitioning piece. If you REORG only selected partitions of a table space, then you’ll end up with some partitions—the ones that have been reorganized in the new reformatted row format. The remaining partitions that haven’t yet been reorganized will stay in basic row format. Basic row format is the phrase used to describe the current row format, the row format prior to DB2 9 for z/OS.

There’s a potential issue that might arise from using reordered row format. It concerns those of you who have just moved to DB2 9 for z/OS NFM and also use table space compression. If you’re satisfied with your compression's dictionary, when you run REORG or LOAD REPLACE, you should probably specify the keyword KEEPDICTIONARY. KEEPDICTIONARY avoids a dictionary rebuild, a task that could add a bit of time to your REORG process.  With DB2 9, the first access to a table space by REORG or LOAD REPLACE changes the row format from basic row format to the new reordered row format, assuming you’re in DB2 9 NFM. It’s anticipated that rebuilding your compression dictionary after converting to reordered row format will yield a more efficient compression dictionary. The possible problem is that many shops have KEEPDICTIONARY specified in their existing REORG and LOAD job streams and the dictionary won’t get rebuilt. APAR PK41156 was introduced to avoid forcing everyone to change all their jobs for just one execution to get the dictionary rebuilt. APAR PK41156 modifies REORG and LOAD REPLACE so they ignore KEEPDICTIONARY for that onetime run when the rows are reordered; this allows for a rebuild of the dictionary regardless of the KEEPDICTIONARY setting.

However, what happens if you really don’t want to do a rebuild of the compression dictionary right now? How do you get around this APAR’s change? Well, the APAR also introduces a new keyword for REORG and LOAD REPLACE that gives you a workaround and still doesn’t require you to change your jobs if you simply want DB2 to rebuild the dictionary. The new keyword is HONOR_ KEEPDICTIONARY and it defaults to NO. So, if you don’t specify this keyword, your dictionary will be rebuilt. However, if you do want to “honor” your current dictionary, you can add this keyword to your REORG or LOAD REPLACE job and things will behave as they did previously. This pertains only to table space compression; index compression doesn’t use a dictionary.

The plan is to also convert a table space to reordered row format if an ALTER ADD PART or an ALTER ROTATE PART is performed on the table space and you’re in NFM and none of the exclusions mentioned in the previous paragraph exist on the table.

However, there are exceptions to the aforementioned process automatically occurring. The DB2 catalog and directory table spaces and LOB table spaces won’t be converted to the new reordered row format. In addition, any table space containing   a table that uses an EDITPROC or VALIDPROC also won’t be converted.

As you move to reordered row format, you should be aware that the reordered row format only affects how your data is stored on disk; it has no effect on the logical order of columns. Your columns will still be returned in the order specified when the table was created and new columns (ALTER ADD) will still logically go to the end of the table description. Reordered row format also will add a few new columns to the DB2 9 catalog and make changes to the format of some log records.

You also need to be cautious when using DSN1COPY with the OBID translation option (OBIDXLAT) to move data between DB2 table spaces. If one table space uses reordered row format and the other table space uses basic row format and you want to move their data, you can’t. The row formats of the table spaces being copied must match. If they don’t, you can get unpredictable results. The good news is that the catalog table SYSIBM. SYSTABLEPART has a new column called FORMAT that identifies what record format the table space is using. If it’s the original, pre-DB2 9 basic row format, the column FORMAT will be blank. If the table space has been converted to the new DB2 9 reordered row format, the column FORMAT will contain an “R” (without the quotes, of course). Take the time to check before doing a copy.


This article is only an introduction to universal table space and reordered row format. There’s still more to the story and more to learn. If you’d like to read more about these new features and the rest of the enhancements in DB2 9 for z/OS, check out the latest DB2 Redbook from IBM, DB2 9 for z/OS Technical Overview (SG24-7330). Z

2 Pages