Coordinator Partition
in [DB2]
|
From: Ian on 12 Apr 2010 20:28 On 4/12/10 12:10 PM, whatever wrote: > On Apr 12, 2:53 am, Gladiator<vkamalnath1...(a)gmail.com> wrote: >> Hello All , >> >> Also can someone explain how to make a partition as Coordinator >> partition. >> >> Thanks in advance. > > From DB2 V9.5 Information center...assuming you are running V9.5 > > SELECT * FROM TABLE(DB_PARTITIONS()) AS T > > Following is sample output from the LIST DATABASE PARTITION GROUPS > SHOW DETAIL command: > DATABASE PARTITION GROUP NAME PMAP_ID DATABASE PARTITION NUMBER > IN_USE > ------------------------------ ------- ------------------------- > ------ > IBMCATGROUP 0 0 > Y > IBMDEFAULTGROUP 1 0 Y > > > > The catalog partition is the database partition on which all system > catalog tables are stored. All access to system tables must go through > this database partition. All federated database objects (for example, > wrappers, servers, and nicknames) are stored in the system catalog > tables at this database partition. The OP asked about coordinator partitions, not the catalog partition. > If possible, you should create each database in a separate instance. > If this is not possible (that is, you must create more than one > database per instance), you should spread the catalog partitions among > the available database partitions. Doing this reduces contention for > catalog information at a single database partition. This is not correct. You do not get catalog contention between 2 different databases. In extreme edge cases, the FCM network traffic to/from the physical server holding the system catalog may be somewhat higher than other partitions, but this is in extreme cases only. The catalog cache can be tuned to mitigate this as well. It is much more common that a single server is dedicated to be a catalog partition (and the primary coordintor partition) for a number of databases because the workload on this partition will generally be much lower than the partitions holding data.
From: Ian on 12 Apr 2010 20:36 On 4/12/10 5:26 PM, Mark A wrote: > "Ian"<ianbjor(a)mobileaudio.com> wrote in message > news:AJOwn.133118$Ye4.123760(a)newsfe11.iad... >> >> Yes: execute the statement "VALUES CURRENT DBPARTITIONNUM" >> >> Note: ANY database partition can be a coordinator partition. >> Whichever partition your application connects to will be the >> coordinator. > > Let's say that there are 3 physical servers as follows: > > Admin Node: Partition 0 > Data Node 1: Partitions 1-4 > Data Node 2: Partitions 5-9 > > Nodegroup 1 = partition 0 > Nodegroup 2 = partitions 1-8 > > Note: In the above example, Partition 0 can contain data, and usually does > if it is non-partitioned data, but in theory could contain partitioned data > as well if included in a nodegroup with other partitions. > > Let's assume a user logs on to Data Node 1 and connects to the database (not > to a partition). Which is coordinator partition? By default, the coordinator partition is the first logical partition** on a physical server. So in your case, partition 1 will be the coordinator. You can override this using the DB2NODE environment variable (this is what db2_all does to connect to each partition). ** The first logical partition is the partition with logical port 0 on a particular host If your db2nodes.cfg looks like: 0 admin_node 0 <--- 1 data_node1 0 <--- 2 data_node1 1 3 data_node1 2 4 data_node1 3 5 data_node2 0 <--- 6 data_node2 1 7 data_node2 2 8 data_node2 3
From: Gladiator on 13 Apr 2010 00:34 Hello All , Thanks for the reply. But i am asking about Co-ordinator partition not catalog partition. I want to understand the below lines. "The coordinator partition is the database partition to which a client application or a user connects. The coordinator partition compiles the query, collects the results, and then passes the results back to the client, therefore handling the workload of satisfying client requests. In a DPF environment, any partition can be the coordinator node." It says in a DPF environment any partition can be a Coordinator partition . So how do we identify which is the coordinator partition and how do we make a particular partition as Coordinator partition. Or is it like when we execute a statement say from partition 4 then this becomes the coordinator partition for that statement ? Thanks in advance.
From: stefan.albert on 13 Apr 2010 04:27 Hello, the coordinator partition is the one you start the connection/session to - with "db2 connect to DBname" (or implicit connect). For "CLI" (command line interface - "UNIX-level") : Normally you'll be connected to the first partition found - normally "0". If you want another partition to be the coordinating one, you have to set the environment variable DB2NODE: $ export DB2NODE=3 $ db2 terminate # important! $ db2 connect to DBname Now your coordinating node in number 3. This can be checked with: $ db2 list applications show detail The output has a column with the coord-part. This is also possible with db2pd, but a bit more complicated. Of course you can select catalog information from a partition different to the catalog partition, you'll then have some FCM traffic as mentioned above. HTH greetings, Stefan
From: Jayesh Thakrar on 14 Apr 2010 08:43 External connections use the appropriate IP address and port # combination to connect to the node of their choice, which then becomes the co-ordinator node. You will find the port #s for each partition in your /etc/services file (which ofcourse you or somebody else may have setup/configured). I don't believe there is a command (I haven't looked hard) that tell you an app's co-ordinator node. For your own connection (i.e. for introspection) you can get your co- ordinator node by using the DB2 special register CURRENT DBPARTITIONNUM You would use the DB2 command "GET CURRENT DBPARTITIONNUM" to get the node. Its very common to have the catalog partition as your co-ordinator node, to save round-trips to the catalog partition (which can happen often, ofcourse). HTH -- Jayesh
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 Prev: question regarding runstats and table partitioning Next: very dynamic cursor |