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 .)
Parameter settings in the NGT Recover options module (AFR$OPTS), which are described in NGT Recover installation options
Number of log sorts that can run concurrently
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.
NGT Recover provides an approximation only for the amount of sort work spaces required even if statistics or NUMRECs are accurate. Real space requirements could vary depending on amount of memory available at run time, number and size of sorts running in parallel, the methods of sorts chosen by BMCSORT, and other variables.
You should use the information generated by the technique described above with caution. If there were significant changes in recoverable objects, used options, environment, and other variables between the ANALYZE ONLY run and real recovery run, NGT Recover could change the recovery plan.
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.
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.
BMC recommends that you allow BMCSORT to dynamically allocate all sort work data sets. You should code sort work DD statements in your JCL only if you have a special situation where dynamic allocation will not work.
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 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.
The sorting requirement at recovery time can be reduced significantly by running R+/CHANGE ACCUM in advance. For more information, see the .