DB2 Version 9.7 for Linux, UNIX, and Windows

DATAPARTITIONNUM scalar function


>>-DATAPARTITIONNUM--(--column-name--)-------------------------><

The schema is SYSIBM.

The DATAPARTITIONNUM function returns the sequence number (SYSDATAPARTITIONS.SEQNO) of the data partition in which the row resides. Data partitions are sorted by range, and sequence numbers start at 0. For example, the DATAPARTITIONNUM function returns 0 for a row that resides in the data partition with the lowest range.

The argument must be 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 column-name references a column in 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 data type of the result is INTEGER and is never null.

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

The DATAPARTITIONNUM function cannot be used within check constraints or in the definition of generated columns (SQLSTATE 42881). The DATAPARTITIONNUM function cannot be used in a materialized query table (MQT) definition (SQLSTATE 428EC).

Examples

Example 1: Retrieve the sequence number of the data partition in which the row for EMPLOYEE.EMPNO resides.
```
   SELECT DATAPARTITIONNUM (EMPNO)
   FROM EMPLOYEE
```
Example 2: To convert a sequence number that is returned by DATAPARTITIONNUM (for example, 0) to a data partition name that can be used in other SQL statements (such as ALTER TABLE...DETACH PARTITION), you can query the SYSCAT.DATAPARTITIONS catalog view. Include the SEQNO obtained from DATAPARTITIONNUM in the WHERE clause, as shown in the following example.
```
   SELECT DATAPARTITIONNAME
   FROM SYSCAT.DATAPARTITIONS
   WHERE TABNAME = 'EMPLOYEE' AND SEQNO = 0
```
results in the value 'PART0'.