Grouping and joins with DB2^® Multisystem

 

If the query contains a join, the partitioning key used to determine the type of grouping that can be implemented is based on any repartitioning of data that was required to implement the join.

In the following example, a repartitioned join is performed before the grouping, which results in a new partitioning key of MGRNO. Because MGRNO is now the partitioning key, grouping can be performed using one-step grouping.

SQL statement:

       SELECT MGRNO, COUNT(*)
              FROM DEPARTMENT, EMPLOYEE               WHERE MGRNO = EMPNO               GROUP BY MGRNO  

OPNQRYF command:

       OPNQRYF FILE((DEPARTMENT) (EMPLOYEE)) FORMAT(GRPFMT2)
                   JFLD((MGRNO EMPNO *EQ))
                   GRPFLD(MGRNO)
                   MAPFLD((CNTMGR '%COUNT'))

In the following example, a repartitioned join is performed before the grouping, which results in a new partitioning key of EMPNO. Because EMPNO is now the partitioning key instead of WORKDEPT, grouping cannot be performed using one-step grouping.

SQL statement:

     SELECT WORKDEPT, COUNT(*)
              FROM DEPARTMENT, EMPLOYEE               WHERE MGRNO = EMPNO               GROUP BY WORKDEPT  

OPNQRYF command:

    OPNQRYF FILE((DEPARTMENT) (EMPLOYEE)) FORMAT(GRPFMT3)
                   JFLD((MGRNO EMPNO *EQ))
                   GRPFLD(WORKDEPT)
                   MAPFLD((CNTDEPT '%COUNT'))

 

Parent topic:

Implementation and optimization of grouping with DB2 Multisystem