IBM Support

PK41899: REMOVE THE NEED TO SPECIFY SORTNUM ON UTILITY STATEMENTS FOR DYNAMICALLY ALLOCATED SORT WORK DATA SETS

A fix is available

Subscribe

You can track all active APARs for this component.

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 V910 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 as follows:
    
    (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.SYSTABLESPACESTATS and
        SYSIBM.SYSINDEXSPACESTATS. If 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.SYSTABLESPACESTATS to NULL,
        for indexes set the column TOTALENTRIES in
        SYSIBM.SYSINDEXSPACESTATS 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 PK47212 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: Run the REORG or REBUILD INDEX
                     utility on the specified object to set the base
                     values in the real-time statistics tables.
    

APAR Information

  • APAR number

    PK41899

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    910

  • Status

    CLOSED UR1

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    YesSpecatt / New Function

  • Submitted date

    2007-03-23

  • Closed date

    2008-02-12

  • Last modified date

    2008-03-03

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

    UK33636

Modules/Macros

  • DSN@ZP   DSNCIM   DSNCIMX  DSNDQWPZ DSNDRTS
    DSNDSPRM DSNDUA   DSNDUCOM DSNDUDTY DSNDUJB  DSNDUKIT DSNDURXP
    DSNDUSMI DSNDUSW  DSNDUTR  DSNFMDIR DSNFUDRE DSNGDBIP DSNIGRTS
    DSNIRTSX DSNTIDXA DSNTIJUZ DSNTINST DSNTIP6  DSNUCINM DSNUCINT
    DSNUCRIB DSNUCRUL DSNUCSIN DSNUCSOR DSNUFCMI DSNUGALC DSNUGBPL
    DSNUGDDI DSNUGEPL DSNUGLSR DSNUGMST DSNUGPRT DSNUGPRV DSNUGSOR
    DSNUGSRP DSNUGSRT DSNUGSUP DSNUGTTI DSNUKINM DSNUKINT DSNUKIUL
    DSNUKPIK DSNUMSGE DSNURFIT DSNURILD DSNURKAC DSNURPCT DSNURPIB
    DSNURPRD DSNURPSS DSNURPTB DSNURSIN DSNURSOR DSNUSDRA DSNUSDRB
    DSNUSITS DSNUSSIN DSNUSSIT DSNUSSOR DSNUZODR DSNWZP   DSN6SPRM
    HDB9910J
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • R910 PSY UK33636

       UP08/02/28 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.



Document information

More support for: DB2 for z/OS

Software version: 9.1

Reference #: PK41899

Modified date: 03 March 2008


Translate this page: