One-step grouping with DB2^® Multisystem

 

If all the fields from the partitioning key are GROUP BY fields, then grouping can be performed using one-step grouping, because all of the data for the group is on the same node.

The following code is an example of one-step grouping.

SQL statement:

      SELECT WORKDEPT, AVG(SALARY)
             FROM EMPLOYEE              GROUP BY WORKDEPT  

OPNQRYF command:

     OPNQRYF FILE((EMPLOYEE)) FORMAT(GRPFMT)
                   GRPFLD(WORKDEPT)
                   MAPFLD((AVGSAL '%AVG(SALARY)'))

Because WORKDEPT is both the partitioning key and the grouping field, all like values of WORKDEPT are on the same nodes; for example, all values of A00 are on SYSA, all values of A01 are on SYSB, all values of B00 are on SYSC, and all values of B01 are on SYSA. The grouping is performed in parallel on all three nodes.

To implement one-step grouping, all of the fields of the partitioning key must be grouping fields. Additional nonpartitioning key fields can also be grouping fields.

 

Parent topic:

Implementation and optimization of grouping with DB2 Multisystem