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.
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 provides the latest product updates. If you have 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.
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...............
Create a SQLFire Cluster...........................................................................................................................37 Connect to the Cluster Using SQLF...........................................................................................................39 Create Replicated Tables and Execute Queries.........................................................................................40 Implement a Partitioning Strategy..................................................
Contents Persist Table Data to a Disk Store...............................................................................................................86 Optimizing Availability and Performance.....................................................................................................87 Starting System with Disk Stores................................................................................................................87 Disk Store Management...........................................
Generic Coding with the SQLFire ADO.NET Driver..................................................................................143 Chapter 22: Using SQLFire.NET Designer...........................................147 Installing SQLFire.NET Designer..............................................................................................................147 Connecting to a SQLFire Distributed System...........................................................................................
Contents Chapter 30: SQLFire Cache Strategies.................................................185 Chapter 31: Using a RowLoader to Load Existing Data......................187 How SQLFire Invokes a RowLoader.........................................................................................................187 Implementing the RowLoader Interface....................................................................................................188 Using the JDBCRowLoader Example.........................
Chapter 40: Starting and Configuring SQLFire Servers.....................227 Start and Stop SQLFire Servers Using sqlf..............................................................................................227 Specify the Server Working Directory.......................................................................................................228 Specify Client Connection Information......................................................................................................
Contents Using VSD to Analyze Statistics...............................................................................................................284 Chapter 48: Using Java Management Extensions (JMX)....................295 Using a JMX Manager Node.....................................................................................................................295 Using a vFabric SQLFire JMX Agent.......................................................................................................
sqlf remove-jar..........................................................................................................................................383 sqlf replace-jar..........................................................................................................................................385 sqlf revoke-missing-disk-store...................................................................................................................387 sqlf run........................................
Contents relative......................................................................................................................................................454 remove......................................................................................................................................................454 rollback......................................................................................................................................................455 run...............
SYSROLES...............................................................................................................................................671 SYSROUTINEPERMS..............................................................................................................................672 SYSSCHEMAS.........................................................................................................................................673 SYSSTATEMENTS................................................
About the SQLFire User's Guide Revised August 22, 2013. The SQLFire User's Guide provides step-by-step procedures for installing, configuring, managing, and developing applications with VMware® vFabric™ SQLFire. The guide also provides a complete reference for the SQLFire tools, supported SQL statements, and APIs.
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.
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).
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.
Part 1 Getting Started with vFabric SQLFire Getting Started with vFabric SQLFire provides step-by-step procedures for installing, configuring, and using VMware® vFabric™ SQLFire. The guide also explains main concepts and provides tutorials to help you quickly begin using SQLFire.
vFabric SQLFire in 15 Minutes Need a quick introduction to vFabric SQLFire? Take this 15-minute tour to try out the basic features and functionality. The vFabric SQLFire tutorials expand on these concepts, and demonstrate additional product features. See Tutorials on page 37. 1. Download the latest SQLFire 1.1 ZIP file distribution from the download page: https://my.vmware.com/web/vmware/info/slug/application_platform/vmware_vfabric_sqlfire/1_0. Save the downloaded file in your home directory. 2.
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.
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.
15. To view the details of the query plan, disconnect as a thin client from the distributed system, and then reconnect as a peer client. A peer client participates as a member of the SQLFire distributed system and can coordinate queries, but it does not host any actual data.
returned_rows 542 no_opens 1 RESULT-HOLDER execute_time 0.490328 ms returned_rows 267 no_opens 1 member_node localhost(17535):52946 RESULT-HOLDER execute_time 1.65846 ms returned_rows 275 no_opens 1 member_node localhost(17438):1230 DISTRIBUTION-END (64.76%) execute_time 19.233818 ms returned_rows 542 Local plan: member localhost(17535):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.
Chapter 1 Overview of vFabric SQLFire vFabric SQLFire is a memory-optimized, distributed database management system designed for applications that have 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.
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.
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.
Chapter 2 Understanding the SQLFire Distributed System A SQLFire deployment consists of distributed member processes that connect to each other to form a peer-to-peer network, also known as a distributed system or SQLFire cluster. The sections that follow explain the interactions of main system components and processes. Tutorials on page 37 help you get started configuring and using a SQLFire distributed system.
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.
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 "leave" requests, and also handles members that are suspected of having left the system.
Getting Started with vFabric SQLFire // SQLFire data-aware procedure invocation CallableStatement callableStmt = connection.prepareCall("{CALL order_credit_check() " + "ON TABLE Orders WHERE customerID IN (?)}"); callableStmt.
Chapter 3 Installing vFabric SQLFire You can install vFabric SQLFire from the VMware yum repository (RHEL only), from a downloaded RPM file (RHEL only), or from a downloaded ZIP file. The installation procedure varies 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 vFabric Suite Advanced.
Getting Started with vFabric SQLFire Install SQLFire as a Component of vFabric Suite Advanced If you purchased vFabric Suite Advanced, VMware recommends that you install vFabric SQLFire by first installing the VMware RPM repository and then using yum to perform the actual installation. Follow this procedure: 1. On the RHEL computer, start a terminal either as the root user or as an unprivileged user who has sudo privileges.
Installing vFabric SQLFire Note: The yum install command installs the most recent version of the vFabric SQLFire RPM that it finds in all installed repository. If you want to install a different version, you must explicitly specify that version with the yum install command. Use yum search vfabric-sqlfire --showduplicates to find all versions that are available in the installed repositories.
Getting Started with vFabric SQLFire Install SQLFire as a Standalone Product If you purchased the standalone product (rather than as part of vFabric Suite Advanced), follow this procedure to download and install only the SQLFire RPM: 1. From the VMware downloads page, select VMware vFabric SQLFire. On the SQLFire 1.
Installing vFabric SQLFire 7. (Optional) 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.
Getting Started with vFabric SQLFire • Download the ZIP file distribution of vFabric SQLFire. Procedure 1. Change to the directory where you downloaded the SQLFire software, and unzip the ZIP file: • UNIX and Linux (Bourne and Korn shells - sh, ksh, bash). If you are using the command line, type the following command: $ unzip vFabric_SQLFire_XX_bNNNNN.
Chapter 4 Activating vFabric SQLFire Licensing vFabric SQLFire includes a default evaluation license that enables you to run the product tutorial and perform simple evaluation activities. You can obtain custom evaluation licensing and purchase production licensing from your VMware account manager or the VMware license portal. Understand vFabric SQLFire License Options vFabric SQLFire has a number of licensing options. Read the following sections to understand the different license types.
Getting Started with vFabric SQLFire SQLFire licenses do not restrict the number of clients that can connect to a distributed system. Any number of thin clients and peer clients (SQLFire accessor members) are permitted. vFabric SQLFire Local Licensing and vFabric Suite Licensing If you obtained vFabric SQLFire as a standalone product, you install a license locally on each physical and virtual machine that runs vFabric SQLFire.
Activating vFabric SQLFire Licensing Installing a vFabric SQLFire Professional License 1. Obtain a product vFabric SQLFire Professional license from VMware. 2. Install the license in SQLFire. Choose one of the following options: • On each SQLFire data store member, add the vFabric SQLFire license serial number directly to the sqlfire.properties file. See Option 1: Install Licenses Using sqlfire.
Getting Started with vFabric SQLFire 2. Install the license in SQLFire. Choose one of the following options: • On each data store member in the distributed system, add the vFabric SQLFire Enterprise serial number directly to the sqlfire.properties file. See Option 1: Install Licenses Using sqlfire.properties on page 25; or • Create a file that contains both the vFabric SQLFire Enterprise serial number, and copy the file to the vFabric serial number directory on all SQLFire data store members.
Activating vFabric SQLFire Licensing Local VMware vFabric Directories The location of the local VMware vFabric home directory, if it exists, varies by operating system: Windows %ALLUSERSPROFILE%\VMware\vFabric Linux (or other OS) /opt/vmware/vFabric The location of the local VMware vFabric serial number directory, if it exists, varies by operating system: Windows %ALLUSERSPROFILE%\VMware\vFabric Linux (or other OS) /etc/opt/vmware/vfabric Install and Configure vFabric SQLFire Licenses Installation
Getting Started with vFabric SQLFire the vFabric License Server. See vFabric Suite Only: Configure vFabric SQLFire for vFabric License Server on page 26.) For example: # sqlfire.properties for data store or accessor member license-serial-number=#####-#####-#####-#####-##### If you need to specify multiple serial numbers, use a comma separated list: # sqlfire.
Activating vFabric SQLFire Licensing Verify Your License and Check Your License Usage You can verify the validity of your license and monitor your license usage. If you install an invalid serial number or if SQLFire cannot obtain a dynamic license from the vFabric License Server, SQLFire fails to start and throws an exception. In Getting Started with vFabric Suite , see "Check the Validity of an Existing License Key." to learn how to verify your license.
Chapter 5 Upgrading vFabric SQLFire SQLFire 1.1 is not compatible with persistence files (disk stores) that were created in earlier versions of SQLFire (1.0.x). You must use the sqlf upgrade-disk-store command to upgrade disk stores to the SQLFire 1.1 format after upgrading the SQLFire software to version 1.1. Refer to the Release Notes to learn about changes that were introduced in the latest update.
Getting Started with vFabric SQLFire RHEL: Upgrade vFabric SQLFire from RPM If your guest operating system is Red Hat Enterprise Linux (RHEL) and you have installed a previous version of SQLFire using yum and RPM, VMware recommends that you use the RPM distribution to upgrade vFabric SQLFire. You complete the upgrade procedure on every virtual and physical machine that runs SQLFire. Note: Thoroughly test your development systems with the new version before moving into production.
Upgrading vFabric SQLFire 4. If you are upgrading from SQLFire 1.0.x, run the sqlf command upgrade-disk-store to upgrade each disk store to a format that is compatible with vFabric SQLFire 1.1. See sqlf upgrade-disk-store on page 408 for more information. Note: You must update the default disk stores as well as any additional disk stores that were defined through the CREATE DISKSTORE statement. Specify the full path to each disk store in multiple invocations of the sqlf upgrade-disk-store command.
Getting Started with vFabric SQLFire 2. Stop all members of the system running the prior version of SQLFire. For example, in a SQLFire deployment you can use the shut-down-all command to stop all members of the distributed system: sqlf shut-down-all -locators=mylocator[10101] 3. Install the latest version of SQLFire in a different directory than the existing version. See Install vFabric SQLFire from a ZIP File on page 19.
Upgrading vFabric SQLFire Version Compatibility Rules Upgraded SQLFire members cannot participate in a distributed system that contains SQLFire members from an earlier version. Shut down all existing cluster members before restarting the cluster with upgraded SQLFire members. Thin clients from earlier versions of SQLFire can operate against the latest version of SQLFire. However, thin clients using the latest SQLFire JDBC drivers are not compatible with older versions of SQLFire.
Chapter 6 Connect to vFabric SQLFire with JDBC Tools Third-party JDBC tools can help you browse data in tables, issue SQL commands, design new tables, and so forth. You can configure these tools to use the SQLFire JDBC thin client driver to connect to a SQLFire distributed system. Although the instructions for setting up each tool vary, the general process for establishing a connection involves configuring the JDBC client driver and setting JDBC connection URL properties. Follow these basic steps: 1.
Chapter 7 Tutorials Learn to configure and use SQLFire features such as table replication and partitioning, persisting data to disk, and dynamically resizing the cluster. Main Steps The tutorial is divided into the following steps, which explain how to set up a cluster of SQLFire servers on multiple Java VMs and then distribute data across the cluster. Perform the steps in the sequence shown.
Getting Started with vFabric SQLFire 4. To manage client connections to the available SQLFire server members, the cluster in this tutorial uses a SQLFire locator member. Create a new directory for the locator member: mkdir locator A locator maintains a list of available servers in the cluster, and updates that list as servers join and leave the cluster. Locators also load balance client connections across all available servers. 5.
Tutorials sqlf server start -dir=server2 -locators=ip_address[10101] -client-bind-address=ip_address -client-port=1529 In each command, the -locators option defines the peer discovery address to use for joining the SQLFire distributed system. (Production deployments generally use multiple locator members, in which case you would specify a comma-separated list of locator host[port] connections when starting a server.
Getting Started with vFabric SQLFire 4. To connect to the SQLFire cluster using the JDBC thin client driver, use the connect client command and specify the host and port number of the SQLFire locator: connect client 'ip_address:1527'; Notice that SQLFire does not have the concept of a "database". When you connect to a SQLFire cluster, the distributed system that you connect to is defined by the locator (or alternately, the mcast-port) specified in the JDBC or ADO.NET connection. 5.
Tutorials 0 rows inserted/updated/deleted [...] 2. Run the loadTables.sql script to populate the tables with data: run 'loadTables.sql'; The script output completes with: sqlf> insert into FLIGHTAVAILABILITY values ('US1357',2,'2004-04-18',0,0,3); 1 row inserted/updated/deleted 3. Enter the following command to show the table names that you created (tables in the APP schema): show tables in APP; 4.
Getting Started with vFabric SQLFire FLIGHTS, FLIGHTS_HISTORY, and FLIGHTAVAILABILITY are fact tables, and they will be partitioned. You will co-locate these tables to ensure that all rows that are associated with FLIGHT_ID are maintained in a single partition. This step ensures that frequent join queries based on a selected flight are pruned to a single member and executed efficiently. Procedure 1. In a separate terminal window or GUI editor, open the create_colocated_schema.
Tutorials SEGMENT_NUMBER INTEGER NOT NULL , FLIGHT_DATE DATE NOT NULL , ECONOMY_SEATS_TAKEN INTEGER DEFAULT 0, BUSINESS_SEATS_TAKEN INTEGER DEFAULT 0, FIRSTCLASS_SEATS_TAKEN INTEGER DEFAULT 0, CONSTRAINT FLIGHTAVAIL_PK PRIMARY KEY ( FLIGHT_ID, SEGMENT_NUMBER, FLIGHT_DATE), CONSTRAINT FLIGHTS_FK2 Foreign Key ( FLIGHT_ID, SEGMENT_NUMBER) REFERENCES FLIGHTS ( FLIGHT_ID, SEGMENT_NUMBER) ) PARTITION BY COLUMN (FLIGHT_ID) COLOCATE WITH (FLIGHTS); 2. In the sqlf session, execute the create_colocated_schema.
Getting Started with vFabric SQLFire Procedure 1. In a separate terminal window or GUI editor, examine the contents of the create_persistent_schema.sql script. Notice that this script uses the PERSISTENT keyword in each CREATE TABLE statement.
Tutorials /10.118.33.206:/Users/yozie/vFabric_SQLFire_11_bNNNNN/server1/./datadictionary created at timestamp 1343149012698 version 0 diskStoreId 1fc6a853-69d6-4ffe-8029-218acf165c34 is waiting for the data previously hosted at [/10.118.33.206:/Users/yozie/vFabric_SQLFire_11_bNNNNN /server2/./datadictionary created at timestamp 1343149035681 version 0 diskStoreId 49c1e827-4bb5-49cc-951b-221d47bbc92f] to be available These are not error messages.
Getting Started with vFabric SQLFire 8. You can stop an individual SQLFire server by using the sqlf server stop command and specifying the server directory. To shut down all data stores at once, use the sqlf shut-down-all command: sqlf shut-down-all -locators=ip_address[10101] Connecting to distributed system: locators=ip_address[10101] Successfully shut down 3 members 9.
Chapter 8 vFabric SQLFire Features and Benefits The sections that follow summarize main features. The SQLFire community site provides additional information about the features and benefits of SQLFire and a comparison of SQLFire to other data management systems. See also the SQLFire product page.
Part 2 Managing Your Data in vFabric SQLFire Managing Your Data in vFabric SQLFire describes how to design your database schema to take advantage of replication and partitioning in a distributed SQLFire cluster. You can use this information to design new databases, or to adapt an existing database for use with SQLFire. This guide also describes how to persist your data to disk store files, and it provides a simple procedure for importing an existing database into SQLFire.
Chapter 9 Designing vFabric SQLFire Databases Unlike in traditional database design, SQLFire requires the designer to analyze the common access patterns and choose a partitioning strategy that results in queries that are pruned to a single partition. In addition, fact tables may need to be replicated in order to make data available for join operations against partitioned data. This chapter describes the basic table design principles that you can use to achieve linear scaling with SQLFire.
Managing Your Data in vFabric SQLFire • In an online auction application, you may need to stream incoming auction bids to hundreds of clients with very low latency. To do so, you must manage selected "hot" auctions on a single partition so that they receive sufficient processing power. As the processing demand increases, add more partitions and route the application logic that matches bids to clients to the data store itself.
Designing vFabric SQLFire Databases • Use parallel, data-aware procedures to run the logic for the query on the member that stores some or all of the data (to minimize data distribution hops). The procedure should execute multiple queries and then join the results using application code. Using Data-Aware Stored Procedures on page 157 provides more information. • Split the query into multiple queries, and perform the join in the application client code.
Managing Your Data in vFabric SQLFire Step 1: Determine the entity groups Entity groups are generally course-grained entities that have children, grand children, and so forth, and they are commonly used in queries. This example chooses these entity groups: Entity group Description Customer This group uses the customer identity along with orders and order details as the children. Product This group uses product details along with the associated supplier information.
Designing vFabric SQLFire Databases Entity group Tables Order Details Product Product Suppliers Category Step 3: Define the partitioning key for each group In this example, the partitioning keys are: Entity group Partitioning key Customer CustomerID Product ProductID This example uses customerID as the partitioning key for the Customer group. The customer row and all associated orders will be collocated into a single partition.
Managing Your Data in vFabric SQLFire So for the product entity group, table Products is partitioned by ProductID, and the Suppliers and Categories tables are replicated to all of the members where Products is partitioned. Applications can now join Products, Suppliers and categories. For example: select * from Products p , Suppliers s, Categories c where c.categoryID = p.categoryID and p.supplierID = s.supplierID and p.
Chapter 10 Using Server Groups to Manage Data Use server groups to control where table data is stored. Server Groups Overview A server group specifies the SQLFire members that will host data for a table. You use a server group to logically group SQLFire data stores for managing a table's data. Any number of SQLFire members that host data can participate in one or more server groups. You specify named server groups when you start a SQLFire data store.
Managing Your Data in vFabric SQLFire A SQLFire member that is booted with host-data=false is an accessor and does not host table data, even if you specify one or more server groups. However, peer clients that host data can also participate in server groups.
Using Server Groups to Manage Data By default, all servers that host data are added to the "default" server group. Different logical database schema are often managed in different server groups. For example, an order management system might manage all customers and their orders in an "Orders" schema deployed to one server group. The same system might manage shipping and logistics data in a different server group.
Managing Your Data in vFabric SQLFire For example, if you start a SQLFire server from the command line with sqlf, use the server-groups property to specify the names of one or more server groups that the server should join: sqlf server start -server-groups=OrdersDB,OrdersReplicationGrp,DBProcessInstance1 In this example, the SQLFire server participates in three server groups: OrdersDB, OrdersReplicationGrp and DBProcessInstance1.
Chapter 11 Partitioning Tables Horizontal partitioning involves spreading a large data set (many rows in a table) across members in a cluster. SQLFire uses a variant of the consistent hash algorithm to help ensure that data is uniformly balanced across all members of the target server group. How Table Partitioning Works You specify the partitioning strategy of a table in the PARTITION BY clause of the CREATE TABLE statement.
Managing Your Data in vFabric SQLFire Figure 2: Partitioned Table Data Although each bucket is assigned to one or more specific servers, you can use a procedure to relocate buckets in a running system, in order to improve the utilization of resources across the cluster. See Rebalancing Partitioned Data on SQLFire Members on page 69. You can also pre-allocate buckets before loading data into the table, to ensure that imported data is evenly distributed among table partitions.
Partitioning Tables Figure 3: Partitioned Data in Buckets If you set the redundant-copies for the table to be greater than zero, SQLFire designates one of the copies of each bucket as the primary copy. All writes to the bucket go through the primary copy. This ensures that all copies of the bucket are consistent.
Managing Your Data in vFabric SQLFire Creating Partitioned Tables You create a partitioned table on a set of servers identified by named server groups (or on the default server group if you do not specify a named server group). Clauses in the CREATE TABLE statement determine how table data is partitioned, colocated, and replicated across the server group. This topic focuses on the partitioning_clause in CREATE TABLE. The CREATE TABLE reference page describes all of the options in more detail.
Partitioning Tables PARTITION BY RANGE ( column-name ) ( VALUES BETWEEN value AND value [ , VALUES BETWEEN value AND value ]* ) | PARTITION BY LIST ( column-name ) ( VALUES ( value [ , value ]* ) [ , VALUES ( value [ , value ]* ) ]* ) | PARTITION BY ( expression ) } [ COLOCATE WITH ( table-name [ , table-name ] * ) ] } [ [ [ [ REDUNDANCY integer-constant ] BUCKETS integer-constant ] MAXPARTSIZE integer-constant ] RECOVERYDELAY integer-constant ] Note: If the table has no primary key, then SQLFire generat
Managing Your Data in vFabric SQLFire Partition Based on Columns This statement creates a table that is partitioned by the "CustomerName" column. All rows with the same CustomerName are guaranteed to be colocated in the same process space. Here, the SERVER GROUPS clause determines the peers and servers that host data for the partitioned table. A server group is a subset of all the peers and servers that host data in the distributed system.
Partitioning Tables ) PARTITION BY LIST ( Status ) ( VALUES ( 'pending', 'returned' ), VALUES ( 'shipped', 'received' ), VALUES ( 'hold' ) ); Partition Based on an Expression Expression partitioning partitions a table by evaluating a SQL expression that you supply.
Managing Your Data in vFabric SQLFire You can colocate another table, "cities," using the command: CREATE TABLE CITIES ( CITY_ID INTEGER NOT NULL CONSTRAINT CITIES_PK Primary key, CITY_NAME VARCHAR(24) NOT NULL, COUNTRY VARCHAR(26) NOT NULL, AIRPORT VARCHAR(3), LANGUAGE VARCHAR(16), COUNTRY_ISO_CODE CHAR(2) CONSTRAINT COUNTRIES_FK REFERENCES COUNTRIES (COUNTRY_ISO_CODE) ) PARTITION BY COLUMN (COUNTRY_ISO_CODE) COLOCATE WITH (COUNTRIES) In this example, both "countries" and "cities" are partitioned using th
Partitioning Tables Pre-Allocating Buckets As a best pratice, use the SYS.CREATE_ALL_BUCKETS procedure to create the buckets for a partitioned table before you begin loading data. SYS.CREATE_ALL_BUCKETS immediately creates the necessary buckets on datastores for partitioned tables. (Normally SQLFire assigns buckets to servers lazily, as inserts are executed against the table.) Pre-allocating the buckets ensures that partitioned table data is evenly distributed in the cluster. If you do not use SYS.
Managing Your Data in vFabric SQLFire When to Rebalance a Partitioned Table You typically want to trigger rebalancing when overall capacity is increased or reduced through member startup, shut down or failure. You may also need to rebalance when you use partitioned table redundancy for high availability, and you have configured your table to not automatically recover redundancy after a SQLFire member fails (the default RECOVERYDELAY setting).
Chapter 12 Replicating Tables SQLFire server groups control which SQLFire data store members replicate the table's data. SQLFire replicates table data both when a new table is initialized in a cluster and when replicated tables are updated. How SQLFire Replicates Tables SQLFire replicates every single row of a replicated table synchronously to each table replica in the target server group(s).
Managing Your Data in vFabric SQLFire Also note that this version of SQLFire supports joins only on co-located data. Instead of using partitioning in all cases, you should consider having applications replicate smaller tables that are joined with other partitioned tables. Note: If multiple applications update the same row of a replicated table at the same time outside of a transaction, the table data can become out of sync when SQLFire replicates those updates.
Chapter 13 Estimating Memory Requirements Designing a SQLFire database also involves estimating the memory requirements for your data based on the size of the actual table values and indexes, the overhead that SQLFire requires for your data, and the overall usage pattern for your data. You can estimate the memory requirements for tables using general guidelines for SQLFire overhead. Or, you can load tables with representative data and then query the SQLFire SYS.
Managing Your Data in vFabric SQLFire Viewing Memory Usage in SYS.MEMORYANALYTICS SQLFire includes instrumentation to display the memory used by individual tables and indexes in a SQLFire member. You can view this memory usage information by starting a Java agent process when you boot SQLFire members, and then querying the SYS.MEMORYANALYTICS virtual table from a client connection. Enabling SYS.MEMORYANALYTICS Follow this procedure to enable the SYS.MEMORYANALYTICS virtual table.
Estimating Memory Requirements APP.FLIGHTS.DESTINDEX (Index Entry Size, Value Size, Row Count) |dyozie-e4310(6880):3439/59731 |4888,3784,87 [...] Understanding Table and Index Values Querying the SYS.MEMORYANALYTICS table provides run-time information about the tables and indexes available in SQLFire. Table Values Each table has a single row in SYS.MEMORYANALYTICS identitfied with the SQLENTITY format: schema_name.table_name (Entry Size, Value Size, Row Count).
Managing Your Data in vFabric SQLFire For example, the following row from SYS.MEMORYANALYTICS shows that APP.FLIGHTS.DESTINDEX uses 2803 bytes for skip lists: SQLENTITY |ID |MEMORY ---------------------------------------------------------------APP.FLIGHTS.DESTINDEX (Index Entry Overhead, SkipList Size, Max& |dyozie-e4310(6880):3439/59731 |2104,696,3 (2803 = 2.74 kb) The second row for an index uses the SQLENTITY format: schema_name.table_name.index_name (Index Entry Size, Value Size, Row Count).
Estimating Memory Requirements APP.FLIGHTS (sqlfire,gemfire,others) |dyozie-e4310(6880):3439/59731 |12912,299736,680704 (993352 = 970.07 kb) APP.FLIGHTS.DESTINDEX (sqlfire,gemfire,others) |dyozie-e4310(6880):3439/59731 |5072,0,3288 (8360 = 8.16 kb) [...
Chapter 14 Using Disk Stores to Persist Data By default, a SQLFire distributed system persists only the data dictionary for the tables and indexes you create. These persistence files are stored in the datadictionary subdirectory of each locator and data store that joins the distributed system. Table data, however, is not persisted by default; if you shut down al SQLFire members, the tables are empty on the next startup.
Managing Your Data in vFabric SQLFire Peer Client Considerations for Persistent Data Peer clients (clients started using the host-data=false property) do not use disk stores and can never persist the SQLFire data dictionary. Instead, peer clients rely on other data stores or locators in the distributed system for persisting data.
Using Disk Stores to Persist Data When a disk store is offline, its data is unavailable to the SQLFire distributed system. For partitioned tables, the data is split between multiple members, so you can access the offline data only if you store replicas of the partitioned table on other members of the cluster. Disk Store Directories When you create a disk store, optionally specify the location of directories where sqlf stores persistence-related files.
Managing Your Data in vFabric SQLFire File Names File names have three parts. First Part of File Name: Usage Identifier Usage Identifier Values Usage Examples OVERFLOW Oplog data from overflow tables and queues OVERFLOWoverflowDS1_1.crf only. BACKUP Oplog data from persistent and persistent+overflow tables and queues. BACKUPoverflowDS1.if, BACKUPSQLF-DEFAULT-DISKSTORE.if DRLK_IF Access control - locking the disk store. DRLK_IFoverflowDS1.lk, DRLK_IFSQLF-DEFAULT-DISKSTORE.
Using Disk Stores to Persist Data File Extension Values Usage Notes crf Oplog: create, update, and invalidate operations Pre-allocated 90% of the total max-oplog-size at creation. drf Oplog: delete operations Pre-allocated 10% of the total max-oplog-size at creation. krf Oplog: key and crf offset information Created after the oplog has reached the max-oplog-size. Used to improve performance at startup.
Managing Your Data in vFabric SQLFire Pooling Each SQLFire member manages its own disk store, and no disk contention exists between processes. Each partition can manage its data on local disks. If the application "write" load can be uniformly balanced across the cluster, the aggregate disk throughput is equal to the disk transfer rate time the number of partitions, assuming a single disk per partition. Disk transfer rates can be up to 100MB/sec on commodity machines today.
Using Disk Stores to Persist Data agree with each other. Each member of the distributed system logs membership changes to its persistent files and uses this information during recovery to determine the replica that has the latest changes, and members automatically synchronize these changes at startup. Guidelines for Designing Disk Stores Work with your system designers and developers to plan for disk storage requirements in testing and production database systems.
Managing Your Data in vFabric SQLFire Related Topics CREATE DISKSTORE on page 480 CREATE TABLE on page 491 Persist Table Data to a Disk Store on page 86 Evicting table data from memoryUse eviction to remove table data from memory, or to persist the overflow data in a disk store. Creating a Disk Store or Using the Default You can create a disk store for persistence and/or overflow or use the default disk store. Data from multiple tables can be stored in the same disk store.
Using Disk Stores to Persist Data 2. Create the disk store that you want to use for persisting the table's data, or use the default disk store. See Creating a Disk Store or Using the Default on page 86. 3. Specify table persistence and the named disk store in the CREATE TABLE statement. For example: CREATE TABLE Orders(OrderId INT NOT NULL,ItemId INT ) persistent 'OrdersDiskStore' asynchronous This example uses asynchronous writes to persist table data to the "OrdersDiskStore.
Managing Your Data in vFabric SQLFire Shutdown: Most Recent Data from the Last Run If more than one member has the same persistent table or queue, the last member to exit leaves the most up-to-date data on disk. SQLFire stores information on member exit order in the disk stores, so it can start your members with the most recent data set: • For a persistent replicated table, the last member to exit leaves the most recent data on disk.
Using Disk Stores to Persist Data Example Startup Scenarios • Stop order for a replicated, persistent table: 1. Member A (MA) exits first, leaving persisted data on disk for TableP. 2. Member B (MB) continues to run DML operations on TableP, which update its disk store and leaves the disk store for MA in a stale condition. 3. MB exits, leaving the most up-to-date data on disk for Table P. • Restart order Scenario 1: 1. MB is started first.
Managing Your Data in vFabric SQLFire a. Make sure you have started the member. Check the logs for any failure messages. b. Make sure your disk store files are accessible. If you have moved your member or disk store files, you must update your disk store configuration to match. c. If disk stores are missing that you know are lost, because you have deleted them or their files are otherwise unavailable, revoke them so the startup can continue. See Handling Missing Disk Stores on page 96.
Using Disk Stores to Persist Data java.io.IOException: The file "hostA/ds1dir1/DRLK_IFds1.lk" is being used by another process. Validate a Disk Store The sqlf validate-disk-store command verifies the health of your offline disk store. It gives you information about the tables in it, the total rows, and the number of records that would be removed if you compacted the store. When to use this command: • Before compacting an offline disk store to help decide whether it’s worth doing.
Managing Your Data in vFabric SQLFire Offline compaction runs essentially in the same way, but without the incoming DML operations. Also, because there is no current open log, the compaction creates a new one to get started. Run Online Compaction Old log files become eligible for online compaction when their garbage content surpasses a configured percentage of the total file. A record is garbage when its operation is superseded by a more recent operation for the same record.
Using Disk Stores to Persist Data You must provide all of the directories in the disk store. If no oplog max size is specified, SQLFire uses the system default. Offline compaction can take a lot of memory. If you get a java.lang.OutOfMemory error while running this, you made need to increase your heap size. See the sqlf command help for instructions on how to do this.
Managing Your Data in vFabric SQLFire drwxrwxr-x bash-2.05$ 2 jpearson users 512 Apr 7 15:09 . Backing Up and Restoring Online Disk Stores When you invoke the sqlf backup command, SQLFire backs up disk stores for all members that are running in the distributed system at that time. Each member with persistent data creates a backup of its own configuration and disk stores.
Using Disk Stores to Persist Data If the operation does not succeed at backing up all known members, you see a message like this: Connecting to distributed system: locators=warsaw.vmware.com[26357] The following disk stores were backed up: DiskStore at hosta.vmware.com /home/dsmith/dir1 DiskStore at hostb.vmware.com /home/dsmith/dir2 The backup may be incomplete. The following disk stores are not online: DiskStore at hostc.vmware.
Managing Your Data in vFabric SQLFire To restore a backup of an offline system: 1. Make sure the system is either down or not using the directories you will use for the restored files. 2. Make sure your members are configured to use the directories where you put the files. 3. Reverse your backup file copy procedure, copying all the backed up files into the directories you want to use. 4. Start the system members.
Using Disk Stores to Persist Data table are online, because their offline status may be causing PartitionOfflineExceptions in cache operations or preventing the system from satisfying redundancy. Example: sqlf list-missing-disk-stores Connecting to distributed system: mcast=/239.192.81.2:12348 DiskStore at straw.gemstone.com /export/straw3/users/jpearson/testGemFire/hostB/DS1 Note: Make sure this sqlf call can find a sqlfire.properties file to locate the distributed system.
Chapter 15 Exporting and Importing Data with vFabric SQLFire You can use either sqlf commands or Apache DdlUtils to import data into SQLFire. SQLFire installs and uses DdlUtils for sqlf import and export functionality. Using SQLF Commands to Export and Import Data The sqlf utility provides several commands that use the DdlUtils 1.1 API to export and import database schemas and table data. You can use these sqlf commands with SQLFire and other JDBC datasources. Note: As a best practice, use the SYS.
Managing Your Data in vFabric SQLFire The sections that follow describe how to use the above sqlf commands to migrate a third-party database to SQLFire. Export, Alter, and Import a Database Schema Using SQLF Use sqlf commands to export the schema and data from a third-party database, and then import the schema and data to vFabric SQLFire. Procedure 1. To use the sqlf export commands with a third-party database, you require a JDBC driver and connection URL for the database.
Exporting and Importing Data with vFabric SQLFire -url=jdbc:mysql://localhost:3306/sqlfdb?user=username&password=password sqlf write-data-to-xml -file=mysql-data.xml -driver-class=com.mysql.jdbc.Driver -url=jdbc:mysql://localhost:3306/sqlfdb?user=username&password=password 8. Use the sqlf write-data-to-db command and specify both the data XML file and the schema XML file to import the data to SQLFire: sqlf write-data-to-db -files=mysql-data.xml -schema-files=mysql-schema.
Managing Your Data in vFabric SQLFire 3. Read the available DdlUtils documentation to understand the various Ant tasks. Documentation is installed in the ddlutils/DdlUtils-1.0-doc.zip file and is also available at the DdlUtils project site. This step is not required if you only want to export DDL from a schema and then apply it to SQLFire. 4. Make sure that you can connect to the external database and to SQLFire servers.
Exporting and Importing Data with vFabric SQLFire classname="org.apache.ddlutils.task.DatabaseToDdlTask"> --> PAGE 122Managing Your Data in vFabric SQLFire
