7 Using the Java Database Connectivity Handler

This chapter explains the Java Database Connectivity (JDBC) Handler and includes examples so that you can understand this functionality.

Topics:

7.1 Overview

The Generic Java Database Connectivity (JDBC) Handler provides the ability to replicate source transactional data to a target or database using a JDBC interface and is applicable for targets that support JDBC connectivity.

Using the JDBC API, you can 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 provide additional capability to use Replicat features, such as column mapping and column functions, see Using the Metadata Provider

See the Oracle Java JDBC API Documentation website for more information:

http://docs.oracle.com/javase/8/docs/technotes/guides/jdbc/index.html

7.2 Detailed Functionality

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

Topics:

7.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.

7.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

7.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

7.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

7.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

7.3 Setting Up and Running the JDBC Handler

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:

7.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

7.3.2 Handler Configuration

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

gg.handler.name.type

Required

jdbc

None

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

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.

7.3.3 Statement Caching

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:

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)

7.3.4 Setting Up Error Handling

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

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

7.4 Sample Configurations

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

Topics:

7.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

7.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

7.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

7.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

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.