I was told the right hand column is the one that counts- it should be in
numeric order starting with 0 for each physical host
1 qa-devdb2 0 << flipped from 1 to 0
0 qa-devdb2 1 << flipped from 0 to 1
your coordinator node is 1. You can use the db2advis utility from command
line to look at recent sql or specific sql statements- it will recommend
MQTs which will be repartitioned partitioned tables. I used the following
paterns for 8 and 16 nodes
0 catalog + non partitioned application tables
1-7 partitioned application tables
0 catalog + small non partitioned application tables
1-14 large partitioned application tables
990 large non partitioned application tables.
NOTICE TO RECIPIENT: If you are not the intended recipient of this
e-mail, you are prohibited from sharing, copying, or otherwise using or
disclosing its contents. If you have received this e-mail in error,
please notify the sender immediately by reply e-mail and permanently
delete this e-mail and any attachments without reading, forwarding or
saving them. Thank you.
"Ashok Rathi " <db2udbdba-ezmlmshield-x74852639.[Email address protected]
05/23/2007 12:28 AM
To
"LazyDBA Discussion" <[Email address protected]
cc
Subject
Node Flipping in Partitioned Environment - Anyone?
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
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
DB2 & UDB email list listserv db2-l LazyDBA home page