JDBC Drivers
This topic lists the JDBC drivers that are shipped with RunMyJobs and shows the basic database URL syntax and driver name for each.
By default, all of these JDBC drivers support secure SSL connections. However, each has its own way of establishing SSL connections within RunMyJobs. In some cases, multiple certificates may be required, but this topic covers the most common SSL connection strategy, where a client connects to the database with a root CA certificate. In this strategy, only one certificate should be present in the associated RunMyJobs Credential.
Note: JDBC drivers must be in the classpath and accessible to RunMyJobs. If the driver you want to use is not on the classpath, upload the JAR file containing the driver into a RunMyJobs Library, then navigate to Environment > Databases and enter its name in the Connection >Library field for your database.
IBM DB2
-
Database URL syntax:
jdbc:db2://<host>:<port>/<db> -
Driver name:
com.ibm.db2.jcc.DB2Driver
No special query parameters are needed to connect with SSL. Here is an example of a full connection string with SSL enabled:
jdbc:db2://localhost:55000/testdb:sslConnection=true
The root CA certificate should be associated with a Credential as an X509_TrustedCertificate.
Oracle Thin Client
-
Database URL syntax:
jdbc:oracle:thin:@<host>:<port>:<SID> -
Driver name:
oracle.jdbc.OracleDriver
The Oracle Thin Client JDBC driver requires the following parameters in the JDBC connection URL to establish an SSL connection to the database and use the trusted root certificate in the Redwood credential store:
javax.net.ssl.keyStore=NONE
javax.net.ssl.trustStore=NONE
Here is an example of a full connection string:
jdbc:oracle:thin:@tcps://localhost:2484/orclpdb1?javax.net.ssl.keyStore=NONE&javax.net.ssl.trustStore=NONE
The root CA certificate should be associated with a Credential as an X509_TrustedCertificate.
The full documentation for this driver can be found here.
PostgreSQL
-
Database URL syntax:
jdbc:postgresql://localhost:5432/example -
Driver name:
org.postgresql.Driver
To successfully connect with SSL, this driver requires the following URL query parameter:
sslfactory=org.postgresql.ssl.DefaultJavaSSLFactory
Here is an example of a full connection string:
jdbc:postgresql://localhost:5437/postgres?ssl=true&sslmode=verify-ca&sslfactory=org.postgresql.ssl.DefaultJavaSSLFactory
The root CA certificate should be associated with a Credential as an X509_TrustedCertificate.
The full documentation for this driver can be found here.
Microsoft SQL Server
-
Database URL syntax:
jdbc:sqlserver://<host>:<port>[[;DatabaseName=<db>] [<property>=<value>] ...] -
Driver name:
com.microsoft.sqlserver.jdbc.SQLServerDriver
Microsoft SQL Server negotiates SSL within its TDS login process, so you cannot associate the certificate with a Credential. Instead, you must use the connection property trustServerCertificate=true. For example:
jdbc:sqlserver://myhost:1433;encrypt=true;trustServerCertificate=true;databaseName=master
This means the certificate that is sent by SQL Server is always trusted, so RunMyJobs bypasses certificate validation.
Note: MS SQL Server must be configured to listen on a static port (default 1433) and accept SQL Server authentication (also known as mixed mode or SQL Server and Windows authentication). The above syntax should cover most implementations; more information on Microsoft SQL Server JDBC connect strings can be found on MSDN. Note that integrated authentication will use the credentials of the owner of the central Redwood Server process and that the examples on MSDN ften illustrate integrated authentication. When Redwood Server is run on UNIX, integrated authentication will require additional configuration that is out of the scope of this document. Furthermore, kerberos authentication is also possible, however, kerberos authentication configuration is out of the scope of this document.
Tip: When you attempt to retrieve date-related data from an MS SQL Server database using the MS SQL Server JDBC driver in combination with a Java 11 JVM, the date will be off by two days. This is an issue in the MS SQL Server JDBC driver version 3 and you have the following options to work around this issue: Install the hotfix released by Microsoft or use the version 2 or 4 of the MS SQL Server JDBC driver. See the Microsoft JDBC team's blog.
SAP/Sybase Adaptive Server Enterprise, Sybase IQ, SQL Anywhere, and Replication Server
You will have to retrieve the driver from SAP and import it into a custom library.
-
Database URL syntax:
jdbc:sybase:Tds:<host>:<port>/<db> -
Driver name:
com.sybase.jdbc3.jdbc.SybDriver
SAP HANA
-
Database URL syntax:
jdbc:sap://<host>:<port>[?<option1>=<value1>[&<option2>=<value2>]*] -
Driver name:
com.sap.db.jdbc.Driver
To use the SAP HANA JDBC driver, you must get it from SAP and import it into a custom RunMyJobs Library. If you have Apache Maven, you can retrieve it from mvn ngdbc.
If SAP HANA is configured to accept SSL connections with self-signed certificates, the JDBC connection string can be relatively simple. For example:
jdbc:sap://myhost:39041/?encrypt=true&validateCertificate=true
The above example assumes that port 39041 is the port of a tenant database in the SAP HANA server.
Note: The Test Connection button for an SAP HANA database may not work. However, if you create a JDBC Process Server for the database, it will connect successfully.
Examples
The following examples illustrate how to use the above drivers in a JDBC Process Server named Example.JDBCProcessServer connecting to a non-BTP HANA database:
IBM DB2
| Tab | Field | Value |
|---|---|---|
| Database | Partition | Example |
| Database | Name | JDBCProcessServer |
| Database | Description | Production IBM DB2 database |
| Database | Connection > Library |
|
| Database | Connection > Jdbc Driver Class Name | com.ibm.db2.jcc.DB2Driver |
| Database | Jdbc Url > Advanced | jdbc:db2://db2.example.com:50000/msln |
Oracle
| Tab | Field | Value |
|---|---|---|
| Database | Partition | Example |
| Database | Name | JDBCProcessServer |
| Database | Description | Production Oracle database |
| Database | Connection > Library |
|
| Database | Connection > Jdbc Driver Class Name | oracle.jdbc.OracleDriver |
| Database | Jdbc Url > Avanced | jdbc:oracle:thin:@oracle.example.com:1521/orcl |
PostgreSQL
| Tab | Field | Value |
|---|---|---|
| Database | Partition | Example |
| Database | Name | JDBCProcessServer |
| Database | Description | Production PostgreSQL database |
| Database | Connection > Library |
|
| Database | Connection > Jdbc Driver Class Name | org.postgresql.Driver |
| Database | Jdbc Url > Avanced | jdbc:postgresql://localhost:5432/example |
SQL Server
| Tab | Field | Value |
|---|---|---|
| Database | Partition | Example |
| Database | Name | JDBCProcessServer |
| Database | Description | Production MS SQL database |
| Database | Connection > Library |
|
| Database | Connection > Jdbc Driver Class Name | com.microsoft.sqlserver.jdbc.SQLServerDriver |
| Database | Jdbc Url > Avanced | jdbc:microsoft:sqlserver://mssql.example.com:1433;DatabaseName=Example |
Note: MS SQL Server does not by default listen on TCP network ports, this has to be configured.
SAP/Sybase Adaptive Server Enterprise
| Tab | Field | Value |
|---|---|---|
| Database | Partition | Example |
| Database | Name | JDBCProcessServer |
| Database | Description | Production SAP ASE database |
| Database | Connection > Library | Custom_JDBCDrivers |
| Database | Connection > Jdbc Driver Class Name | com.sybase.jdbc3.jdbc.SybDriver |
| Database | Jdbc Url > Avanced | jdbc:sybase:Tds:syb.example.com:5000/Example |
SAP HANA
This example validates the certificate, this means the certificate must be in your Java trust store or in a X509_TrustedCertificate credential. Ensure your central server can access your SAP HANA database, you need to set the Allow only specific IP addresses and IP ranges to its IP address or an IP address range.
| Tab | Field | Value |
|---|---|---|
| Database | Partition | Example |
| Database | Name | JDBCProcessServer |
| Database | Description | Production PostgreSQL database |
| Database | Connection > Library | Custom_JDBCDrivers |
| Database | Connection > Jdbc Driver Class Name | com.sap.db.jdbc.Driver |
| Database | Jdbc Url > Avanced | jdbc:sap:ecc1234.hana.us10.hanacloud.ondemand.com:443&encrypt=true&validateCertificate=true |