Note: I's assumed SQLFire is installed in a directory as follows:
/Users/papicella/vmware/software/sqlfire/vFabric_SQLFire_10
1. Setup your environment as shown below. MAC example.
export PATH=/Users/papicella/vmware/software/sqlfire/vFabric_SQLFire_10/bin:$PATH
2. Ensure you have a valid JDK in your path as shown below
[Mon Dec 26 22:01:26 papicella@:~/vmware/software/sqlfire/vFabric_SQLFire_10/pasdemos/deptemp ] $ java -version
java version "1.6.0_26"
Java(TM) SE Runtime Environment (build 1.6.0_26-b03-383-11A511)
Java HotSpot(TM) 64-Bit Server VM (build 20.1-b02-383, mixed mode)
3. In your current directory created 2 folders called "server1" and "server2"
mkdir server1
mkdir server24. Start 2 servers as shown below they will discover each other using multicast port 12333
sqlf server start -dir=server1 -client-port=1527 -mcast-port=12333 &
sqlf server start -dir=server2 -client-port=1528 -mcast-port=12333 &
sqlf server start -dir=server2 -client-port=1528 -mcast-port=12333 &
Output:
[Mon Dec 26 22:11:49 papicella@:~/vmware/software/sqlfire/vFabric_SQLFire_10/pasdemos/deptemp ] $ ./start-servers.sh
[Mon Dec 26 22:11:51 papicella@:~/vmware/software/sqlfire/vFabric_SQLFire_10/pasdemos/deptemp ] $ Starting SQLFire Server using multicast for peer discovery: 239.192.81.1[12333]
Starting network server for SQLFire Server at address localhost/127.0.0.1[1527]
Starting SQLFire Server using multicast for peer discovery: 239.192.81.1[12333]
Starting network server for SQLFire Server at address localhost/127.0.0.1[1528]
SQLFire Server pid: 1032 status: running
Distributed system now has 2 members.
Other members: 192-168-1-3.tpgi.com.au(1031:datastore):14506/50489
Logs generated in /Users/papicella/vmware/software/sqlfire/vFabric_SQLFire_10/pasdemos/deptemp/server2/sqlfserver.log
SQLFire Server pid: 1031 status: running
Distributed system now has 2 members.
Other members: 192-168-1-3.tpgi.com.au(1032:datastore):23687/50492
Logs generated in /Users/papicella/vmware/software/sqlfire/vFabric_SQLFire_10/pasdemos/deptemp/server1/sqlfserver.log
[Mon Dec 26 22:11:51 papicella@:~/vmware/software/sqlfire/vFabric_SQLFire_10/pasdemos/deptemp ] $ Starting SQLFire Server using multicast for peer discovery: 239.192.81.1[12333]
Starting network server for SQLFire Server at address localhost/127.0.0.1[1527]
Starting SQLFire Server using multicast for peer discovery: 239.192.81.1[12333]
Starting network server for SQLFire Server at address localhost/127.0.0.1[1528]
SQLFire Server pid: 1032 status: running
Distributed system now has 2 members.
Other members: 192-168-1-3.tpgi.com.au(1031:datastore)
Logs generated in /Users/papicella/vmware/software/sqlfire/vFabric_SQLFire_10/pasdemos/deptemp/server2/sqlfserver.log
SQLFire Server pid: 1031 status: running
Distributed system now has 2 members.
Other members: 192-168-1-3.tpgi.com.au(1032:datastore)
Logs generated in /Users/papicella/vmware/software/sqlfire/vFabric_SQLFire_10/pasdemos/deptemp/server1/sqlfserver.log
5. Connect as shown below
[Mon Dec 26 22:13:06 papicella@:~/vmware/software/sqlfire/vFabric_SQLFire_10/pasdemos/deptemp ] $ sqlf
sqlf version 10.4
sqlf> connect client 'localhost:1527';
sqlf>
6. In a separate terminal window create a file called "deptempPERSIST.sql" with contents as follows.
AUTOCOMMIT OFF;
create diskstore STORE1;
CREATE TABLE DEPT (
DEPTNO INTEGER NOT NULL CONSTRAINT DEPTNO_PK PRIMARY KEY,
DNAME VARCHAR(14),
LOC VARCHAR(13))
partition by column (deptno)
persistent 'STORE1'
REDUNDANCY 1;
INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');
CREATE TABLE EMP (
EMPNO INTEGER NOT NULL CONSTRAINT EMP_PK Primary Key,
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR INTEGER,
HIREDATE DATE,
SAL INTEGER,
COMM INTEGER,
DEPTNO INTEGER)
partition by column (deptno)
COLOCATE WITH (DEPT)
persistent 'STORE1'
REDUNDANCY 1;
INSERT INTO EMP VALUES
(7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20);
INSERT INTO EMP VALUES
(7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-21', 1600, 300, 30);
INSERT INTO EMP VALUES
(7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
INSERT INTO EMP VALUES
(7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20);
INSERT INTO EMP VALUES
(7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);
INSERT INTO EMP VALUES
(7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL, 30);
INSERT INTO EMP VALUES
(7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10);
INSERT INTO EMP VALUES
(7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09', 3000, NULL, 20);
INSERT INTO EMP VALUES
(7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);
INSERT INTO EMP VALUES
(7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);
INSERT INTO EMP VALUES
(7876, 'ADAMS', 'CLERK', 7788, '1983-01-12', 1100, NULL, 20);
INSERT INTO EMP VALUES
(7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, NULL, 30);
INSERT INTO EMP VALUES
(7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20);
INSERT INTO EMP VALUES
(7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10);
COMMIT;
ALTER TABLE EMP
ADD CONSTRAINT EMP_FK Foreign Key (DEPTNO)
REFERENCES DEPT (DEPTNO);
COMMIT;
7. Run the file "deptempPERSIST.sql" as shown below by returning to the terminal window at step #5
sqlf> run 'deptempPERSIST.sql';
8. Query and verify you now have DEPT / EMP tables with data as shown below.
sqlf> select * from dept; DEPTNO |DNAME |LOC ---------------------------------------- 10 |ACCOUNTING |NEW YORK 20 |RESEARCH |DALLAS 30 |SALES |CHICAGO 40 |OPERATIONS |BOSTON 4 rows selected sqlf> select * from emp; EMPNO |ENAME |JOB |MGR |HIREDATE |SAL |COMM |DEPTNO ------------------------------------------------------------------------------------------- 7934 |MILLER |CLERK |7782 |1982-01-23|1300 |NULL |10 7782 |CLARK |MANAGER |7839 |1981-06-09|2450 |NULL |10 7839 |KING |PRESIDENT|NULL |1981-11-17|5000 |NULL |10 7902 |FORD |ANALYST |7566 |1981-12-03|3000 |NULL |20 7876 |ADAMS |CLERK |7788 |1983-01-12|1100 |NULL |20 7788 |SCOTT |ANALYST |7566 |1982-12-09|3000 |NULL |20 7369 |SMITH |CLERK |7902 |1980-12-17|800 |NULL |20 7566 |JONES |MANAGER |7839 |1981-04-02|2975 |NULL |20 7521 |WARD |SALESMAN |7698 |1981-02-22|1250 |500 |30 7499 |ALLEN |SALESMAN |7698 |1981-02-21|1600 |300 |30 7900 |JAMES |CLERK |7698 |1981-12-03|950 |NULL |30 7844 |TURNER |SALESMAN |7698 |1981-09-08|1500 |0 |30 7654 |MARTIN |SALESMAN |7698 |1981-09-28|1250 |1400 |30 7698 |BLAKE |MANAGER |7839 |1981-05-01|2850 |NULL |30 14 rows selected
9. Check how our DEPT/EMP tables stored within the distributed system.
sqlf> select substr(tablename, 1, 10) as tablename, datapolicy from sys.systables where tableschemaname='APP'; TABLENAME |DATAPOLICY ----------------------------------- EMP |PERSISTENT_PARTITION DEPT |PERSISTENT_PARTITION 2 rows selected
Here the data is not only partitioned by also persisted to local disk to ensure it can be rehydrated when the members are brought down and re-started.
10. Verify where our data is stored and ensure that it's partioned by DEPTNO and that the EMP records are co located with the DEPT data.
sqlf> select substr(dsid(), 1, 48) as dsid, deptno, dname from dept; DSID |DEPTNO |DNAME --------------------------------------------------------------------------- 192-168-1-3.tpgi.com.au(1031)<v0>:14506/50489 |30 |SALES 192-168-1-3.tpgi.com.au(1031)<v0>:14506/50489 |40 |OPERATIONS 192-168-1-3.tpgi.com.au(1032)<v1>:23687/50492 |10 |ACCOUNTING 192-168-1-3.tpgi.com.au(1032)<v1>:23687/50492 |20 |RESEARCH 4 rows selected sqlf> select substr(dsid(), 1, 48) as dsid, deptno, ename from emp; DSID |DEPTNO |ENAME ----------------------------------------------------------------------- 192-168-1-3.tpgi.com.au(1031)<v0>:14506/50489 |30 |WARD 192-168-1-3.tpgi.com.au(1031)<v0>:14506/50489 |30 |ALLEN 192-168-1-3.tpgi.com.au(1031)<v0>:14506/50489 |30 |JAMES 192-168-1-3.tpgi.com.au(1031)<v0>:14506/50489 |30 |TURNER 192-168-1-3.tpgi.com.au(1031)<v0>:14506/50489 |30 |MARTIN 192-168-1-3.tpgi.com.au(1031)<v0>:14506/50489 |30 |BLAKE 192-168-1-3.tpgi.com.au(1032)<v1>:23687/50492 |10 |MILLER 192-168-1-3.tpgi.com.au(1032)<v1>:23687/50492 |10 |CLARK 192-168-1-3.tpgi.com.au(1032)<v1>:23687/50492 |10 |KING 192-168-1-3.tpgi.com.au(1032)<v1>:23687/50492 |20 |FORD 192-168-1-3.tpgi.com.au(1032)<v1>:23687/50492 |20 |ADAMS 192-168-1-3.tpgi.com.au(1032)<v1>:23687/50492 |20 |SCOTT 192-168-1-3.tpgi.com.au(1032)<v1>:23687/50492 |20 |SMITH 192-168-1-3.tpgi.com.au(1032)<v1>:23687/50492 |20 |JONES 14 rows selected
For more information on vFabric SQLFire visit the link below.
http://www.vmware.com/products/application-platform/vfabric-sqlfire/overview.html
No comments:
Post a Comment