|
|
DB2 und DFSORTExcerpt from IBM Informational APAR II14047 and II14213 (Dated August 2007). Additional text bases on lab results of Mr. Kingsbury, IBM Labor San Jose. The following DB2 Version 8 Utilities use DFSORT exclusively to perform their SORT and MERGE functions:
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. Configuration and tuning of the DB2 Version 8 and DFSORT options for improved performance and reliability: 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 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. a. DSA - Set default amount of main storage requested for each sort. b. DYNAUTO=IGNWKDD to allow dynamic allocation of work data sets even if JCL work datasets are present c. 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 Finally see Chapter “How DB2 Utilities use DFSORT” for important hints on optimum performance. 1. ALLOWING DB2 UTILITIES TO PROVIDE ACCURATE INFORMATION TO DFSORTEach 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 * 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). 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.ibm.com/servers/storage/support/software/sort/mvs/tuning/pdf/srtmltun.pdf 2. INSTALLING DFSORT IF IT IS NOT YOUR PRIMARY SORT PRODUCTIf 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. Installing the DFSORT SVCThe DFSORT SVC and the Syncsort SVC can coexist. The DFSORT installation default is to use SVC=(109). Alternately, the DFSORT 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: 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 new copy 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. The DFSORT SVC is called to write SMF type-16 records. The 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. If you receive an ABENDS16D-08 please refer to the technote "Setting up the DFSORT SVC" (S1002196). You can find more information on installing SVCs in "z/OS MVS Initialization and Tuning Guide". Making the DFSORT Libraries AvailableEven 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. To accomplish this, first determine how your primary sort product is made available, 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:
3. TAILORING DFSORT INSTALLATION DEFAULTS TO YOUR ENVIRONMENTDFSORT supports various options for four invocation environments (JCL, INV, TSO and TSOINV) and four time-of-day environments (TD1-4). With DFSORT as your primary sort product, you can customize the DFSORT options for these environments as appropriate. But when you have another primary sort product and are just using DFSORT for DB2 Utilities, the only environment you can change is the INV environment. DFSORT is shipped with default options for this environment that generally provide efficient operation. However, you may want to change some of the INV options to better match your own environment for running DB2 Utilities. In particular:
See "z/OS DFSORT Installation and Customization" and "z/OS DFSORT Tuning Guide" for more information. You can access these and the other DFSORT books on-line using the "Publications" link on the DFSORT website at: http://www.ibm.com/storage/dfsort To modify your DFSORT installation options, write a usermod to modify ICEMAC. The following instructions illustrate the process for changing the DSA value to 128 (megabytes) for use by DB2 Utilities. The sample library SICESAMP contains the following members:
Update the ICEMAC INV statement in the ICEAM2 module within the ICEOPREC sample job ++ SRCUPD (ICEAM2) DISTMOD (AICELIB) DISTLIB (AICESRCE) You can remove the ICEAM1, ICEAM3, ICEAM4 updates in the ICEOPREC sample job. Ensure the FMID is correct (HSM1G00 for DFSORT R14, or HSM1H00 for DFSORT V1R5). Also ensure you use the same SMP target zone and options you use for installing z/OS products. Then do the RECEIVE using the modified ICEOPREC sample job and the APPLY using the ICEOPAPP sample job. When you install a new release of z/OS, this usermod has to be RECEIVEd and APPLYed to the new release. 4. VERIFYING ACS ROUTINES DO NOT ALLOCATE DFSORT WORK DATA SETS TO VIOThe use of Virtual I/O (VIO) for DFSORT work data sets is NOT recommended. VIO=NO is DFSORT's shipped installation default and recommended setting for the VIO parameter. If your site's ACS routines allocate DFSORT work data sets to VIO, it is recommended that you change your ACS routines to avoid using VIO for SORTWK*, STATWK*, DATAWK*, DAnnWK*, STnnWK* and SWnnWK* data definitions. This is because DFSORT already performs storage hierarchy management (the efficient balancing of processor memory and disk storage), and mixing this with VIO which uses processor memory results in suboptimal efficiency. DFSORT exploits processor storage through the use of either Hiperspaces, Dataspaces or Memory Objects. Memory Object sorting will only be used if MEMLIMIT is non-zero. If MEMLIMIT is not set in your JCL then it defaults to the MEMLIMIT value specified in the SMFPRMxx member of SYS1.PARMLIB. If MEMLIMIT is not specified in SMFPRMxx then that default is zero. More information on the relationship between MEMLIMIT, REGION, IEFUSI and SMFPRMxx can be found in the z/OS MVS Programming: Extended Addressability Guide. DFSORT uses a primary allocation of 0 tracks for the initial allocation of work data sets. This can impact the decisions made by DFSMS Automatic Class Selection (ACS) routines if the &size variable is being used to assign a unit (such as VIO) or select a storage class and group based on data set size. It is recommended that the &maxsize variable be used as a basis for decisions related to data set size. Since DFSORT work data set allocations contain a secondary space quantity, the &maxsize variable will allow for a more accurate decision on the assignment of unit, storage class, storage group, etc. While DFSORT performs the initial work data set allocations with a primary space quantity of 0, it will attempt to extend them once the accurate amount of required space is determined. Should a work data set reside on a volume which does not have sufficient space available, DFSORT invokes recovery routines that may result in the data set being deleted and re-allocated. 5. EXCLUDING DFSORT WORK DATA SETS FROM OEM PRODUCT'S USEExclude SORTWK*, STATWK*, DATAWK*, DAnnWK*, STnnWK*, and SWnnWK* datasets from any OEM products that reduce space allocations. While running concurrent utilities, if the percent free of your work pool is low, you can either reduce the number of concurrently running utilities or you can increase the number of volumes in your work pool. If you increase the number of volumes, you can then monitor utilization and bring that number back down if it's consistently underutilized. As a general rule, your work pool should average over 20% free to allow for optimal allocation of DFSORT work data sets and reduce the risk of failures due to lack of work space (ICE083A, ICE046A, etc.) ServiceEven if you do not have a license for DFSORT, IBM will provide central service for DFSORT problems related to the use of DFSORT by DB2 Utilities. IBM already provides all DFSORT service (PTFs) to all z/OS customers. If a DFSORT defect is encountered, DFSORT service and any prerequisite PTFs will be required. You must be aware that you now "care" about DFSORT service and take appropriate actions. For example, IBM recommends that you specify DFSORT in the group of FMIDs that you want services. If you use Enhanced HOLDDATA, DFSORT HIPERs will show up in the REPORT ERRSYSMODS listing. If you've tailored notifications (like ASAP in ServiceLink) at the FMID level, you should add notifications for DFSORT HIPERs. If you have a any problem with DFSORT (e.g., DFSORT messages ICE039A, or ICE046A, or you receive an abend), you should open a PMR against DB2. If necessary, DB2 support will transfer the problem to DFSORT. If the problem can be reproduced, add SORTDIAG and DFSPARM DD statements to the failing job and rerun it: //SORTDIAG DD DUMMY This will allow DFSORT to display additional diagnostic (ICE8xxx and ICE9xxx) messages, and ABEND if a DFSORT error message is issued. Send the complete JOBLOG and dump (if an error occurs) to the appropriate IBM service representative. Recommended DFSORT service: ++APAR PK25047 - ICE046A can result when DFSORT uses multiple Hiperspaces and more than 2G of hiperspace is required to successfully complete the sort. How DB2 Utilities use DFSORTDB2 utilities have progressed greatly since the days when an entire suite of vendor replacement utilities was a "must-have" for any substantial DB2 implementation. Intelligent interfacing of the utilities with DFSORT has been a key component of this improvement. Similar general recommendations apply for DFSORT with large utility operation as with any other batch sort task; however, some special considerations warrant mentioning. REORG TABLESPACE UtilityThree parameters for this utility relate to DFSORT performance. SORTDATA When specified, this parameter directs the utility to perform unload via a tablespace or partition scan and batch sort to clustering index sequence – as opposed to unloading using the clustering index, the equivalent of a SQL SELECT * ... ORDER BY ... statement. Except where the underlying data clustering is very near to perfect, SORTDATA will provide a substantial - often radical - improvement in elapsed time. DB2 makes no decision to use SORTDATA dynamically, even if the CLUSTERRATIO column of the index in the DB2 catalog (SYSIBM.SYSINDEXES or SYSIBM.SYSINDEXSTATS) contains a low value. Note, however, that if a clustering index is not explicitly defined on the table – as opposed to implicitly, by having the lowest OBID - DB2 will ignore the parameter if specified, and alwaysunload via the implicit clustering index. NOSYSREC Ordinarily, REORG SORTDATA will unload the tablespace or partition to SYSREC, use DFSORT to perform a batch sort of this dataset, and reload the data by reading the sorted SYSREC. NOSYSREC eliminates the I/O to SYSREC, by passing data directly to DFSORT in the UNLOAD phase of the utility, and retrieving data directly from DFSORT in the RELOAD phase. One word of caution. If this parameter is used in a traditional (SHRLEVEL NONE) REORG execution, a SYSREC dataset is no longer available to re-start the utility should it fail during the RELOAD phase. The tablespace or partition must be RECOVERed - hence, a QUIESCE to establish a recovery point just prior to the REORG is mandatory. SHRLEVEL NONE is the default. SORTKEYS This parameter is specified without a key count estimate for REORG - this can be accurately determined by the utility itself. It directs REORG to pass non-clustering index key values directly from the RELOAD phase to a DFSORT subtask separate from that used for SORTDATA, rather than the traditional method of writing the key values to SYSUT1 for batch sorting into SORTOUT. The index BUILD phase then retrieves the sorted key values directly from DFSORT, eliminating all I/O to SYSUT1 and SORTOUT. SYSUT1 and SORTOUT must still be present in the utility JCL, but may have minimal space allocation. From Version 6 of DB2 on, this index building is accomplished by multiple concurrent sort tasks. Note that this parameter is only of benefit for indexes additional to the partitioning or (explicit or implicit) clustering index - the UNLOAD phase has already ordered the key values for these indexes, and an index SORT phase is not executed. Recommendations
LOAD UtilityThe LOAD utility has a SORTKEYS parameter similar to that for REORG. Again, the parameter requests LOAD to pass index key and foreign key values directly from the RELOAD phase to a DFSORT subtask - rather than using SYSUT1/SORTOUT - and then directly to the BUILD and ENFORCE phases. The following points are worth noting:
Recommendations When LOADing large partitioned tables with only a partitioning index:
For large segmented tablespaces with only a single index, the input file should be similarly pre-sorted.
DSNTIAUL UtilityDB2 (up to version 5) does not provide a standard "unload" utility; facilities are limited to sample program DSNTIAUL which simply executes dynamic SQL to produce a default-format (or user-specified) unload dataset. Note that with version 6, the REORG utility provides this facility via the FORMAT EXTERNAL option, but without the full power of SQL As a general rule, executing a table unload via a SQL SELECT ... ORDER BY has always been preferable to unloading via a tablespace scan and post-sorting the unload dataset. Two exceptions are notable, however:
One possible improvement is to:
Note that parallel unload of data need not be restricted to partitioned tablespaces; the unload of any substantial segmented tablespace may be divided in this manner. |
© Gernot Ruban