HASHEDVALUE scalar function

The HASHEDVALUE function returns the distribution map index of the row obtained by applying the partitioning function on the distribution key value of the row.

The schema is SYSIBM.

column-name: The qualified or unqualified name of a column in a table. The column can have any data type.
If the column is 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.

An example application for this function is in a SELECT clause, where it returns the distribution map index for each row of the table that was used to form the result of the SELECT statement.

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

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

Result

The data type of the result is INTEGER in the range 0 to 32767. For a table with no distribution 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.

Notes

The HASHEDVALUE function cannot be used on replicated tables, within check constraints, or in the definition of generated columns (SQLSTATE 42881).
The HASHEDVALUE function cannot be used as a source function when creating a user-defined function. Because it accepts any data type as an argument, it is not necessary to create additional signatures to support user-defined distinct types.
The HASHEDVALUE function cannot be used as part of an expression-based key in a CREATE INDEX statement.
Syntax alternatives: For compatibility with previous versions of Db2® products, the function name PARTITION is a synonym for HASHEDVALUE.

Examples

Example 1: List the employee numbers (EMPNO) from the EMPLOYEE table for all rows with a distribution map index of 100.
```
   SELECT EMPNO FROM EMPLOYEE
     WHERE HASHEDVALUE(PHONENO) = 100
```

Example 2: Log the employee number and the projected distribution 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 ROW
     INSERT INTO EMPINSERTLOG2
       VALUES(NEWTABLE.EMPNO, HASHEDVALUE(NEWTABLE.EMPNO))