VMware vFabric SQLFire - 1.1.1 User's Guide
vFabric SQLFire User's Guide
VMware vFabric SQLFire 1.1
VMware vFabric Suite 5.3
This document supports the version of each product listed and
supports all subsequent versions until the document is replaced by
a new edition. To check for more recent editions of this document,
see http://www.vmware.com/support/pubs.
EN-001171-00
You can find the most up-to-date technical documentation on the VMware Web site
at: http://www.vmware.com/support/
The VMware Web site also pro vides the latest product updates. If you hav e comments
about this documentation, submit your feedback to: docfeedback@vmware.com
Copyright © 2013 VMware, Inc. All rights reserved.This product is protected by U.S.
and international copyright and intellectual property laws.VMware products are
covered by one or more patents listed at http://www.vmware.com/go/patents
VMware is a registered trademark or trademark of VMw are , Inc. in the United States
and/or other jurisdictions. All other marks and names mentioned herein may be
trademarks of their respective companies.
VMware, Inc.
3401 Hillview Ave.
Palo Alto, CA 94304
Contents
About the SQLFire User's Guide.................................................................................13
Supported Configurations and System Requirements.............................................15
Part I: Getting Started with vFabric SQLFire............................................1
vFabric SQLFire in 15 Minutes.....................................................................................................................2
Chapter 1: Overview of vFabric SQLFire..................................................7
Data Flow Diagram.......................................................................................................................................8
GemFire, Apache Derby, and SQL Components..........................................................................................8
Chapter 2: Understanding the SQLFire Distributed System.................11
vFabric SQLFire Members..........................................................................................................................11
Servers, Peer Clients, and Server Groups..................................................................................................12
Discovery Mechanisms...............................................................................................................................12
Group Membership Service........................................................................................................................12
Replicated Tables and Par titioned Tables...................................................................................................13
Parallel Execution of Data-Aware Stored Procedures.................................................................................13
Cache Plug-ins for External Data Connections..........................................................................................14
Chapter 3: Installing vFabric SQLFire.....................................................15
Installation Note for vFabr ic Suite Customers.............................................................................................15
RHEL Only: Install vFabric SQLFire from an RPM.....................................................................................15
Install vFabric SQLFire from a ZIP File.......................................................................................................19
Chapter 4: Activating vFabric SQLFire Licensing.................................21
Understand vFabric SQLFire License Options...........................................................................................21
Choose a License Option Based on Topology............................................................................................22
How vFabric SQLFire Manages Licensing..................................................................................................24
Install and Configure vFabric SQLFire Licenses.........................................................................................25
Ver ify Your License and Check Your License Usage...................................................................................27
Chapter 5: Upgrading vFabric SQLFire..................................................29
Before You Upgrade....................................................................................................................................29
RHEL: Upgrade vFabric SQLFire from RPM..............................................................................................30
Upgrade vFabric SQLFire from a ZIP File..................................................................................................31
Version Compatibility Rules........................................................................................................................33
Chapter 6: Connect to vFabric SQLFire with JDBC Tools.....................35
Chapter 7: Tutorials..................................................................................37
Main Steps..................................................................................................................................................37
iii
Create a SQLFire Cluster...........................................................................................................................37
Connect to the Cluster Using SQLF...........................................................................................................39
Create Replicated Tables and Execute Quer ies.........................................................................................40
Implement a Partitioning Strategy...............................................................................................................41
Persist Tables to Disk..................................................................................................................................43
Add Servers to the Cluster and Stop Servers.............................................................................................45
Perfor m Additional Tasks............................................................................................................................46
Chapter 8: vFabric SQLFire Features and Benefits...............................47
Part II: Managing Your Data in vFabric SQLFire.....................................49
Chapter 9: Designing vFabric SQLFire Databases................................51
Design Principles of Scalable, Partition-Aware Databases.........................................................................51
Identify Entity Groups and Partitioning Keys...............................................................................................51
Replicate Code Tables................................................................................................................................52
Dealing with Many-to-Many Relationships..................................................................................................52
Example: Adapting a Database Schema for SQLFire.................................................................................53
Chapter 10: Using Server Groups to Manage Data................................57
Server Groups Overview.............................................................................................................................57
Adding Members to Server Groups............................................................................................................59
Assigning Tables to Server Groups.............................................................................................................60
Chapter 11: Partitioning Tables...............................................................61
How Table Par titioning Wor ks.....................................................................................................................61
Understanding Where Data Is Stored.........................................................................................................62
Failure and Redundancy.............................................................................................................................63
Creating Partitioned Tables.........................................................................................................................64
Rebalancing Partitioned Data on SQLFire Members..................................................................................69
Managing Replication Failures....................................................................................................................70
Chapter 12: Replicating Tables................................................................71
How SQLFire Replicates Tables.................................................................................................................71
Deciding When to Use Replicated Tables...................................................................................................71
Creating Replicated Tables.........................................................................................................................72
Chapter 13: Estimating Memory Requirements.....................................73
Estimating SQLFire Overhead....................................................................................................................73
Viewing Memory Usage in SYS.MEMORYANALYTICS..............................................................................74
Chapter 14: Using Disk Stores to Persist Data......................................79
Overview of Disk Stores..............................................................................................................................79
Guidelines for Designing Disk Stores.........................................................................................................85
Creating a Disk Store or Using the Default.................................................................................................86
vFabric SQLFire User's Guideiv
Contents
Persist Table Data to a Disk Store...............................................................................................................86
Optimizing Availability and Performance.....................................................................................................87
Starting System with Disk Stores................................................................................................................87
Disk Store Management.............................................................................................................................90
Chapter 15: Expor ting and Importing Data with vFabric SQLFire.......99
Using SQLF Commands to Export and Impor t Data..................................................................................99
Using Apache DdlUtils to Import Data......................................................................................................101
Exporting and Importing Data from Text Files...........................................................................................104
Chapter 16:Using T able Functions to Import Data as a SQLFire T ables.107
Overview of SQLFire Table Functions.......................................................................................................107
Example Table Function............................................................................................................................109
Part III: Developing Applications with SQLFire....................................111
Chapter 17:Starting SQLFire Servers with the FabricServer Interface.113
Starting a Network Ser ver........................................................................................................................114
Chapter 18: Developing Java Clients and Peers..................................117
Connect to a SQLFire Server with the Thin Client JDBC Driver...............................................................117
Start a SQLFire Peer with the Peer Client JDBC Driver...........................................................................120
Chapter 19: Configuring SQLFire as a JDBC Datasource..................123
Using SQLFire with Hibernate..................................................................................................................112
Chapter 20: Storing and Loading JAR Files in SQLFire......................125
Class Loading Overview...........................................................................................................................125
Alternate Methods for Managing JAR Files..............................................................................................127
Chapter 21: Developing ADO.NET Client Applications.......................131
About the ADO.NET Driver.......................................................................................................................131
ADO.NET Driver Classes..........................................................................................................................132
Installing and Using the ADO.NET driver..................................................................................................133
Connecting to SQLFire with the ADO.NET Driver....................................................................................134
Managing Connections.............................................................................................................................134
Executing SQL Commands.......................................................................................................................135
Working with Result Sets..........................................................................................................................135
Storing a Table..........................................................................................................................................136
Storing Multiple Tables..............................................................................................................................137
Specifying Command Parameters with SQLFParameter..........................................................................138
Updating Row Data...................................................................................................................................139
Adding Rows to a Table............................................................................................................................140
Managing SQLFire Transactions...............................................................................................................141
Perfor ming Batch Updates........................................................................................................................142
v
Generic Coding with the SQLFire ADO.NET Driver..................................................................................143
Chapter 22: Using SQLFire.NET Designer...........................................147
Installing SQLFire.NET Designer..............................................................................................................147
Connecting to a SQLFire Distributed System...........................................................................................147
Editing Tables............................................................................................................................................148
Chapter 23: Understanding the Data Consistency Model...................149
Data Consistency Concepts.....................................................................................................................149
No Ordering Guarantee for DML in Separate Threads.............................................................................150
Updates on Any Row Are Atomic and Isolated.........................................................................................150
Atomicity for Bulk Updates........................................................................................................................150
Chapter 24: Using Distributed Transactions in Your Applications.....151
Overview of SQLFire Distributed Transactions.........................................................................................151
Sequence of Events for a Distributed Transaction....................................................................................154
SQLFire Transaction Design.....................................................................................................................155
Best Practices for Using Transactions.......................................................................................................155
Transaction Functionality and Limitations.................................................................................................156
Chapter 25: Using Data-Aware Stored Procedures.............................157
Configuring a Procedure...........................................................................................................................157
Configuring a Custom Result Processor...................................................................................................158
Invoking a Procedure................................................................................................................................159
Example JDBC Client...............................................................................................................................160
Chapter 26: Using the Procedure Provider API....................................163
Procedure Parameters..............................................................................................................................163
Populating OUT and INOUT Parameters..................................................................................................164
Populating Result Sets..............................................................................................................................164
Using the <local> and <global> Escape Syntax with Nested Queries......................................................166
Chapter 27: Using the Custom Result Processor API.........................167
Implementing the ProcedureResultProcessor Interface ..........................................................................167
Example Result Processor: MergeSort.....................................................................................................167
Chapter 28: Programming User-Defined Types....................................171
Chapter 29: Using Result Sets and Cursors........................................175
Non-updatable, Forward-Only Result Sets...............................................................................................175
Updatable Result Sets..............................................................................................................................176
Scrollable Insensitive Result Sets.............................................................................................................181
Result Sets and Autocommit.....................................................................................................................182
Part IV: Caching Data with vFabric SQLFire.........................................183
vFabric SQLFire User's Guidevi
Contents
Chapter 30: SQLFire Cache Strategies.................................................185
Chapter 31: Using a RowLoader to Load Existing Data......................187
How SQLFire Invokes a RowLoader.........................................................................................................187
Implementing the RowLoader Interface....................................................................................................188
Using the JDBCRowLoader Example.......................................................................................................188
Chapter 32: Evicting Table Data from SQLFire.....................................191
How LRU Eviction Works..........................................................................................................................191
Limitations of Eviction...............................................................................................................................191
Eviction in Partitioned Tables....................................................................................................................192
Create a Table with Eviction Settings........................................................................................................192
Chapter 33: Handling DML Events Synchronously.............................195
Writer and Listener Cache Plug-ins..........................................................................................................195
Example Writer Implementation................................................................................................................196
Example Listener Implementation............................................................................................................196
Chapter 34: Handling DML Events Asynchronously..........................197
How the AsyncEventListener Works.........................................................................................................197
Implementation Requirements..................................................................................................................197
Implementing an AsyncEventListener.......................................................................................................198
Chapter 35: Using DBSynchronizer to Apply DML to an RDBMS......203
How DBSynchronizer Works.....................................................................................................................203
Restrictions and Limitations......................................................................................................................204
Configuring DBSynchronizer....................................................................................................................205
Chapter 36:Suppressing Event Callbacks for a vFabric SQLFire Connection.209
Part V: Deploying vFabric SQLFire........................................................211
Chapter 37: SQLFire Deployment Models............................................213
Embedded Peer-to-Peer Deployment.......................................................................................................214
Client-Server Deployment.........................................................................................................................216
Multi-site Deployment...............................................................................................................................217
Chapter 38: Steps to Plan and Configure a Deployment....................221
Chapter 39: Configuring Discovery Mechanisms................................223
Using Locators..........................................................................................................................................223
Configure Multicast Discovery..................................................................................................................225
vii
Chapter 40: Star ting and Configuring SQLFire Servers.....................227
Start and Stop SQLFire Ser vers Using sqlf..............................................................................................227
Specify the Server Working Directory.......................................................................................................228
Specify Client Connection Information......................................................................................................228
Define Server Groups...............................................................................................................................228
Execute SQL When You Start a Server.....................................................................................................229
Using Additional Boot Properties..............................................................................................................229
Chapter 41: Configuring Multi-site (WAN) Deployments.....................231
About Gateways........................................................................................................................................231
About High Availability for WAN Deployments..........................................................................................233
Limitations of Multi-Site Replication..........................................................................................................234
Prerequisites for WAN Replication............................................................................................................235
Steps to Configure a Multi-site Deployment..............................................................................................235
Chapter 42: Configuring Authentication and Authorization...............241
Configuring User Authentication...............................................................................................................241
User Names in Authentication and Authorization ....................................................................................249
Configuring User Authorization.................................................................................................................250
Configuring Network Encryption and Authentication with SSL/TLS..........................................................254
Part VI: Managing and Monitoring vFabric SQLFire............................259
Chapter 43: Configuring and Using SQLFire Log Files.......................261
Log Message Format................................................................................................................................261
Severity Levels..........................................................................................................................................261
Using java.util.logging.Logger for Application Log Messages...................................................................262
Using Trace Flags for Advanced Debugging.............................................................................................262
Chapter 44: Querying SQLFire System Tables and Indexes...............265
Getting Information About SQLFire Members..........................................................................................265
Getting Information About User Tables.....................................................................................................266
Chapter 45: Evaluating Query Plans and Query Statistics.................271
Capture a Query Plan for an Individual Statement...................................................................................271
Capture Query Plans for All Statements...................................................................................................272
Example Query Plan Analysis..................................................................................................................272
Query Plan Codes....................................................................................................................................276
Chapter 46: Overriding Optimizer Choices..........................................279
Chapter 47: Evaluating System and Application Performance..........283
Collecting System Statistics......................................................................................................................283
Collecting Application Statistics................................................................................................................284
vFabric SQLFire User's Guideviii
Contents
Using VSD to Analyze Statistics...............................................................................................................284
Chapter 48: Using Java Management Extensions (JMX)....................295
Using a JMX Manager Node.....................................................................................................................295
Using a vFabric SQLFire JMX Agent........................................................................................................298
Chapter 49: Best Practices for Tuning Performance...........................311
Tune Application Logic..............................................................................................................................311
Reduce Distribution Overhead..................................................................................................................311
Reduce Overhead of Eviction to Disk.......................................................................................................312
Minimize Update Latency for Replicated Tables.......................................................................................312
Tune FabricServers...................................................................................................................................312
Tuning Disk I/O.........................................................................................................................................313
Running SQLFire in Virtualized Environments..........................................................................................314
Chapter 50:Detecting and Handling Network Segmentation ("Split Brain").315
Part VII: vFabric SQLFire Reference......................................................317
Chapter 51: Configuration Properties..................................................319
Chapter 52: JDBC API............................................................................357
Mapping java.sql.Types to SQL Types......................................................................................................357
java.sql.BatchUpdateException Class......................................................................................................358
java.sql.Connection Interface....................................................................................................................358
java.sql.DatabaseMetaData Interface.......................................................................................................359
java.sql.Driver Interface............................................................................................................................361
java.sql.DriverManager.getConnection Method........................................................................................361
java.sql.PreparedStatement Interface.......................................................................................................361
java.sql.ResultSet Interface......................................................................................................................363
java.sql.SavePoint Class...........................................................................................................................363
java.sql.SQLException Class....................................................................................................................363
java.sql.Statement Class..........................................................................................................................363
javax.sql.XADataSource...........................................................................................................................363
Chapter 53: sqlf Launcher Commands................................................365
sqlf backup................................................................................................................................................367
sqlf compact-all-disk-stores......................................................................................................................369
sqlf compact-disk-store.............................................................................................................................370
sqlf encrypt-password...............................................................................................................................371
sqlf install-jar.............................................................................................................................................373
sqlf list-missing-disk-stores.......................................................................................................................376
sqlf locator................................................................................................................................................377
sqlf Logging Support.................................................................................................................................382
sqlf merge-logs.........................................................................................................................................382
ix
sqlf remove-jar..........................................................................................................................................383
sqlf replace-jar..........................................................................................................................................385
sqlf revoke-missing-disk-store...................................................................................................................387
sqlf run......................................................................................................................................................389
sqlf server.................................................................................................................................................391
sqlf show-disk-store-metadata..................................................................................................................402
sqlf shut-down-all......................................................................................................................................403
sqlf stats....................................................................................................................................................404
sqlf upgrade-disk-store.............................................................................................................................408
sqlf validate-disk-store..............................................................................................................................409
sqlf version................................................................................................................................................409
sqlf write-data-dtd-to-file...........................................................................................................................410
sqlf write-data-to-db..................................................................................................................................412
sqlf write-data-to-xml................................................................................................................................416
sqlf write-schema-to-db............................................................................................................................420
sqlf write-schema-to-sql............................................................................................................................424
sqlf write-schema-to-xml...........................................................................................................................429
Chapter 54: sqlf Interactive Commands...............................................435
absolute ...................................................................................................................................................435
after last ...................................................................................................................................................436
async .......................................................................................................................................................437
autocommit ..............................................................................................................................................437
before first ................................................................................................................................................438
close ........................................................................................................................................................438
commit .....................................................................................................................................................439
connect ....................................................................................................................................................439
connect client ...........................................................................................................................................440
connect peer ............................................................................................................................................441
describe ...................................................................................................................................................441
disconnect ................................................................................................................................................442
driver ........................................................................................................................................................443
elapsedtime .............................................................................................................................................443
execute ....................................................................................................................................................444
exit ...........................................................................................................................................................445
first............................................................................................................................................................446
get scroll insensitive cursor.......................................................................................................................446
GetCurrentRowNumber ...........................................................................................................................448
help ..........................................................................................................................................................448
last ...........................................................................................................................................................449
LocalizedDisplay.......................................................................................................................................449
MaximumDisplayWidth.............................................................................................................................450
next...........................................................................................................................................................451
prepare ....................................................................................................................................................451
previous....................................................................................................................................................452
protocol.....................................................................................................................................................453
vFabric SQLFire User's Guidex
Contents
relative......................................................................................................................................................454
remove......................................................................................................................................................454
rollback......................................................................................................................................................455
run.............................................................................................................................................................456
set connection...........................................................................................................................................457
show..........................................................................................................................................................457
wait for......................................................................................................................................................461
Chapter 55: SQLFire API........................................................................463
CredentialInitializer...................................................................................................................................464
UserAuthenticator.....................................................................................................................................464
Procedure Implementation Interfaces.......................................................................................................464
Procedure Result Processor Interfaces....................................................................................................466
Chapter 56: SQL Language Reference.................................................471
Keywords and Identifiers...........................................................................................................................471
SQL Statements.......................................................................................................................................472
SQL Queries.............................................................................................................................................532
SQL Clauses.............................................................................................................................................533
SQL Expressions......................................................................................................................................538
JOIN Operations.......................................................................................................................................551
Built-in Functions......................................................................................................................................553
Built-in System Procedures......................................................................................................................595
Data Types................................................................................................................................................624
SQL Standards Conformance...................................................................................................................635
Chapter 57: System Tables.....................................................................655
ASYNCEVENTLISTENERS.....................................................................................................................655
GATEWAYRECEIVERS............................................................................................................................656
GATEWAYSENDERS...............................................................................................................................657
INDEXES..................................................................................................................................................659
JARS.........................................................................................................................................................659
MEMBERS................................................................................................................................................659
MEMORYANALYTICS...............................................................................................................................661
STATEMENTPLANS.................................................................................................................................661
SYSALIASES............................................................................................................................................663
SYSCHECKS............................................................................................................................................664
SYSCOLPERMS......................................................................................................................................664
SYSCOLUMNS.........................................................................................................................................665
SYSCONGLOMERATES..........................................................................................................................667
SYSCONSTRAINTS.................................................................................................................................667
SYSDEPENDS.........................................................................................................................................668
SYSDISKSTORES....................................................................................................................................669
SYSFILES.................................................................................................................................................670
SYSFOREIGNKEYS.................................................................................................................................670
SYSKEYS.................................................................................................................................................671
xi
SYSROLES...............................................................................................................................................671
SYSROUTINEPERMS..............................................................................................................................672
SYSSCHEMAS.........................................................................................................................................673
SYSSTATEMENTS...................................................................................................................................674
SYSSTATISTICS.......................................................................................................................................674
SYSTABLEPERMS...................................................................................................................................675
SYSTABLES.............................................................................................................................................677
SYSTRIGGERS........................................................................................................................................678
SYSVIEWS...............................................................................................................................................680
Chapter 58: Exception Messages and SQL States..............................681
Chapter 59: ADO.NET Driver Reference...............................................685
SQLFire Data Types in ADO.NET.............................................................................................................685
VMware.Data.SQLFire.BatchUpdateException........................................................................................685
VMWare.Data.SQLFire.SQLFClientConnection.......................................................................................685
VMware.Data.SQLFire.SQLFCommand...................................................................................................690
VMware.Data.SQLFire.SQLFCommandBuilder........................................................................................696
VMware.Data.SQLFire.SQLFType............................................................................................................696
VMware.Data.SQLFire.SQLFDataAdapter...............................................................................................697
VMware.Data.SQLFire.SQLFDataReader................................................................................................699
VMware.Data.SQLFire.SQLFException....................................................................................................701
VMware.Data.SQLFire.SQLFParameter...................................................................................................702
VMware.Data.SQLFire.SQLFParameterCollection...................................................................................702
VMware.Data.SQLFire.SQLFTransaction.................................................................................................703
Chapter 60: vFabric SQLFire Limitations.............................................707
SQL Language Limitations.......................................................................................................................707
ADO.NET Driver Limitations.....................................................................................................................715
Troubleshooting Common Problems........................................................................717
vFabric SQLFire Glossary..........................................................................................721
vFabric SQLFire User's Guidexii
About the SQLFire User's Guide
Revised August 22, 2013.
The SQLFire User's Guide provides step-by-step procedures for installing, configuring, managing, and developing
applications with VMware® vFabric™ SQLFire. The guide also provides a complete reference for the SQLFire
tools, supported SQL statements, and APIs.
Intended Audience
The SQLFire User's Guide is intended for anyone who wants to install or deploy SQLFire, and for developers
who want to program applications that access a SQLFire system and/or implement SQLFire APIs. The guide
assumes that you are familiar with SQL databases and database terminology, and that you have experience in
developing database applications using Java or .NET technologies.
13
Supported Configurations and System Requirements
Before installing vFabric SQLFire, make sure your system meets the minimum system requirements for installing
and running the product.
Before installing vFabric SQLFire, make sure your system meets the minimum system requirements for installing
and running the product.
• Supported Configurations on page 15
• Host Machine Requirements on page 16
• Increase Unicast Buffer Size on Linux Platforms on page 16
• Disable SYN Cookies on Linux Platforms on page 16
• Client Requirements on page 16
Supported Configurations
The following table shows all supported configurations for vFabric SQLFire. These include Supported
Configurations and System Requirements for vFabric Suite.
Note:
The tables indicate whether the supported configuration is for production or development. Generally,
production support means you can run your production application on the platform; developer support
means you can develop on the platform but you should not run your production application on it.
Operating System
Architecture
SP2 Professional or
Enterprise*
Standard*
Professional or
Enterprise*
Production or Developer SupportJVMProcessor
ProductionJava SE 6, update 0_26x86 (64bit and 32 bit)Red Hat EL 5
ProductionJava SE 6, update 0_26x86 (64bit and 32 bit)Red Hat EL 6
ProductionJava SE 6, update 0_26x86 (64bit and 32 bit)Windows Server 2003 R2
ProductionJava SE 6, update 0_26x8 (64bit and 32 bit)Windows Server 2008 R2
DeveloperJava SE 6, update 0_26x8 (64bit and 32 bit)Windows 7 SP1
DeveloperJava SE 6x86 (64 bit)Windows XP
DeveloperJava SE 6x86 (64 bit)Ubuntu 10.04
*The Microsoft Loopback Adapter is not supported.
Note: The SQLFire product download does not include Java; you must download and install a supported
JDK for your system.
15
Host Machine Requirements
Requirements for each host:
• A supported Java SE installation.
• File system that supports long file names.
• Adequate per-user quota of file handles (ulimit for Linux)
• TCP/IP.
• System clock set to the correct time.
•
For each Linux host, the hostname and host files must be properly configured. See the system manpages
for hostname and hosts.
• Time synchronization service such as Network Time Protocol (NTP).
Note: For troubleshooting, you must run a time synchronization service on all hosts. Synchronized time
stamps allow you to merge log messages from different hosts, for an accurate chronological history of a
distributed run.
Increase Unicast Buffer Size on Linux Platforms
On Linux platforms, execute the following commands as the root user to increase the unicast buffer size:
1.
Edit the /etc/sysctl.conf file to include the following lines:
net.core.rmem_max=1048576
net.core.wmem_max=1048576
2.
Reload sysctl.conf:
sysctl -p
Disable SYN Cookies on Linux Platforms
Many default Linux installations use SYN cookies to protect the system against malicious attacks that flood
TCP SYN packets. The use of SYN cookies dramatically reduces network bandwidth, and can be triggered by
a running SQLFire distributed system.
If your SQLFire distributed system is otherwise protected against such attacks, disable SYN cookies to ensure
that SQLFire network throughput is not affected.
To disable SYN cookies permanently:
1.
Edit the /etc/sysctl.conf file to include the following line:
net.ipv4.tcp_syncookies = 0
Setting this value to zero disables SYN cookies.
2.
Reload sysctl.conf:
sysctl -p
Client Requirements
SQLFire supports two JDBC drivers: a thin client JDBC dri ver and a peer JDBC driv er . SQLFire server instances
and the peer driver are supported only with Java SE 6. You can download Java from
http://www.oracle.com/technetwork/java/javase/downloads/index.htm.
vFabric SQLFire User's Guide16
SQLFire provides a managed ADO.NET driver that you can use for developing non-Java client applications.
The ADO.NET driver uses IKVM technology to translate SQLFire JDBC core libraries to .NET MSIL. The
ADO.NET driver is supported for applications built using the Microsoft .NET 2.0 SP1 or higher framework.
17
Part 1
Getting Started with vFabric SQLFire
Getting Started with vF abric SQLFir e provides step-by-step procedures for installing, configuring, and using VMw are
vFabric™ SQLFire. The guide also explains main concepts and provides tutorials to help you quickly begin using
SQLFire.
Topics:
• vFabric SQLFire in 15 Minutes
• Overview of vFabric SQLFire
• Understanding the SQLFire Distributed System
• Installing vFabric SQLFire
• Activating vFabric SQLFire Licensing
• Upgrading vFabric SQLFire
• Connect to vFabric SQLFire with JDBC Tools
• Tutorials
• vFabric SQLFire Features and Benefits
®
1
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 on page 37.
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_b NNNNN 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 v ersions supported with this release of vFabric SQLFire, see Supported Configurations and
System Requirements on page 15.
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]
vFabric SQLFire User's Guide2
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 alternati ve, they could disable the netw ork 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
3
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.
vFabric SQLFire User's Guide4
15. T o 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 distrib uted 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
5
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 dif ferent 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 distrib uted 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 on page 37.
vFabric SQLFire User's Guide6
Chapter 1
Overview of vFabric SQLFire
vFabric SQLFire is a memory-optimized, distributed database management system designed for applications that hav e
demanding scalability and availability requirements. Applications can manage database tables entirely in memory, or
they can persist tables to disk to reload the data after restarting the system. A SQLFire distributed system can be easily
scaled out using commodity hardware.
7
Getting Started with vFabric SQLFire
Data Flow Diagram
GemFire, Apache Derby, and SQL Components
vFabric SQLFire incorporates core vFabric GemFire technology and Apache Derby RDBMS components to
provide a high-performance, distributed database management system. SQLFire extends standard SQL statements
where necessary for creating and managing tables and configuring the SQLFire system.
The sections that follow document how SQLFire utilizes the GemFire and Derby component functionality.
vFabric GemFire Technology
vFabric SQLFire incorporates the following VMware vFabric GemFire technology:
• Reliable data distribution
• High performance replication and partitioning
• Caching framework
• Parallel 'data-aware' application behavior routing
vFabric SQLFire User's Guide8
Overview of vFabric SQLFire
The SQLFire community site provides a comparison of SQLFire to other data management systems, such as
vFabric GemFire.
Apache Derby RDBMS Components
SQLFire integrates vFabric GemFire functionality with several components of the Apache Derby relational
database management system (RDBMS):
• JDBC driver. SQLFire supports a native, high performant JDBC driver (peer driver) and a thin JDBC driver.
The peer driver is based on the Derby embedded driver and JDBC 4.0 interfaces, but all communication with
SQLFire servers is implemented through the vFabric GemFire distribution layer.
• Query engine. SQLFire uses Derby to parse the SQL queries and generate parse trees. SQLFire injects its own
logic for intermediate plan creation and distributes the plan to data stores in the cluster . SQLFire also capitalizes
on some aspects of the built-in optimizer in Derby to generate query plans. The query execution itself uses
memory-based indexes and custom storage data structures. When query e xecution requires distribution, SQLFire
uses a custom algorithm to execute the query in parallel on multiple data stores.
• Network server. SQLFire servers embed the Derby network server for connectivity from thin JDBC and
ADO.NET clients. The communication protocol is based on the DRDA standard that is used by in IBM DB2
drivers.
SQL Extensions
vFabric SQLFire modifies and extends the query engine and SQL interface to provide support for partitioned
and replicated tables, data-aware procedures, data persistence, data eviction, and other features unique to the
distributed SQLFire architecture. SQLFire also adds SQL commands, stored procedures, system tables, and
functions to help easily manage features of the distributed system, such as persistent disk stores, listeners, and
locators.
9
Chapter 2
Understanding the SQLFire Distributed System
A SQLFire deployment consists of distributed member processes that connect to each other to form a peer-to-peer
network, also known as a distributed system or SQLFire cluster.
The sections that follow explain the interactions of main system components and processes. Tutorials on page 37 help
you get started configuring and using a SQLFire distributed system.
vFabric SQLFire Members
Member processes form a single, logical system, and each member has single-hop access to any other member,
with single-hop or no-hop access to data.
A SQLFire member is an instance of the SQLFire code that runs in a JVM. A SQLFire member can optionally
host data, provide network server functionality for client connections, and provide location services for the
distributed system.
A SQLFire distributed system is dynamic, and members can be added or removed at any time. The SQLFire
implementation guarantees a consistent view of the distributed system to ensure that data consistency and data
integrity are not compromised.
Most SQLFire members are configured to host data, and are referred to as data stores. Members that are configured
to not host data are referred to as accessors. Both data stores and accessors can execute the DDL and DML
commands that SQLFire supports. Data stores provide single-hop or no-hop access to data stored that is stored
on members of the distributed system. Accessors provide single-hop access to data stores in the distributed
system. Data stores and accessors are licensed separately.
A third type of member, the standalone locator, does not host data and does not support DDL and DML statements
on user-defined tables. You use locators to discover members of the SQLFire cluster.
A SQLFire agent is an optional distributed system member that provides access to JMX MBeans for monitoring
and managing SQLFire.
For more information, see:
• Starting and Configuring SQLFire Servers on page 227
• Using Locators on page 223
• Start a SQLFire Peer with the Peer Client JDBC Driver on page 120
• Using a vFabric SQLFire JMX Agent on page 298
11
Getting Started with vFabric SQLFire
Servers, Peer Clients, and Server Groups
A SQLFire server is a process that hosts data and is a member of a peer-to-peer distributed system. SQLFire
servers run in Java Virtual Machines (JVMs).
You start a SQLFire server using the sqlf tool from a command prompt or terminal window. sqlf launches
servers as standalone processes that are akin to database servers. The servers can accept TCP connections from
thin clients, authenticate credentials, manage sessions, delegate work to a thread pool for SQL processing, and
so forth.
A peer client, also known as an embedded client, is a SQLFire-aware JDBC client that connects to the distrib uted
system using the JDBC peer driver. Peer clients are always connected to the distributed system, and they have
single-hop access to data. A peer client can be configured as a pure client (referred to as an accessor member)
or as a client that also hosts data (a data store).
Both JDBC peer client processes and server processes are peer members of the distributed system. The members
discover each other dynamically through a built-in multicast based discovery mechanism or by using a locator
service when TCP discovery is more desirable.
Note: In addition to peer client members that participate in a cluster, SQLFire servers support thin client
connections from JDBC and ADO.NET thin client drivers. See Developing Java Clients and Peers on
page 117 and Developing ADO.NET Client Applications on page 131.
SQLFire servers and peer clients that host data (when the host-data property is set to true) are automatically
part of the default server group. A server gr oup is a logical grouping of SQLFire serv er and peer client members
that defines the data stores that should host data for table. When any SQLFire object is created, such as a table,
in the CREATE TABLE statement you can specify the server group name where the table will be hosted. If no
group is specified, the table is hosted in the default server group. Using Server Groups to Manage Data on page
57 provides additional information.
For more information, see:
• Starting and Configuring SQLFire Servers on page 227
• Using Locators on page 223
• Start a SQLFire Peer with the Peer Client JDBC Driver on page 120
• Using Server Groups to Manage Data on page 57
Discovery Mechanisms
A peer member (a server or peer client process) announces itself to the distributed system using one of two
mechanisms.
SQLFire provides these discovery mechanisms:
• Locator (TCP/IP). A locator service maintains a registry of all peer members in the distrib uted system at an y
given moment. A locator is typically started as a separate process (with redundancy), but you can also embed
a locator in any peer member, such as a SQLFire server. The locator opens a TCP port through which all new
members connect to get initial membership information.
• UDP/IP Multicast. Members can optionally use a multicast address to broadcast their presence and receive
membership notification information.
Configuring Discovery Mechanisms on page 223 provides more information.
Group Membership Service
The Group Membership Service (GMS) uses self-defined system membership. Processes can join or leave the
distributed system at any time. The GMS communicates this information to every other member in the system,
vFabric SQLFire User's Guide12
Loading...