DBPARTITIONNUM scalar function

The DBPARTITIONNUM function returns the database partition number for a row. For example, if used in a SELECT clause, it returns the database partition number for each row in the result set.

The schema is SYSIBM.

column-name: The qualified or unqualified name of any column in the table. Because row-level information is returned, the result is the same regardless of which column is specified. The column can have any data type.
If the column is a column of a view, the expression for the column in the view 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.

The specific row (and table) for which the database partition number is returned by the DBPARTITIONNUM function is determined from the context of the SQL statement that uses the function.

The database partition number 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 returns the projected database partition number, given the current values of the new transition variables. However, the values of the distribution key columns might be modified by a subsequent before insert trigger. Thus, the final database partition number of the row when it is inserted into the database might differ from the projected value.

Result

The data type of the result is INTEGER and is never null. If there is no db2nodes.cfg file, the result is 0.

Notes

The DBPARTITIONNUM function cannot be used on replicated tables, within check constraints, or in the definition of generated columns (SQLSTATE 42881).
The DBPARTITIONNUM 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 DBPARTITIONNUM function cannot be used as part of an expression-based key in a CREATE INDEX statement.

Examples

Example 1: Count the number of instances in which the row for a given employee in the EMPLOYEE table is on a different database partition than the description of the employee's department in the DEPARTMENT table.
```
   SELECT COUNT(*) FROM DEPARTMENT D, EMPLOYEE E
     WHERE D.DEPTNO=E.WORKDEPT
     AND DBPARTITIONNUM(E.LASTNAME) <> DBPARTITIONNUM(D.DEPTNO)
```
Example 2: Join the EMPLOYEE and DEPARTMENT tables so that the rows of the two tables are on the same database partition.
```
   SELECT * FROM DEPARTMENT D, EMPLOYEE E
     WHERE DBPARTITIONNUM(E.LASTNAME) = DBPARTITIONNUM(D.DEPTNO)
```

Example 3: Using a before trigger on the EMPLOYEE table, log the employee number and the projected database partition number of any new row in the EMPLOYEE table in a table named EMPINSERTLOG1.

   CREATE TRIGGER EMPINSLOGTRIG1
   BEFORE INSERT ON EMPLOYEE
   REFERENCING NEW AW NEWTABLE
   FOR EACH ROW
   INSERT INTO EMPINSERTLOG1
   VALUES(NEWTABLE.EMPNO, DBPARTITIONNUM
   (NEWTABLE.EMPNO))