1.1

There is a window in which a DML operation executed on the SQLFire system has not yet been placed into
the internal queue of DBSynchronizer. The record is guaranteed to be placed into the internal DBSynchronizer
queue only when the DML operation is completed.
This restriction also applies to DML operations placed on a gateway sender queue for WAN replication.
• Case-insensitive identifiers required
If your third-party database provides an option for using case-sensitive SQL identifiers, you must configure
the database to use case-insensitive identifiers. For example, if you use MySQL as a backend database you
must set the lower_case_table_name server system variable to 1. See lower_case_table_names in the
MySQL documentation for more information.
Per the SQL-99 standard, DBSynchronizer treats identifiers as insensitive unless they are delimited in quotes.
Specifically, DBSynchronizer converts identifiers to uppercase characters for INSERT operations (but not
DELETE operations), so the backend database must be compliant with case-insensitive identifiers.
• Auto-generated key configuration
If cached tables require automatic generation of identity column values, then you should use SQLFire to
automatically generate the key values but disable generated keys in the backend database. See Considerations
for Auto-Generated Keys on page 181.
Configuring DBSynchronizer
You install and configure DBSynchronizer similar to the way you would install any other AsyncEventListener
implementation, using the CREATE ASYNCEVENTLISTENER statement. However, DBSynchronizer requires
initialization attributes to establish a connection with a third-party database.
Prerequisites
Preconfigure the third-party database with the schema and table definitions using the same names as those used
in the SQLFire database. When you deploy DBSynchronizer, you specify an initialization String of the form
DRIVER_CLASS_NAME,DB_URL. DRIVER_CLASS_NAME is the name of the JDBC driver for the third-party
database (the class that implements java.sql.Driver). DB_URL is the full JDBC connection string to use to connect
to the third-party database. Include any required connection parameters, such as user credentials, database names,
and so forth, in the URL. DBSynchronizer uses the string to obtain a java.sql.Connection to the third party
RDBMS.
Procedure
The following procedure describes how to configure SQLFire with DBSynchronizer to apply table DML operations
to a third-party database.
Note: The example commands in this procedure use MySQL Community Server 5.5.18 as the third-party
database with the Connector/J 5.1.18 driver. However, the general steps are the same regardless of which
database you use. Consult your third-party database and JDBC driver documentation for more information.
Note: If you are synchronizing tables in a MySQL database, you must configure MySQL to use
case-insensitive identifiers. See Restrictions and Limitations on page 200.
1. Configure the third-party database
Before you configure DBSynchronizer in SQLFire, install and configure the third-party database. Create the
database schema and table definitions for all of the tables that you intend to use in SQLFire.
To ensure that the tables stay in synch with those in SQLFire, only create the table definitions in the third-party
database. Insert and update rows only in SQLFire, so that DBSynchronizer can apply your changes to the
database.
201
Using DBSynchronizer to Apply DML to an RDBMS