Sort file size estimation

The sort file size for each sort required is the major factor that affects the distribution of workloads and resources between separate sorts.

In almost all cases NGT Recover can automatically estimate the file size using DB2 statistics, data set size, image copy size, or DB2 log ranges.

If the file size estimates that result from the use of the defaults are inaccurate and cause performance to suffer, NGT Recover offers ways to override these estimates with the NUMREC option.

Log sort default

NGT Recover attempts to estimate a file size for a log sort by calculating the number of pages contained in the SYSIBM.SYSLGRNX log range that is required to recover the all the objects in a step and assuming there will be one log record per page input to the sort.

Depending on the number of objects in the recovery, the size of log records, and the number of records for other objects, this strategy may or may not be reasonable. The values that are reported by the LOGSCAN command provide a better estimate for NUMREC and AVGRECSZ.

For more information about sorting the log, see LOGSORT strategy.

Key sort default

NGT Recover can determine index sort work space estimates based on RUNSTATS or real-time statistics.

Use of RUNSTATS

For index rebuilds, if the DB2 UT SORT DATA SET ALLOCATION field (UTSORTAL subsystem parameter) is set to NO, NGT Recover uses the value that is stored by RUNSTATS in the CARD column of SYSIBM.SYSTABLES to determine the cardinality of the table. If the index is for a partitioned table space, the number of rows is divided equally among all partitions. If a -1 is in the CARD column, NGT Recover (by default) assumes that RUNSTATS has not been run and calculates an estimated file size. This calculation is based on the size of the table space, the maximum rows per page, and the number of indexes.

Note

If you chose the NUMREC CALC method and a -1 is found in the CARD column, and the underlying table space for the index is being recovered at the same time, this automatic calculation is not performed. In this case, no file size estimate is made and sort performance could suffer and sort capacity problems could occur or both.

For best performance on index sorts, use NOWORKDDN and keep your catalog statistics up to date with BMCSTATS, RUNSTATS, or the statistics option in NGT Copy. (If you use WORKDDN, NGT Recover determines the exact number of records to sort, but I/O to the work file may degrade performance.)

Use of real-time statistics

For index rebuilds with the DSN6SPRM UTSORTAL parameter set to YES, NGT Recover uses real-time statistics by using the TOTALROWS value from table SYSIBM.SYSTABLESPACESTATS for single-table table spaces.

Note

If UTSORTAL is set to YES and you perform a migration, TOTALROWS may not be accurate. If it is not accurate after the migration, you must reset the value to NULL. However, TOTALROWS is generally accurate and you may not need to reset its value in the following situations:

If you migrate data daily and the size of the space does not change much
If you include the real-time statistics tables in your migration

Separate index rebuilds

When rebuilding multiple indexes of varying lengths on the same table space and not enough sorts are allowed to distribute the indexes with different length to separate sorts, sort performance is improved if some indexes are rebuilt in separate jobs because NGT Recover pads all keys to the length of the longest key directed to the same sort to avoid a variable length record sort. Rebuilding some indexes in separate jobs also reduces the amount of sort work space that is required.

Related topic

Sort performance