How to override dynamic sort work data set allocation

Db2 estimates how many records are to be sorted. This information is used for dynamic allocation of sort work space. Sort work space is allocated by Db2 or by the sort program that is used.

If the table space contains rows with VARCHAR columns, Db2 might not be able to accurately estimate the number of records. If the estimated number of records is too high, if the requested sort work space is not available, or if the estimated number of records is too low, which causes the sort to overflow, the utility might fail and cause an abend.

Recommendation:

To enable Db2 to calculate a more accurate estimate, run RUNSTATS UPDATE ALL before REORG. To update RTS statistics in the RTS catalog tables, also specify SHRLEVEL REFERENCE when executing RUNSTATS.

You can override the dynamic allocation of sort work space in one of the following ways:
  • Start of changeAllocate the sort work data sets with SORTWKnn DD statements in your JCL. The first of these DD statements must be SORTWK01.End of change
  • If the number of rows in the affected table space in column TOTALROWS of table SYSIBM.SYSTABLESPACESTATS is not available or is significantly incorrect, you can update the value to a more appropriate value using an SQL UPDATE statement. When REORG on the affected table space completes, TOTALROWS is set to the number of rows in the associated table space.
  • If the number of keys for an associated index in column TOTALENTRIES of table SYSIBM.SYSINDEXSPACESTATS is not available or is significantly incorrect, you can update the value to a more appropriate value using an SQL UPDATE statement. The next time that REBUILD INDEX is run, TOTALENTRIES is set to the number of keys for the affected index.