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:
  • For a table space that is partitioned (non-universal), run RUNSTATS UPDATE ALL before REORG.
  • For any other type of table space, run RUNSTATS UPDATE SPACE before REORG.
When you run RUNSTATS with SHRLEVEL REFERENCE, real-time statistics values are also updated.
You can override the dynamic allocation of sort work space in one of the following ways:
  • Allocate the sort work data sets with SORTWKnn DD statements in your JCL.
  • 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.