9 Using the Java Database Connectivity Handler

Learn how to use the Java Database Connectivity (JDBC) Handler, which can replicate source transactional data to a target or database.

Topics:

9.1 Overview

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 Providers

For more information about using the JDBC API, see http://docs.oracle.com/javase/8/docs/technotes/guides/jdbc/index.html.

Parent topic: Using the Java Database Connectivity Handler

9.2 Detailed Functionality

The JDBC Handler replicates source transactional data to a target or database by using a JDBC interface.

Topics:

Parent topic: Using the Java Database Connectivity Handler

9.2.1 Single Operation Mode

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.

Parent topic: Detailed Functionality

9.2.2 Oracle Database Data Types

The following column data types are supported for Oracle Database targets:

  • NUMBER
  • DECIMAL
  • INTEGER
  • FLOAT
  • REAL
  • DATE
  • TIMESTAMP
  • INTERVAL YEAR TO MONTH
  • INTERVAL DAY TO SECOND
  • CHAR
  • VARCHAR2
  • NCHAR
  • NVARCHAR2
  • RAW
  • CLOB
  • NCLOB
  • BLOB
  • TIMESTAMP WITH TIMEZONEFoot 1
  • TIME WITH TIMEZONEFoot 2

Parent topic: Detailed Functionality

9.2.3 MySQL Database Data Types

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

Parent topic: Detailed Functionality

9.2.4 Netezza Database Data Types

The following column data types are supported for Netezza database targets:

  • byteint
  • smallint
  • integer
  • bigint
  • numeric(p,s)
  • numeric(p)
  • float(p)
  • Real
  • double
  • char
  • varchar
  • nchar
  • nvarchar
  • date
  • time
  • Timestamp

Parent topic: Detailed Functionality

9.2.5 Redshift Database Data Types

The following column data types are supported for Redshift database targets:

  • SMALLINT 
  • INTEGER
  • BIGINT
  • DECIMAL
  • REAL
  • DOUBLE
  • CHAR
  • VARCHAR
  • DATE
  • TIMESTAMP

Parent topic: Detailed Functionality

9.3 Setting Up and Running the JDBC Handler

The following sections provide instructions for configuring the JDBC Handler components and running the handler.

Note:

Use the JDBC Metadata Provider with the JDBC Handler to obtain column mapping features, column function features, and better data type mapping.

Topics:

Parent topic: Using the Java Database Connectivity Handler

9.3.1 Java Classpath

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

Parent topic: Setting Up and Running the JDBC Handler

9.3.2 Handler Configuration

You configure the JDBC Handler operation using the properties file. These properties are located in the Java Adapter properties file (not in the Replicat properties file).

To enable the selection of the JDBC Handler, you must first configure the handler type by specifying gg.handler.name.type=jdbc and the other JDBC properties as follows:

Table 9-1 JDBC Handler Configuration Properties

Properties Required/ Optional Legal Values Default Explanation

gg.handler.name.type

Required

jdbc

None

Selects the JDBC Handler for streaming change data capture into name.

gg.handler.name.connectionURL

Required

A valid JDBC connection URL

None

The target specific JDBC connection URL.

gg.handler.name.DriverClass

Target database dependent.

The target specific JDBC driver class name

None

The target specific JDBC driver class name.

gg.handler.name.userName

Target database dependent.

A valid user name

None

The user name used for the JDBC connection to the target database.

gg.handler.name.password

Target database dependent.

A valid password

None

The password used for the JDBC connection to the target database.

gg.handler.name.maxActiveStatements

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.

Parent topic: Setting Up and Running the JDBC Handler

9.3.3 Statement Caching

To speed up DML operations, JDBC driver implementations typically allow multiple statements to be cached. This configuration avoids repreparing a 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 the underlying JDBC driver supports. 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:

INSERT

(table name, operation type, ordered after-image column indices)

UPDATE

(table name, operation type, ordered after-image column indices)

DELETE

(table name, operation type)

TRUNCATE

(table name, operation type)

Parent topic: Setting Up and Running the JDBC Handler

9.3.4 Setting Up Error Handling

The JDBC Handler supports using the REPERROR and HANDLECOLLISIONS Oracle GoldenGate parameters. See Reference for Oracle GoldenGate.

You must configure the following properties in the handler properties file to define the mapping of different error codes for the target database.

gg.error.duplicateErrorCodes

A comma-separated list of error codes defined in the target database that indicate a duplicate key violation error. Most of the drivers of the JDBC drivers return a valid error code so, REPERROR actions can be configured based on the error code. 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 the target database.

In some cases, the JDBC driver errors occur 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 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
Setting Codes

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

Parent topic: Setting Up and Running the JDBC Handler

9.4 Sample Configurations

The following sections contain sample configurations for the databases supported by the JDBC Handler from the Java Adapter properties file.

Topics:

Parent topic: Using the Java Database Connectivity Handler

9.4.1 Sample Oracle Database Target

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

Parent topic: Sample Configurations

9.4.2 Sample Oracle Database Target with JDBC Metadata Provider

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

Parent topic: Sample Configurations

9.4.3 Sample MySQL Database Target

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

Parent topic: Sample Configurations

9.4.4 Sample MySQL Database Target with JDBC Metadata Provider

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

Parent topic: Sample Configurations


Footnote Legend

Footnote 1:

Time zone with a two-digit hour and a two-digit minimum offset.


Footnote 2:

Time zone with a two-digit hour and a two-digit minimum offset.