SORT and SORT2 statements and parameters

The SORT and SORT2 statements specify primary and secondary sort keys for ordering group data in all reports that are produced by subsequent REPORT statements in the same job stream.

The SORT statement overrides the primary sort key that is already defined in the report. The SORT2 statement overrides the secondary sort key that is already defined in the report.

Note

The SORT and SORT2 statements do not override variables in the fixed section of a report.

Example

If the Db2 subsystem ID is in the fixed section of the report and you specify SORT(PLAN), the report is ordered first by Db2 subsystem ID and is sorted by plan within each Db2 subsystem ID.

When used as parameters of the REPORT statement, the rearrangement of data relates only to the reports that are specified in that REPORT statement. It is comparable to using the SORT command in a report online. The SORT and SORT2 parameters override the default ordering defined in the reports and any SORT or SORT2 statement that was previously specified.

Syntax

The following figures show the syntax of the SORT and SORT2 statements and parameters.

Statements and parameters

The following table displays the values for the SORT and SORT2 statements and parameters.

Keyword	Description	Value	Description of value
SORT SORT2	Indicates the rearranged sort order for reports The sort keys can be a qualifier type, F6 (percent of CPU utilization), or a measure or formula name. One of these values is required. If you specify SORT without SORT2, the primary sort key is taken from the SORT specification, and the secondary sort key is taken from the report definition. If you specify SORT2 without SORT, the primary sort key is taken from the report definition and the secondary sort key is taken from the SORT2 specification. Valid abbreviation (SORT): S Valid abbreviation (SORT2): S2	type	Qualifier type to be used as the primary or secondary sort key for ordering the report The following qualifier types are valid: PLAN (plan name) Valid abbreviation: P OPERATOR (original operator ID) Valid abbreviations: OPER, OPID, O Db2 (Db2 subsystem ID) Valid abbreviation: U CONNECTION (connection ID) Valid abbreviations: CONN, N¹ DBRM or PACKAGE (database request module or package name) Valid abbreviation: G¹ PCST# (Precompile statement number) Valid abbreviations: PC, STMT SMFID (SMF ID) Valid abbreviation: SID COLLECTION (collection ID) Valid abbreviations: COLL, W
		F6	Specifies that the report should be sorted in order of the percent of CPU usage.
		Mnnn	Specifies the name of the measure, the value of which is to be used as the primary or secondary sort key for ordering the reports
		Fnnnn	Specifies the name of the formula, the value of which is to be used as the primary or secondary sort key for ordering the report
A	Specifies that the values for the primary or secondary sort key will be reported in ascending order A is the default.	Not applicable	Not applicable
D	Specifies that the values for the primary or secondary sort key will be reported in descending order	Not applicable	Not applicable

^{1 If you are producing a report with SAP data, specify CONN (connection ID) in your SORT or SORT2 statement or parameter to report work process numbers and specify G (DBRM or package) to report servers.}

Sort values for batch reporting

The following table lists all reports that can be run in batch. For those reports that can be run only in batch, the sort values that are listed are the suggested values for optimum results. For the other reports, the values that are listed are the default values used when running the reports online. Use these values to produce batch reports that are identical to the default online reports.

Valid formulas for batch SQL Statement Analysis report

The Batch SQL Statement Analysis report (SQMBSTMT) is the most detailed of the batch reports. The following table lists the measures and formulas that can be used as sort fields for this report.

Formula	Description
F307, D	Percentage of elapsed time, descending (default)
F6	Percentage of CPU utilization
M305	Total number of SQL calls for the statement
M303	Total Db2 elapsed time
F23	Average Db2 elapsed time per SQL call
F301	Total Db2 CPU time
F24	Average Db2 CPU time per SQL call
M304	Total GETPAGE requests
F327	Average number of synchronous read I/Os per SQL call
M319	Total number of synchronous read I/Os
F308	Average GETPAGE requests per SQL call
M307	Total synchronous read I/O wait time
F318	Average elapsed time per synchronous read I/O
F315	Total number of asynchronous I/O events
F328	Average number of asynchronous I/O events per SQL call
F310	Total asynchronous wait time
F319	Average wait time per asynchronous I/O event
F322	Percentage of total IN-SQL time spent waiting for locks

Example

The REPORT statement produces a Batch SQL Statement Analysis report (SQMBSTMT). The data is sorted by percentage of CPU utilization, in descending order.

REPORT(NAME(SQMBSTMT)
SORT (F6,D))