Create Replicated Tables and Execute Queries

By default SQLFire replicates tables to members of the cluster. In this procedure you create new tables that are replicated the SQLFire cluster.

Procedure
  1. In the same sqlf session, run the ToursDB_schema.sql script to create the tables associated with the ToursDB sample database:
    run 'ToursDB_schema.sql';

    You see DDL output such as:

    sqlf> CREATE TABLE AIRLINES
       (
          AIRLINE CHAR(2) NOT NULL CONSTRAINT AIRLINES_PK PRIMARY KEY,
          AIRLINE_FULL VARCHAR(24),
          BASIC_RATE DOUBLE PRECISION,
          DISTANCE_DISCOUNT DOUBLE PRECISION,
          BUSINESS_LEVEL_FACTOR DOUBLE PRECISION,
          FIRSTCLASS_LEVEL_FACTOR DOUBLE PRECISION,
          ECONOMY_SEATS INTEGER,
          BUSINESS_SEATS INTEGER,
          FIRSTCLASS_SEATS INTEGER
       );
    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 and Replicating Tables 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';