Implementation and optimization of record ordering with DB2^® Multisystem

 

When ordering is specified on a query, the ordering criteria is sent along with the query, so that each node can perform the ordering in parallel. Whether a final merge or a sort is performed on the coordinator node is dependent on the type of query that you specify.

A merge of the ordered records received from each node is the most optimal. A merge occurs as the records are received by the coordinator node. The main performance advantage that a merge has over a sort is that the records can be returned without having to sort all of the records from every node.

A sort of the ordered records received from each node causes all of the records from each node to be read, sorted, and written to a temporary result file before any records are returned.

A merge can occur if ordering is specified and no UNION and no final grouping are required. Otherwise, for ordering queries, a final sort is performed on the coordinator node.

The allow copy data (ALWCPYDTA) parameter affects how each node of the distributed query processes the ordering criteria. The ALWCPYDTA parameter is specified on the Open Query File (OPNQRYF) and Start SQL (STRSQL) CL commands and also on the Create SQLxxx (CRTSQLxxx) precompiler commands:

• ALWCPYDTA(*OPTIMIZE) allows each node to choose to use either a sort or an index to implement the ordering criteria. This option is the most optimal.

• For the OPNQRYF command and the query API (QQQQRY), ALWCPYDTA(*YES) or ALWCPYDTA(*NO) enforces that each node use an index that exactly matches the specified ordering fields. This is more restrictive than how the optimizer processes ordering for local files.

 

Parent topic:

Query design for performance with DB2 Multisystem