It seems that I cannot use "PARTITION BY PRIMARY KEY" of a table definition.
About what time can I use "PARTITION BY PRIMARY KEY" now?
Or is it already ending with correspondence?

[DDL]
Create Table TEST_TABLE(
idx int,
AccountID varchar(10),
OrderNo varchar(20),
OrderRev varchar(3),
Product varchar(20),
OrderType varchar(10),
LimitPrice varchar(10),
OrderDateTime varchar(20),
OrderSize varchar(10),
primary key(idx)
)
PARTITION BY PRIMARY KEY;

[SQLF]
sqlf fabricserver start -J"-server -Xms512m -Xmx1024m" -dir=fabricserver1 -port=1527 mcast-port=12333
sqlf fabricserver start -J"-server -Xms512m -Xmx1024m" -dir=fabricserver2 -port=1528 mcast-port=12333
sqlf fabricserver start -J"-server -Xms512m -Xmx1024m" -dir=fabricserver3 -port=1529 mcast-port=12333

[Java]
java version "1.6.0_17"
Java(TM) SE Runtime Environment (build 1.6.0_17-b04)
Java HotSpot(TM) Server VM (build 14.3-b01, mixed mode)

[OS]
Win XP sp3
mem 3.5GB


please teach me.

thank you.
--
yonyon

Hi yonyon,

What's the error you are seeing? The DDL is fine and I checked that it works as expected. I think the problem is in the fabricserver startup command:
sqlf fabricserver start -J"-server -Xms512m -Xmx1024m" -dir=fabricserver1 -port=1527 mcast-port=12333
The start_fabricserver.log file in the fabricserver1 directory should mention that the JVM arg is unknown. The above should be changed to:
sqlf fabricserver start -J-server -J-Xms512m -J-Xmx1024m -dir=fabricserver1 -port=1527 mcast-port=12333
Same for other fabricserver start commands.

Note that you can also use "-initialHeap" and "-maxHeap" instead of Xms and Xmx respectively i.e. "sqlf fabricserver start -J-server -initialHeap=512m -maxHeap=1024m -dir=fabricserver1 -port=1527 mcast-port=12333"

Also note that by default a table is partitioned by primary key unless it has a foreign key relation with another table (in which case it will be partitioned by the foreign key columns to enable colocation with the parent table). So in your particular example the explicit partition by primary key clause will not make a difference.

regards
sumedh

sumedh , thank you.

i not have a error.

I am performing performance comparison verification with the database of the other company.
Please teach me how to verify the table containing having been divided into two or more nodes in SQLFabric.

thank you.
------
yonyon

Yonyon,

There is a simple way to verify that things are working, and a more complicated way.

The Simple Way

Set up two (or more) servers in a cluster (using either multicast or locators).
Start one server.
Connect with a client - a thin client will be best (as opposed to an embedded client).

Create a table, adding a 'REPLICATE' clause. For most dramatic results, use a simple, single Integer as the primary key - using a more complex primary key will work, but the results may be skewed.

CREATE TABLE APP.TEST1 (id INTEGER NOT NULL PRIMARY KEY) REPLICATE;

Add some entries to the table (four or more).

Do a SELECT COUNT(*) FROM TABLE APP.TEST1; to verify the count.
Start up a second server in the cluster.

Connect the client to the other server (if on the same machine, will need a different netserver port).

Do a SELECT COUNT(*) FROM TABLE APP.TEST1; to verify the count.

Shut down the first server.

Do a SELECT COUNT(*) FROM TABLE APP.TEST1; to verify the count.

In all cases, the count should be the same, showing that replication was working.

Now, if you drop the table and repeat the test, but instead of 'REPLICATE' add 'PARTITION' (we will intentionally not add a REDUNDANCY clause), and then repeat the test, you should see that only some of the entries survive the shutdown of one of the servers.


That should demonstrate that the tables are distributed across the nodes.

The More Complicated Way

The more complicated way is to use VSD "Visual Stat Display" - the statistics system in SQLFabric.
If you start your servers with the additional properties ...

-Pgemfire.statistic-sampling-enabled=true -Pgemfire.statistic-archive-file=<path to file>/<filename>.gfs

The servers will generate statistics files that you can inspect (runtime or after the fact) using our VSD tool (downloadable from our download page). There are a lot of statistics here, and not all of them will make sense to you (they don't all make sense to me!). This is primarily a diagnostics tool for advanced administrators and our support group. I don't want to go too much deeper into this solution yet because things may still change before GA release. If you want help with this let me know.

The Eventual Way

We are also working on some tools that will be simpler and more obvious.

Hope this helps.
Dave

hi Dave.
Thank you.
I became study very intelligibly.


i try using VSD tool . but very difficult...
have are documents?

Please teach me the verification method of operation at the time of changing "PARTITION BY RANGE" clause.


----- DDL -------------------
Create Table TEST_TABLE(
idx int,
AccountID varchar(10),
OrderNo varchar(20),
OrderRev varchar(3),
Product varchar(20),
OrderType varchar(10),
LimitPrice varchar(10),
OrderDateTime varchar(20),
OrderSize varchar(10),
primary key(idx)
)
PARTITION BY RANGE ( IDX )
( VALUES BETWEEN 0 AND 1000,
VALUES BETWEEN 1001 AND 2000,
VALUES BETWEEN 2001 AND 3000,
VALUES BETWEEN 3001 AND 4000,
VALUES BETWEEN 4001 AND 5000,
VALUES BETWEEN 5001 AND 6000,
VALUES BETWEEN 6001 AND 7000,
VALUES BETWEEN 7001 AND 8000,
VALUES BETWEEN 8001 AND 9000,
VALUES BETWEEN 9001 AND 10000
);
------------------------------

In this case ...
If data registration is performed when the server of six nodes has started in one machine, do I divide and register with six nodes?
Do I hear that for example, I will be registered every 50Records to each node if the data of 10000 affairs is registered?

ex)
node.1
idx=0
:
:
idx=1000

node.2
idx=1001
:
:
idx=2000


node.3
idx=1001
:
:
idx=2000
:
:


Is there any method of verifying and checking the state of this node visually?

thak you.
--
yonyon

Yonyon,

When you use Range Partitioning, each range becomes an indivisible 'bucket' of entries. If you have only one node, it will host all buckets. If you have fewer nodes than you have ranges, some nodes will host multiple ranges. The system will distribute these 'buckets'/ranges across the available nodes when you add more nodes and execute a rebalance command. If you have more nodes than ranges, then some nodes will not host any buckets/ranges. Data in one bucket/range will not be spread across multiple nodes.

Our engineering group is working on tools that will help you visualize the distribution of the data more easily. I think I may be able to write some code using a distributed procedure that may help you verify this - I will work on this when I can.

I hope this answers your question. If not, please ask again.

Dave