DBSynchronizer Initialization Parameters

The built-in DBSynchronizer implementation requires specific parameters in order to initiate a connection to a JDBC data source. You can supply these parameters either in a separate parameter file (recommended), or in a string passed to the INITPARMS option of the CREATE ASYNCEVENTLISTENER statement.

Syntax

To provide DBSynchronizer parameters in a text-formatted parameter file, specify the key=value pairs with one parameter definition per line:
driver=db-driver-class
url=db-url
[ user=user-name
[ password=plain-text-password | secret=encrypted-password ] ]
[ transformation=transformation-name ]
[ keySize=size ]
[ errorTries=number-of-tries ]
[ errorFile=file-name ]
[ skipIdentityColumns= true | false ]
To define the parameters directly in the INITPARAMS option, include the key=value pairs on the same line, separated by commas:
INITPARAMS 'driver=db-driver-class,url=db-url[,user=user-name][,password=plain-text-password] | [,secret=encrypted-password]]
[,transformation=transformation-name][,keySize=size][,errorTries=number-of-tries][,errorFile=file-name]
[,skipIdentityColumns=true | false]
For backwards compatibility with earlier versions of DBSynchronizer, you can omit the key names for the first four parameters when specifying parameters in the INITPARAMS string. However, if you omit any keywords, you must specify the first four parameters in the exact order shown:
INITPARAMS 'db-driver-class,db-url,user-name,plain-text-password[,transformation=transformation-name]
[,keySize=size][,errorTries=number-of-tries][,errorFile=file-name]
[,skipIdentityColumns= true | false]
Note: As a best practice, always specify the key=value pairs. Always use a text-formatted parameter file, so that you can later change parameters as necessary without having to drop and recreate the DBSynchronizer configuration.
driver=db-driver-class
Required. Specifies the fully-qualified class name of the JDBC driver to use for connecting to the external database. For example, to connect to an external Apache derby database you would specify a driver value similar to db-driver=org.apache.derby.jdbc.EmbeddedDriver.
url=db-url
Required. 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 URL string similar to url=jdbc:derby:newDB;create=true;.
Note: Any password that you specify as part of the URL (for example by appending ?user=username&password=password) may appear in external exception messages. GemFire XD 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 parameters.
user=user-name
password=plain-text-password
secret=encrypted-password
(Optional.) These parameters optionally specify the username and password to use for the connection. If you do include these options, then GemFire XD uses the db-url value as-is, and that URL must provide the credentials required to connect to the database.
If you use the password parameter, specify the plain-text password for the user. For example:
User=user-name
password=plain-text-password
To avoid storing plain-text passwords in scripts that are used to configure DBSynchronizer, you can instead specify the secret parameter with the encrypted password for the user. Use the encrypt-password command with the external option to encrypt the password. When you specify an encrypted password with the secret parameter, you can also include the transformation and keySize parameters to match the transformation algorithm and key size that you specified when encrypting the password with encrypt-password. For example:
User=user-name
secret=encrypted-password
transformation=transformation-name
keySize=size
transformation=transformation-name
keySize=size
(Optional.) Use these parameters along with the secret parameter if generated an encrypted password and used a non-default algorithm and key size with the encrypt-password command. If you do not include these options, the default transformation is AES with a keySize of 128 bits.
errorTries=number-of-tries
errorFile=file-name
(Optional.) These parameters specify a custom error file to use for logging DBSynchronizer errors, as well as the number of times DBSynchronizer should retry a failed write operation to the database before logging an error to that file. By default, DBSynchronizer does not retry a failed operation, but instead immediately logs an error message. The default error file is dbsync_failed_dmls.xml, which stores failed entries in dbsync_failed_dmls_entries.xml.
skipIdentityColumns= true | false
(Optional.) If a table has an automatically-generated identity column, by default DBSynchronizer does not apply the generated identity column value to the synchronized database (skipIdentityColumns=true). DBSynchronizer expects that the backend database table also has an identity column, and that the database generates its own identity value.
If you would prefer that DBSynchronizer apply the same GemFire XD-generated identity column value to the underlying database, then set skipIdentityColumns=false in the DBSynchronizer parameter file. In this case, you must ensure that the table in the external database does not itself include an identity column constraint.
Setting skipIdentityColumns=false is also useful in cases where INSERT statements use functions such as CURRENT TIMESTAMP, but you want the external database to use the same timestamp value that was generated in GemFire XD.
Note: If a synchronized table is updated in the scope of a transaction, then DBSynchronizer delivers the full DML statement to the underlying database instead of synchronizing table values.

Basic Examples

The following listing shows a sample DBSynchronizer parameter file for use with MySQL, using a plain text password:
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/gfxddb
user=gfxduser
password=gfxdpassword
To reference the above parameter file when configuring DBSynchronizer, you would simply use the file=filename as the INITPARAMS string. For example:
CREATE ASYNCEVENTLISTENER mydbsynch
(
  LISTENERCLASS 'com.pivotal.gemfirexd.callbacks.DBSynchronizer'
  INITPARAMS 'file=/Users/gfxd/dbsync-params.txt'
  ENABLEPERSISTENCE true
  DISKSTORENAME mystore 
)
SERVER GROUPS ( SG1 );
The same parameters could instead be provided (separated by commas) directly in the INITPARAMS string, as shown here:
CREATE ASYNCEVENTLISTENER mydbsynch
(
  LISTENERCLASS 'com.pivotal.gemfirexd.callbacks.DBSynchronizer'
  INITPARAMS 'driver=com.mysql.jdbc.Driver,url=jdbc:mysql://localhost:3306/gfxddb,user=gfxduser,password=gfxdpassword'
  ENABLEPERSISTENCE true
  DISKSTORENAME mystore 
)
SERVER GROUPS ( SG1 );

In the above case, however, you would not be able to change any parameter values except by dropping and then recreating the DBSynchronizer configuration.

For backwards compatibility, DBSynchronizer accepts the first four parameters without providing the associated keyword (driver, url, user, and password) when providing the parameters in an INITPARMS string. However, if any keywords are omitted, the first four parameters must be defined in the exact order shown. For example, the following string omits the first two keywords, but still defines the associated parameter values in the correct order:
CREATE ASYNCEVENTLISTENER mydbsynch
(
  LISTENERCLASS 'com.pivotal.gemfirexd.callbacks.DBSynchronizer'
  INITPARAMS 'com.mysql.jdbc.Driver,jdbc:mysql://localhost:3306/gfxddb,user=gfxduser,password=gfxdpassword'
  ENABLEPERSISTENCE true
  DISKSTORENAME mystore 
)
SERVER GROUPS ( SG1 );

Example Using an Encrypted Password

Instead of storing a plain text password in the parameters file, you can generate an encrypted password and use it as the value of the secret parameter. For example:
$ gfxd encrypt-password external -mcast-port=10334
Enter User Name: gfxduser
Enter password: gfxdpassword
Re-enter password: gfxdpassword
Connecting to distributed system: mcast=/239.192.81.1:10334
Encrypted to 25325ffc3345be8888eda8156bd1c313
An example parameter file would then contain the entries:
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/gfxddb
user=gfxduser
secret=25325ffc3345be8888eda8156bd1c313

If you specified a custom transformation or key size when encrypting the password, you would also need to include the transformation and keySize parameters. See encrypt-password.