II14047: USE OF DFSORT BY DB2 UTILITIES
Closed as canceled.
***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.
This info apar is being closed so it is searchable by customers.
Reported component name
V2 LIB INFO ITE
Reported component ID
NoSpecatt / Xsystem
Last modified date
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
Applicable component levels
More support for:
Software version: 001
Operating system(s): MVS, OS/390, z/OS
Reference #: II14047
Modified date: 17 August 2009