We experienced some performance issues with Partitioned DB. I want to get opinion from the experts. Let me explain the situation. We created 8 logical partitions (on the same machine) on Linux AMD machine. We are running DB2 8.1/FP-14. Here is the physical layout of partitions:

Partition 0: All catalog tables (catalog node)
Partition 1: All Non-partitioned tables, and One partitioned table (same table as below)
Partition 2-7: One Partitioned Table

Essentially we have only one table that is partitioned across nodes 1 thru 7. When we queried from remote machine, we had some performance issue with the query that involved Partitioned table. We tweaked FCM Buffer size, COMM rate etc., but made no difference. Interestingly when we set "DB2NODE=1", we got much better performance. But since we use Type-4 JDBC driver (Thin driver) with our application, DB2NODE variable is not available. Then IBM suggested that we try flipping node-0 and node-1 in db2nodes.cfg, and we did as follows:

0 qa-devdb2 1 << flipped from 0 to 1
1 qa-devdb2 0 << flipped from 1 to 0
2 qa-devdb2 2
3 qa-devdb2 3
4 qa-devdb2 4
5 qa-devdb2 5
6 qa-devdb2 6
7 qa-devdb2 7

It definitely improved query performance. Now when we load SQL store procedures, we have to specifically set DB2NODE=0 for Catalog Node since default connection is always to Node=1. Questions are:

#1) Has anyone experienced this kind of issue?

#2) Could node-flipping above be a problem in any respect?

#3) Any other ideas or thoughts around this issue? Could we partition differently?

Regards,

Ashok Rathi | Director, Database Engineering Group
DemandTec, Inc. | 1 Circle Star Way, Suite 200 | San Carlos, CA 94070
p | 650.226.4666 f | 650.556.1190



**************************************************************************

DemandTec Email Notice

This email and any attachments may contain confidential and/or proprietary information and is intended solely for the use of the addressee. If you are not the intended recipient we request that you notify us via email or telephone and delete all copies of the message from your systems. Additionally, although DemandTec has taken reasonable precautions to ensure the security of this email and any attachments, we encourage you to take similar precautions and accept no liability for any loss or damage resulting from its use.
DemandTec, 1 Circle Star Way, Suite 200, San Carlos, CA 94070, 650-226-4600

DB2 & UDB email list listserv db2-l LazyDBA home page