SQL Reference

PARTITION

>>-PARTITION--(--column-name--)--------------------------------><
 

The schema is SYSIBM.

The PARTITION function returns the partitioning map index of the row obtained by applying the partitioning function on the partitioning key value of the row. For example, if used in a SELECT clause, it returns the partitioning map index for each row of the table that was used to form the result of the SELECT statement.

The partitioning map index returned on transition variables and tables is derived from the current transition values of the partitioning key columns. For example, in a before insert trigger, the function will return the projected partitioning map index given the current values of the new transition variables. However, the values of the partitioning key columns may be modified by a subsequent before insert trigger. Thus, the final partitioning map index of the row when it is inserted into the database may differ from the projected value.

The argument must be the qualified or unqualified name of a column of a table. The column can have any data type. ⁴⁶ If column-name references a column of a view the expression in the view for the column must reference a column of the underlying base table and the view must be deletable. A nested or common table expression follows the same rules as a view. See Notes for the definition of a deletable view.

The specific row (and table) for which the partitioning map index is returned by the PARTITION function is determined from the context of the SQL statement that uses the function.

The data type of the result is INTEGER in the range 0 to 4095. For a table with no partitioning key, the result is always 0. A null value is never returned. Since row-level information is returned, the results are the same, regardless of which column is specified for the table.

The PARTITION function cannot be used on replicated tables, within check constraints, or in the definition of generated columns (SQLSTATE 42881).

Example:

• List the employee numbers (EMPNO) from the EMPLOYEE table for all rows with a partitioning map index of 100.

         SELECT EMPNO FROM EMPLOYEE
           WHERE PARTITION(PHONENO) = 100
  

• Log the employee number and the projected partitioning map index of the new row into a table called EMPINSERTLOG2 for any insertion of employees by creating a before trigger on the table EMPLOYEE.

  	CREATE TRIGGER EMPINSLOGTRIG2
  		BEFORE INSERT ON EMPLOYEE
  		REFERENCING NEW AW NEWTABLE
  		FOR EACH MODE ROW MODE DB2SQL
  		INSERT INTO EMPINSERTLOG2
  		  VALUES(NEWTABLE.EMPNO, PARTITION(NEWTABLE.EMPNO))
  

⁴⁶

This function may not be used as a source function when creating a user-defined function. Since it accepts any data type as an arguments, it is not necessary to create additional signatures to support user-defined distinct types.