Persist Tables to Disk

At this point, the SQLFire cluster manages the ToursDB tables only in memory. In this procedure you persist table data to disk.

Procedure
  1. In a separate terminal window or GUI editor, examine the contents of the create_persistent_schema.sql script. Notice that this script uses the PERSISTENT keyword in each CREATE TABLE statement. For example:

    CREATE TABLE COUNTRIES
       (
          COUNTRY VARCHAR(26) NOT NULL CONSTRAINT COUNTRIES_UNQ_NM Unique,
          COUNTRY_ISO_CODE CHAR(2) NOT NULL CONSTRAINT COUNTRIES_PK PRIMARY KEY,
          REGION VARCHAR(26),
          CONSTRAINT COUNTRIES_UC
            CHECK (country_ISO_code = upper(country_ISO_code) )
    
       ) REPLICATE PERSISTENT;
    
  2. In the sqlf session, execute the create_persistent_schema.sql script, then load table data:
    run 'create_persistent_schema.sql';
    run 'loadTables.sql';
  3. Exit the sqlf session:
    exit;
  4. Now list the contents of each SQLFire server directory:
    ls -l ../server1 ../server2
    Notice that each SQLFire directory now contains several disk store files to manage the table data on disk. For example:
    BACKUPSQLF-DEFAULT-DISKSTORE.if		
    BACKUPSQLF-DEFAULT-DISKSTORE_1.crf	
    BACKUPSQLF-DEFAULT-DISKSTORE_1.drf
  5. Because the data is persisted to disk, SQLFire will recover data from disk even if you shut down the entire cluster. Shutdown the cluster, and then restart the locator and servers:
    cd ~/vFabric_SQLFire_10x
    sqlf shut-down-all -locators=ip_address[10101]
    
    Connecting to distributed system: locators=ip_address[10101]
    Successfully shut down 3 members
    sqlf locator start -dir=locator -peer-discovery-address=ip_address -peer-discovery-port=10101 \
                       -client-bind-address=ip_address -client-port=1527 & 
    sqlf server start -dir=server1 -locators=ip_address[10101] -client-bind-address=ip_address -client-port=1528 &
    sqlf server start -dir=server2 -locators=ip_address[10101] -client-bind-address=ip_address -client-port=1529 &
    Note: If you are running the tutorial on a Windows system, you will need to start the servers in parallel using two separate command windows.
    When you restart the servers, you may notice messages similar to:
    [info 2012/07/24 10:18:11.949 PDT  <main> tid=0x1] Region /_DDL_STMTS_META_REGION initialized with data from 
    /10.118.33.206:/Users/yozie/vFabric_SQLFire_103/server1/./datadictionary created at timestamp 1343149012698 version 0 diskStoreId 
    1fc6a853-69d6-4ffe-8029-218acf165c34 is waiting for the data previously hosted at 
    [/10.118.33.206:/Users/yozie/vFabric_SQLFire_103/server2/./datadictionary created at timestamp 1343149035681 version 0 diskStoreId 
    49c1e827-4bb5-49cc-951b-221d47bbc92f] to be available

    These are not error messages. They indicate that the SQLFire member you are starting is waiting for another member to become available online.

  6. Now verify that the persistent tables were reloaded:
    sqlf
    connect client 'ip_address:1527';
    select id from sys.systables s, sys.members where s.tablename='FLIGHTS';
    select count(*) memberRowCount, dsid() from flights group by dsid();