The Generic Java Database Connectivity (JDBC) is a handler that lets you replicate source transactional data to a target system or database. This chapter explains the Java Database Connectivity (JDBC) Handler and includes examples so that you can understand this functionality.
Topics:
The Generic Java Database Connectivity (JDBC) Handler lets you replicate source transactional data to a target system or database by using a JDBC interface. You can use it with targets that support JDBC connectivity.
You can use the JDBC API to access virtually any data source, from relational databases to spreadsheets and flat files. JDBC technology also provides a common base on which the JDBC Handler was built. The JDBC handler with the JDBC metadata provider also lets you use Replicat features such as column mapping and column functions. For more information about using these features, see Using the Metadata Provider
For more information about using the JDBC API, see the Oracle Java JDBC API Documentation website for more information:
http://docs.oracle.com/javase/8/docs/technotes/guides/jdbc/index.html
The JDBC Handler replicates source transactional data to a target (or database) using a JDBC interface.
Topics:
The JDBC Handler performs SQL operations on every single trail record (row operation) when the trail record is processed by the handler. The JDBC Handler does not use the BATCHSQL
feature of the JDBC API to batch operations.
The following column data types are supported for MySQL Database targets:
INT
REAL
FLOAT
DOUBLE
NUMERIC
DATE
DATETIME
TIMESTAMP
TINYINT
BOOLEAN
SMALLINT
BIGINT
MEDIUMINT
DECIMAL
BIT
YEAR
ENUM
CHAR
VARCHAR
Instructions for configuring the JDBC Handler components and running the handler are described in the following sections.
Note:
You should use the JDBC Metadata Provider with the JDBC Handler to obtain better data type mapping, column mapping, and column function features.
Topics:
The JDBC Java Driver location must be included in the class path of the handler using the gg.classpath
property.
For example, the configuration for a MySQL database could be:
gg.classpath= /path/to/jdbc/driver/jar/mysql-connector-java-5.1.39-bin.jar
You configure the JDBC Handler operation using the properties file. To enable the selection of the JDBC handler, one must first configure the handler type by specifying gg.handler.name.type=jdbc
and the other JDBC properties as follows:
Table 7-1 JDBC Handler Configuration Properties
Properties | Required/ Optional | Legal Values | Default | Explanation |
---|---|---|---|---|
|
Required |
|
None |
Selects the JDBC Handler for streaming change data capture into JDBC. |
|
Required |
A valid JDBC connection URL. |
None |
The target specific JDBC connection URL. |
|
Target database dependent. |
The target specific JDBC driver class name. |
None |
The target specific JDBC driver class name. |
|
Target database dependent. |
A valid user name. |
None |
The user name used for the JDBC connection to the target database. |
|
Target database dependent. |
A valid password. |
None |
The password used for the JDBC connection to the target database. |
|
Optional |
Unsigned integer. |
Target database dependent |
If this property is not specified, the JDBC Handler queries the target dependent database metadata indicating maximum number of active prepared SQL statements. Some targets do not provide this metadata so then the default value of 256 active SQL statements is used. If this property is specified, the JDBC Handler will not query the target database for such metadata and use the property value provided in the configuration. In either case, when the JDBC handler finds that the total number of active SQL statements is about to be exceeded, the oldest SQL statement is removed from the cache to add one new SQL statement. |
Typically, JDBC driver implementations allow multiple statements to be cached in order to speed up the execution of the DML operations. This avoids repreparing the statement for operations that share the same profile or template.
The JDBC Handler uses statement caching to speed up the process and caches as many statements as supported by the underlying JDBC driver. The cache is implemented by using an LRU cache where the key is the profile of the operation (stored internally in the memory as an instance of StatementCacheKey
class), and the value is the PreparedStatement
object itself.
A StatementCacheKey
object contains the following information for the various DML profiles that are supported in the JDBC Handler:
DML operation type | StatementCacheKey contains a tuple of: |
---|---|
|
(table name, operation type, ordered after-image column indices) |
|
(table name, operation type, ordered after-image column indices) |
|
(table name, operation type) |
|
(table name, operation type) |
The JDBC Handler supports using the REPERROR
and HANDLECOLLISIONS
Oracle GoldenGate parameters, see Reference for Oracle GoldenGate for Windows and UNIX.
Additional configuration is required in the handler properties file to define the mapping of different error codes for the target database as follows:
gg.error.duplicateErrorCodes
A comma-separated list of error codes defined in the target database that indicates a duplicate key violation error. Most the JDBC drivers return a valid error code so REPERROR
actions can be configured based on the error code configured. For example:
gg.error.duplicateErrorCodes=1062,1088,1092,1291,1330,1331,1332,1333
gg.error.notFoundErrorCodes
A comma-separated list of error codes that indicate missed DELETE
or UPDATE
operations on target database.
In some cases, the JDBC driver errors when an UPDATE
or DELETE
operation does not modify any rows in the target database so no additional handling is required by the JDBC Handler.
Most JDBC drivers do not return an error when a DELETE
or UPDATE
is affecting zero rows so the JDBC Handler automatically detects a missed UPDATE
or DELETE
operation and triggers an error to indicate a not-found error to the Replicat process. The Replicat process can then execute the specified REPERROR
action.
The default error code used by the handler is the value zero. When you configure this property to a non-zero value, the configured error code value is used when the handler triggers a not found error. For example:
gg.error.notFoundErrorCodes=1222
gg.error.deadlockErrorCodes
A comma-separated list of error codes that indicate a deadlock error in the target database. For example:
gg.error.deadlockErrorCodes=1213
Oracle recommends that you set a non-zero error code for the gg.error.duplicateErrorCodes
, gg.error.notFoundErrorCodes
, and gg.error.deadlockErrorCodes
properties because Replicat does not respond to REPERROR
and HANDLECOLLISIONS
configuration when the error code is set to zero.
Sample Oracle Database Target Error Codes
gg.error.duplicateErrorCodes=1 gg.error.notFoundErrorCodes=0 gg.error.deadlockErrorCodes=60
Sample MySQL Database Target Error Codes
gg.error.duplicateErrorCodes=1022,1062 gg.error.notFoundErrorCodes=1329 gg.error.deadlockErrorCodes=1213,1614
The following sections contain sample configurations for the databases supported by the JDBC Handler from the Java Adapter properties file:
Topics:
gg.handlerlist=jdbcwriter gg.handler.jdbcwriter.type=jdbc #Handler properties for Oracle database target gg.handler.jdbcwriter.DriverClass=oracle.jdbc.driver.OracleDriver gg.handler.jdbcwriter.connectionURL=jdbc:oracle:thin:@<DBServer address>:1521:<database name> gg.handler.jdbcwriter.userName=<dbuser> gg.handler.jdbcwriter.password=<dbpassword> gg.classpath=/path/to/oracle/jdbc/driver/ojdbc5.jar goldengate.userexit.timestamp=utc goldengate.userexit.writers=javawriter javawriter.stats.display=TRUE javawriter.stats.full=TRUE gg.log=log4j gg.log.level=INFO gg.report.time=30sec javawriter.bootoptions=-Xmx512m -Xms32m -Djava.class.path=.:ggjava/ggjava.jar:./dirprm
gg.handlerlist=jdbcwriter gg.handler.jdbcwriter.type=jdbc #Handler properties for Oracle database target with JDBC Metadata provider gg.handler.jdbcwriter.DriverClass=oracle.jdbc.driver.OracleDriver gg.handler.jdbcwriter.connectionURL=jdbc:oracle:thin:@<DBServer address>:1521:<database name> gg.handler.jdbcwriter.userName=<dbuser> gg.handler.jdbcwriter.password=<dbpassword> gg.classpath=/path/to/oracle/jdbc/driver/ojdbc5.jar #JDBC Metadata provider for Oracle target gg.mdp.type=jdbc gg.mdp.ConnectionUrl=jdbc:oracle:thin:@<DBServer address>:1521:<database name> gg.mdp.DriverClassName=oracle.jdbc.driver.OracleDriver gg.mdp.UserName=<dbuser> gg.mdp.Password=<dbpassword> goldengate.userexit.timestamp=utc goldengate.userexit.writers=javawriter javawriter.stats.display=TRUE javawriter.stats.full=TRUE gg.log=log4j gg.log.level=INFO gg.report.time=30sec javawriter.bootoptions=-Xmx512m -Xms32m -Djava.class.path=.:ggjava/ggjava.jar:./dirprm
gg.handlerlist=jdbcwriter gg.handler.jdbcwriter.type=jdbc #Handler properties for MySQL database target gg.handler.jdbcwriter.DriverClass=com.mysql.jdbc.Driver gg.handler.jdbcwriter.connectionURL=jdbc:<a target="_blank" href="mysql://">mysql://</a><DBServer address>:3306/<database name> gg.handler.jdbcwriter.userName=<dbuser> gg.handler.jdbcwriter.password=<dbpassword> gg.classpath=/path/to/mysql/jdbc/driver//mysql-connector-java-5.1.39-bin.jar goldengate.userexit.timestamp=utc goldengate.userexit.writers=javawriter javawriter.stats.display=TRUE javawriter.stats.full=TRUE gg.log=log4j gg.log.level=INFO gg.report.time=30sec javawriter.bootoptions=-Xmx512m -Xms32m -Djava.class.path=.:ggjava/ggjava.jar:./dirprm
gg.handlerlist=jdbcwriter gg.handler.jdbcwriter.type=jdbc #Handler properties for MySQL database target with JDBC Metadata provider gg.handler.jdbcwriter.DriverClass=com.mysql.jdbc.Driver gg.handler.jdbcwriter.connectionURL=jdbc:<a target="_blank" href="mysql://">mysql://</a><DBServer address>:3306/<database name> gg.handler.jdbcwriter.userName=<dbuser> gg.handler.jdbcwriter.password=<dbpassword> gg.classpath=/path/to/mysql/jdbc/driver//mysql-connector-java-5.1.39-bin.jar #JDBC Metadata provider for MySQL target gg.mdp.type=jdbc gg.mdp.ConnectionUrl=jdbc:<a target="_blank" href="mysql://">mysql://</a><DBServer address>:3306/<database name> gg.mdp.DriverClassName=com.mysql.jdbc.Driver gg.mdp.UserName=<dbuser> gg.mdp.Password=<dbpassword> goldengate.userexit.timestamp=utc goldengate.userexit.writers=javawriter javawriter.stats.display=TRUE javawriter.stats.full=TRUE gg.log=log4j gg.log.level=INFO gg.report.time=30sec javawriter.bootoptions=-Xmx512m -Xms32m -Djava.class.path=.:ggjava/ggjava.jar:./dirprm
Footnote Legend
Footnote 1:Time zone with a two digit hour and a two digit minimum offset.
Time zone with a two digit hour and a two digit minimum offset.