vFabric SQLFire in 15 Minutes

Need a quick introduction to vFabric SQLFire? Take this 15-minute tour to try out the basic features and functionality.

The vFabric SQLFire tutorials expand on these concepts, and demonstrate additional product features. See Tutorials.

  1. Download the latest SQLFire 1.1 ZIP file distribution from the download page: https://my.vmware.com/web/vmware/info/slug/application_platform/vmware_vfabric_sqlfire/1_0. Save the downloaded file in your home directory.
  2. Install SQLFire 1.1 by uncompressing the ZIP file:
    $ cd ~
    $ unzip vFabric_SQLFire_11_bNNNNN.zip

    Substitute the exact filename that you downloaded.

    This installs SQLFire in a new vFabric_SQLFire_11_bNNNNN subdirectory in your home directory, where NNNNN is the specific SQLFire build number that you downloaded.
  3. If you have not already done so, download and install Java. For a list of Java versions supported with this release of vFabric SQLFire, see Supported Configurations and System Requirements.
  4. Set your PATH environment variable to include the bin subdirectory of the vFabric SQLFire directory. For example:
    $ export PATH=$PATH:/home/username/vFabric_SQLFire_11_bNNNNN/bin
  5. Change to the SQLFire installation quickstart directory, and create three new directories for the locator and two servers that will make up the SQLFire distributed system:
    $ cd ~/vFabric_SQLFire_11_bNNNNN/quickstart
    $ mkdir locator1 server1 server2
  6. Start the locator:
    $ sqlf locator start -peer-discovery-address=localhost -dir=locator1
    Starting network server for SQLFire Locator at address localhost/127.0.0.1[1527]
    SQLFire Locator pid: 8787 status: running
    Logs generated in /home/yozie/vFabric_SQLFire_11_bNNNNN/quickstart/locator1/sqlflocator.log

    This command starts a default locator that accepts connections on the localhost address. The default port of 10334 is used for communication with other members of the distributed system. (You can double-check that this port is used by examining the locator1/sqlflocator.log file.) All new members of the distributed system must specify this locator's address and peer discovery port, localhost[10334], in order to join the system.

    The default port of 1527 is used for client connections to the distributed system.

  7. Start both servers:
    $ sqlf server start -locators=localhost[10334] -bind-address=localhost -client-port=1528 -dir=server1
    $ sqlf server start -locators=localhost[10334] -bind-address=localhost -client-port=1529 -dir=server2
    Starting SQLFire Server using locators for peer discovery: localhost[10334]
    Starting network server for SQLFire Server at address localhost/127.0.0.1[1528]
    SQLFire Server pid: 8897 status: running
    Logs generated in /home/yozie/vFabric_SQLFire_11_bNNNNN/quickstart/server1/sqlfserver.log
    Starting SQLFire Server using locators for peer discovery: localhost[10334]
    Starting network server for SQLFire Server at address localhost/127.0.0.1[1529]
    SQLFire Server pid: 9003 status: running
    Logs generated in /home/yozie/vFabric_SQLFire_11_bNNNNN/quickstart/server2/sqlfserver.log

    Both servers also bind to the localhost address. They must specify unique client ports in order to avoid conflicts with the locator's default client port. As an alternative, they could disable the network server entirely by specifying -run-netserver=false, and all clients would need to connect through the locator.

  8. Before going any further, check to make sure that you're in the SQLFire quickstart subdirectory. You'll need to run the script files in this directory later in the tutorial, and you must execute those scripts from within the quickstart directory:
    $ cd ~/vFabric_SQLFire_11_bNNNNN/quickstart
  9. Connect to the distributed system as a thin client, and display information about the system members:
    $ sqlf
    sqlf> connect client 'localhost:1527';
  10. Now that you're connected to the system, run a simple query to display information about the SQLFire system members:
    sqlf> select id, kind, netservers from sys.members;
    ID                            |KIND             |NETSERVERS                   
    ------------------------------------------------------------------------------
    localhost(17355):1374         |locator(normal)  |localhost/127.0.0.1[1527]    
    localhost(17535)<v2>:52946    |datastore(normal)|localhost/127.0.0.1[1529]    
    localhost(17438)<v1>:1230     |datastore(normal)|localhost/127.0.0.1[1528]    
    
    3 rows selected
    By default, SQLFire servers are started as datastores, so that they can host database schemas. In this cluster, you can connect as a client to any member by specifying localhost with the unique port number of the member (the one specified in the NETSERVERS column). However, connecting to the locator provides basic load balancing by routing the connection request to an available server member.
  11. Create a simple table and insert a few rows:
    sqlf> create table quicktable (id int generated always as identity, item char(25));
    0 rows inserted/updated/deleted
    sqlf> insert into quicktable values (default, 'widget');
    1 row inserted/updated/deleted
    sqlf> insert into quicktable values (default, 'gadget');
    1 row inserted/updated/deleted
    sqlf> select * from quicktable;
    ID         |ITEM                     
    -------------------------------------
    2          |gadget                
    1          |widget                   
    
    2 rows selected
  12. By default, SQLFire replicates new tables that you create onto data store members. You can validate this using the query:
    sqlf> select tablename, datapolicy from sys.systables where tablename='QUICKTABLE';
    TABLENAME                                                    |DATAPOLICY     
    -----------------------------------------------------------------------------
    QUICKTABLE                                                   |REPLICATE      
    
    1 row selected
  13. Execute two SQL scripts to generate a schema that has both replicated and partitioned tables, and then load the schema with data:
    sqlf> run 'create_colocated_schema.sql';
    sqlf> run 'loadTables.sql';

    You see numerous messages as various SQL commands are executed. The first script creates both replicated and partitioned tables, as you can see using the query:

    sqlf> select tablename, datapolicy from sys.systables where tableschemaname='APP';
    TABLENAME                                                     |DATAPOLICY     
    ------------------------------------------------------------------------------
    FLIGHTS_HISTORY                                               |PARTITION      
    FLIGHTAVAILABILITY                                            |PARTITION      
    FLIGHTS                                                       |PARTITION      
    MAPS                                                          |REPLICATE      
    CITIES                                                        |REPLICATE      
    COUNTRIES                                                     |REPLICATE      
    AIRLINES                                                      |REPLICATE      
    QUICKTABLE                                                    |REPLICATE      
    
    8 rows selected
  14. To observe a benefit of table partitioning, look at a query plan that involves one of the partitioned tables. Use the EXPLAIN command with a query to generate a query execution plan:
    sqlf> explain select * from flights;
    The EXPLAIN command stores the query execution plan for the statement in STATEMENTPLANS system table.
  15. To view the details of the query plan, disconnect as a thin client from the distributed system, and then reconnect as a peer client. A peer client participates as a member of the SQLFire distributed system and can coordinate queries, but it does not host any actual data. Execute these commands:
    sqlf> disconnect;
    sqlf> connect peer 'host-data=false;locators=localhost[10334]';

    You can see that your peer client connection introduces a new member to the distributed system:

    sqlf> select id, kind, netservers from sys.members;
    ID                            |KIND             |NETSERVERS                   
    ------------------------------------------------------------------------------
    localhost(17355):1374         |locator(normal)  |localhost/127.0.0.1[1527]    
    localhost(17438)<v1>:1230     |datastore(normal)|localhost/127.0.0.1[1528]    
    localhost(17535)<v2>:52946    |datastore(normal)|localhost/127.0.0.1[1529]    
    10.0.1.18(17894)<v3>:22695    |accessor(normal) |                             
    
    4 rows selected

    The term accessor indicates that the member only accesses data, but does not store data for the distributed system.

  16. To view the query execution plan that you generated earlier, query the SYS.STATEMENTPLANS table to view the statement ID (STMT_ID), then use EXPLAIN again with the ID to view the plan:
    sqlf> select stmt_id, stmt_text from sys.statementplans;
    STMT_ID                             |STMT_TEXT                              
    -------------------------------------------------------------------------------
    00000001-ffff-ffff-ffff-00020000004c| select * from flights                   
    
    1 row selected
    sqlf> explain '00000001-ffff-ffff-ffff-00020000004c';
    member   localhost(17438)<v1>:1230 begin_execution  2013-02-27 15:33:30.759 end_execution  2013-02-27 15:33:30.779
    QUERY-RECEIVE  execute_time 19.440818 ms member_node localhost(17535)<v2>:52946
      RESULT-SEND  execute_time 0.130708 ms member_node localhost(17535)<v2>:52946
        RESULT-HOLDER  execute_time 10.600616 ms returned_rows 275 no_opens 1
          TABLESCAN (100.00%) execute_time 3.250588 ms returned_rows 275 no_opens 1 scan_qualifiers None scanned_object APP.FLIGHTS scan_type HEAP
    member   localhost(17535)<v2>:52946 begin_execution  2013-02-27 15:33:30.758 end_execution  2013-02-27 15:33:30.89
    QUERY-SCATTER  execute_time 13.358717 ms member_node localhost(17438)<v1>:1230,localhost(17535)<v2>:52946
      QUERY-SEND  execute_time 3.345079 ms member_node localhost(17438)<v1>:1230
        QUERY-SEND  execute_time 1.140207 ms member_node localhost(17535)<v2>:52946
          RESULT-RECEIVE  execute_time 0.008155 ms member_node localhost(17535)<v2>:52946
            RESULT-RECEIVE  execute_time 1.4887 ms member_node localhost(17438)<v1>:1230
              SEQUENTIAL-ITERATION (35.23%) execute_time 10.463731 ms returned_rows 542 no_opens 1
                RESULT-HOLDER  execute_time 0.490328 ms returned_rows 267 no_opens 1 member_node localhost(17535)<v2>:52946
                  RESULT-HOLDER  execute_time 1.65846 ms returned_rows 275 no_opens 1 member_node localhost(17438)<v1>:1230
                    DISTRIBUTION-END (64.76%) execute_time 19.233818 ms returned_rows 542
    Local plan:
    member   localhost(17535)<v2>:52946 begin_execution  2013-02-27 15:33:30.762 end_execution  2013-02-27 15:33:30.842
    TABLESCAN (100.00%) execute_time 3.297607 ms returned_rows 267 no_opens 1 scan_qualifiers None scanned_object APP.FLIGHTS scan_type HEAP
    Note: The generated statement ID may be different on your system. Copy the exact ID from the output of the SELECT statement and paste it into the second EXPLAIN statement.

    The plan describes exactly how SQLFire performed the query. Note the two QUERY-SEND entries. These entries indicate that the results for the query were obtained from each of the two data store members in the distributed system. Because the FLIGHTS table was created as a partitioned table, new rows that are added to the table are uniquely assigned to partitions based on the partitioning key (in this case, the key is the FLIGHT_ID column). Partitions are then placed on data stores, which can process their portion of the data independently when queries are performed against the table. Results from multiple data stores are then merged on a single query coordinator member to provide the final result set.

  17. Either continue executing queries against the sample database, or shut down the SQLFire distributed system. To shut down all members of the system, first use the shut-down-all command to stop data stores and accessors. Then shut down any remaining locators:
    sqlf> quit;
    $ sqlf shut-down-all -locators=localhost[10334]
    Connecting to distributed system: locators=localhost[10334]
    Successfully shut down 2 members
    $ sqlf locator stop -dir=locator1
    The SQLFire Locator has stopped.
  18. To continue learning about vFabric SQLFire, read or work through the remaining Tutorials.