1.1.1

SEGMENT_NUMBER INTEGER NOT NULL ,
FLIGHT_DATE DATE NOT NULL ,
ECONOMY_SEATS_TAKEN INTEGER DEFAULT 0,
BUSINESS_SEATS_TAKEN INTEGER DEFAULT 0,
FIRSTCLASS_SEATS_TAKEN INTEGER DEFAULT 0,
CONSTRAINT FLIGHTAVAIL_PK PRIMARY KEY (
FLIGHT_ID,
SEGMENT_NUMBER,
FLIGHT_DATE),
CONSTRAINT FLIGHTS_FK2 Foreign Key (
FLIGHT_ID,
SEGMENT_NUMBER)
REFERENCES FLIGHTS (
FLIGHT_ID,
SEGMENT_NUMBER)
)
PARTITION BY COLUMN (FLIGHT_ID)
COLOCATE WITH (FLIGHTS);
2.
In the sqlf session, execute the create_colocated_schema.sql script to drop the existing tables
and recreate them with the new partitioning and replication strategy. Execute loadTables.sql to populate
the tables with data:
run 'create_colocated_schema.sql';
run 'loadTables.sql';
3. Confirm that the tables are created:
show tables in APP;
4. Verify whether individual tables are replicated or partitioned:
select tablename, datapolicy from sys.systables where
tableschemaname='APP';
5. The FLIGHTS table and others are now partitioned across the SQLFire cluster. Query the sys.members table
again to display the member IDs that host the table:
select id from sys.systables s, sys.members where s.tablename='FLIGHTS';
6. Now use the DSID Function to see how many rows of the partitioned FLIGHT table are stored on that
SQLFire server. For example:
select count(*) memberRowCount, dsid() from flights group by dsid();
7. Execute a join on both partition members in parallel.
select * from flights f, flightavailability fa
where f.flight_id = fa.flight_id and f.flight_id = 'AA1116';
The combined results are returned. Because the table is partitioned by FLIGHT_ID, the execution of the join
is pruned to the partition that stores the value 'AA1116.' You can verify that flight_id 'AA1116' is located
on only one data store using the query:
select count(*), dsid() from flights where flight_id = 'AA1116';
Persist Tables to Disk
By default, a SQLFire distributed system persists only the data dictionary for the tables and indexes you create.
These persistence files are stored in the datadictionary subdirectory of each locator and data store that
joins the distributed system. Table data, however, is not persisted by default; if you shut down al SQLFire
members, the tables are empty on the next startup. In this procedure you will persist table data to disk.
43
Tutorials