Examples of PARTITION with DB2® Multisystem

Here is an example about how to use the PARTITION function.

Find the PARTITION number for every row of the EMPLOYEE table.

SQL statement:

SELECT PARTITION(CORPDATA.EMPLOYEE), LASTNAME
   FROM CORPDATA.EMPLOYEE

OPNQRYF command:

OPNQRYF FILE((CORPDATA/EMPLOYEE))
        FORMAT(FNAME)
        MAPFLD((PART1 '%PARTITION(1)'))

Select the employee number (EMPNO) from the EMPLOYEE table for all rows where the partition number is equal to 100.
SQL statement:
```
SELECT EMPNO
   FROM CORPDATA.EMPLOYEE
   WHERE PARTITION(CORPDATA.EMPLOYEE) = 100
```
OPNQRYF command:
```
OPNQRYF FILE((EMPLOYEE)) QRYSLT('%PARTITION(1) *EQ 100')
```

Join the EMPLOYEE and DEPARTMENT tables, select all rows of the result where the rows of the two tables have the same partition number.

SQL statement:

SELECT *
   FROM CORPDATA.EMPLOYEE X, CORPDATA.DEPARTMENT Y
   WHERE PARTITION(X)=PARTITION(Y)

OPNQRYF command:

OPNQRYF FILE((CORPDATA/EMPLOYEE) (CORPDATA/DEPARTMENT))
        FORMAT(FNAME)
        JFLD((1/PART1 2/PART2 *EQ))
        MAPFLD((PART1 '%PARTITION(1)')
               (PART2 '%PARTITION(2)'))