CREATE ASYNCEVENTLISTENER

Installs an AsyncEventListener implementation to SQLFire peers in a specified server group.

Syntax

CREATE ASYNCEVENTLISTENER listener-name
(
  LISTENERCLASS 'class-name'
  INITPARAMS 'init-params'
  [ MANUALSTART boolean-constant ]
  [ ENABLEBATCHCONFLATION boolean-constant ]
  [ BATCHSIZE integer-constant ]
  [ BATCHTIMEINTERVAL integer-constant ]
  [ ENABLEPERSISTENCE boolean-constant ]
  [ DISKSTORENAME  store-name]
  [ MAXQUEUEMEMORY integer-constant ]
  [ ALERTTHRESHOLD integer-constant ]
)
  SERVER GROUPS ( server_group_name [, server_group_name ]*)
LISTENERCLASS
The fully-qualified name of the class that implements the AsyncEventListener interface. For example, "user.application.TestAsyncEventListener". If you are configuring the built-in DBSynchronizer implementation, specify com.vmware.sqlfire.callbacks.DBSynchronizer.
INITPARAMS
Specify initialization parameters to pass to the init() method of the AsyncEventListener implementation. SQLFire passes the single string of initialization parameters when you execute the CREATE ASYNCEVENTLISTENER statement. You must include the INITPARAMS argument, even if it defines no parameters (INITPARAMS ''). If you send user credentials in the initialization parameters, consider encrypting the password using sqlf encrypt-password with the external option. Your listener will need to use the AsyncEventHelper.decryptPassword method to decrypt the password before sending credentials to the outside data source.
The built-in DBSynchronizer requires an initialization string of the format:
'db-driver-class,db-url[,user=user-name,[,password=plan-text-password|secret=encrypted-password[,transformation=tranformation-name][,keysize=size]]'
In this initialization string:
  • db-driver-class specifies the fully-qualified class name of the JDBC driver to use for connecting to the external database.
  • db-url specifies the JDBC connection string to use to connect to the external database. For example, to connect to an external Apache derby database, you would specify a DBSynchronizer INITPARAMS string similar to 'org.apache.derby.jdbc.EmbeddedDriver,jdbc:derby:newDB;create=true;'.
    Note: Any password that you specify in the JDBC URL (for example by appending ?user=username&password=password) may appear in external exception messages. SQLFire attempts to mask password values in exception messages by matching common patterns such as password=..., password=.., or pwd=... However, the actual value may appear in exception messages under certain circumstances. To prevent the password from appearing in external exceptions, omit the username and password from the URL and instead specify them using the remaining options.
  • [,user=user-name[,password=plan-text-password|secret=encrypted-password[,transformation=tranformation-name][,keysize=size]]' optionally specifies the username and password to use for the connection. If you do not specify these options, then SQLFire uses the db-url value is used as-is. If you use the password option, specify the plaint-text password for the user.

    To avoid storing plain-text passwords in scripts used to start DBSynchronizer, you can instead specify the secret option with the encrypted password for the user. Use the sqlf encrypt-password command with the external option to encrypt the password. When you specify an encrypted password with the secret option, you can also include the transformation and keysize options to match the transformation algorithm and key size that you specified when encrypting the password with sqlf encrypt-password. If you do not include these options, the default transformation is AES with a keysize of 128 bits.

As an alternative to specifying a complete DBSynchronizer initialization string, you can instead use the string file=path-to-file and include all necessary items as properties in the referenced ASCII text file. In this case, the file should have the format:
Driver=db-driver-class
URL=db-url
To specify the username and password outside of the JDBC URL, also include:
User=user-name
password=plain-text-password
Or, if you want to use an encrypted password in the properties file, specify the relevant options instead:
User=user-name
secret=encrypted-password
transformation=transformation-name
keysize=size
Using a properties file enables you to change DBSynchronizer initialization parameters after you have configured the listener in SQLFire. Only the file path and location are hard-coded in the DBSynchronizer configuration.
MANUALSTART
A boolean value that specifies whether you need to manually start the listener with SYS.START_ASYNC_EVENT_LISTENER. If you do not supply a value, the default is "true" and you must start the listener manually using SYS.START_ASYNC_EVENT_LISTENER.
ENABLEBATCHCONFLATION
A Boolean value that determines whether SQLFire should conflate messages. The default is false.
BATCHSIZE
The maximum number of messages that a batch can contain. The default is 100 messages.
BATCHTIMEINTERVAL
The maximum number of milliseconds that can elapse between sending batches. The default is 1000 milliseconds.
ENABLEPERSISTENCE
A Boolean value that determines whether SQLFire persists the event queue to disk for high availability. The default is "False."
DISKSTORENAME
The named disk store to use for persisting the event queue. If you specify a value, the named disk store must exist. If you specify a null value, SQLFire uses the default disk store for persistence.
MAXQUEUEMEMORY
The maximum amount of memory in megabytes that the queue can consume before overflowing to disk. The default is 100 megabytes.
ALERTTHRESHOLD
The maximum number of milliseconds that an event can remain in this queue before SQLFire logs an alert. The default is value is "0."
SERVER_GROUPS
A comma-separated list of server group names. SQLFire deploys the event listener implementation on all peers in the specified server group(s).
Note: The specified server groups must contain at least one data store member at the time you execute the CREATE ASYNCEVENTLISTENER statement.
Note: When you deploy an event listener (or DBSynchronizer) to listen for asynchronous events, specify a server group that contains no more than 2 data stores. SQLFire automatically designates one of the listeners as a "primary," and the remaining listeners become "secondaries". Every additional "secondary" can slow down the publisher. By default all SQLFire members that store data are members of a single "default" server group.

Examples

Install a basic AsyncEventListener implementation:

CREATE ASYNCEVENTLISTENER MyListener
(
   LISTENERCLASS 'user.application.TestAsyncEventListener'
   INITPARAMS ''
)  SERVER GROUPS ( SG1 );

Install a DBSynchronizer configuration with a persistent event queue:

CREATE ASYNCEVENTLISTENER mydbsynch
(
  LISTENERCLASS 'com.vmware.sqlfire.callbacks.DBSynchronizer'
  INITPARAMS 'org.apache.derby.jdbc.EmbeddedDriver,jdbc:derby:newDB;create=true;'
  ENABLEPERSISTENCE true
  DISKSTORENAME mystore 
)
SERVER GROUPS (primary_listener_group);
Install another DBSynchronizer for failover:
CREATE ASYNCEVENTLISTENER mydbsynch
(
  LISTENERCLASS 'com.vmware.sqlfire.callbacks.DBSynchronizer'
  INITPARAMS 'org.apache.derby.jdbc.EmbeddedDriver,jdbc:derby:newDB;create=true;'
  ENABLEPERSISTENCE true
  DISKSTORENAME mystore 
)
SERVER GROUPS (secondary_listener_group);

Create a new table and attach the installed DBSynchronizer:

CREATE TABLE TESTTABLE (ID INT NOT NULL, 
     NAME VARCHAR(10)) 
     ASYNCEVENTLISTENER(mydbsynch);
Note: You can optionally install the AsyncEventListener configuration after you associate a table with the listener name. Make sure that you use the same listener name with both the CREATE ASYNCEVENTLISTNER command and the CREATE TABLE command.