VMware vFabric SQLFire - 1.0 User's Guide
vFabric SQLFire User's Guide
VMware vFabric SQLFire 1.0.3
VMware vFabric Suite 5.1
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-000663-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 © 2012 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
Chapter 1: Overview of vFabric SQLFire..................................................3
Data Flow Diagram.......................................................................................................................................4
GemFire, Apache Derby, and SQL Components..........................................................................................4
Chapter 2: Understanding the SQLFire Distributed System...................7
vFabric SQLFire Members............................................................................................................................7
Servers, Peer Clients, and Server Groups....................................................................................................8
Discovery Mechanisms.................................................................................................................................8
Group Membership Service..........................................................................................................................8
Replicated Tables and Par titioned Tables.....................................................................................................9
Parallel Execution of Data-Aware Stored Procedures...................................................................................9
Cache Plug-ins for External Data Connections..........................................................................................10
Chapter 3: Installing vFabric SQLFire.....................................................11
Installation Note for vFabr ic Suite Customers.............................................................................................11
RHEL Only: Install vFabric SQLFire from the VMWare YUM Repository....................................................11
Install vFabric SQLFire from a JAR File......................................................................................................13
Chapter 4: Activating vFabric SQLFire Licensing.................................15
Understand vFabric SQLFire License Options...........................................................................................15
Choose a License Option Based on Topology............................................................................................16
How vFabric SQLFire Manages Licensing..................................................................................................18
Install and Configure vFabric SQLFire Licenses.........................................................................................19
Ver ify Your License and Check Your License Usage...................................................................................21
Chapter 5: Upgrading vFabric SQLFire..................................................23
RHEL Only: Upgrade vFabric SQLFire from the VMWare YUM Repository...............................................23
Upgrade Issues...........................................................................................................................................24
Chapter 6: Connect to vFabric SQLFire with JDBC Tools.....................25
Chapter 7: Tutorials..................................................................................27
Main Steps..................................................................................................................................................27
Create a SQLFire Cluster...........................................................................................................................27
Connect to the Cluster Using SQLF...........................................................................................................29
Create Replicated Tables and Execute Quer ies.........................................................................................30
iii
Implement a Partitioning Strategy...............................................................................................................31
Persist Tables to Disk..................................................................................................................................34
Add Servers to the Cluster and Stop Servers.............................................................................................35
Perfor m Additional Tasks............................................................................................................................36
Chapter 8: vFabric SQLFire Features and Benefits...............................37
Part II: Managing Your Data in vFabric SQLFire.....................................39
Chapter 9: Designing vFabric SQLFire Databases................................41
Design Principles of Scalable, Partition-Aware Databases.........................................................................41
Identify Entity Groups and Partitioning Keys...............................................................................................41
Replicate Code Tables................................................................................................................................42
Dealing with Many-to-Many Relationships..................................................................................................42
Example: Adapting a Database Schema for SQLFire.................................................................................43
Chapter 10: Using Server Groups to Manage Data................................47
Server Groups Overview.............................................................................................................................47
Adding Members to Server Groups............................................................................................................49
Assigning Tables to Server Groups.............................................................................................................50
Chapter 11: Partitioning Tables...............................................................51
How Table Par titioning Wor ks.....................................................................................................................51
Understanding Where Data Is Stored.........................................................................................................52
Failure and Redundancy.............................................................................................................................53
Creating Partitioned Tables.........................................................................................................................54
Rebalancing Partitioned Data on SQLFire Members..................................................................................59
Managing Replication Failures....................................................................................................................60
Chapter 12: Replicating Tables................................................................61
How SQLFire Replicates Tables.................................................................................................................61
Deciding When to Use Replicated Tables...................................................................................................61
Creating Replicated Tables.........................................................................................................................62
Chapter 13: Estimating Memory Requirements.....................................63
Estimating SQLFire Overhead....................................................................................................................63
Viewing Memory Usage in SYS.MEMORYANALYTICS..............................................................................64
Chapter 14: Using Disk Stores to Persist Data......................................69
Overview of Disk Stores..............................................................................................................................69
Guidelines for Designing Disk Stores.........................................................................................................74
Creating a Disk Store or Using the Default.................................................................................................75
Persist Table Data to a Disk Store...............................................................................................................76
Optimizing Availability and Performance.....................................................................................................77
Starting System with Disk Stores................................................................................................................77
vFabric SQLFire User's Guideiv
Contents
Disk Store Management.............................................................................................................................79
Chapter 15: Expor ting and Importing Data with vFabric SQLFire.......89
Using SQLF Commands to Export and Impor t Data..................................................................................89
Using Apache DdlUtils to Import Data........................................................................................................91
Exporting and Importing Data from Text Files.............................................................................................94
Chapter 16:Using T ab le Functions to Import Data as a SQLFire T ables.95
Overview of SQLFire Table Functions.........................................................................................................95
Example Table Function..............................................................................................................................97
Part III: Developing Applications with SQLFire......................................99
Chapter 17:Starting SQLFire Servers with the FabricServer Interface.101
Starting a Network Ser ver........................................................................................................................102
Chapter 18: Developing Java Clients and Peers..................................105
Connect to a SQLFire Server with the Thin Client JDBC Driver...............................................................105
Start a SQLFire Peer with the Peer Client JDBC Driver...........................................................................107
Chapter 19: Configuring SQLFire as a JDBC Datasource..................109
Chapter 20: Storing and Loading JAR Files in SQLFire......................111
Class Loading Overview...........................................................................................................................111
Alternate Methods for Managing JAR Files..............................................................................................113
Chapter 21: Developing ADO.NET Client Applications.......................117
About the ADO.NET Driver.......................................................................................................................117
ADO.NET Driver Classes..........................................................................................................................118
Installing and Using the ADO.NET driver..................................................................................................119
Connecting to SQLFire with the ADO.NET Driver....................................................................................119
Managing Connections.............................................................................................................................120
Executing SQL Commands.......................................................................................................................120
Working with Result Sets..........................................................................................................................121
Storing a Table..........................................................................................................................................122
Storing Multiple Tables..............................................................................................................................123
Specifying Command Parameters with SQLFParameter..........................................................................124
Updating Row Data...................................................................................................................................125
Adding Rows to a Table............................................................................................................................126
Managing SQLFire Transactions...............................................................................................................127
Perfor ming Batch Updates........................................................................................................................128
Generic Coding with the SQLFire ADO.NET Driver..................................................................................129
Chapter 22: Using SQLFire.NET Designer...........................................133
Installing SQLFire.NET Designer..............................................................................................................133
v
Connecting to a SQLFire Distributed System...........................................................................................133
Editing Tables............................................................................................................................................134
Chapter 23: Understanding the Data Consistency Model...................135
Data Consistency Concepts.....................................................................................................................135
No Ordering Guarantee for DML in Separate Threads.............................................................................136
Updates on Any Row Are Atomic and Isolated.........................................................................................136
Atomicity for Bulk Updates........................................................................................................................136
Chapter 24: Using Distributed Transactions in Your Applications.....137
Overview of SQLFire Distributed Transactions.........................................................................................137
Sequence of Events for a Distributed Transaction....................................................................................140
SQLFire Transaction Design.....................................................................................................................141
Best Practices for Using Transactions.......................................................................................................141
Transaction Functionality and Limitations.................................................................................................142
Chapter 25: Using Data-Aware Stored Procedures.............................143
Configuring a Procedure...........................................................................................................................143
Configuring a Custom Result Processor...................................................................................................144
Invoking a Procedure................................................................................................................................145
Example JDBC Client...............................................................................................................................146
Chapter 26: Using the Procedure Provider API....................................149
Procedure Parameters..............................................................................................................................149
Populating OUT and INOUT Parameters..................................................................................................150
Populating Result Sets..............................................................................................................................150
Using the <local> and <global> Escape Syntax with Nested Queries......................................................152
Chapter 27: Using the Custom Result Processor API.........................153
Implementing the ProcedureResultProcessor Interface ..........................................................................153
Example Result Processor: MergeSort.....................................................................................................153
Chapter 28: Programming User-Defined Types....................................157
Chapter 29: Using Result Sets and Cursors........................................161
Non-updatable, Forward-Only Result Sets...............................................................................................161
Updatable Result Sets..............................................................................................................................162
Scrollable Insensitive Result Sets.............................................................................................................167
Result Sets and Autocommit.....................................................................................................................168
Holdable Result Sets................................................................................................................................168
Part IV: Caching Data with vFabric SQLFire.........................................171
Chapter 30: SQLFire Cache Strategies.................................................173
vFabric SQLFire User's Guidevi
Contents
Chapter 31: Using a RowLoader to Load Existing Data......................175
How SQLFire Invokes a RowLoader.........................................................................................................175
Implementing the RowLoader Interface....................................................................................................176
Using the JDBCRowLoader Example.......................................................................................................176
Chapter 32: Evicting Table Data from SQLFire.....................................179
How LRU Eviction Works..........................................................................................................................179
Limitations of Eviction...............................................................................................................................179
Eviction in Partitioned Tables....................................................................................................................180
Create a Table with Eviction Settings........................................................................................................180
Chapter 33: Handling DML Events Synchronously.............................183
Writer and Listener Cache Plug-ins..........................................................................................................183
Example Writer Implementation................................................................................................................184
Example Listener Implementation............................................................................................................184
Chapter 34: Handling DML Events Asynchronously..........................185
How the AsyncEventListener Works.........................................................................................................185
Implementation Requirements..................................................................................................................186
Implementing an AsyncEventListener.......................................................................................................186
Chapter 35: Using DBSynchronizer to Apply DML to an RDBMS......191
How DBSynchronizer Works.....................................................................................................................191
Restrictions and Limitations......................................................................................................................192
Configuring DBSynchronizer....................................................................................................................193
Chapter 36:Suppressing Event Callbacks for a vFabric SQLFire Connection.197
Part V: Deploying vFabric SQLFire........................................................199
Chapter 37: SQLFire Deployment Models............................................201
Embedded Peer-to-Peer Deployment.......................................................................................................202
Client-Server Deployment.........................................................................................................................204
Multi-site Deployment...............................................................................................................................205
Chapter 38: Steps to Plan and Configure a Deployment....................209
Chapter 39: Configuring Discovery Mechanisms................................211
Using Locators..........................................................................................................................................211
Configure Multicast Discovery..................................................................................................................213
Chapter 40: Star ting and Configuring SQLFire Servers.....................215
Start and Stop SQLFire Ser vers Using sqlf..............................................................................................215
Specify the Server Working Directory.......................................................................................................216
vii
Specify Client Connection Information......................................................................................................216
Define Server Groups...............................................................................................................................216
Execute SQL When You Start a Server.....................................................................................................217
Using Additional Boot Properties..............................................................................................................217
Chapter 41: Configuring Multi-site (WAN) Deployments.....................219
About Gateways........................................................................................................................................219
About High Availability for WAN Deployments..........................................................................................221
Limitations of Multi-Site Replication..........................................................................................................222
Prerequisites for WAN Replication............................................................................................................223
Steps to Configure a Multi-site Deployment..............................................................................................223
Chapter 42: Configuring Authentication and Authorization...............229
Configuring User Authentication...............................................................................................................229
User Names in Authentication and Authorization ....................................................................................237
Configuring User Authorization.................................................................................................................238
Configuring Network Encryption and Authentication with SSL/TLS..........................................................242
Part VI: Managing and Monitoring vFabric SQLFire............................247
Chapter 43: Configuring and Using SQLFire Log Files.......................249
Log Message Format................................................................................................................................249
Severity Levels..........................................................................................................................................249
Using java.util.logging.Logger for Application Log Messages...................................................................250
Using Trace Flags for Advanced Debugging.............................................................................................250
Chapter 44: Querying SQLFire System Tables and Indexes...............253
Getting Information About SQLFire Members..........................................................................................253
Getting Information About User Tables.....................................................................................................254
Chapter 45:Evaluating Query Execution Plans and Query Statistics.259
Capture the Query Execution Plan for Individual Statements...................................................................259
Capture Query Execution Plans for All Statements..................................................................................260
View Query Plans and Statistics...............................................................................................................260
SQLFire Query Execution Plan Codes.....................................................................................................262
Chapter 46: Overriding Optimizer Choices..........................................265
Chapter 47: Evaluating System and Application Performance..........269
Collecting System Statistics......................................................................................................................269
Collecting Application Statistics................................................................................................................270
Using VSD to Analyze Statistics...............................................................................................................270
Chapter 48: Using Java Management Extensions (JMX)....................281
Overview of the vFabric SQLFire JMX Agent...........................................................................................281
vFabric SQLFire User's Guideviii
Contents
Example Configuration.............................................................................................................................281
Starting the JMX Agent.............................................................................................................................282
Stopping the JMX Agent...........................................................................................................................286
Configuring JMX Connectors and Adapters.............................................................................................286
SSL Communication Properties................................................................................................................288
vFabric SQLFire MBeans..........................................................................................................................288
Chapter 49: Best Practices for Tuning Performance...........................291
Tune Application Logic..............................................................................................................................291
Reduce Distribution Overhead..................................................................................................................291
Reduce Overhead of Eviction to Disk.......................................................................................................292
Minimize Update Latency for Replicated Tables.......................................................................................292
Tune FabricServers...................................................................................................................................292
Tuning Disk I/O.........................................................................................................................................293
Running SQLFire in Virtualized Environments..........................................................................................294
Chapter 50:Detecting and Handling Network Segmentation ("Split Brain").295
Part VII: vFabric SQLFire Reference......................................................297
Chapter 51: Configuration Properties..................................................299
Chapter 52: JDBC API............................................................................331
Mapping java.sql.Types to SQL Types......................................................................................................331
java.sql.BatchUpdateException Class......................................................................................................332
java.sql.Connection Interface....................................................................................................................332
java.sql.DatabaseMetaData Interface.......................................................................................................333
java.sql.Driver Interface............................................................................................................................335
java.sql.DriverManager.getConnection Method........................................................................................335
java.sql.PreparedStatement Interface.......................................................................................................335
java.sql.ResultSet Interface......................................................................................................................337
java.sql.SavePoint Class...........................................................................................................................337
java.sql.SQLException Class....................................................................................................................337
java.sql.Statement Class..........................................................................................................................337
javax.sql.XADataSource...........................................................................................................................337
Chapter 53: sqlf Launcher Commands................................................339
sqlf backup................................................................................................................................................340
sqlf compact-all-disk-stores......................................................................................................................343
sqlf compact-disk-store.............................................................................................................................344
sqlf encrypt-password...............................................................................................................................345
sqlf install-jar.............................................................................................................................................345
sqlf list-missing-disk-stores.......................................................................................................................348
sqlf locator................................................................................................................................................349
sqlf Logging Support.................................................................................................................................352
ix
sqlf merge-logs.........................................................................................................................................353
sqlf remove-jar..........................................................................................................................................353
sqlf replace-jar..........................................................................................................................................355
sqlf revoke-missing-disk-store...................................................................................................................358
sqlf server.................................................................................................................................................359
sqlf shut-down-all......................................................................................................................................368
sqlf stats....................................................................................................................................................369
sqlf validate-disk-store..............................................................................................................................372
sqlf version................................................................................................................................................373
sqlf write-data-dtd-to-file...........................................................................................................................373
sqlf write-data-to-db..................................................................................................................................376
sqlf write-data-to-xml................................................................................................................................380
sqlf write-schema-to-db............................................................................................................................384
sqlf write-schema-to-sql............................................................................................................................388
sqlf write-schema-to-xml...........................................................................................................................392
Chapter 54: sqlf Interactive Commands...............................................397
absolute ...................................................................................................................................................397
after last ...................................................................................................................................................398
async .......................................................................................................................................................399
autocommit ..............................................................................................................................................399
before first ................................................................................................................................................400
close ........................................................................................................................................................400
commit .....................................................................................................................................................401
connect ....................................................................................................................................................401
connect client ...........................................................................................................................................402
connect peer ............................................................................................................................................403
describe ...................................................................................................................................................403
disconnect ................................................................................................................................................404
driver ........................................................................................................................................................404
elapsedtime .............................................................................................................................................405
execute ....................................................................................................................................................406
exit ...........................................................................................................................................................407
first............................................................................................................................................................407
get scroll insensitive cursor.......................................................................................................................408
GetCurrentRowNumber ...........................................................................................................................410
help ..........................................................................................................................................................410
last ...........................................................................................................................................................410
LocalizedDisplay.......................................................................................................................................411
MaximumDisplayWidth.............................................................................................................................412
next...........................................................................................................................................................412
prepare ....................................................................................................................................................413
previous....................................................................................................................................................414
protocol.....................................................................................................................................................415
relative......................................................................................................................................................415
remove......................................................................................................................................................416
vFabric SQLFire User's Guidex
Contents
rollback......................................................................................................................................................417
run.............................................................................................................................................................418
set connection...........................................................................................................................................418
show..........................................................................................................................................................419
wait for......................................................................................................................................................422
Chapter 55: SQLFire API........................................................................423
CredentialInitializer...................................................................................................................................424
UserAuthenticator.....................................................................................................................................424
Procedure Implementation Interfaces.......................................................................................................424
Procedure Result Processor Interfaces....................................................................................................426
Chapter 56: SQL Language Reference.................................................431
Keywords and Identifiers...........................................................................................................................431
SQL Statements.......................................................................................................................................432
SQL Clauses.............................................................................................................................................489
SQL Expressions......................................................................................................................................493
JOIN Operations.......................................................................................................................................506
Built-in Functions......................................................................................................................................508
Built-in System Procedures......................................................................................................................550
Data Types................................................................................................................................................573
SQL Standards Conformance...................................................................................................................584
Chapter 57: System Tables.....................................................................605
ASYNCEVENTLISTENERS table.............................................................................................................605
GATEWAYRECEIVERS table...................................................................................................................606
GATEWAYSENDERS table.......................................................................................................................607
MEMBERS system table...........................................................................................................................608
MEMORYANALYTICS system table..........................................................................................................609
STATEMENTPLANS system table............................................................................................................609
SYSALIASES system table.......................................................................................................................610
SYSCHECKS system table.......................................................................................................................610
SYSCOLPERMS system table.................................................................................................................611
SYSCOLUMNS system table....................................................................................................................612
SYSCONGLOMERATES system table.....................................................................................................613
SYSCONSTRAINTS system table............................................................................................................613
SYSDEPENDS system table....................................................................................................................614
SYSDISKSTORES system table..............................................................................................................615
SYSFILES system table............................................................................................................................615
SYSFOREIGNKEYS system table............................................................................................................616
SYSKEYS system table............................................................................................................................616
SYSROLES system table.........................................................................................................................617
SYSROUTINEPERMS system table.........................................................................................................618
SYSSCHEMAS system table....................................................................................................................619
SYSSTATEMENTS system table..............................................................................................................619
SYSSTATISTICS system table..................................................................................................................620
xi
SYSTABLEPERMS system table..............................................................................................................620
SYSTABLES system table........................................................................................................................622
SYSTRIGGERS system table...................................................................................................................624
SYSVIEWS system table..........................................................................................................................625
Chapter 58: Exception Messages and SQL States..............................627
Chapter 59: ADO.NET Driver Reference...............................................631
SQLFire Data Types in ADO.NET.............................................................................................................631
VMware.Data.SQLFire.BatchUpdateException........................................................................................631
VMWare.Data.SQLFire.SQLFClientConnection.......................................................................................631
VMware.Data.SQLFire.SQLFCommand...................................................................................................636
VMware.Data.SQLFire.SQLFCommandBuilder........................................................................................642
VMware.Data.SQLFire.SQLFType............................................................................................................642
VMware.Data.SQLFire.SQLFDataAdapter...............................................................................................643
VMware.Data.SQLFire.SQLFDataReader................................................................................................645
VMware.Data.SQLFire.SQLFException....................................................................................................647
VMware.Data.SQLFire.SQLFParameter...................................................................................................648
VMware.Data.SQLFire.SQLFParameterCollection...................................................................................648
VMware.Data.SQLFire.SQLFTransaction.................................................................................................649
Chapter 60: vFabric SQLFire Limitations.............................................653
SQL Language Limitations.......................................................................................................................653
ADO.NET Driver Limitations.....................................................................................................................660
Troubleshooting Common Problems........................................................................663
vFabric SQLFire Glossary..........................................................................................665
vFabric SQLFire User's Guidexii
About the SQLFire User's Guide
Revised July 31, 2012.
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 vFabric Suite
supported configurations, as well as additional configurations supported by standalone vFabric SQLFire.
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_64, x86_32Red Hat EL 5
ProductionJava SE 6, update 0_26x86_64, x86_32Red Hat EL 6
ProductionJava SE 6, update 0_26x86_64, x86_32Windows Server 2003 R2
ProductionJava SE 6, update 0_26x86_64, x86_32Windows Server 2008 R2
DeveloperJava SE 6, update 0_26x86_64, x86_32Windows 7 SP1
DeveloperJava SE 6x86_64Windows XP
DeveloperJava SE 6x86_64Ubuntu 10.x, 11.x**
*The Microsoft Loopback Adapter is not supported.
**Only stable and non-EOL releases 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:
sysctl -w net.core.rmem_max=1048576
sysctl -w net.core.wmem_max=1048576
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.
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.
vFabric SQLFire User's Guide16
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:
• 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
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.
3
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 Guide4
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.
5
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 27 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 215
• Using Locators on page 211
• Start a SQLFire Peer with the Peer Client JDBC Driver on page 107
• Overview of the vFabric SQLFire JMX Agent on page 281
7
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 105 and Developing ADO.NET Client Applications on page 117.
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
47 provides additional information.
For more information, see:
• Starting and Configuring SQLFire Servers on page 215
• Using Locators on page 211
• Start a SQLFire Peer with the Peer Client JDBC Driver on page 107
• Using Server Groups to Manage Data on page 47
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 211 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 Guide8
Understanding the SQLFire Distributed System
with certain consistency guarantees. Each member in the group participates in membership decisions, which
ensures that either all members see a new member or no members see it.
The membership coordinator, a key component of the GMS, handles "join" and "lea ve" requests, and also handles
members that are suspected of having left the system. The system automatically elects the oldest member of the
distributed system to act as the coordinator, and it elects a new one if the member fails or is unreachable. The
coordinator's basic purpose is to relay the current membership view to each member of the distributed system
and to ensure the consistency of the view at all times.
Because the SQLFire distributed system is dynamic, you can add or remove members in a very short time period.
This makes it easy to reconfigure the system to handle added demand (load).The GMS permits the distributed
system to progress under conditions in which a statically-defined membership system could not. A static model
defines members by host and identity, which mak es it difficult to add or remove members in an acti ve distributed
system. The system would have to shut do wn partially or completely to expand or contract the number of members
that participate in the system.
For more information, see:
• Start and Stop SQLFire Servers Using sqlf on page 215
• Connect to a Distributed System Using Locators on page 213
• Rebalancing Partitioned Data on SQLFire Members on page 59
Replicated Tables and Partitioned Tables
Tables in SQLFire can be partitioned or replicated. A replicated table keeps a copy of its entire data set locally
on every SQLFire server in its server group. A partitioned table manages large volumes of data by partitioning
it into manageable chunks and distributing those chunks across all members in the table's server group.
By default, all tables are replicated unless you specify partitioning in the CREATE TABLE statement. (You can
change the default behavior to create partitioned tables using the table-default-partitioned boot
property). The schema information for all SQLFire objects is visible at all times to all peer members of the
distributed system including peer clients, but excluding standalone locators.
Partitioning Tables on page 51 and Replicating Tables on page 61 provide more information.
Parallel Execution of Data-Aware Stored Procedures
In a traditional relational database, stored procedures are application routines that are stored as part of the data
dictionary and executed on the database system itself. Stored procedures generally offer high performance
because they execute in close proximity to data required by the application logic. SQLFire extends this basic
stored procedure capability to support parallel execution of application logic on table data that is partitioned
across many peers.
SQLFire applications can execute stored procedures on specific data hosts, in parallel on all the members of a
server group, or can target specific members based on the data requirements for the procedure. Essentially,
application behavior that is encapsulated in stored procedures is moved to the process that hosts the associated
data set, and it is executed there. If the required data set is spread across multiple partitions, the procedure is
executed in parallel on the partition members. Results are streamed to a coordinating member and aggregated
for the client invoking the procedure.
For example, consider an 'Order' table that is partitioned by its 'customer_id', and an application wanting to
execute an expensive 'credit check' for several customers. Assume the credit test requires iteration over all the
order history. You can parallelize the execution on all members that manage data for these customers and stream
the results to the client. All order history required by each execution is locally available in-process.
// typical procedure call
CallableStatement callableStmt = connection.prepareCall("{CALL
9
Getting Started with vFabric SQLFire
order_credit_check(?) ");
callableStmt.setArray(1, <list of customer IDs>);
// SQLFire data-aware procedure invocation
CallableStatement callableStmt = connection.prepareCall("{CALL
order_credit_check() "
+ "ON TABLE Orders WHERE customerID IN (?)}");
callableStmt.setArray(1, <list of customer IDs>);
// order_credit_check will be executed in parallel on all members where the
orders
// corresponding to the customerIDs are managed
For more information, see:
• Using Data-Aware Stored Procedures on page 143
• Using the Procedure Provider API on page 149
• Using the Custom Result Processor API on page 153
Cache Plug-ins for External Data Connections
SQLFire is commonly used as a distributed SQL cache in an embedded (peer client) or client-server configuration.
It provides a plug-in framework to connect the cache to external data sources such as another relational database.
Several cache plug-ins are supported:
• Reading from Backend on a Miss ("read through")
When SQLFire is used as a cache, applications can configure a SQL RowLoader that is triggered to load the
data from a backend repository on a miss in SQLFire. When an incoming query request for a uniquely identified
row cannot be satisfied by the distributed cache, the loader is invoked to retrieve the data from an external
source. SQLFire locks the associated row and prevents concurrent readers trying to fetch the same row from
bombarding the backend database.
Note: When SQLFire is used as a "pure" cache (that is, some or all tables are configured with LRU
eviction, and only the actively used subset of data is cached in SQLFire), queries on these tables can be
based only on the primary key. Only primary key-based queries invoke a configured loader. Any other
query potentially could produce inconsistent results from the cache.
See Using a RowLoader to Load Existing Data on page 175.
• Synchronous Write Back to Backend ("write through")
When data is strictly managed in memory, even with replication, a failure of the entire cluster means loss of
data. With synchronous write-through, all changes can be synchronously written to the backend repository
before the cache is changed. If and only if the write-through succeeds, the data becomes visible in the cache.
You configure synchronous write-through with a SQLFire "cache writer". See Handling DML Events
Synchronously on page 183.
• Asynchronous Write Back to Backend ("write behind")
If synchronous writes to the backend are too costly, the application can configure the use of a "write behind
cache listener". SQLFire supports several options for how the events are queued, batched, and written to the
database of record. It is designed for very high reliability and handles many failure conditions. Persistent queues
ensure writes even when the backend database is temporarily unavailable. You can order event delivery or
batch it based on a time interval. You can also conflate a certain number of rows and continuous updates to a
single update, to reduce the load on the backend database of an 'update heavy' system. See Handling DML
Events Asynchronously on page 185.
vFabric SQLFire User's Guide10
Chapter 3
Installing vFabric SQLFire
You can install vFabric SQLFire from the VMware yum repository (RHEL only) or from a downloaded JAR file.
Installation procedures also vary according to whether you obtained vFabric SQLFire as a standalone product or as
part of VMware® vFabric Suite™Advanced.
Installation Note for vFabric Suite Customers
vFabric SQLFire is available as a standalone product installation, and as part of vF abric Suite Advanced. vF abric
Suite is a set of runtime components that let you build, scale, and run modern, agile applications in virtual
environments.
If you obtain SQLFire standalone, you can install it on physical or virtual machines according to the procedures
in this document.
If you obtain SQLFire as part of vFabric Advanced, you install it exclusively on VMware virtual machines that
run on vSphere. The vFabric Suite install process adds the vFabric License Server to an e xisting vCenter serv er.
The vFabric License Server accepts a network license, actually a pool of licenses for vFabric components, which
makes it easy to manage the licensing of all components installed on the VMs.
If you obtain SQLFire as part of vFabric Suite Advanced, first complete the license activation and installation
procedures in Getting Started with vFabric Suite . Then follow procedures in this document to set up your
environment for SQLFire and complete any remaining SQLFire-specific installation and configuration tasks.
RHEL Only: Install vFabric SQLFire from the VMWare YUM Repository
If your operating system is Red Hat Enterprise Linux (RHEL), you have the option to use yum to install and
manage vFabric SQLFire instead of using the JAR file installer.
The yum installation process can be used whether you have purchased vFabric Suite or only the vFabric SQLFire
product. vFabric Suite users must first install the vFabric License Server in the vCenter environment before
installing components. See Getting Started with vFabric Suite for more information.
Prerequisites
See Supported Configurations and System Requirements on page 15 for specific host machine requirements for
Linux systems.
Procedure
1. Log in as the root user to the RHEL VM on which you are going to install the vFabric component and start
a terminal.
2. Run the rpm command to install both the vfabric-5-repo and vfabric-all-repo RPMs from the VMware
repository.
11
Getting Started with vFabric SQLFire
For RHEL 5:
rpm -Uvh
http://repo.vmware.com/pub/rhel5/vfabric/5.1/vfabric-5.1-repo-5.1-1.noarch.rpm
rpm -Uvh
http://repo.vmware.com/pub/rhel5/vfabric-all/vfabric-all-repo-1-1.noarch.rpm
For RHEL 6:
rpm -Uvh
http://repo.vmware.com/pub/rhel6/vfabric/5.1/vfabric-5.1-repo-5.1-1.noarch.rpm
rpm -Uvh
http://repo.vmware.com/pub/rhel6/vfabric-all/vfabric-all-repo-1-1.noarch.rpm
3.
Execute the yum install command to install vFabric SQLFire:
yum install vfabric-sqlfire
Note: The yum install command installs the most recent version of the vFabric SQLFire RPM
that it finds in all installed repositories. If you want to install another version, you must explicitly
specify the version with the yum install command. Use yum search vfabric-sqlfire
--showduplicates to find all versions that are available in the installed repositories.
The yum command begins the install process, resolves dependencies, and displays the packages it plans to
install.
If this is the first time that you install a vFabric component on the VM, the yum command also installs the
vfabric-eula RPM and prompts you to accept the VMware license agreement.
4.
Enter y at the prompt to begin the actual installation.
5. The installer may prompt you to install a GNU Privacy Guard (GPG) k ey for the vFabric repositories. Enter
y at each prompt to continue the installation.
6.
Press Space to scroll and read the license agreement. When you reach the end of the agreement, enter yes
to accept the license.
The installer displays Complete! when the installation process is finished.
7. If you have not already done so, download and install a compatible JDK or JRE on the RHEL computer or
VM.
8.
Optionally, specify that the vfabric-sqlfire process should automatically start when the operating
system starts by running the following command:
chkconfig --level 35 vfabric-sqlfire on
9.
Optionally, specify the configuration of the vfabric-sqlfire process by editing the file
/etc/sysconfig/sqlfire, which is the file sourced by the script that you will later use to start the
SQLFire process (/etc/init.d/vfabric-sqlfire.)
The /etc/sysconfig/sqlfire file includes many comments to help you decide whether you need to
modify it. Here are additional pointers:
• If you do not modify the /etc/sysconfig/sqlfire file but simply use the one installed by default, the
vfabric-sqlfire process starts up a server instance in a multicast configuration.
• If you want the vfabric-sqlfire process to start up using a locator-based configuration, change the LOCATOR
property in the /etc/sysconfig/sqlfire file to local, as shown:
LOCATOR=local
This configuration allows a local locator process to start along with a local server instance. To add additional
remote locators, add their IP address and port to the LOCA T OR_IP_STRING as shown in the configuration
file as a commented-out example.
vFabric SQLFire User's Guide12
Installing vFabric SQLFire
• If you want to start up only a local locator process and not a local server instance, set the LOCATOR
property to locator_only. This sets up a redundant locator configuration; be sure you add the locator IP
addresses and port numbers to the LOCATOR_IP_STRING; an example is shown in the configuration file.
• Finally, set the LOCATOR property to remote if you want to start a local server instance that relies on
having locator processes running on one or more remote hosts. Specify these remote hosts using the
LOCATOR_IP_STRING property.
10. Start the processes associated with SQLFire by running the following command:
/etc/init.d/vfabric-sqlfire start
By default, the process uses an evaluation license; if you ha ve purchased a production license, see Acti vating
vFabric SQLFire Licensing on page 15 for information about configuring it in the
/opt/vmware/vfabric-sqlfire/vFabric_SQLFire_10x/sqlfire.properties file. The
RPM installation process creates a skeleton sqlfire.properties file to get you started.
T o stop, restart, and get status about the processes, pass the stop, restart, and status parameters, respectively,
to the /etc/init.d/vfabric-sqlfire script:
/etc/init.d/vfabric-sqlfire status
Install vFabric SQLFire from a JAR File
This procedure describes how to install the SQLFire software on a single computer or VM, for either a peer
client or client/server deployments. Repeat the procedure to install and license SQLFire on each physical or
virtual machine where you want to run a SQLFire member.
Prerequisites
1. Confirm that your system meets the hardware and software requirements described in Supported Configurations
and System Requirements on page 15.
2. Download SQLFire from the VMware downloads page.
Procedure
1. The SQLFire installation program is distributed as an executable JAR file, and it requires Java SE 6 or higher
JVM to run. Ensure that the path to a supported Java executable is in your PATH environment variable:
java -version
2. Change to the directory where you downloaded the SQLFire software, and execute the JAR file installer:
java -jar vFabric_SQLFire_10x_Installer.jar
Use the actual filename of the installer you downloaded (for example,
vFabric_SQLFire_103_Installer.jar).
3.
Press Enter to scroll and read the license agreement. When you reach the end of the agreement, enter agree
to accept the license.
4. Enter the full path where you want to install SQLFire. By default, SQLFire is installed in the directory where
you executed the JAR file.
5.
Enter yes to create the directory if necessary, or to verify that the installation directory is correct.
The installer copies the license agreement and installs SQLFire to a top-level vFabric_SQLFire_10x
directory in the location you specified.
6. Configure your SQLFire license, if you have purchased a production license. Activating vFabric SQLFire
Licensing on page 15 provides more information.
13
Getting Started with vFabric SQLFire
7.
To begin using the sqlf utility to start servers or execute other commands, add the
vFabric_SQLFire_10x/bin directory to your path. For example:
export PATH=$PATH:/vFabric_SQLFire_10x/bin
The sqlf script automatically sets the class path relative to the installation directory.
8. Repeat this procedure to install and license SQLFire on each different computer where you want to run a
SQLFire member.
vFabric SQLFire User's Guide14
Loading...