Configuring the database connection

Configuring the database connection involves configuring the JDBC driver and specifying values for the connection-related properties.

JDBC drivers

You must obtain the JDBC driver for the target database from the database vendor and install it according to the vendor's instructions. The drivers are usually provided as Java™ archives (.jar).

You must copy the JDBC driver .jar file to the following directory:

$OMNIHOME/gates/java

Database connection properties

To enable the gateway to communicate with the target database, you must specify values for the following properties:
  • Gate.Jdbc.Connections

    This property specifies the number of connections that the gateway makes to the target database. Increasing the number of connections increases the level of parallelism available to the gateway and potentially increases performance. Start with low values and increase as needed to find the desired performance level.

  • Gate.Jdbc.Driver

    This property specifies the JDBC driver. If the Gate.Jdbc.Driver property is left empty (the default), then the internal null JDBC driver is used. This internal JDBC driver is used for testing purposes to allow the generic portions of the gateway to be run without requiring a database connection to be configured. The null JDBC driver also simulates error insertion and row updating in order to test the error handling code of the gateway. The null JDBC driver is also useful for enabling users to familiarize themselves with the gateway operations before committing updates to their target database. It can be run in parallel to an existing database gateway, to allow this familiarization to be done without downtime of the existing gateway.

    Running the gateway in debug log level allows you to see the logging produced by the gateway and to inspect the resulting SQL that would have been sent to their target database.
    Note: The JDBC gateway persistent state should be deleted after running in test mode to delete alert history used for de-duplication.
  • Gate.Jdbc.Url

    This property specifies the URL of the target database.

  • Gate.Jdbc.Username

    This property specifies the user name for the target database.

  • Gate.Jdbc.Password

    This property specifies the password for the target database.

The following table lists example values for the Gate.Jdbc.Driver and Gate.Jdbc.Url properties for use with each database. Consult your driver documentation for more information about setting up database connections. Default values may be different depending on your setup.

Table 1. Example JDBC property values

DB2® LUW

Gate.Jdbc.Driver

com.ibm.db2.jcc.DB2Driver

Gate.Jdbc.Url

jdbc:db2://host_name:port/db_name

Where host_name is the name of the database host machine, port is the port number, and db_name is the name of the database. For example:

jdbc:db2://server.example.ibm.com:9999/REPORTER

DB2 z/OS®

Gate.Jdbc.Driver

com.ibm.db2.jcc.DB2Driver

Gate.Jdbc.Url

jdbc:db2://host_name:port/db_name

Where host_name is the name of the database host machine, port is the port number, and db_name is the name of the database. For example:

jdbc:db2://server.example.ibm.com:9999/REPORTER

Informix®

Gate.Jdbc.Driver

com.informix.jdbc.IfxDriver

Gate.Jdbc.Url

jdbc:informix-sqli://host_name:port/db_name:INFORMIXSERVER=server_name

Where host_name is the name of the database host machine, port is the port number, db_name is the name of the database, and server_name is the same as the host_name. For example:

jdbc:informix-sqli://server.example.ibm.com:1433/REPORTER:INFORMIXSERVER=server.example.ibm.com

Microsoft SQL Server

Gate.Jdbc.Driver

com.microsoft.sqlserver.jdbc.SQLServerDriver

Gate.Jdbc.Url

jdbc:sqlserver://host_name:port;databaseName=db_name;instanceName=instance_name;encrypt=false

Where host_name is the name of the database host machine, port is the port number, and db_name is the name of the database, `instance_name specifies the instance name of the database and encrypt specifies if encryption is enabled for the database. Both instance_name and encrypt are optional and may be omitted if default values are used to connect to the database. For more details on additional parameters that can be specified, please refer to the Microsoft JDBC Driver for SQL Server website.. The default port is 1433. For example:

jdbc:sqlserver://server.example.ibm.com:1433;databaseName=REPORTER;instanceName=MSSQLSERVER;encrypt=false

MySQL

Gate.Jdbc.Driver

com.mysql.cj.jdbc.Driver

Gate.Jdbc.Url

jdbc:mysql://host_name[,failover_host]:port/db_name[?param1=value1&param2=value2]

Where host_name is the name of the database host machine, failover_host is the name of the optional failover host, port is the port number, db_name is the name of the database, and param1 and param2 are optional parameters. The default port is 3306. For example:

jdbc:mysql://server.example.ibm.com:3306/alerts

Oracle

Gate.Jdbc.Driver

oracle.jdbc.driver.OracleDriver

Gate.Jdbc.Url

jdbc:oracle:thin:@host_name:port:db_name

Where host_name is the name of the database host machine, port is the port number, and db_name is the name of the database. The default port is 1521. For example:

jdbc:oracle:thin:@server.example.ibm.com:1521: REPORTER

Sybase

Gate.Jdbc.Driver

com.sybase.jdbc4.jdbc.SybDriver

Gate.Jdbc.Url

jdbc:sybase:Tds:host_name:port/db_name[?property=value;]

Where host_name is the name of the database host machine, port is the port number, db_name is the name of the database, and property is an optional parameter. For example:

jdbc:sybase:Tds:server.example.ibm.com:1521/REPORTER

Netezza

Gate.Jdbc.Driver

org.netezza.Driver

Gate.Jdbc.Url

jdbc:netezza://host_name:port/db_name

Where host_name is the name of the database host machine, port is the port number, and db_name is the name of the database. For example:

jdbc:netezza://server.example.ibm.com:5480/ALERTS