Technical Blog Post
Abstract
How to find out which member a row exists?
Body
You can use the DBPARTITIONNUM scalar function to find out which member a row exists.
Example 1: Find out which member a row with EMPNO='000050' exists:
$ db2 "select dbpartitionnum(EMPNO) partition_number from employee where EMPNO='000050'"
PARTITION_NUMBER
----------------
3
Example 2: Find out the member on which MICHAEL THOMPSON's employee data is stored:
db2 "select dbpartitionnum(EMPNO) partition_number from employee where FIRSTNME='MICHAEL' and LASTNAME='THOMPSON'"
PARTITION_NUMBER
----------------
1
Example 3: Find out the row count for each member, you can use this result to check whether the data distribution is even or skew:
$ db2 "select dbpartitionnum(EMPNO) partition_number,count(*) as row_count from employee group by dbpartitionnum(EMPNO) order by dbpartitionnum(EMPNO)"
PARTITION_NUMBER ROW_COUNT
---------------- -----------
0 4
1 9
2 11
3 9
4 9
Note: 1. All above using the sample database to demonstrate. db2nodes.cfg used:
0 host1 0
1 host1 1
2 host1 2
3 host2 0
4 host2 1
2. Instead of EMPNO, you can specify any column name of EMPLOYEE to DBPARTITIONNUM function, the result should be the same.
UID
ibm13286773