Repartitioned join with DB2® Multisystem

In a repartitioned join, the partitioning keys of the files are not used as the join fields.

Records of both files must be moved by hashing the join field values of each of the files. Because neither of the files' partitioning key fields are included in the join criteria, the files must be repartitioned by hashing on a new partitioning key that includes one or more of the join fields. This method is valid only for equijoin queries.

SQL statement:

    SELECT DEPTNAME, FIRSTNME, LASTNAME
              FROM DEPARTMENT, EMPLOYEE
              WHERE MGRNO = EMPNO

OPNQRYF command:

   OPNQRYF FILE((DEPARTMENT) (EMPLOYEE))
                   FORMAT(JOINFMT)
                   JFLD((MGRNO EMPNO *EQ))

In this example, the data must be redistributed because neither MGRNO nor EMPNO is a partitioning key.

Data from DEPARTMENT is redistributed:

Table 1. Redistributed DEPARTMENT data
Old node	New node	DEPTNAME	MGRNO (New partitioning key)
SYSA	SYSB	Support services	000010
SYSB	SYSB	Planning	000010
SYSC	SYSC	Accounting	000050
SYSA	SYSC	Programming	000050

Data from EMPLOYEE is redistributed:

Table 2. Redistributed EMPLOYEE data
Old node	New node	FIRSTNME	LASTNAME	EMPNO (New partitioning key)
SYSA	SYSB	Christine	Haas	000010
SYSA	SYSC	Sally	Kwan	000020
SYSB	SYSA	John	Geyer	000030
SYSB	SYSB	Irving	Stern	000040
SYSC	SYSC	Michael	Thompson	000050
SYSC	SYSA	Eileen	Henderson	000060
SYSA	SYSB	Jennifer	Lutz	000070
SYSA	SYSC	David	White	000080

Records returned by this query:

Table 3. Display of the query results
DEPTNAME	FIRSTNME	LASTNAME
Support services	Christine	Haas
Planning	Christine	Haas
Accounting	Michael	Thompson
Programming	Michael	Thompson