Collocated join with DB2^® Multisystem

 

In a collocated join, corresponding records of files being joined exist on the same node.

The values of the partitioning key of the files being joined are partition compatible. No data needs to be moved to another node to perform the join. This method is only valid for queries where all of the fields of the partitioning keys are join fields and the join operator is the = (equals) operator. Also, the nth field (where n=1 to the number of fields in the partitioning key) of the partitioning key of the first file must be joined to the nth field of the partitioning key of the second file, and the data types of the nth fields must be partition compatible. Note that all of the fields of the partitioning key must be involved in the join. Additional join predicates that do not contain fields of the partitioning key do not affect your ability to do a collocated join.

In the following example, because the join predicate involves the partitioning key fields of both files and the fields are partition compatible, a collocated join can be performed. This implies that matching values of DEPTNO and WORKDEPT are located on the same node.

SQL statement:

   SELECT DEPTNAME, FIRSTNME, LASTNAME               FROM DEPARTMENT, EMPLOYEE               WHERE DEPTNO=WORKDEPT

OPNQRYF command:

   OPNQRYF FILE((DEPARTMENT) (EMPLOYEE))
                   FORMAT(JOINFMT)
                   JFLD((DEPTNO WORKDEPT *EQ))

Records returned by this query:

Table 1. Display of the query results DEPTNAME FIRSTNME LASTNAME Support services Christine Haas Support services Sally Kwan Planning John Geyer Planning Irving Stern Accounting Michael Thompson Accounting Eileen Henderson Programming Jennifer Lutz Programming David White

In the following example, the additional join predicate MGRNO=EMPNO does not affect the ability to perform a collocated join, because the partitioning keys are still involved in a join predicate.

SQL:       SELECT DEPTNAME, FIRSTNME, LASTNAME               FROM DEPARTMENT, EMPLOYEE               WHERE DEPTNO=WORKDEPT AND MGRNO=EMPNO

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

Records returned by this query:

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

 

Parent topic:

Implementation and optimization of join operations with DB2 Multisystem