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)'))

 

Parent topic:

PARTITION with DB2 Multisystem