VMware vFabric SQLFire - 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...................................................................................26
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........................................................................................................................32
Chapter 6: Connect to vFabric SQLFire with JDBC Tools.....................33
Chapter 7: Tutorials..................................................................................35
Main Steps..................................................................................................................................................35
iii
Create a SQLFire Cluster...........................................................................................................................35
Connect to the Cluster Using SQLF...........................................................................................................37
Create Replicated Tables and Execute Quer ies.........................................................................................38
Implement a Partitioning Strategy...............................................................................................................39
Persist Tables to Disk..................................................................................................................................41
Add Servers to the Cluster and Stop Servers.............................................................................................43
Perfor m Additional Tasks............................................................................................................................44
Chapter 8: vFabric SQLFire Features and Benefits...............................45
Part II: Managing Your Data in vFabric SQLFire.....................................47
Chapter 9: Designing vFabric SQLFire Databases................................49
Design Principles of Scalable, Partition-Aware Databases.........................................................................49
Identify Entity Groups and Partitioning Keys...............................................................................................49
Replicate Code Tables................................................................................................................................50
Dealing with Many-to-Many Relationships..................................................................................................50
Example: Adapting a Database Schema for SQLFire.................................................................................51
Chapter 10: Using Server Groups to Manage Data................................55
Server Groups Overview.............................................................................................................................55
Adding Members to Server Groups............................................................................................................57
Assigning Tables to Server Groups.............................................................................................................58
Chapter 11: Partitioning Tables...............................................................59
How Table Par titioning Wor ks.....................................................................................................................59
Understanding Where Data Is Stored.........................................................................................................60
Failure and Redundancy.............................................................................................................................61
Creating Partitioned Tables.........................................................................................................................62
Rebalancing Partitioned Data on SQLFire Members..................................................................................66
Managing Replication Failures....................................................................................................................67
Chapter 12: Replicating Tables................................................................69
How SQLFire Replicates Tables.................................................................................................................69
Deciding When to Use Replicated Tables...................................................................................................69
Creating Replicated Tables.........................................................................................................................70
Chapter 13: Estimating Memory Requirements.....................................71
Estimating SQLFire Overhead....................................................................................................................71
Viewing Memory Usage in SYS.MEMORYANALYTICS..............................................................................72
Chapter 14: Using Disk Stores to Persist Data......................................77
Overview of Disk Stores..............................................................................................................................77
Guidelines for Designing Disk Stores.........................................................................................................83
Creating a Disk Store or Using the Default.................................................................................................84
vFabric SQLFire User's Guideiv
Contents
Persist Table Data to a Disk Store...............................................................................................................84
Optimizing Availability and Performance.....................................................................................................85
Starting System with Disk Stores................................................................................................................85
Disk Store Management.............................................................................................................................87
Chapter 15: Expor ting and Importing Data with vFabric SQLFire.......97
Using SQLF Commands to Export and Impor t Data..................................................................................97
Using Apache DdlUtils to Import Data........................................................................................................99
Exporting and Importing Data from Text Files...........................................................................................102
Chapter 16:Using T able Functions to Import Data as a SQLFire T ables.103
Overview of SQLFire Table Functions.......................................................................................................103
Example Table Function............................................................................................................................105
Part III: Developing Applications with SQLFire....................................107
Chapter 17:Starting SQLFire Servers with the FabricServer Interface.109
Starting a Network Ser ver........................................................................................................................110
Chapter 18: Developing Java Clients and Peers..................................113
Connect to a SQLFire Server with the Thin Client JDBC Driver...............................................................113
Start a SQLFire Peer with the Peer Client JDBC Driver...........................................................................116
Chapter 19: Configuring SQLFire as a JDBC Datasource..................119
Chapter 20: Storing and Loading JAR Files in SQLFire......................121
Class Loading Overview...........................................................................................................................121
Alternate Methods for Managing JAR Files..............................................................................................123
Chapter 21: Developing ADO.NET Client Applications.......................127
About the ADO.NET Driver.......................................................................................................................127
ADO.NET Driver Classes..........................................................................................................................128
Installing and Using the ADO.NET driver..................................................................................................129
Connecting to SQLFire with the ADO.NET Driver....................................................................................129
Managing Connections.............................................................................................................................130
Executing SQL Commands.......................................................................................................................130
Working with Result Sets..........................................................................................................................131
Storing a Table..........................................................................................................................................132
Storing Multiple Tables..............................................................................................................................133
Specifying Command Parameters with SQLFParameter..........................................................................134
Updating Row Data...................................................................................................................................135
Adding Rows to a Table............................................................................................................................136
Managing SQLFire Transactions...............................................................................................................137
Perfor ming Batch Updates........................................................................................................................138
Generic Coding with the SQLFire ADO.NET Driver..................................................................................139
v
Chapter 22: Using SQLFire.NET Designer...........................................143
Installing SQLFire.NET Designer..............................................................................................................143
Connecting to a SQLFire Distributed System...........................................................................................143
Editing Tables............................................................................................................................................144
Chapter 23: Understanding the Data Consistency Model...................145
Data Consistency Concepts.....................................................................................................................145
No Ordering Guarantee for DML in Separate Threads.............................................................................146
Updates on Any Row Are Atomic and Isolated.........................................................................................146
Atomicity for Bulk Updates........................................................................................................................146
Chapter 24: Using Distributed Transactions in Your Applications.....147
Overview of SQLFire Distributed Transactions.........................................................................................147
Sequence of Events for a Distributed Transaction....................................................................................150
SQLFire Transaction Design.....................................................................................................................151
Best Practices for Using Transactions.......................................................................................................151
Transaction Functionality and Limitations.................................................................................................152
Chapter 25: Using Data-Aware Stored Procedures.............................153
Configuring a Procedure...........................................................................................................................153
Configuring a Custom Result Processor...................................................................................................154
Invoking a Procedure................................................................................................................................155
Example JDBC Client...............................................................................................................................156
Chapter 26: Using the Procedure Provider API....................................159
Procedure Parameters..............................................................................................................................159
Populating OUT and INOUT Parameters..................................................................................................160
Populating Result Sets..............................................................................................................................160
Using the <local> and <global> Escape Syntax with Nested Queries......................................................162
Chapter 27: Using the Custom Result Processor API.........................163
Implementing the ProcedureResultProcessor Interface ..........................................................................163
Example Result Processor: MergeSort.....................................................................................................163
Chapter 28: Programming User-Defined Types....................................167
Chapter 29: Using Result Sets and Cursors........................................171
Non-updatable, Forward-Only Result Sets...............................................................................................171
Updatable Result Sets..............................................................................................................................172
Scrollable Insensitive Result Sets.............................................................................................................177
Result Sets and Autocommit.....................................................................................................................178
Part IV: Caching Data with vFabric SQLFire.........................................179
vFabric SQLFire User's Guidevi
Contents
Chapter 30: SQLFire Cache Strategies.................................................181
Chapter 31: Using a RowLoader to Load Existing Data......................183
How SQLFire Invokes a RowLoader.........................................................................................................183
Implementing the RowLoader Interface....................................................................................................184
Using the JDBCRowLoader Example.......................................................................................................184
Chapter 32: Evicting Table Data from SQLFire.....................................187
How LRU Eviction Works..........................................................................................................................187
Limitations of Eviction...............................................................................................................................187
Eviction in Partitioned Tables....................................................................................................................188
Create a Table with Eviction Settings........................................................................................................188
Chapter 33: Handling DML Events Synchronously.............................191
Writer and Listener Cache Plug-ins..........................................................................................................191
Example Writer Implementation................................................................................................................192
Example Listener Implementation............................................................................................................192
Chapter 34: Handling DML Events Asynchronously..........................193
How the AsyncEventListener Works.........................................................................................................193
Implementation Requirements..................................................................................................................193
Implementing an AsyncEventListener.......................................................................................................194
Chapter 35: Using DBSynchronizer to Apply DML to an RDBMS......199
How DBSynchronizer Works.....................................................................................................................199
Restrictions and Limitations......................................................................................................................200
Configuring DBSynchronizer....................................................................................................................201
Chapter 36:Suppressing Event Callbacks for a vFabric SQLFire Connection.205
Part V: Deploying vFabric SQLFire........................................................207
Chapter 37: SQLFire Deployment Models............................................209
Embedded Peer-to-Peer Deployment.......................................................................................................210
Client-Server Deployment.........................................................................................................................212
Multi-site Deployment...............................................................................................................................213
Chapter 38: Steps to Plan and Configure a Deployment....................217
Chapter 39: Configuring Discovery Mechanisms................................219
Using Locators..........................................................................................................................................219
Configure Multicast Discovery..................................................................................................................221
vii
Chapter 40: Star ting and Configuring SQLFire Servers.....................223
Start and Stop SQLFire Ser vers Using sqlf..............................................................................................223
Specify the Server Working Directory.......................................................................................................224
Specify Client Connection Information......................................................................................................224
Define Server Groups...............................................................................................................................224
Execute SQL When You Start a Server.....................................................................................................225
Using Additional Boot Properties..............................................................................................................225
Chapter 41: Configuring Multi-site (WAN) Deployments.....................227
About Gateways........................................................................................................................................227
About High Availability for WAN Deployments..........................................................................................229
Limitations of Multi-Site Replication..........................................................................................................230
Prerequisites for WAN Replication............................................................................................................231
Steps to Configure a Multi-site Deployment..............................................................................................231
Chapter 42: Configuring Authentication and Authorization...............237
Configuring User Authentication...............................................................................................................237
User Names in Authentication and Authorization ....................................................................................245
Configuring User Authorization.................................................................................................................246
Configuring Network Encryption and Authentication with SSL/TLS..........................................................250
Part VI: Managing and Monitoring vFabric SQLFire............................255
Chapter 43: Configuring and Using SQLFire Log Files.......................257
Log Message Format................................................................................................................................257
Severity Levels..........................................................................................................................................257
Using java.util.logging.Logger for Application Log Messages...................................................................258
Using Trace Flags for Advanced Debugging.............................................................................................258
Chapter 44: Querying SQLFire System Tables and Indexes...............261
Getting Information About SQLFire Members..........................................................................................261
Getting Information About User Tables.....................................................................................................262
Chapter 45: Evaluating Query Plans and Query Statistics.................267
Capture a Query Plan for an Individual Statement...................................................................................267
Capture Query Plans for All Statements...................................................................................................268
Example Query Plan Analysis..................................................................................................................268
Query Plan Codes....................................................................................................................................271
Chapter 46: Overriding Optimizer Choices..........................................275
Chapter 47: Evaluating System and Application Performance..........279
Collecting System Statistics......................................................................................................................279
Collecting Application Statistics................................................................................................................280
vFabric SQLFire User's Guideviii
Contents
Using VSD to Analyze Statistics...............................................................................................................280
Chapter 48: Using Java Management Extensions (JMX)....................291
Using a JMX Manager Node.....................................................................................................................291
Using a vFabric SQLFire JMX Agent........................................................................................................294
Chapter 49: Best Practices for Tuning Performance...........................307
Tune Application Logic..............................................................................................................................307
Reduce Distribution Overhead..................................................................................................................307
Reduce Overhead of Eviction to Disk.......................................................................................................308
Minimize Update Latency for Replicated Tables.......................................................................................308
Tune FabricServers...................................................................................................................................308
Tuning Disk I/O.........................................................................................................................................309
Running SQLFire in Virtualized Environments..........................................................................................310
Chapter 50:Detecting and Handling Network Segmentation ("Split Brain").311
Part VII: vFabric SQLFire Reference......................................................313
Chapter 51: Configuration Properties..................................................315
Chapter 52: JDBC API............................................................................351
Mapping java.sql.Types to SQL Types......................................................................................................351
java.sql.BatchUpdateException Class......................................................................................................352
java.sql.Connection Interface....................................................................................................................352
java.sql.DatabaseMetaData Interface.......................................................................................................353
java.sql.Driver Interface............................................................................................................................355
java.sql.DriverManager.getConnection Method........................................................................................355
java.sql.PreparedStatement Interface.......................................................................................................355
java.sql.ResultSet Interface......................................................................................................................357
java.sql.SavePoint Class...........................................................................................................................357
java.sql.SQLException Class....................................................................................................................357
java.sql.Statement Class..........................................................................................................................357
javax.sql.XADataSource...........................................................................................................................357
Chapter 53: sqlf Launcher Commands................................................359
sqlf backup................................................................................................................................................360
sqlf compact-all-disk-stores......................................................................................................................363
sqlf compact-disk-store.............................................................................................................................364
sqlf encrypt-password...............................................................................................................................365
sqlf install-jar.............................................................................................................................................365
sqlf list-missing-disk-stores.......................................................................................................................368
sqlf locator................................................................................................................................................369
sqlf Logging Support.................................................................................................................................374
sqlf merge-logs.........................................................................................................................................374
ix
sqlf remove-jar..........................................................................................................................................375
sqlf replace-jar..........................................................................................................................................377
sqlf revoke-missing-disk-store...................................................................................................................379
sqlf server.................................................................................................................................................380
sqlf show-disk-store-metadata..................................................................................................................391
sqlf shut-down-all......................................................................................................................................392
sqlf stats....................................................................................................................................................393
sqlf upgrade-disk-store.............................................................................................................................397
sqlf validate-disk-store..............................................................................................................................398
sqlf version................................................................................................................................................398
sqlf write-data-dtd-to-file...........................................................................................................................399
sqlf write-data-to-db..................................................................................................................................401
sqlf write-data-to-xml................................................................................................................................405
sqlf write-schema-to-db............................................................................................................................409
sqlf write-schema-to-sql............................................................................................................................413
sqlf write-schema-to-xml...........................................................................................................................417
Chapter 54: sqlf Interactive Commands...............................................423
absolute ...................................................................................................................................................423
after last ...................................................................................................................................................424
async .......................................................................................................................................................425
autocommit ..............................................................................................................................................425
before first ................................................................................................................................................426
close ........................................................................................................................................................426
commit .....................................................................................................................................................427
connect ....................................................................................................................................................427
connect client ...........................................................................................................................................428
connect peer ............................................................................................................................................429
describe ...................................................................................................................................................429
disconnect ................................................................................................................................................430
driver ........................................................................................................................................................430
elapsedtime .............................................................................................................................................431
execute ....................................................................................................................................................432
exit ...........................................................................................................................................................433
first............................................................................................................................................................433
get scroll insensitive cursor.......................................................................................................................434
GetCurrentRowNumber ...........................................................................................................................436
help ..........................................................................................................................................................436
last ...........................................................................................................................................................436
LocalizedDisplay.......................................................................................................................................437
MaximumDisplayWidth.............................................................................................................................438
next...........................................................................................................................................................438
prepare ....................................................................................................................................................439
previous....................................................................................................................................................440
protocol.....................................................................................................................................................441
relative......................................................................................................................................................441
vFabric SQLFire User's Guidex
Contents
remove......................................................................................................................................................442
rollback......................................................................................................................................................443
run.............................................................................................................................................................444
set connection...........................................................................................................................................444
show..........................................................................................................................................................445
wait for......................................................................................................................................................448
Chapter 55: SQLFire API........................................................................449
CredentialInitializer...................................................................................................................................450
UserAuthenticator.....................................................................................................................................450
Procedure Implementation Interfaces.......................................................................................................450
Procedure Result Processor Interfaces....................................................................................................452
Chapter 56: SQL Language Reference.................................................457
Keywords and Identifiers...........................................................................................................................457
SQL Statements.......................................................................................................................................458
SQL Queries.............................................................................................................................................517
SQL Clauses.............................................................................................................................................518
SQL Expressions......................................................................................................................................523
JOIN Operations.......................................................................................................................................536
Built-in Functions......................................................................................................................................538
Built-in System Procedures......................................................................................................................580
Data Types................................................................................................................................................604
SQL Standards Conformance...................................................................................................................615
Chapter 57: System Tables.....................................................................635
ASYNCEVENTLISTENERS.....................................................................................................................635
GATEWAYRECEIVERS............................................................................................................................636
GATEWAYSENDERS...............................................................................................................................637
INDEXES..................................................................................................................................................639
JARS.........................................................................................................................................................639
MEMBERS................................................................................................................................................639
MEMORYANALYTICS...............................................................................................................................641
STATEMENTPLANS.................................................................................................................................641
SYSALIASES............................................................................................................................................643
SYSCHECKS............................................................................................................................................644
SYSCOLPERMS......................................................................................................................................644
SYSCOLUMNS.........................................................................................................................................645
SYSCONGLOMERATES..........................................................................................................................647
SYSCONSTRAINTS.................................................................................................................................647
SYSDEPENDS.........................................................................................................................................648
SYSDISKSTORES....................................................................................................................................649
SYSFILES.................................................................................................................................................650
SYSFOREIGNKEYS.................................................................................................................................650
SYSKEYS.................................................................................................................................................651
SYSROLES...............................................................................................................................................651
xi
SYSROUTINEPERMS..............................................................................................................................652
SYSSCHEMAS.........................................................................................................................................653
SYSSTATEMENTS...................................................................................................................................654
SYSSTATISTICS.......................................................................................................................................654
SYSTABLEPERMS...................................................................................................................................655
SYSTABLES.............................................................................................................................................657
SYSTRIGGERS........................................................................................................................................658
SYSVIEWS...............................................................................................................................................660
Chapter 58: Exception Messages and SQL States..............................661
Chapter 59: ADO.NET Driver Reference...............................................665
SQLFire Data Types in ADO.NET.............................................................................................................665
VMware.Data.SQLFire.BatchUpdateException........................................................................................665
VMWare.Data.SQLFire.SQLFClientConnection.......................................................................................665
VMware.Data.SQLFire.SQLFCommand...................................................................................................670
VMware.Data.SQLFire.SQLFCommandBuilder........................................................................................676
VMware.Data.SQLFire.SQLFType............................................................................................................676
VMware.Data.SQLFire.SQLFDataAdapter...............................................................................................677
VMware.Data.SQLFire.SQLFDataReader................................................................................................679
VMware.Data.SQLFire.SQLFException....................................................................................................681
VMware.Data.SQLFire.SQLFParameter...................................................................................................682
VMware.Data.SQLFire.SQLFParameterCollection...................................................................................682
VMware.Data.SQLFire.SQLFTransaction.................................................................................................683
Chapter 60: vFabric SQLFire Limitations.............................................687
SQL Language Limitations.......................................................................................................................687
ADO.NET Driver Limitations.....................................................................................................................695
Troubleshooting Common Problems........................................................................697
vFabric SQLFire Glossary..........................................................................................699
vFabric SQLFire User's Guidexii
About the SQLFire User's Guide
Revised April 30, 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 35.
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 -include-admins -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 35.
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 35 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 223
• Using Locators on page 219
• Start a SQLFire Peer with the Peer Client JDBC Driver on page 116
• Using a vFabric SQLFire JMX Agent on page 294
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 113 and Developing ADO.NET Client Applications on page 127.
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
55 provides additional information.
For more information, see:
• Starting and Configuring SQLFire Servers on page 223
• Using Locators on page 219
• Start a SQLFire Peer with the Peer Client JDBC Driver on page 116
• Using Server Groups to Manage Data on page 55
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 219 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...