Broadcast join with DB2^® Multisystem

 

In a broadcast join, all of the selected records of one file are sent or broadcast to all the nodes of the other file before the join is performed.

This is the join method that is used for all nonequijoin queries. This method is also used when the join criteria uses fields that have a data type of date, time, timestamp, or floating-point numeric.

In the following example, the distributed query optimizer decides to broadcast EMPLOYEE, because applying the selection JOB = 'Manager' results in broadcasting a smaller set of records. The temporary file at each node in the node group contains all the selected records. (Records are duplicated at each node.)

SQL statement:

   SELECT DEPTNAME, FIRSTNME, LASTNAME            FROM DEPARTMENT, EMPLOYEE            WHERE DEPTNO <> WORKDEPT AND JOB = 'Manager'

OPNQRYF command:

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

The distributed query optimizer sends the following two selected records to each node:

Table 1. Records that the distributed query optimizer sends to each node Old node New node FIRSTNME LASTNAME WORKDEPT SYSA SYSA, SYSB, SYSC Christine Haas A00 SYSC SYSA, SYSB, SYSC Michael Thompson B00

Records returned by this query:

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

 

Parent topic:

Implementation and optimization of join operations with DB2 Multisystem