Directed join with DB2^® Multisystem

The join fields do not match the partitioning keys of the other files. Records of one file are directed to or sent to the nodes of the second file based on the hashing of the join field values using the partition map and node group of the second file. As soon as the records have been moved to the nodes of the second file through a temporary distributed file, a collocated join is used to join the data. This method is valid only for equijoin queries where all fields of the partitioning key are join fields for at least one of the files.

In the following query, join field (WORKDEPT) is the partitioning key for file EMPLOYEE; however, join field (ADMRDEPT) is not the partitioning key for DEPARTMENT. If the join was attempted without moving the data, result records would be missing because record 2 of DEPARTMENT should be joined to records 1 and 2 of EMPLOYEE and these records are stored on different nodes.

SQL statement:

   SELECT DEPTNAME, FIRSTNME, LASTNAME
              FROM DEPARTMENT, EMPLOYEE
              WHERE ADMRDEPT = WORKDEPT AND JOB = 'Manager'

OPNQRYF command:

   OPNQRYF FILE((DEPARTMENT) (EMPLOYEE))
                   FORMAT(JOINFMT)
                   QRYSLT('JOB *EQ 'Manager')
                   JFLD((ADMRDEPT WORKDEPT *EQ))

The records of DEPARTMENT that are needed to run the query are read, and the data in ADMRDEPT is hashed using the partitioning map and the node group of EMPLOYEE. A temporary file is created and looks like this:

Table 1. A temporary table Old node New node DEPTNAME ADMRDEPT (New partitioning key) SYSA SYSA Support services A00 SYSB SYSA Planning A00 SYSC SYSC Accounting B00 SYSA SYSC Programming B00

This temporary table is joined to EMPLOYEE. The join works because ADMRDEPT is partition compatible with WORKDEPT.

Table 2. EMPLOYEE joined table DEPTNAME FIRSTNME LASTNAME Support services Christine Haas Planning Christine Haas Accounting Michael Thompson Programming Michael Thompson