Defining TUSEMTD table to use partition-by-growth table space (UTS) in DB2

As described in the Administration Guide and Reference manual (V8.1 - Chapter 11 Troubleshooting), the default definition for the VUSEMTD table space has a space allocation limit of 64GB.  In the "Troubleshooting" section, the example is for definition of a partition-by-growth and segmented table space (also called universal table space).  

Note that in DB2 V10, it is possible to migrate an existing VUSEMTD segmented table space to a partition-by-growth segmented table space (UTS) using the DB2 ALTER command by defining a value the MAXPARTITIONS parameter.  This option is not supported in DB2 V9 and the steps involved in the migration are described in "Chapter 11 Troubleshooting" section.    

Once this 64 GB limit is reached (DB2 reason code 00C900A4), redefining VUSEMTD as a partition-by-growth table space and then porting the data (for DB2 V9) requires significant effort on the part of the DBA and TADz personnel. The best strategy is to avoid having to perform this task. This wiki write-up is a supplement on some strategies in overcoming this limitation.  

Based on customer data, following are rough estimates on number of records in the TUSEMTD table when the 64GB threshold is likely to be reached.  The numbers may vary depending on fragmentation of the VUSEMTD table space.       

a. V7.5 - 780 million rows

b. V8.1 - 630 million rows

Here are some helpful strategies:

1.  Define VUSEMTD as a partition-by-growth up-front

After HSISCUST, customization, in member HSISSQ17 of PARMLIB, change the definitions of VUSEMTD table space.  Check with your DBA on specific parameter values in the definition.  An example is provided in the manual (V8.1 - Chapter 11 Troubleshooting).

If you intend to import all your usage data into a single repository, then define VUSEMTD as a partition-by-growth table space up-front.

2. Plan based on growth 

This strategy is only useful for planning in advance whether you still need to redefine VUSEMTD as a partition-by-growth table space and perform the onerous task of porting the data. 

Run these SQL statements to track the growth of TUSEMTD records per LPAR and/or period:

SELECT FPERIOD, COUNT(*) FROM xxxx.TUSEMTD GROUP BY FPERIOD ;

SELECT FLPARID, FPERIOD, COUNT(*) FROM xxxx.TUSEMTD
    GROUP BY FLPARID, FPERIOD ;                      

As explained in the manual, performing regular housekeeping on the TUSEMTD table with jobs HSISUSUM, HSISUDEL and HSISLDEL to maintain the TUSEMTD table at an optimal level is one approach to avoid reaching the 64GB limit and also to improve SQL query performance.