Determining sort work space requirements

• Virtual memory requirements, which are controlled by the SMCORE installation option (see SMCORE=(0K,0K))

• Fixed disk storage (DASD) requirements, which are controlled by the following settings:

  • BMCSORT options settings (For more information, see the BMCSORT documentation .)

  • Parameter settings in the NGT Recover options module (AFR$OPTS), which are described in NGT Recover installation options

• Number of parallel index key sorts, which is controlled by the MAXKSORT option (MAXKSORT integer and MAXKSORT) and the KSORTSHARE option (MAXKSORT integer and KSORTSHARE=YES)

• Number of log sorts that can run concurrently

These requirements have an affect on sort performance. For more information, see Sort performance and UNLOADKEYS and BUILDINDEX strategy.

Index sort work space requirements

If you need to know the amount of sort work space that will be required for rebuilding indexes, you can request that NGT Recover provide this information. You do this by coding at least one sort work DD statement in your JCL.

For example, you could code the following DD statement:

S001WK01 DD DUMMY

You could then run NGT Recover with OPTIONS ANALYZE ONLY to receive a list of sort work data sets that will be used in this job along with the estimated space required for each sort work data set. This output is in the AFRSTMT message data set. An example of the resulting output follows:

BMC96308I ESTIMATED NUMBER OF TRACKS FOR ALL INDEX KEY SORT WORK DATA SETS FOLLOWS:
 BMC96309I    S001WK**:       5627  (TRACKS) 
 BMC96309I    S002WK**:       5627  (TRACKS) 
 BMC96309I    S003WK**:       9184  (TRACKS) 
 BMC96309I    S004WK**:       9184  (TRACKS) 
 BMC96309I    S005WK**:       9184  (TRACKS) 
 BMC96309I    S006WK**:       5627  (TRACKS)

You can then code DD statements for one or more of the sort work data sets with the amount of disk space given for each data set in the AFRSTMT output. If you do not code DD statements for some of the sort work data sets, BMCSORT dynamically allocates them.

If you do not code any sort work DDs in your JCL (you allow BMCSORT to dynamically allocate all sort work data sets), NGT Recover dynamically selects work files for each sort based on availability, and which set of sort work files are assigned to a particular index is not predictable. In this case, the information in AFRSTMT, as shown in the example output above, is only useful in determining the total amount of required sort work space.

If you code at least one sort work DD in your JCL, NGT Recover statically assigns sort work data sets to indexes during the ANALYZE phase. In this case, you can reliably determine the space requirements for each set of sort work data sets from AFRSTMT, as shown in the example output above.

The estimates that NGT Recover provides are based entirely on the estimated number of keys for each index. NGT Recover obtains this information from the NUMREC parameter if you specify it on the REBUILD statement. Otherwise, NGT Recover uses the following values to determine the estimates:

• For DB2 Version 10 and later with the UT SORT DATA SET ALLOCATION field (DSN6SPRM UTSORTAL subsystem parameter) set to NO (the default value), NGT Recover uses partition or table cardinality if a RUNSTATS operation has been performed for the table space.

• For DB2 Version 10 and later with UTSORTAL 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

If none of these sources is available, NGT Recover attempts to calculate the number of keys by using the size of the table space data sets. This calculation can be very inaccurate if the table space contains multiple tables or the table contain VARCHAR columns. To get accurate sort work size estimates, you should

• Ensure that RUNSTATS is run frequently

• Use real-time statistics (by setting UTSORTAL to YES)

• Provide an accurate estimate on the NUMREC parameter

The amount of parallelism that NGT Recover can achieve can be affected by coding sort work DDs in the JCL. When you code one or more sort work DDs, NGT Recover statically assigns sorts to indexes, which prevents any overlap of index rebuilds with table space recoveries if keys are being extracted.

For example, assume that you want to recover table space A and table space B and rebuild all indexes for the two table spaces (and MAXLSORT=1). If table space A needs 8 sorts, table space B needs 12 sorts, and MAXKSORT is set to 20, NGT Recover can overlap the building of indexes for table space A with the recovery of table space B. Table space A will use sort work data sets S001WK** through S008WK** and table space B will use sort work data sets S009WK** through S020WK**.

Using this same example, if you code any sort work DDs in your JCL, NGT Recover will use S001WK** through S008WK** for table space A and S001WK** through S012WK** for table space B. When the recovery for table space B begins, the recovery will have to wait for the index builds for table space A to complete because sort work data sets S001WK** through S008WK** are not available.

Log sort space requirements

If a significant number of log records exist, the sort process can require additional system resources.

NGT Recover invokes BMCSORT. The amount and type of resources that are required for the sort are dependent on the number of log records to be sorted. The total amount of sort work space that is needed to sort the log optimally is roughly equal to the sum of the lengths of all log records involved in the recovery times two. You can specify L001WKnn DD statements in the JCL for log sort work data sets or use the SORTDEVT and SORTNUM options for NGT Recover.

You can use the MAXLSORT option (MAXLSORT integer and MAXLSORT=0) to specify that you want to run multiple log sorts.

You can specify LxxxWKnn DD statements in the JCL for log sort work data sets or use the SORTDEVT and SORTNUM options for NGT Recover.

The LOGSCAN command can help you size the log sort work data sets by estimating the number of bytes of log data to be sorted. This command also provides NUMREC and AVGRECSZ estimates that can be used to improve log sort performance.