1.0

)
PARTITION BY COLUMN (FLIGHT_ID);
The remaining facts tables are also partitioned, and also colocated with the FLIGHTS table. For example:
CREATE TABLE FLIGHTAVAILABILITY
(
FLIGHT_ID CHAR(6) NOT NULL ,
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';
33
Tutorials