1.1

0 rows inserted/updated/deleted
[...]
2.
Run the loadTables.sql script to populate the tables with data:
run 'loadTables.sql';
The script output completes with:
sqlf> insert into FLIGHTAVAILABILITY values
('US1357',2,'2004-04-18',0,0,3);
1 row inserted/updated/deleted
3. Enter the following command to show the table names that you created (tables in the APP schema):
show tables in APP;
4. The new tables that you create and the data that you load are replicated on the two SQLFire servers by default.
You can check whether tables are partitioned or replicated by querying information in sys.systables. Use the
following query to check the data policy that SQLFire has assigned to the tables you just created:
select tablename, datapolicy from sys.systables where
tableschemaname='APP';
The output shows that each of the ToursDB tables that you created are replicated. SQLFire replicates tables
by default if you do not use the PARTITION BY clause in the CREATE TABLE statement.
Partitioning Tables on page 59 and Replicating Tables on page 69 provide more information about creating
tables in SQLFire.
5. You can also use the sys.members table to determine which members host a particular table:
select id from sys.systables s, sys.members where s.tablename='FLIGHTS';
In the tutorial, both of the SQLFire servers store information for each replicated table.
6. SQLFire provides query features similar to those available in other data management products. For example,
the following command executes a simple query:
SELECT city_name, country, language FROM cities WHERE language LIKE
'%ese';
The following query executes a join between tables:
SELECT city_name, countries.country, region, language
FROM cities, countries
WHERE cities.country_iso_code = countries.country_iso_code AND language
LIKE '%ese';
Implement a Partitioning Strategy
In this procedure you drop all tables in the ToursDB schema and then recreate them using a new partitioning
and replication strategy.
The ToursDB schema in this tutorial is similar to a 'STAR' schema, having only a few fact tables and several
dimension tables. Dimension tables are generally small and change infrequently, but are commonly used in join
queries. Dimension tables are good candidates for replicating across SQLFire members, because join queries
can execute in parallel.
The AIRLINES, CITIES, COUNTRIES, and MAPS tables are treated as dimension tables, and are replicated
across the SQLFire cluster. In the tutorial it is assumed that applications frequently join these related tables based
on the FLIGHT_ID column, which is chosen as the partitioning column.
39
Tutorials