IBM Support

II14047: USE OF DFSORT BY DB2 UTILITIES

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as canceled.

Error description

  • ***Note: This APAR is periodically updated***
    .
    The following DB2 Version 8 Utilities use DFSORT exclusively
    to perform their SORT and MERGE functions:
      - LOAD
      - REORG TABLESPACE and REORG INDEX
      - REBUILD INDEX
      - RUNSTATS
      - CHECK DATA, CHECK INDEX, and CHECK LOB
    .
    IBM's DB2 and DFSORT Teams developed the interface (DFSORT R14
    PTF UQ90054 which was incorporated into the base for z/OS DFSORT
    V1R5) between the two products in such a way that DB2 Utilities
    can use DFSORT regardless of whether or not you have a license
    for DFSORT on your system.  For considerations in a 64-bit
    environment, refer to DFSORT INFO APAR II13495.
    .
    The information in this document is intended to assist in
    configuration and tuning of the DB2 Version 8 and DFSORT options
    for improved performance and reliability.  Some of the topics
    discussed include:
    .
    1. Allowing DB2 utilities to provide accurate information to
       DFSORT
    a. Keep RUNSTATS statistics updated
    b. Use SORTNUM to override DFSORT defaults
    c. Provide key count on the SORTKEYS parameter when LOAD input
       is not a sequential file on disk or is a file with varying
       length records
    d. Understand how parallel processing can be impacted when
       SORTNUM is too high
    2. Installing DFSORT if it is not your primary sort product
    3. Tailoring DFSORT installation defaults to your environment
    a. DYNALOC - Set default number of work data sets and device
       type. Consider increasing from default number of 4.
    b. DSA - Set default amount of main storage requested for each
       sort.
    c. DYNAUTO=IGNWKDD to allow dynamic allocation of work data sets
       even if JCL work datasets are present
    d. SMF=SHORT or SMF=FULL to allow recording of DFSORT data to
       aid in analysis
    4. Verifying ACS routines will not direct sort work datasets to
       VIO
    a. Use &MAXSIZE instead of &SIZE to determine size of allocation
    b. Exclude SORTWK*, STATWK*, DATAWK*, DAnnWK*,
       STnnWK* and SWnnWK* datasets from VIO
    5. Exclude SORTWK*, STATWK*, DATAWK*, DAnnWK*, STnnWK*
       and SWnnWK* datasets from any OEM products that
       reduce space allocations.
    6. Region size of the batch DB2 Utility job.
    7. Limiting parallel DFSORT tasks for DB2 Utilities.
    .
    DETAILS
    .
    1. ALLOWING DB2 UTILITIES TO PROVIDE ACCURATE INFORMATION TO
       DFSORT
    .
    Each DB2 Utility determines how to invoke DFSORT based on
    various factors and passes the needed information to DFSORT
    using a special interface.  Multiple parallel invocations of
    DFSORT will be used when appropriate.
    .
    Recommendation:  Keep the RUNSTATS statistics updated
    (specifically, CARDF, KEYCOUNTF, AVGROWLEN ).  These statistics
    are used to project the size of the sort for REORG and REBUILD
    INDEX.  RUNSTATS statistics are not used for projecting the size
    of the sort for the LOAD utility.  If the input data for the
    LOAD utility is a sequential file on disk, then the size of this
    file will be used for the sort calculations.  If the input is
    not a sequential file on disk (i.e., it is on tape, a member of
    a PDS(E), or it is a cursor) then it is vital that a value be
    provided on the SORTKEYS parameter.  See "Building indexes in
    parallel for LOAD" in the "Utility Guide and Reference" manual
    for calculating this value.
    .
    The most important parameters DB2 Utilities passes to DFSORT are
    related to storage usage and the number of work data sets.  If
    appropriate, you can use the DB2 SORTNUM parameter to change the
    number of work data sets for each sort (e.g., SORTNUM=8
    specifies 8 work data sets per sort).  But you cannot use
    DFSORT's DFSPARM data set to change the MAINSIZE value (storage)
    or DYNALLOC n value (number of work data sets) used by DB2
    Utilities at run-time because DB2 Utilities will not be aware of
    the changes in these values.
    .
    You need at least two sort work data sets for each sort. The
    SORTNUM value applies to each sort invocation in the utility.
    For example, if there are three indexes, SORTKEYS is specified,
    there are no constraints limiting parallelism, and SORTNUM is
    specified as 8, then a total of 24 sort work data sets will be
    allocated for the job.
    .
    Each sort work data set consumes both above the line and below
    the line virtual storage, so if you specify too high a value for
    SORTNUM, the utility may decrease the degree of parallelism due
    to virtual storage constraints, and possibly decreasing the
    degree down to one, meaning no parallelism.  To determine the
    size of the sort datasets, search for "Calculating the size of
    the sort work data sets" under each utility that performs sorts
    in the "DB2 Utility Guide and Reference."
    .
    You can override other DFSORT parameters, such as the device
    type of the work data sets or the ddname of DFSORT's message
    data set, using the DFSPARM data set, e.g.,
    //DFSPARM DD *
      OPTION DYNALLOC=WORK,MSGDDN=SRTMSGS
    .
    Note that you can also use the DB2 SORTDEVT parameter to change
    the device type of the work data sets (e.g. SORTDEVT=WORK
    specifies WORK as the device type).
    .
    Note: DFSORT APAR PK01155 (R14 DFSORT PTF UK03170 or z/OS
    DFSORT V1R5 PTF UK03171) uses the MAINSIZE and DYNALLOC n values
    passed by DB2 Utilities even if the user attempts to override
    them from other sources (e.g., DFSPARM).
    .
    SICELINK and SORTLPA libraries must be APF-authorized.  Use the
    PROGxx parmlib member to authorize these libraries.  For more
    information, see "z/OS Initialization and Tuning Reference".
    See the following for some information on tuning DFSORT for DB2
    Utilities:
    http://www-03.ibm.com/servers/storage/support/software/
    sort/mvs/tuning/pdf/srtmltun.pdf
    .
    2. INSTALLING DFSORT IF IT IS NOT YOUR PRIMARY SORT PRODUCT
    .
    If DFSORT is installed as your primary z/OS sort product, no
    additional actions are required to make DFSORT available to DB2
    Utilities and the rest of this document does not apply to you.
    If DFSORT is not installed as your primary z/OS sort product,
    the rest of this document applies to you.
    .
    If you are not licensed to use DFSORT, do not define DFSORT as a
    licensed feature in SYS1.PARMLIB member IFAPRDxx.  DB2 Utilities
    can use DFSORT regardless of whether or not you have a license
    for DFSORT on your system.
    .
    Note that DB2 Utilities uses only the SORT and MERGE functions
    of DFSORT.  If you want to use DFSORT for any other uses outside
    of this limited DB2 support, you must separately order and
    license DFSORT.  Use of DFSORT by DB2 Utilities does not
    interfere with the use of another sort product for other
    purposes.
    .
    You do not need to change any aliases for DFSORT or for your
    primary sort product.  DB2 Utilities calls DFSORT using the new
    DFSORT-only aliases of ICEDFSRT and ICEDFSRB instead of ICEMAN
    and ICEBLDX.
    .
    DFSORT'S SVC IS USED TO WRITE SMF RECORDS
    -----------------------------------------
    The DFSORT SVC is called to write SMF type-16 records.
    correct DFSORT SVC for this release must be loaded in LPA
    or MLPA to write either SHORT or FULL SMF type-16 records.
    The DFSORT SVC is not required for the DFSORT installation
    default of SMF=NO. SORT installation default of SMF=NO.
    .
    If you receive an ABENDS16D-08 please refer to the technote
    "Setting up the DFSORT SVC" (S1002196).
    .
    Cache Fast Write (CFW)
    ----------------------
    If you would like to use Cache Fast Write, the SVC is required.
    .
    Installing the DFSORT SVC
    -------------------------
    .
    If you require DFSORT's SVC, it will need to be installed.
    Also, if necessary, it is possible for the DFSORT SVC and
    the Syncsort SVC to coexist.
    .
    The DFSORT installation default is to use SVC=(109). If
    you choose to use the installation default, you need to
    load (add SICELPA) the SVC into LPA or MLPA.
    .
    Alternately, the installation default for the SVC can be
    changed to use:
     - an alternate SVC 109 (IGX00038), or
     - to use an user SVC (e.g., specified as SVC=(109,ALT)
       or SVC=239).
    .
    If you choose to use the alternate SVC or a user SVC, you
    will need to do the following and load (add SICELPA) the
    SVC into LPA or MLPA.
    .
    a. Create a copy of SVC 109 using the sample jobs ICESVREC
       and ICESVAPP. Ensure the FMID is correct (HSM1G00 for
       DFSORT R14, or HSM1H00 for DFSORT V1R5). Change ICESVC
       to the new module name (for example, IGX00038 for the
       alternate SVC or IGC0023I for SVC 239). Use the same
       SMP target zone and options you use for installing
       z/OS products.
    b. Make the new copy of the SVC available on your system
       by placing the updated SICELPA library where you have
       the SVCs on your system.
    c. Change the DFSORT SVC installation default value to use
       your selected SVC number. Instructions for modifying your
       DFSORT installation default values are given below, under
       Customization. For example, use SVC=(109,ALT) for the
       alternate SVC or SVC=239 for SVC 239.
    .
    You can find more information on installing SVCs in "z/OS
    MVS Initialization and Tuning Guide".
    
    .
    Making the DFSORT Libraries Available
    -------------------------------------
    Even though DFSORT is not licensed, the DFSORT libraries are
    shipped with z/OS.  When you install z/OS, you also install the
    DFSORT libraries.  DFSORT should go into the same target and
    distribution zones as z/OS.  However, to make DFSORT available
    for DB2's use, you must make these libraries part of the
    system's search order for programs and available to all LPARs
    where DB2 calls DFSORT.
    .
    Where you choose to place DFSORT in the system's search order
    really depends on how heavily you use DB2 Utilities and your
    interest in optimizing central storage usage and access time.
    If DB2 Utilities are heavily used, DFSORT will be called many
    times on a system, so placing DFSORT SORTLPA into LPA and DFSORT
    SICELINK into the LINKLIST is recommended for maximum
    efficiency.
    .
    To enable DB2 Utilities to use DFSORT, and avoid causing
    problems with the operation of your primary sort product, you
    must ensure DFSORT will be found in the system search order
    after your primary sort product. It is important to determine
    where your primary sort product modules (SORT, ICEMAN and
    ICEBLDX) are located in your search order.  If these
    primary sort product modules are in your link pack area, then
    DFSORT's SORTLPA can also be added to the link pack area list
    after your primary sort product library(s).
    If your primary sort product modules SORT, ICEMAN and ICEBLDX
    are in the link list, then both DFSORT's SICELINK and SORTLPA
    libraries (in that order) must be added to the link list
    after your primary sort product library(s) or found by way of
    STEPLIB/JOBLIB.
    .
    To accomplish this, first determine how your primary sort
    product is made available (specifically modules SORT, ICEMAN and
    ICEBLDX), for example, in the link pack area,
    in the link list, or in JOBLIB/STEPLIB.  Then select an
    appropriate method, as described in the bullets below, that
    puts DFSORT in the system search order after your primary sort
    product.
    .
    For example, if your primary sort product libraries are in the
    link pack area and in the link list, you could place your DFSORT
    SORTLPA library in the link pack area after your primary sort
    product library in the link pack area, and place your DFSORT
    SICELINK library in the link list after your primary sort
    product library in the link list.  Alternatively, you could
    place your DFSORT SICELINK library and your DFSORT SORTLPA
    library (in that order) in the link list after your primary sort
    product library in the link list.  As appropriate, do one or
    more of the following:
     - Add the DFSORT SORTLPA library to the link pack area list and
       add the DFSORT SICELINK library to the link list
     - Add the DFSORT SICELINK library and then the SORTLPA library,
       in that order, to the link list (the order is important).
     - Add the DFSORT SICELINK library and then the SORTLPA library,
       in that order (the order is important), to the JOBLIB DD
       statements you use for your DB2 Utility jobs.
     - Add the DFSORT SICELINK library and then the SORTLPA library,
       in that order (the order is important), to the STEPLIB DD
       statements you use for your DB2 Utility jobs.
     - Add the DFSORT modules to a private library that is
       equivalent to one of the above configurations.  In this case,
       ensure for the above JOBLIB and STEPLIB configurations, that
       all of the DFSORT SICELINK modules are before the DFSORT
       SORTLPA modules in the system search order.
       Important:  If your primary sort product is run from a
       private library, you must use a JOBLIB or STEPLIB for your
       DB2 Utility jobs.
    .
    THIS APAR IS CONTINUED IN II14213. PLEASE REVIEW APAR II14213
    FOR ADDITIONAL INFORMATION REGARDING THE USE OF DFSORT BY
    DB2 UTILITIES.
    

Local fix

Problem summary

Problem conclusion

Temporary fix

Comments

  • This info apar is being closed so it is searchable
    by customers.
    

APAR Information

  • APAR number

    II14047

  • Reported component name

    V2 LIB INFO ITE

  • Reported component ID

    INFOV2LIB

  • Reported release

    001

  • Status

    CLOSED CAN

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2005-06-06

  • Closed date

    2006-08-31

  • Last modified date

    2009-08-17

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

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

Fix information

Applicable component levels



Document information

More support for: z/OS family

Software version: 001

Operating system(s): MVS, OS/390, z/OS

Reference #: II14047

Modified date: 17 August 2009