28 Using Coherence Query Language

This chapter describes how to use Coherence Query Language (CohQL) to interact with Coherence caches. CohQL is a light-weight syntax (in the tradition of SQL) that is used to perform cache operations on a Coherence cluster. The language can be used either programmatically or from a command-line tool.

This chapter includes the following sections:

Note:

  • Although the CohQL syntax may appear similar to SQL, it is important to remember that the syntax is not SQL and is actually more contextually related to the Java Persistence Query Language (JPQL) standard.

  • CQL (Continuous Query Language) is a query language related to Complex Event Processing (CEP) and should not be confused with CohQL.

28.1 Understanding Coherence Query Language Syntax

The following sections describe the functionality provided by CohQL. Each section describes a particular statement, its syntax, and an example. You can find more query examples in "Additional Coherence Query Language Examples".

Note:

CohQL does not support subqueries.

This section includes the following topics:

For reference, Table 28-1 lists the Coherence query statements, clauses, and expressions in alphabetical order.

Table 28-1 Coherence Query Language Statements

For this statement, clause, or expression... See this section

ARCHIVE SNAPSHOT

Archiving Snapshots

bind variables

Using Bind Variables

CREATE CACHE

Creating a Cache

CREATE INDEX

Creating an Index on the Cache

CREATE SNAPSHOT

Creating Snapshots

DELETE

Deleting Entries in the Cache

DROP CACHE

Removing a Cache from the Cluster

DROP INDEX

Removing an Index from the Cache

ENSURE CACHE

Creating a Cache

ENSURE INDEX

Creating an Index on the Cache

GROUP BY

Aggregating Query Results

INSERT

Inserting Entries in the Cache

key() pseudo-function

Using Key and Value Pseudo-Functions

LIST [ARCHIVED] SNAPSHOTS

Recovering Snapshots and Retrieving Archived Snapshots

LIST ARCHIVER

Archiving Snapshots

path-expressions

Using Path-Expressions

RECOVER SNAPSHOT

Recovering Snapshots

REMOVE [ARCHIVED] SNAPSHOT

Removing Snapshots

RESUME SERVICE

Suspending Services During Persistence Operations

RETRIEVE ARCHIVED SNAPSHOT

Retrieving Archived Snapshots

SELECT

Retrieving Data from the Cache

SOURCE

Processing Query Statements in Batch Mode

SUSPEND SERVICE

Suspending Services During Persistence Operations

UPDATE

Changing Existing Values

VALIDATE ARCHIVED SNAPSHOT

Validating Archived Snapshots

VALIDATE SNAPSHOT

Validating Snapshots

value() pseudo-function

Using Key and Value Pseudo-Functions

WHENEVER COHQLERROR THEN [EXIT|CONTINUE]

Handling Errors

WHERE

Filtering Entries in a Result Set

28.1.1 Query Syntax Basics

This section describes some building blocks of the syntax, such as path expressions, bind variables, and pseudo-functions.

28.1.1.1 Using Path-Expressions

One of the main building blocks of CohQL are path-expressions. Path expressions are used to navigate through a graph of object instances. An identifier in a path expression is used to represent a property in the Java Bean sense. It is backed by a ReflectionExtractor that is created by prepending a get and capitalizing the first letter. Elements are separated by the "dot" (.) character, that represents object traversal. For example the following path expression is used to navigate an object structure:

a.b.c

It reflectively invokes these methods:

getA().getB().getC()

28.1.1.2 Using Bind Variables

For programmatic uses, the API passes strings to a simple set of query functions. Use bind variables to pass the value of variables without engaging in string concatenation. There are two different formats for bind variables.

  • the question mark (?)—Enter a question mark, immediately followed by a number to signify a positional place holder that indexes a collection of objects that are "supplied" before the query is run. The syntax for this form is: ?n where n can be any number. Positional bind variables can be used by the QueryHelper class in the construction of filters. For example:

    QueryHelper.createFilter("number = ?1" , new Object[]{new Integer(42)};
    
  • the colon (:)—Enter a colon, immediately followed by the identifier to be used as a named place holder for the object to be supplied as a key value pair. The syntax for this is :identifier where identifier is an alpha-numeric combination, starting with an alphabetic character. Named bind variables can be used by the QueryHelper class in the construction of filters. For example:

    HashMap env = new HashMap();
    env.put("iNum",new Integer(42));
    QueryHelper.createFilter("number = :iNum" , env};
    

See "Building Filters in Java Programs" for more information on the QueryHelper class and constructing filters programmatically.

28.1.1.3 Using Key and Value Pseudo-Functions

CohQL provides a key() pseudo-function because many users store objects with a key property. The key() represents the cache's key. The query syntax also provides a value() pseudo-function. The value() is implicit in chains that do not start with key(). The key() and value() pseudo-functions are typically used in WHERE clauses, where they test against the key or value of the cache entry. For examples of using key() and value(), see "Key and Value Pseudo-Function Examples" and "A Command-Line Example".

28.1.1.4 Using Aliases

Although not needed semantically, CohQL supports aliases to make code artifacts as portable as possible to JPQL. CohQL supports aliases attached to the cache name and at the head of dotted path expressions in the SELECT, UPDATE, and DELETE commands. CohQL also allows the cache alias as a substitute for the value() pseudo function and as an argument to the key() pseudo function.

28.1.1.5 Using Quotes with Literal Arguments

Generally, you do not have to enclose literal arguments (such as cache-name or service-name) in quotes. Quotes (either single or double) would be required only if the argument contains an operator (such as -, +, ., <, >, =, and so on) or whitespace.

Filenames should also be quoted. Filenames often contain path separators (/ or \) and dots to separate the name from the extension.

The compiler throws an error if it encounters an unquoted literal argument or filename that contains an offending character.

28.1.2 Managing the Cache Lifecycle

The following sections describe how to create and remove caches.

28.1.2.1 Creating a Cache

Use the CREATE CACHE or ENSURE CACHE statements to create a new cache or connect to an existing cache, respectively. This statement first attempts to connect to a cache with the specified cache-name. If the cache is not found in the cluster, an attempt is made to create a cache with the specified name based on the current cache configuration file. This statement is especially useful on the command line. If you are using this statement in a program, then you have the option of specifying service and classloader information instead of a name (classloaders cannot be accessed from the command line).

Note:

Cache names and service names must be enclosed in quotes (either double-quotes (" ") or single-quotes (' ')) in a statement.

The syntax is:

[ CREATE | ENSURE ] CACHE "cache-name" 
[ SERVICE "service-name" ]

Example:

  • Create a cache named dept.

    create cache "dept"
    

28.1.2.2 Removing a Cache from the Cluster

Use the DROP CACHE statement to remove the specified cache completely from the cluster. The cache is removed by a call to the Java destroy() method. If any cluster member holds a reference to the dropped cache and tries to perform any operations on it, then the member receives an IllegalStateException. The syntax for the Coherence query DROP CACHE statement is:

DROP CACHE  "cache-name"

Example:

  • Remove the cache orders from the cluster.

    drop cache "orders"
    

28.1.2.3 Writing a Serialized Representation of a Cache to a File

Note:

The BACKUP CACHE statement is deprecated. Use the persistence statements instead. For details about the persistence statements, see Persisting Cache Data to Disk

Use the BACKUP CACHE statement to write a serialized representation of the given cache to a file represented by the given filename. The filename is an operating system-dependent path and must be enclosed in single or double quotes. The BACKUP CACHE statement is available only in the command-line tool. The syntax is:

BACKUP CACHE "cache-name" [ TO ] [ FILE ] "filename"

Note:

The backup (and subsequent restore) functionality is designed for use in a development and testing environment and should not be used on a production data set as it makes no provisions to ensure data consistency. It is not supported as a production backup, snapshot, or checkpointing utility.

In particular:

  • The backup is slow since it only operates on a single node in the cluster.

  • The backup is not atomic. That is, it misses changes to elements which occur during the backup and results in a dirty read of the data.

  • The backup stops if an error occurs and results in an incomplete backup. In such scenarios, an IOException is thrown that describes the error.

  • The backup is not forward or backward compatible between 3.x and 12.1.x.

Example:

  • Write a serialized representation of the cache dept to the file textfile.

    backup cache "dept" to file "textfile"
    

28.1.2.4 Restoring Cache Contents from a File

Note:

The RESTORE CACHE statement is deprecated. Use the persistence statements instead. For details about the persistence statements, see Persisting Cache Data to Disk

Use the RESTORE CACHE statement to read a serialized representation of the given cache from a file represented by the given filename. The filename is an operating system-dependent path and must be enclosed in single or double quotes. The RESTORE CACHE statement is available only in the command-line tool. The syntax is:

RESTORE CACHE "cache-name" [ FROM ] [ FILE ] "filename"

Note:

Backups cannot be restored between 3.x and 12.1.x versions of Coherence.

Example:

  • Restore the cache dept from the file textfile.

    restore cache "dept" from file "textfile"
    

28.1.3 Retrieving Data

The following sections describe the SELECT statement and the WHERE clause. These entities are the basic building blocks of most cache queries.

28.1.3.1 Retrieving Data from the Cache

The SELECT statement is the basic building block of a query: it retrieves data from the cache. The clause can take several forms, including simple and complex path expressions, key expressions, transformation functions, multiple expressions, and aggregate functions. The SELECT statement also supports the use of aliases.

The form of the SELECT statement is as follows:

SELECT (properties* aggregators* | * | alias) 
FROM "cache-name" [[AS] alias]
[WHERE conditional-expression] [GROUP [BY] properties+]

The asterisk (*) character represents the full object instead of subparts. It is not required to prefix a path with the cache-name. The FROM part of the SELECT statement targets the cache that forms the domain over which the query should draw its results. The cache-name is the name of an existing cache.

See "Simple SELECT * FROM Statements that Highlight Filters" for additional examples.

Example:

  • Select all of the items from the cache dept.

    select * from "dept"
    

28.1.3.2 Filtering Entries in a Result Set

Use the WHERE clause to filter the entries returned in a result set. One of the key features of CohQL is that they can use path expressions to navigate object structure during expression evaluation. Conditional expressions can use a combination of logical operators, comparison expressions, primitive and function operators on fields, and so on.

In the literal syntax of the WHERE clause, use single quotes to enclose string literals; they can be escaped within a string by prefixing the quote with another single quote. Numeric expressions are defined according to the conventions of the Java programming language. Boolean values are represented by the literals TRUE and FALSE. Date literals are not supported.

Note:

CohQL does not have access to type information. If a getter returns a numeric type different than the type of the literal, you may get a false where you would have expected a true on the comparison operators. The work around is to specify the type of the literal with l, for long, d for double, or s for short. The defaults are Integer for literals without a period (.) and Float for literals with a period (.).

Operator precedence within the WHERE clause is as follows:

  1. Path operator (.)

  2. Unary + and -

  3. Multiplication (*) and division (/)

  4. Addition (+) and subtraction (-)

  5. Comparison operators: =, >, >=, <, <=, <>, [NOT] BETWEEN, [NOT] LIKE, [NOT] IN, IS [NOT] NULL, CONTAINS [ALL|ANY]

  6. Logical operators (AND, OR, NOT)

The WHERE clause supports only arithmetic at the language level.

The BETWEEN operator can be used in conditional expressions to determine whether the result of an expression falls within an inclusive range of values. Numeric, or string expressions can be evaluated in this way. The form is: BETWEEN lower AND upper.

The LIKE operator can use the _ and % wildcards. The _ wildcard is used to match exactly one character, while the % wildcard is used to match zero or more occurrences of any characters. To escape the wildcards, precede them with an escape character that is defined using the escape keyword. The following example escapes the % wildcard using the \ escape character in order to select a key literally named k%1.

SELECT key(),value() FROM mycache WHERE key() LIKE "k\%1" escape "\"

In addition, any character may be defined as the escape character. For example:

SELECT key(),value() FROM mycache WHERE key() LIKE "k#%1" escape "#"

The IN operator can check whether a single-valued path-expression is a member of a collection. The collection is defined as an inline-list or expressed as a bind variable. The syntax of an inline-list is:

"(" literal* ")"

CONTAINS [ALL|ANY] are very useful operators because Coherence data models typically use de-normalized data. The CONTAINS operator can determine if a many-valued path-expression contains a given value. For example:

e.citys CONTAINS "Boston"

The ALL and ANY forms of CONTAINS take a inline-list or bind-variable with the same syntax as the IN operator.

Note:

Coherence provides a programmatic API that enables you to create standalone Coherence filters based on the WHERE clause conditional-expression syntax. See "Building Filters in Java Programs".

See "Simple SELECT * FROM Statements that Highlight Filters" for additional examples.

Example:

  • Select all of the items in the cache dept where the value of the deptno key equals 10.

    select * from "dept" where deptno = 10
    

28.1.4 Working with Cache Data

The following sections describe how to work with data in the cache, such as inserting and deleting cache data and filtering result sets.

28.1.4.1 Aggregating Query Results

An aggregate query is a variation on the SELECT query. Use an aggregate query when you want to group results and apply aggregate functions to obtain summary information about the results. A query is considered an aggregate query if it uses an aggregate function or has a GROUP BY clause. The most typical form of an aggregate query involves the use of one or more grouping expressions followed by aggregate functions in the SELECT clause paired with the same lead grouping expressions in a GROUP BY clause.

CohQL supports these aggregate functions: COUNT, AVG, MIN, MAX, and SUM. The functions can operate on the Double, Long, and BigDecimal types. To specify a type, include the type followed by an underscore (_) as a prefix to the function. For example:

long_sum, bd_sum

The Double type is assumed if a type is not explicitly specified.

See "Complex Queries that Feature Projection, Aggregation, and Grouping" for additional examples.

Example:

  • Select the total amount and average price for items from the orders cache, grouped by supplier.

    select supplier,sum(amount),avg(price) from "orders" group by supplier
    
  • Select the total amount and average price (using a BigDecimal type) for items from the orders cache, grouped by supplier.

    select supplier,bd_sum(amount),bd_avg(price) from "orders" group by supplier
    

28.1.4.2 Changing Existing Values

Use the UPDATE statement to change an existing value in the cache. The syntax is:

UPDATE "cache-name" [[AS] alias]
SET update-statement {, update-statement}* 
[ WHERE conditional-expression ]

Each update-statement consists of a path expression, assignment operator (=), and an expression. The expression choices for the assignment statement are restricted. The right side of the assignment must resolve to a literal, a bind-variable, a static method, or a new Java-constructor with only literals or bind-variables. The UPDATE statement also supports the use of aliases.

See "UPDATE Examples" for additional examples.

Example:

  • For employees in the employees cache whose ranking is above grade 7, update their salaries to 1000 and vacation hours to 200.

    update "employees" set salary = 1000, vacation = 200 where grade > 7
    

28.1.4.3 Inserting Entries in the Cache

Use the INSERT statement to store the given VALUE under the given KEY. If the KEY clause is not provided, then the newly created object is sent the message getKey(), if possible. Otherwise, the value object is used as the key.

Note that the INSERT statement operates on Maps of Objects. The syntax is:

INSERT INTO "cache-name"
[ KEY (literal | new java-constructor | static method) ]
VALUE (literal | new java-constructor | static method)

Example:

  • Insert the key writer with the value David into the employee cache.

    insert into "employee" key "writer" value "David"
    

28.1.4.4 Deleting Entries in the Cache

Use the DELETE statement to delete specified entries in the cache. The syntax is:

DELETE FROM "cache-name" [[AS] alias] 
[WHERE conditional-expression]

The WHERE clause for the DELETE statement functions the same as it would for a SELECT statement. All conditional-expressions are available to filter the set of entities to be removed. The DELETE statement also supports the use of aliases.

Note:

If the WHERE clause is not present, then all entities in the given cache are removed.

Example:

  • Delete the entry from the cache employee where bar.writer key is not David.

    delete from "employee" where bar.writer IS NOT "David"
    

28.1.5 Working with Indexes

The following sections describe how to create and remove indexes on cache data. Indexes are a powerful tool that allows Coherence's built-in optimizer to more quickly and efficiently analyze queries and return results.

28.1.5.1 Creating an Index on the Cache

Use the CREATE INDEX or the ENSURE INDEX statement to create indexes on an identified cache. The syntax is:

[ CREATE | ENSURE ] INDEX [ON] "cache-name" (value-extractor-list)

The value-extractor-list is a comma-delimited list that uses path expressions to create ValueExtractors. If multiple elements exist, then a MultiExtractor is used. To create a KeyExtractor, then start the path expression with a key() pseudo-function.

Natural ordering for the index is assumed.

Example:

  • Create a index on the attribute lastname in the orders cache.

    create index "orders" lastname
    

28.1.5.2 Removing an Index from the Cache

The DROP INDEX statement removes the index based on the given ValueExtractor. This statement is available only for the command-line tool. The syntax is:

DROP INDEX [ON] "cache-name" (value-extractor-list)

Example:

  • Remove the index on the lastname attribute in the orders cache.

    drop index "orders" lastname
    

28.1.6 Issuing Multiple Query Statements

The following section describes how to more efficiently issue multiple query statements to the cache.

28.1.6.1 Processing Query Statements in Batch Mode

The SOURCE statement allows for the "batch" processing of statements. The SOURCE statement opens and reads one or more query statements from a file represented by the given filename. The filename is an operating system-dependent path and must be enclosed in single or double quotes. Each query statement in the file must be separated by a semicolon (;) character. Sourcing is available only in the command-line tool, where you naturally want to load files consisting of sequences of commands. Source files may source other files. The syntax is:

SOURCE FROM [ FILE ] "filename"

SOURCE can be abbreviated with an "at" symbol (@) as in @"filename". On the command command line only, a "period" symbol '.' can be used as an abbreviation for '@' but must no contain quotes around the filename.

Example:

  • Process the statements in the file command_file.

    source from file "command_file"
    

    or,

    @ "command_file"
    

    or,

    . command_file
    

28.1.7 Persisting Cache Data to Disk

The statements in this section are used to backup and restore caches. The persistence statements rely on the persistence settings that are configured for a service. For details about the persistence feature and configuring persistence, see Administering Oracle Coherence. For details about running these statements as part of a script, see "Using Command-Line Tool Arguments."

28.1.7.1 Creating Snapshots

The CREATE SNAPSHOT statement persists the data partitions of a service to disk. The syntax is:

CREATE SNAPSHOT "snapshot-name" "service"

The snapshot-name argument is any user defined name and can include any of the following macros: %y - Year, %m - Month, %d - Day, %hh - Hour, %mm - Minute, %w - weekday, %M - Month name. The service argument is the name of the partitioned or federated cache service for which the snapshot is created.

Example:

Create a snapshot that is named Backup for a partitioned cache service that is named OrdersCacheService.

create snapshot "Backup" "OrdersCacheService"

Use the LIST SERVICES statement to see all active services and the currently configured persistence mode, quorum, and status of each service. Include the ENVIRONMENT option to see details about the persistence environment configuration. For example:

list services environment

28.1.7.2 Validating Snapshots

The VALIDATE SNAPSHOT statement is used to check whether a snapshot is complete and without error. The syntax is:

VALIDATE SNAPSHOT ["snapshot-directory" | "snapshot-name" "service-name"] [VERBOSE]

The snapshot-directory argument is the full path to a snapshot and must include the snapshot name. The snapshot-name argument is the name of an archived snapshot to be validated. The service-name argument is the name of the partitioned or federated cache service for which the snapshot was created. If the snapshot-name and service-name arguments are used, then the location is derived. The default snapshot location is USER_HOME/coherence/snapshots. To see detailed validation information, use the VERBOSE option. When specifying the VERBOSE option, each partition in the snapshot is opened and read and a summary of the caches persisted, including the number of entries and actual size, are displayed. Information about the number indexes, triggers, locks, and listeners are also displayed.

Example:

Validate a snapshot that is named Backup.

validate snapshot "c:\coherence\snapshots\MyCluster\OrdersCacheService\Backup"
   verbose

Validate a snapshot that is named Backup which is managed by the OrdersCacheService service:

validate snapshot "Backup" "OrdersCacheService" verbose

28.1.7.3 Recovering Snapshots

The RECOVER SNAPSHOT statement restores the data partitions of a service from disk. Any existing data in the caches of a service are lost.

Caution:

recovering a snapshot causes the current contents of the caches within the service to be dropped.

The syntax is:

RECOVER SNAPSHOT "snapshot-name" "service"

The snapshot-name argument is the name of a snapshot to recover. The service argument is the name of the partitioned or federated cache service for which the snapshot was created. If the service has not been explicitly suspended, then: the service is suspended; the snapshot recovered; and the service is resumed. For details about suspending and resuming a service see, "Suspending Services During Persistence Operations."

Example:

Recover a snapshot that is named Backup for a partitioned cache service that is named OrdersCacheService.

recover snapshot "Backup" "OrdersCacheService"

Use the LIST SNAPSHOTS statement to see a list of available snapshots. For example:

list snapshots "OrdersCacheService"

28.1.7.4 Archiving Snapshots

The ARCHIVE SNAPSHOT statement saves a snapshot to a central location. The location is specified in the snapshot archiver definition that is associated with a service. The syntax is:

ARCHIVE SNAPSHOT "snapshot-name" "service"

The snapshot-name argument is the name of a snapshot to archive. The service argument is the name of the partitioned or federated cache service for which the snapshot was created.

Example:

Archive a snapshot that is named Backup for a partitioned cache service that is named OrdersCacheService.

archive snapshot "Backup" "OrdersCacheService"

Use the LIST ARCHIVER statement to view the snapshot archiver definition location that is currently associated with a service. For example:

list archiver "OrdersCacheService"

28.1.7.5 Validating Archived Snapshots

The VALIDATE ARCHIVED SNAPSHOT statement is used to check whether an archived snapshot is complete and without error. The syntax is:

VALIDATE ARCHIVED SNAPSHOT "snapshot-name" "service-name" [VERBOSE]

The snapshot-name argument is the name of an archived snapshot to be validated. The service-name argument is the name of the partitioned or federated cache service for which the snapshot was created. To see detailed validation information, use the VERBOSE option.

Note:

  • The cluster operational configuration file and cache configuration file must be available in the classpath so that the defined snapshot archiver can be found.

  • Validating archived snapshots involves retrieving each individual partition, unpacking and validating the contents. The operating system default Java temporary directory is used for this operation (for example, the TEMP environment variable on Windows environments). If an archived snapshot is large, then consider changing the default Java temporary directory by using -Djava.io.tmpdir=/path.

Example:

Validate an archived snapshot that is named Backup.

validate archived snapshot "Backup" "OrdersCacheService" verbose

28.1.7.6 Retrieving Archived Snapshots

The RETRIEVE ARCHIVED SNAPSHOT statement is used to retrieve an archived snapshot so that it can be recovered using the RECOVER SNAPSHOT statement. For details about recovering snapshots, see "Recovering Snapshots." The syntax is:

RETRIEVE ARCHIVED SNAPSHOT "snapshot-name" "service" [OVERWRITE]

The snapshot-name argument is the name of an archived snapshot to retrieve. The service argument is the name of the partitioned or federated cache service for which the snapshot was created. Use the OVERWRITE option if a snapshot with the same name already exist in the persistence snapshot directory.

Example:

Retrieve an archived snapshot that is named Backup for a partitioned cache service that is named OrdersCacheService and overwrite the existing snapshot.

retrieve archived snapshot "Backup" "OrdersCacheService" overwrite

Use the LIST SNAPSHOTS statement with the ARCHIVED option to see a list of available archived snapshots. For example:

list archived snapshots "OrdersCacheService"

28.1.7.7 Removing Snapshots

The REMOVE SNAPSHOT statement is used to delete a snapshot or an archived snapshot from disk. The syntax is:

REMOVE [ARCHIVED] SNAPSHOT "snapshot-name" "service"

The snapshot-name argument is the name of a snapshot to remove. The service argument is the name of the partitioned or federated cache service for which the snapshot was created. Use the ARCHIVED option to remove an archived snapshot.

Example:

Remove a snapshot that is named Backup from a partitioned cache service that is named OrdersCacheService.

remove snapshot "Backup" "OrdersCacheService"

Remove an archived snapshot that is named Backup from a partitioned cache that is named OrdersCacheService.

remove archived snapshot "Backup" "OrdersCacheService"

28.1.7.8 Forcing Recovery

The FORCE RECOVERY statement is used to proceed with persistence recovery despite dynamic quorum policy objections. For details about the dynamic quorum policy, see "Using the Dynamic Recovery Quorum Policy" in Administering Oracle Coherence

Note:

Forcing persistence recovery may lead to partial or full data loss at the corresponding cache service.

The syntax is:
FORCE RECOVERY "service"

The service argument is the name of the partitioned cache service being recovered.

Example:

Force recovery for a partitioned cache service that is named OrdersCacheService.

force recovery "OrdersCacheService"

28.1.7.9 Suspending Services During Persistence Operations

Use the SUSPEND SERVICE and RESUME SERVICE to ensure persistence operations are performed on a non-active service. For some persistence operations, the service is automatically suspended and resumed when the statement is executed. The syntax is:

[RESUME | SUSPEND] SERVICE "service"

The service argument is the name of a partitioned cache service to be suspended or resumed.

Note:

Clients that send requests to a suspended service are blocked until the service resumes and the request completes. Services can be configured with a request timeout to ensure that clients are not blocked while waiting for a service to be resumed. A request timeout can be configured using the <request-timeout> element in a distributed scheme definition or the coherence.distributed.request.timeout system property.

Example:

  • Suspend a partitioned cache service that is named OrdersCacheService.

    suspend service "OrdersCacheService"
    
  • Resume a partitioned cache service that is named OrdersCacheService.

    resume service "OrdersCacheService"
    

28.1.8 Viewing Query Cost and Effectiveness

The EXPLAIN PLAN FOR and TRACE commands are used to create and output query records that are used to determine the cost and effectiveness of a query. A query explain record provides the estimated cost of evaluating a filter as part of a query operation. A query trace record provides the actual cost of evaluating a filter as part of a query operation. Both query records take into account whether or not an index can be used by a filter. See "Interpreting Query Records" for additional details on understanding the data provided in an explain plan record and trace record. The syntax for the commands are:

Query Explain Plan:

EXPLAIN PLAN FOR select statement | update statement | delete statement

Trace:

TRACE select statement | update statement | delete statement

Example:

EXPLAIN PLAN FOR select * from "mycache" where age=19 and firstName=Bob

or,

TRACE SELECT * from "MyCache" WHERE age=19

28.1.9 Handling Errors

The WHENEVER COHQLERROR THEN statement is used to specify the action CohQL takes when an error is encountered while executing a statement. The statement is often helpful when CohQL is used as part of a script. For details about running CohQL statements as part of a script, see "Using Command-Line Tool Arguments." The syntax:

WHENEVER COHQLERROR THEN [EXIT|CONTINUE]

Example:

Exit immediately if the validate snapshot statement returns and error:

whenver cohqlerror then exit
validate snapshot "/snapshots/MyCluster/DistCacheService/snapshot"

28.2 Using the CohQL Command-Line Tool

The CohQL command-line tool provides a non-programmatic way to interact with caches by allowing statements to be issued from the command line. The tool can be run using the com.tangosol.coherence.dslquery.QueryPlus class or, for convenience, a startup script is available to run the tool and is located in the COHERENCE_HOME/bin/ directory. The script is available for both Windows (query.cmd) and UNIX (query.sh).

The script starts a cluster node in console mode; that is, storage is not enabled on the node. This is the suggested setting for production environments and assumes that the node joins a cluster that contains storage-enabled cache servers. However, a single storage-enabled node can be created for testing by changing the storage_enabled setting in the script to true.

Note:

As configured, the startup script uses the default operational configuration file (tangosol-coherence.xml) and the default cache configuration file (coherence-cache-config.xml) that are located in the coherence.jar when creating/joining a cluster and configuring caches. For more information on configuring Coherence, see Understanding Configuration.

The script provides the option for setting the COHERENCE_HOME environment variable. If COHERENCE_HOME is not set on the computer, set it in the script to the location where Coherence was installed.

CohQL uses JLine for enhanced command-line editing capabilities, such as having the up and down arrows move through the command history. However, JLine is not required to use CohQL. The script automatically uses the jline.jar library that is located in the COHERENCE_HOME/lib/ directory. A different location can be specified by modifying the JLINE_HOME variable and classpath in the script. If the JLine library is not found, a message displays and CohQL starts without JLine capabilities.

28.2.1 Starting the Command-line Tool

The following procedure demonstrates how to start the CohQL command-line tool using the startup script and assumes that the storage_enabled setting in the script is set to false (the default):

  1. Start a cache server cluster node or ensure that an existing cache server cluster node is started.

    To start a cache server cluster node, open a command prompt or shell and execute the cache server startup script that is located in the /bin directory: cache-server.cmd on the Windows platform or cache-server.sh for UNIX platforms. The cache server starts and output is emitted that provides information about this cluster member.

  2. Open a command prompt or shell and execute the CohQL command-line startup script that is located in the /bin directory: query.cmd on the Windows platform or query.sh for UNIX platforms. Information about the Java environment displays. The command-line tool prompt (CohQL>) is returned.

    Note:

    When joining an existing cache server node, modify the startup script to use the same cluster settings as the existing cache server node, including the same cache configuration. You can also load the Coherence artifacts from a Grid ARchive (GAR) file using the -g argument when starting the command-line tool. For details about the command-line tool arguments, see "Using Command-Line Tool Arguments."

  3. Enter help at the prompt to view the complete command-line help. Enter commands to list the help without detailed descriptions.

    See "A Command-Line Example" for a series of query statements that exercise the command-line tool.

28.2.2 Using Command-Line Tool Arguments

The CohQL command-line tool includes a set of arguments that are read and executed before the CohQL> prompt returns. This is useful when using the script as part of a larger script– for example, as part of a build process or to pipe I/O. Enter help at the CohQL> prompt to view help for the arguments within the command-line tool.

Table 28-2 Coherence Query Language Command-Line Tool Arguments

Argument Description

-a name

Associate an application name with a GAR file. The application name is used to scope caches and services so that they are isolated from other applications that run on the same cluster. This argument is only used if the -g argument is specified. If the -a argument is not used, then the default application name is assigned when loading a GAR file.

-c

Exit the command-line tool after processing the command-line arguments. This argument should not be used when redirecting from standard input; in which case, the tool exits as soon as the command line arguments are finished being processed and the redirected input is never read.

-dp domain-partition list

Specifies a comma delimited list of domain partition names to use in a multi-tenant environment. This argument is only used if the -g argument is specified.

Note that the first domain partition in the list is automatically selected as the active domain partition and is used when creating a cache. To select a different partition, use the domain partition statement from CohQL command line to select the partition:

ALTER SESSION SET DOMAIN PARTITON partition-name

The partition-name value is the name of the domain partition to make active.

-e

Run the command-line tool in extended language mode. This mode allows object literals in update and insert commands. See the command-line help for complete usage information.

-f filename

Process the statements in the given file. The statements in the file must be separated by a semicolon (;). The file is an operating system-dependent path and must be enclosed in single or double quotes. Any number of -f arguments can be used.

-g gar

Load the given Grid ARchive (GAR) file or an exploded GAR file directory before running CohQL and use the default application name. The default application name is the GAR file name without the parent directory name. Use the -a argument to explicitly specify an application name.

-l statement

Execute the given statement. Statements must be enclosed in single or double quotes. Any number of -l arguments can be used.

-s

Run the command-line tool in silent mode to remove extraneous verbiage. This allows the command line tool to be used in pipes or filters by redirecting standard input (<myInput) and standard output (>myOuput).

-t

enable trace mode to print debug information.

-timeout value

Specifies the timeout value for CohQL statements in milli-seconds.

Examples

Return all entries in the contact cache and print the entries to the standard out then exit the command-line tool.

query.sh -c -l "select * from contact"

Return all entries in the dist-example cache and print the entries (suppressing extra verbiage) to the file named myOutput then exit the command-line tool.

query.cmd -s -c -l "select * from 'dist-example'" >myOutput

Process all the segments in the file named myStatements then exit the command-line tool.

query.sh -c -f myStatements

Read the commands from the myInput file and print the output (suppressing extra verbiage) to the file named myOutput.

query.sh -s <myInput >myOutput

Start the command line tool and load the application artifacts from a GAR file named contacts.gar that is located in the /applications directory.

query.sh -g /applications/contacts.gar

Start the command line tool and load the application artifacts from a GAR file named contacts.gar that is located in the /applications directory. Scope the application name to HRContacts.

query.sh -g /applications/contacts.gar -a HRContacts

28.2.3 Setting the Request Timeout

The default request timeout value for a service is set to infinity unless the value is explicitly set using either the <request-timeout> element for a service or the coherence.distributed.request.timeout system property for all services.

The CohQL command-line tool can be used to set a timeout value which applies to statements that are executed in the current session. The default timeout value for the command-line tool is 30 seconds. A timeout exception is thrown if a statement takes longer than 30 seconds to execute. There are two ways to change the timeout value:

  • The -timeout command line argument is used to set the timeout value in milliseconds. For example,

    query.sh -timeout 60000
    
  • The ALTER SESSION statement changes the timeout from the default or from any value specified by the -timeout argument and remains in effect until another ALTER SESSION statement is executed. The syntax is:

    ALTER SESSION SET TIMEOUT value
    

    The value can be an integer that specifies the total number of milliseconds. For example:

    ALTER SESSION SET TIMEOUT 45000
    

    The value can also be a string that specifies a duration. Valid values to use in the duration string are h for hours, m for minutes, s for seconds, and ms for milliseconds. For example:

    ALTER SESSION SET TIMEOUT '5m 30s'
    

28.2.4 A Command-Line Example

The following examples illustrate using the command-line tool on Windows. This example is intended for a single cluster member, so the storage_enabled setting in the startup script is set to true. The example illustrates creating and dropping a cache, storing and retrieving entries, and persisting caches to disk. It also highlights the use of the key() and value() pseudo-functions.

When starting the query.cmd script at the command prompt, information about the Java environment, the Coherence version and edition, and Coherence cache server is displayed. Enter query statements at the prompt (CohQL>).

Start the CohQL command-line tool:

C:/coherence/bin/query.cmd

Create a cache named employees:

CohQL> create cache "employees"

Insert an entry (key-value pair) into the cache:

CohQL> insert into "employees" key "David" value "ID-5070"

Insert an object into the cache:

CohQL> insert into "employees" value new com.my.Employee("John", "Doe", 
"address", 34)

Change the value of the key:

CohQL> update employees set value() = "ID-5080" where key() like "David"

Retrieve the values in the cache:

CohQL> select * from "employees"

Retrieve the value of a key that does not exist. An empty result set is returned:

CohQL> select key(), value() from "employees" where key() is "Richard"

Create a snapshot of the service to backup the cache to disk:

CohQL> create snapshot "Backup" "DistributedCache"

Delete an existing key in the cache. An empty result set is returned:

CohQL> delete from employees where key() = "David"

Delete the contents of the employees cache. An empty result set is returned:

CohQL> delete from "employees"

Destroy the employees cache:

CohQL> drop cache "employees"

Re-create the employees cache:

CohQL> create cache "employees"

Recover the cache contents from the backup:

CohQL> recover snapshot "Backup" "DistributedCache"

Retrieve the keys and value in the employees cache. Notice that the deleted key and value are present:

CohQL> select key(), value() from "employees"

Destroy the employees cache:

CohQL> drop cache "employees"

Exit the command-line tool:

CohQL> bye

28.3 Building Filters in Java Programs

The FilterBuilder API is a string-oriented way to filter a result set from within a Java program, without having to remember details of the Coherence API. The API provides a set of four overloaded createFilter factory methods in the com.tangosol.util.QueryHelper class.

The following list describes the different forms of the createFilter method. The passed string uses the Coherence query WHERE clause syntax (as described in "Filtering Entries in a Result Set"), but without the literal WHERE. The forms that take an Object array or Map are for passing objects that are referenced by bind variables. Each form constructs a filter from the provided Coherence query string.

  • public static Filter createFilter(String s)—where s is a String in the Coherence query representing a Filter.

  • public static Filter createFilter(String s, Object[] aBindings)—where s is a String in the Coherence query representing a Filter and aBindings is an array of Objects to use for bind variables.

  • public static Filter createFilter(String s, Map bindings)—where s is a String in the Coherence query representing a Filter and bindings is a Map of Objects to use for bind variables.

  • public static Filter createFilter(String s, Object[] aBindings, Map bindings)—where s is a String in the Coherence query representing a Filter, aBindings is an array of Objects to use for bind variables, and bindings is a Map of Objects to use for bind variables.

These factory methods throw a FilterBuildingException if there are any malformed, syntactically incorrect expressions, or semantic errors. Since this exception is a subclass of RuntimeException, catching the error is not required, but the process could terminate if you do not.

Example

The following statement uses the createFilter(String s) form of the method. It constructs a filter for employees who live in Massachusetts but work in another state.

..
QueryHelper.createFilter("homeAddress.state = 'MA'  and workAddress.state != 'MA'")
...

This statement is equivalent to the following filter/extractor using the Coherence API:

AndFilter(EqualsFilter(ChainedExtractor(#getHomeAddress[], #getState[]), MA),
NotEqualsFilter(ChainedExtractor(#getWorkAddress[], #getState[]), MA)))

The QueryHelper class also provides a createExtractor method that enables you to create value extractors when building filters. The extractor is used to both extract values (for example, for sorting or filtering) from an object, and to provide an identity for that extraction. The following example demonstrates using createExtractor when creating an index:

cache.addIndex(QueryHelper.createExtractor("key().lastName"),/*fOrdered*/ true,
  /*comparator*/ null);

28.4 Additional Coherence Query Language Examples

This section provides additional examples and shows their equivalent Coherence API calls with instantiated Objects (Filters, ValueExtractors, Aggregators, and so on). The simple select * examples that highlight Filters can understand the translation for FilterBuilder API if you focus only on the Filter part. Use the full set of examples to understand the translation for the QueryBuilder API and the command-line tool.

The examples use an abbreviated form of the path syntax where the cache name to qualify an identifier is dropped.

The Java language form of the examples also use ReducerAggregator instead of EntryProcessors for projection. Note also that the use of KeyExtractor should no longer be needed given changes to ReflectionExtractor in Coherence 3.5.

28.4.1 Simple SELECT * FROM Statements that Highlight Filters

  • Select the items from the cache orders where 40 is greater than the value of the price key.

    select * from "orders" where 40 > price
    
  • Select the items from the cache orders where the value of the price key exactly equals 100, and the value of insurance key is less than 10 or the value of the shipping key is greater than or equal to 20.

    select * from "orders" where price is 100 and insurance < 10 or shipping >= 20
    
  • Select the items from the cache orders where the value of the price key exactly equals 100, and either the value of insurance key is less than 10 or the value of the shipping key is greater than or equal to 20.

    select * from "orders" where price is 100 and (insurance < 10 or shipping >= 20)
    
  • Select the items from the cache orders where either the value of the price key equals 100, or the bar key equals 20.

    select * from "orders" where price = 100 or shipping = 20
    
  • Select the items from the cache orders where the value of the insurance key is not null.

    select * from "orders" where insurance is not null
    
  • Select the items from the cache employees where the emp_id key has a value between 1 and 1000 or the bar.emp key is not "Smith".

    select * from "employees" where emp_id between 1 and 1000 or bar.emp is not "Smith"
    
  • Select items from the cache orders where the value of item key is similar to the value "coat".

    select * from "orders" where item like "coat%"
    
  • Select items from the cache employees where the value of emp_id is in the set 5, 10, 15, or 20.

    select * from "employees" where emp_id in (5,10,15,20)
    
  • Select items from the cache employees where emp_id contains the list 5, 10, 15, and 20.

    select * from "employees" where emp_id contains (5,10,15,20)
    
  • Select items from the cache employees where emp_id contains the all of the items 5, 10, 15, and 20.

    select * from "employees" where emp_id contains all (5,10,15,20)
    
  • Select items from the cache employees where emp_id contains any of the items 5, 10, 15, or 20.

    select * from "employees" where emp_id contains any (5,10,15,20)
    
  • Select items from cache employees where the value of foo key is less than 10 and occurs in the set 10, 20.

    select * from "employees" where emp_id < 10 in (10,20)
    

28.4.2 Complex Queries that Feature Projection, Aggregation, and Grouping

  • Select the home state and age of employees in the cache ContactInfoCache, and group by state and age.

    select homeAddress.state, age, count() from "ContactInfoCache" group by homeAddress.state, age
    
  • Select the spurious frobit key from the orders cache. Note, an empty result set is returned.

    select frobit,supplier,sum(amount),avg(price) from "orders" group by supplier
    
  • For the items in the orders cache that are greater than $1000, select the items, their prices and colors.

    select item_name,price,color from "orders" where price > 1000
    
  • Select the total amount for items from the orders cache. The Double type is assumed.

    select sum(amount) from "orders"
    
  • Select the total amount for items from the orders cache as a Long type.

    select long_sum(amount) from "orders"
    
  • Select the total amount for items from the orders cache as a BigDecimal type.

    select bd_sum(amount) from "orders"
    
  • Select the total amount for items from the orders cache where the color attribute is red or green.

    select sum(amount) from "orders" where color is "red" or color is "green"
    
  • Select the total amount and average price for items from the orders cache

    select sum(amount),avg(price) from "orders"
    
  • Select one copy of the lastname and city from possible duplicate rows from the employees cache, where the state is California.

    select distinct lastName,city from "employees" where state = "CA"
    

28.4.3 UPDATE Examples

  • For employees in the employees cache whose ranking is above grade 7, increase their salaries by 10% and add 50 hours of vacation time.

    update "employees" set salary = salary*1.10, vacation = vacation + 50 where grade > 7
    

28.4.4 Key and Value Pseudo-Function Examples

This section provides examples of how to use the key() and value() pseudo-functions. For additional examples, see "A Command-Line Example".

  • Select the employees from the ContactInfoCache whose home address is in Massachusetts, but work out of state.

    select key().firstName, key().lastName from "ContactInfoCache" where homeAddress.state is 'MA' and workAddress.state != "MA"
    
  • Select the employees from the ContactInfoCache cache whose age is greater than 42.

    select key().firstName, key().lastName, age from "ContactInfoCache" where age > 42