SQL Reference

NODENUMBER

>>-NODENUMBER--(--column-name--)-------------------------------><
 

The schema is SYSIBM.

The NODENUMBER function returns the partition number of the row. For example, if used in a SELECT clause, it returns the partition number for each row of the table that was used to form the result of the SELECT statement.

The partition number 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 partition number 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 partition number 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 partition number is returned by the NODENUMBER function is determined from the context of the SQL statement that uses the function.

The data type of the result is INTEGER and is never null. Since row-level information is returned, the results are the same, regardless of which column is specified for the table. If there is no db2nodes.cfg file, the result is 0.

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

Examples:

• Count the number of rows where the row for an EMPLOYEE is on a different partition from the employee's department description in DEPARTMENT.

         SELECT COUNT(*) FROM DEPARTMENT D, EMPLOYEE E
                  WHERE D.DEPTNO=E.WORKDEPT
                  AND NODENUMBER(E.LASTNAME) <> NODENUMBER(D.DEPTNO)
  

• Join the EMPLOYEE and DEPARTMENT tables where the rows of the two tables are on the same partition.

         SELECT * FROM DEPARTMENT D, EMPLOYEE E
                  WHERE NODENUMBER(E.LASTNAME) = NODENUMBER(D.DEPTNO)
  

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

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

⁴⁴

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