A fix is available
APAR status
Closed as new function.
Error description
Remove the need to specify SORTNUM on utility statements for dynamically allocated sort work data sets
Local fix
Problem summary
**************************************************************** * USERS AFFECTED: All DB2 V810 utility users who experience * * problems with sort work data set allocation * **************************************************************** * PROBLEM DESCRIPTION: The DB2 Utilities have been enhanced: * * (1) Allocate the optimal number of data * * sets for use by DFSORT. This * * eliminates the need to specify the * * SORTNUM option. * * (2) Use real-time statistics to * * estimate the records to be sorted. * * (3) Consider the key length when * * building indexes in parallel to * * make the most efficient use of the * * sort work data sets to minimize * * wasting dasd space. * * (4) Use the optimal degree of * * parallelism for CHECK INDEX. * * (5) Possibility to override sort work * * data set size calculation * **************************************************************** * RECOMMENDATION: Apply PTF when available * **************************************************************** The following problems are addressed in this enhancement: (1) Utilities that invoke DFSORT often experience problems to allocate large sort work data sets through DFSORT. When utilities are invoking DFSORT they pass the SORTNUM value as a guidance on how many data sets should be used to allocate the required disk space. In constrained environments DFSORT might not be able to allocate required disk space in the given number of data sets causing failure of this utility job. To avoid this failure SORTNUM values are often specified too large to enable DFSORT to allocate data sets even in very constrained situations. However a large number of data sets will limit the maximum number of parallel tasks that are used for those utilities that support parallelism. It is desirable to keep the number of data sets to a minimum to enhance the degree of parallelism. (2) Current estimates of sort work disk space can differ greatly from actual requirements if RUNSTATS catalog statistics are outdated or not available at all. In most cases this causes over allocation of sort work data sets but in some cases the estimates might be too low causing MSGICE046A SORT CAPACITY EXCEEDED failures or ABENDB37 when DFSORT tries to extend the allocated data sets. The utility will then end with ABEND04E RC00E40005. (3) When indexes are sorted in parallel and more indexes have to be sorted than parallel tasks are supported by available system resources, multiple indexes have to be processed within each sort task. Current assignment of indexes to sort tasks does not consider key lengths which can result in waste of sort work disk space as all keys have to be padded to the maximum length of the keys that are processed in the same sort task. (4) The CHECK INDEX utility does not achieve the same degree of parallelism when executed against the same set of indexes as compared with the REBUILD INDEX utility in the same environment. (5) The size that is used for dynamically allocated sort work data sets can be overridden with the new OPTIONS FILSZ command.
Problem conclusion
Temporary fix
Comments
DB2 Utilities have been enhanced in the following ways: (1) DB2 utilities CHECK DATA, CHECK INDEX, CHECK LOB, LOAD, REBUILD INDEX, REORG TABLESPACE, RUNSTATS have been enhanced to allocate sort work data sets dynamically before invoking DFSORT if the new ZPARM UTSORTAL is set to YES and no SORTNUM value is specified in the utility statement. Setting new ZPARM IGNSORTN to YES will cause utilities to dynamically allocate sort work data sets even if SORTNUM parameter was specified in the utility statement. The specified SORTNUM value will then be ignored. The utility will never allocate sort work data sets dynamically if DFSORT related DD cards (SORTWKnn, SW01WKnn, DATAWKnn, DA01WKnn, STATWKnn, ST01WKnn) were already specified in the JCL. When the utility allocates sort work data sets dynamically it will calculate the disk space requirements according to available record estimates and record length. Utilities will be trying to allocate the required disk space in two data sets for each sort task but will reduce allocation size if the requested amount is not available. Allocation of additional data sets continues until the required amount was fully allocated. Data sets will be allocated using DSNTYPE=LARGE if supported by the system. (2) To estimate the number of records to be sorted during utility execution the processed object will be looked up in real-time statistics tables SYSIBM.TABLESPACESTATS and SYSIBM.INDEXSPACESTATS. If real-time statistics are not enabled or information is not available for the object (i.e. TOTALROWS or TOTALENTRIES is set to NULL), the current estimation logic based on RUNSTATS catalog statistics will be used. It is recommended to set base values for each object that has counters containing NULL values in the real-time statistics tables by running LOAD REPLACE, REORG, or REBUILD INDEX. REORG and LOAD REPLACE will set base values both for table spaces and their indexes. REBUILD INDEX only sets base values for the specified indexes. When table spaces or indexes have been modified outside of DB2 (e.g. using DSN1COPY), the real-time statistics maintained by DB2 will no longer be in synch with the current object status. To avoid misleading estimates for such objects, the related information should be invalidated to keep DB2 utilities from using it. For table spaces set the column TOTALROWS in SYSIBM.TABLESPACESTATS to NULL, for indexes set the column TOTALENTRIES in SYSIBM.INDEXSPACESTATS to NULL. DB2 utilities will then use the current estimates based on RUNSTATS information. For best results, real-time statistics should soon be re-initialized for such objects by running LOAD REPLACE, REORG, or REBUILD INDEX. (3) The CHECK INDEX, REBUILD INDEX, LOAD and REORG TABLESPACE utilities take the number of allocated data sets into account for the calculation of the maximum number of parallel tasks. If the required disk space could be allocated in only a few large data sets this will increase the number of concurrent tasks and help in reducing elapsed time. The parallel processing for these utilities will now assign indexes to sort tasks in a way to reduce the disk space overhead caused by padding smaller index keys to the maximum key length used in each sort task. This will reduce overall sort work disk space requirements as long as multiple sort tasks are possible. There will be no improvement in disk space usage for processes with a single sort task. (4) CHECK INDEX was improved to use the same revised storage requirement values per data set and per task as the REBUILD INDEX utility when calculating the number of parallel tasks based on the available storage. See PK36329 for details on the REBUILD INDEX change. (5) OPTIONS FILSZ can be used to specify the required sort work data set size for all sort tasks within a utility step. The size is specified in units of 1 MB and will be used for all sort tasks in the utility that follows the OPTIONS statement. Use this parameter as a bypass if the automatically calculated sort work data set size still causes SORT CAPACITY EXCEEDED failures. To override the sort work data set size to 10 MB for CHECK INDEX you would use the following invocation: OPTIONS FILESZ(10) CHECK INDEX(myixname) SORTDEVT mydevt The following four messages were added to DB2: DSNU3340I csect-name UTILITY PERFORMS DYNAMIC ALLOCATION OF SORT DISK SPACE Explanation: The utilities that invoke a sort will perform predictable sort disk space dynamic allocation. Severity: 0 (informational) System action: Processing of the phase continues. Programmer response: No action is required. DSNU3341I csect-name CANNOT ALLOCATE SORT DATASETS WITHIN 255 LIMIT Explanation: When the utility performs dynamic allocation of sort data sets, the total number of data sets per phase must be less than 255. Severity: 8 (error) System action: Utility processing is terminated. Programmer response: No action is required. System programm response: Provide more disk space for sort work data sets. DSNU3342I csect-name NUMBER OF OPTIMAL SORT TASKS = mmm, NUMBER OF ACTIVE SORT TASKS = nnn Explanation: The first number indicates the optimal number of sort tasks for the utility execution. The second number shows the maximum number of sort tasks that was possible for the utility execution. Severity: 0 (informational) System action: Processing of the phase continues. Programmer response: No action is required. DSNU3343I csect-name REAL-TIME STATISTICS INFORMATION MISSING FOR obj-type obj-qual.obj-name partition-information Explanation: The utility could not read necessary information from real time statistics tables for the specified index or table space. Partitioned objects will include the partition number for which information is missing. Severity: 0 (informational) System action: The utility continues but will use estimates based on RUNSTATS information for this object. System programmer response: Make sure that real-time statistics are enabled on the system. Run the REORG or REBUILD INDEX utility on the specified object to set the base values in the real-time statistics tables. ž**** PE08/06/20 FIX IN ERROR. SEE APAR PK66597 FOR DESCRIPTION
APAR Information
APAR number
PK45916
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
810
Status
CLOSED UR1
PE
NoPE
HIPER
NoHIPER
Special Attention
YesSpecatt / New Function
Submitted date
2007-05-25
Closed date
2008-02-14
Last modified date
2008-07-03
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UK33692
Modules/Macros
DSN@ZP DSNDQWPZ DSNDRTS DSNDSPRM DSNDUA DSNDUCOM DSNDUDTY DSNDUJB DSNDUKIT DSNDURXP DSNDUSMI DSNDUSW DSNDUTR DSNFMDIR DSNFUDRD DSNGDBIP DSNIGRTS DSNIRTSX DSNTIDXA DSNTIJUZ DSNTINST DSNUCINM DSNUCRIB DSNUCSIN DSNUCSOR DSNUFCMI DSNUGALC DSNUGBPL DSNUGDDI DSNUGEPL DSNUGLSR DSNUGMST DSNUGPRT DSNUGPRV DSNUGSOR DSNUGSRP DSNUGSRT DSNUGSUP DSNUGTTI DSNUKINM DSNUKINT DSNUKPIK DSNUMSGD DSNURFIT DSNURILD DSNURORG DSNURPIB DSNURPSS DSNURPTB DSNURSIN DSNURSOR DSNURULD DSNUSDRA DSNUSDRB DSNUSITS DSNUSSIN DSNUSSIT DSNUSSOR DSNUZODR DSNWZP DSN6SPRM HDB8810J
Fix information
Fixed component name
DB2 OS/390 & Z/
Fixed component ID
5740XYR00
Applicable component levels
R810 PSY UK33692
UP08/03/03 P F802
Fix is available
Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"8.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SG19M","label":"APARs - z\/OS environment"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"8.1","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
03 July 2008