Functionality in Preview Release

What Works

SQL functionality

• All basic "CRUD" operations, including: INSERT, SELECT, UPDATE, DELETE, CREATE TABLE, DROP TABLE, CREATE INDEX, DROP INDEX.
• Foreign keys that reference the primary keys of another table
• Colocated joins: joins between tables that are replicated, declared to be colocated, or are colocated by default.
• SQL expressions, including many of the functions listed in the Reference guide
• SELECT DISTINCT
• CREATE PROCEDURE
• CREATE SCHEMA
• Automatic creation of global indexes on unique fields and primary keys when those fields are not the partitioning fields
• ALTER TABLE statements that add constraints will work after a table is created, but only if there has been no rows inserted yet
• GROUP BY and ORDER BY
• Large intermediate results, such as with a GROUP BY or ORDER BY, that risk out-of-memory will throw exception
• Default values
• Functions
• aggregates (but combining aggregates with distinct does not work if querying on data store node)
• Procedures
• LOBs (BLOBs and CLOBs)

SQLFabric specific functionality

• peer-clients
• Disk overflow of partitioned & replicated tables (limited testing)
• Persistent Data Dictionary
• Execution of SQL scripts when fabric server is started
• DDL and DML can be executed concurrently, but the product will block all DML operations while DDL is being executed
• persistent replicated tables (limited testing)
• Redundancy in partitioned tables
• edge-clients
• Automatic rebalancing when fabric servers are added or removed - rebalance partitions or add/remove more replicas on the fly
• Parallel data aware procedures

Limitations

• Limitations on DELETE:
  SQL Fabric does not support CASCADE deletions. NO ACTION will be treated the same as RESTRICT. SET NULL is also not supported.
• Foreign keys are only supported for primary keys of another table, not unique fields
• only joins that are on tables that are declared to be colocated, or are colocated by default, are supported. Joins between any mixture of replicated and partitioned tables is supported, but if the replicated tables involved are not hosted on the same members (in the same server groups) as the partitioned tables, an exception will be thrown when a join is attempted.
• Transaction isolation level TRANSACTION_NONE supported only, need description of what consistency guarantees there are, and what the risks are that need to be managed by the application. Applications can still call commit() and rollback(), but they essentially have no effect
• only "colocated" subqueries are supported
• ORDER BY and GROUP BY work for small result sets (where they do not need to overflow to disk)
• other operators (such as LIKE and BETWEEN) should work, but test coverage is limited and no special node pruning is done for them.
• Need some notes on logging, there are currently two separate logs, a gemfire log and a sqlfabric log.
• For batch operation (including dml operations involve multiple records), if a SQLException is raised, it may end up with some records being modified. This is caused by no transactional support in preview release.
• Data-aware Procedures are mostly working, except CALL PROCEDURE ON SERVER GROUPS is not working
• Data loader (load on a cache miss) - limited testing
• Table listeners (receive notifications on data nodes or remote peer clients on row level changes)
• High Availability is mostly working, but there are still some known issues when member processes stop abnormally.
• Cursors work when reading result sets, but are not working for updating result sets.
• Listeners, CacheWriters, and Loaders are working, but the callback APIs are still under review.
• Gateways and AsyncEventListeners are mostly working, but some issues remain.

What is new in Alpha6?

Difference between Alpha5 and Alpha6

• A great number of bug fixes, including in High Availability (stability when a VM goes down abnormally) and use of BLOB/CLOB data types
• Performance improvements, including use of batch distribution for multiple-row inserts
• Memory footprint reduction, including optimization for single field primary keys
• Query cancellation when getting close to out of memory
• Support for left and right outer joins
• Distribution node pruning based on PARTITION BY expression
• Listeners, CacheWriters, Loaders, AsyncEventListeners, and WAN gateways all work, have had changes to callback APIs (which are still not final). AsynchEventListeners and WAN gateways have still not been sufficiently tested.
• JDK 1.6 is now required to execute
• A dash ("-") is now required before all arguments in the sqlf script, e.g.: sqlf fabricserver start -dir=fabricserver1 -port=1527 -mcast-port=10490
• When connecting as a peer-client the connection property "host-data" is now required (in the first connection for this VM), set to either "true" or "false". Use "true" if this VM will host data (either a server or a standalone VM), or "false" if this VM is a peer-client. The "host-data" property defaults to "true" if not specified. (Previously this property was called "role", or in some cases "gemfire.roles", and was set to "client" or "server").
• The old "gemfire" script is obsolete and the "sqlf" script (that requires SQLFabric license instead of GFE license as with "gemfire" script) now provides for all its functionality directly e.g. "sqlf start-locator", "sqlf stop-locator", "sqlf stats". See "sqlf help" for all the commands provided. Previously, the commands looked like this - "sqlf gemfire start-locator".
• For unique indexes/constraints, local indexes are now created as far as possible instead of global indexes. In addition there has been a significant reduction in footprint of global indexes.
• Several extensions for monitoring have been added: SYSSQLF_DIAG.MEMBERS virtual table shows information about all the members in the DS. SYSSQLF_DIAG.QUERYSTATISTICS virtual table that will show query timings, number of executions etc. segregated by query string on each member. To turn on statistics collection globally fire the procedure SYSSQLF_DIAG.SET_QUERYSTATS(1) and SYSSQLF_DIAG.SET_QUERYSTATS(0) to disable it.
• Our performance benchmarks also indicate a decent improvement in query throughput - between 10-40%. Your results may vary given the complexity of the queries and the nature of the data set.

  ---

The following are known not to work:

• SELECT FOR UPDATE has the same functionality as SELECT
• Data-aware procedures have some known issues
• ALTER TABLE will not work if there has been rows inserted into this table previously.
• explicit creation of global indexes
• updates to primary keys
• updates to partitioning fields
• triggers
• typemaps
• RowSet
• distributed joins, i.e. joins on tables that are not colocated
• Autogenerated keys (identity columns)
• global temporary tables
• eviction and expiration have several open tickets and are therefore not ready

The following have not been sufficiently tested:

• RENAME TABLE
• CREATE VIEW
• CREATE SYNONYM
• updating ResultSets
• batch SQL