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

 

Parent topic:

Implementation and optimization of join operations with DB2 Multisystem