Improving Efficiency With External Sorts

Your request requires a matrix (cannot be converted to a TABLEF request). If your request needs a matrix and uses external sort, it will go through two sorts, both external and internal, and it will be hard to realize any performance gains.
To tell if your report is convertible to TABLEF, use ? STAT (as described in How to Query the Sort Type) or run an abbreviated version of the request with a low record limit and external sort on. If the report statistics are printed after the TABLE output, it was performed as TABLEF; if the statistics are printed before the first screen of TABLE output, it went through TABLE processing because it was not convertible to TABLEF.
Your input is sorted or almost sorted.
Your system cannot support a large number of work files (for information, see Sort Work Files and Return Codes). In this case the internal sort may do a better job since internally it implements about 60 logical work files, all sharing space in FOCSORT.

You can turn the external sorting feature on and off using the SET EXTSORT command.

SET EXTSORT = {ON|OFF}

where:

ON: Enables the selective use of a dedicated external sorting product to sort reports. This value is the default in all Mainframe environments.
OFF: Uses the internal sorting procedure to sort all reports. This value is the default in all non-Mainframe environments.

x

Syntax: How to Query the Sort Type

To determine which sort is being used for a given report, issue the following command after the report request:

? STAT

The command displays the following values for the SORT USED parameter:

FOCUS: The internal sorting procedure was used to sort the entire report.
SQL: You are using a relational data source and the RDBMS supplied data already in order.
EXTERNAL: An external sorting product sorted the report.
NONE: The report did not require sorting.

x

Syntax: How to Provide an Estimate of Input Records or Report Size for Sorting

ON TABLE SET ESTRECORDS nON TABLE SET ESTLINES n

where:

n: Is the estimated number of records or lines to be sorted.

x

Reference: Sort Work Files on UNIX, Windows, and OpenVMS

While internal sorting uses only one work file, FOCSORT (allocated in the EDATEMP directory), external sort allows up to 31 work files, allocated on one or more disk drives (spindles) or directories. (Warning: Any one or more of these work files may become very large. Count on using many times the total disk space required by FOCSORT.)

By default, five work files are allocated in the /tmp directory on UNIX, or in the directory pointed to by the TMP environment variable in Windows. This may not be enough sort work space and, even if the files fit in the directory, five files are probably not enough for optimal performance. Also, having all of the sort work files on the same disk may further degrade performance.

You have two other options:

Define the TMPDIR shell variable (UNIX) or TMP environment variable (Windows) to point to some suitable writable directory. For best results, this directory should be on a disk with a lot of available space, and not the same disk as the data source or the EDATEMP directory. Again, you will get five temporary work files allocated on the same spindle, with consequent performance degradation.
Define 1 to 31 shell variables of the form IBITMPDIR01 ... IBITMPDIR31 to point to one or more writable directories.
If the UNIX TMPDIR or Windows TMP variable is set, it must be "unset" in order to make use of the IBITMPDIRnn variables. The UNIX command for unsetting the TMPDIR variable is:
```
unset TMPDIR
```
The Windows command for unsetting the TMP variable is:
```
SET TMP=
```
Different variables may point to the same directory, if desired. If you wish to allocate n work files, you must define variables 01 through n. The first variable missing from the environment determines the number of work files that will be used. (If you define fewer than five, additional files will be allocated using the system default location to make up the difference.) The more work files you allocate, and the more separated they are across different spindles, the better performance you should achieve. The major constraint is the total disk space available.

The work file names are generated by the ANSI tempnam function, however, the names all begin with the characters srtwk. If the sorting process ends normally or terminates because of a detectable error (typically, disk space overflow), all of the allocated work files are deleted. There is no explicit way to save them. If there is another type of abnormal termination, srtwk files may be left on the disk. You can and should erase them.

Top of page

x

Procedure: How to Trace Sort Processing

When an external sort problem occurs, one of the following messages is generated:

(FOC909)  CRITICAL ERROR IN EXTERNAL SORT.  RETURN CODE IS: xxxx
(FOC1810) External sort not found
(FOC1899) Load of %1 (external-sort module) under %2 failed

In response to these messages, as well as for any other problem with sorting, it is useful to trace sort processing. For information on diagnosing external sort problems, see Diagnosing External Sort Errors.

Allocate DDNAME FSTRACE to the terminal or a file. The following example sends trace output to the terminal:
```
//FSTRACE  DD  SYSOUT=*,DCB=(RECFM=FA,LRECL=133,BLKSIZE=133)
```
Activate the trace by adding the following commands in any supported profile or a FOCEXEC:
```
SET TRACEUSER = ON
SET TRACEON  = SORT/1/FSTRACE
```

Top of page

x

Reference: External Sort Messages and Return Codes

When you receive a FOC909 message, it includes a return code:

(FOC909)  CRITICAL ERROR IN EXTERNAL SORT.  RETURN CODE IS: xxxx

You may also receive one of the following messages:

(FOC1810) External sort not found
(FOC1899) Load of %1 (external-sort module) under %2 failed

The following notes apply when this message or a FOC1800 or FOC1899 message is generated by a TABLE request:

The most common value for xxxx is 16. However, return code 16 is issued for a number of problems, including but not limited to the following:
- Syntax errors.
- Memory shortage.
- I/O errors (depending on installation options).
- Space problems with output.
- Space problems with work files.
In order to diagnose the error, you must generate external sort messages (using the instructions in How to Select a Sort Utility and Message Options and How to Trace Sort Processing) and then reproduce the failure.
For return codes not described below, follow the same procedure described for return code 16.
Return code 20 is issued by DFSORT under z/OS if messages were requested (using the MVSMSGDJ option of the SET SORTLIB command), but the SYSOUT DD card is missing. DFSORT terminates after issuing the return code. Under the same conditions, SyncSort attempts to open SYSOUT, producing the following message, and then continues with messages written to the operator or terminal:
```
IEC130I SYSOUT DD STATEMENT MISSING.
```
Return code 36 or a FOC1899 message under z/OS means that the external sort module could not be found; check the STEPLIBs allocated.

When REBUILD INDEX invokes an external sort that fails, it generates a message similar to the following:

ERROR OCCURRED IN THE SORT yyyyyyyyzzzzzzzz

In this case, the return code is yyyyyyyy and it is expressed in hex. The final eight digits (zzzzzzzz) should be ignored.

Translate the return code into decimal and follow the instructions for return codes in a TABLE request.

Note also that when a TABLE request generates a non-zero return code from an external sort, FOCUS is terminated. By contrast, when REBUILD INDEX gets a non-zero return code from an external sort, the REBUILD command is terminated but FOCUS continues.

Top of page

Before following these instructions, make sure that external sort messages were generated (for information, see How to Select a Sort Utility and Message Options) and that they clearly show that the reason for failure was inadequate sort work space.

Make an estimate of the number of lines of output the request will produce.
Set the ESTLINES parameter in the request or FOCEXEC. For information, see Providing an Estimate of Input Records or Report Size for Sorting.
WebFOCUS will pass this estimate to the external sort utility through the parameter list.

Do not override the DD cards for SORTWKnn, S001WKnn, DFSPARM, or $ORTPARM without direct instructions from technical support. The instructions in How to Select a Sort Utility and Message Options, How to Trace Sort Processing, and Providing an Estimate of Input Records or Report Size for Sorting should provide equivalent capabilities.

x

Syntax: How to Use Aggregation in Your External Sort

SET EXTAGGR = aggropt

where:

aggropt

Can be one of the following:

OFF disallows aggregation by an external sort.

NOFLOAT allows aggregation if there are no floating point data fields present.

ON allows aggregation by an external sort. This value is the default.

Top of page

You must use SyncSort or DFSORT.

Your query should be simple (that is, it should be able to take advantage of the TABLEF facility). For related information, see Data Retrieval Using TABLEF.
The PRINT display command may not be used in the query.
SET ALL must be equal to OFF.
Only the following column prefixes are allowed: SUM, AVG, CNT, FST.
Columns can be calculated values or have a row total.
When SET EXTAGGR = NOFLOAT and your query aggregates numeric data, the external sort is not called, and aggregation is performed through the internal sorting procedure.

Top of page

Example: Changing Output by Using an External Sort for Aggregation

If you use SUM on an alphanumeric field in your report request without using an external sort, the last instance of the sorted fields is displayed in the output. Turning on aggregation in the external sort displays the first record instead. However, you can control the order of display using the SUMPREFIX parameter. With SUMPREFIX = LST (the default), the last instance displays even with EXTAGGR = ON.

The following command turns aggregation ON and leaves SUMPREFIX set to LST (the default) and, therefore, displays the last record:

SET EXTAGGR = ON
SET SUMPREFIX = LST
TABLE FILE CAR
SUM CAR BY COUNTRY
END

The output is:

COUNTRY     CAR     
-------     ---     
ENGLAND     TRIUMPH 
FRANCE      PEUGEOT 
ITALY       MASERATI
JAPAN       TOYOTA  
W GERMANY   BMW

Note: SUMPREFIX is described in Changing Retrieval Order With Aggregation (Mainframe Environments Only) .

With SUMPREFIX = FST, the output is:

COUNTRY     CAR       
-------     ---       
ENGLAND     JAGUAR    
FRANCE      PEUGEOT   
ITALY       ALFA ROMEO
JAPAN       DATSUN    
W GERMANY   AUDI

x

Syntax: How to Set Retrieval Order

SET SUMPREFIX = {LST|FST}

where:

LST: Displays the last value when alphanumeric or smart date data types are aggregated. This value is the default.
FST: Displays the first value when alphanumeric or smart date data types are aggregated.

x

Syntax: How to Create HOLD Files With an External Sort

SET EXTHOLD = {OFF|ON}

where:

OFF: Disables HOLD files by an external sort.
ON: Enables HOLD files by an external sort. This value is the default.

Top of page