Creating JDBC Process Servers Manually

If you need to connect to a database not currently supported by the JDBC process server wizard, you set up your process server manually.

Parameters

The following JDBC-specific process server parameters are available:

JDBCOracleDBMSOutput - Use Oracle DBMS Output?.
- JDBCOracleDBMSOutputDestination - either stderr.log or stdout.log. Defaults to stderr.log for CSV, and stdout.log for all others.
- JDBCOracleDBMSOutputHeader - header to print before the dbms output. Defaults to <h3>Output</h3><hr><pre> for HTML, <output> for XML and nothing for all others.
- JDBCOracleDBMSOutputFooter - footer to print after the dbms output. Defaults to </pre></hr> for HTML, </output> for XML and nothing for all others.
JDBCServer - The alternative process server that is used to enable Queue jumping.
JDBCStatementFeedbackFormat Format of the SQL statement feedback in JDBC jobs. Possible options are: Simple, Normal or Extended.
JDBCStatementTimingFormat Should the duration of SQL statements in JDBC jobs be printed and if so, in milliseconds or in pretty format. Possible options are: None, MilliSeconds, Pretty (Default).
DefaultRunAsUser - Though not specifically JDBC-related, this process server parameter allows you to specify the default Run As User for all process definitions running on the process server.
v7PLSQLApiAvailable - This process server parameter is automatically set by the process server when the version 7 PL/SQL API is detected.

JDBC Drivers

Redwood Platform ships with drivers for DB2 9.1 to 11.5, Oracle 11g to 19c, SQL Server 2000 to 2019, and PostgreSQL version 9 to 12.3. If you need different JDBC drivers, you upload the driver to a Custom_Driver library that you reference on the process server.

When you upload a driver to a library, you specify the library and driver class name on the database object.

Prerequisites

username/password and connection string for the database you want to access.
a free slot in your ProcessServerService.JDBC.limit license key.

Procedure

Create the Credential

Navigate to "Security > Credentials".
Choose New Credential from the context-menu.
Ensure the Partition field contains the same value as the process server.
Select JDBC in the Protocol field.
Fill the username and password into the Real User and Password fields, retype the password into the Retype field.
In the Endpoint field, fill the name of the process server.
Choose Save & Close.

Upload a Driver to a Library

Note that this step is only required when there is no JDBC driver on the classpath of your application server.

Retrieve the latest JDBC driver for your database from the database vendor's website.
Rename the JAR file, the name must contain only US ASCII letters, underscores, numbers, and dots(``.).
Navigate to "Scripting > Libraries".
Select Import JAR from the context-menu.
Select or create a library for the JAR file; note that the library name must start with the Custom_ prefix and only one version of a JDBC driver for a specific database vendor should be uploaded per library.
Choose Browse and locate the JAR file to upload.
Choose Send.

Create a Database

Navigate to Environment > Databases.
Choose New Database from the context-menu.
Fill in a Name.
Select Oracle Thin in the Jdbc Driver Class Name field.
Select Jdbc Url > Advanced and fill jdbc:oracle:thin:@ora12.example.com:1521/orcl into the field.
Select example in the Connection User field.
Choose Save & Close.

Create the JDBC Process Server

Navigate to "Environment > Process Servers".
Choose New Process server from the context-menu, select Manual Setup.
Fill a name into the Name field, select a partition (optional).
Choose Add service and select JDBCService.
On the Parameters.
Choose Save & Close.

Add an Object Reference to Database Object

JDBC process servers need an object reference named Jdbc_ProcessServer_Database_Ref pointing at the database containing the database connection settings.

Issue the following code in the Shell:

{
  Partition p = jcsSession.getPartitionByName(<partition>);
  Database db = jcsSession.getDatabaseByName(p, <database_object_name>);
  ProcessServer ps = jcsSession.getProcessServerByName(p, <process_server_name>);
  ObjectReference objRef = ps.createObjectReference(db);
  objRef.setName("Jdbc_ProcessServer_Database_Ref");
  jcsSession.persist();
}

Example

Connecting to Oracle 12c with JDBC Driver

Redwood Platform ships with Oracle JDBC drivers; if you want to use a newer driver version, then you will need to upload Oracle JDBC drivers to a library and reference it in your Database object. See the Connecting to Oracle 12c with JDBC Driver in Library example below.

Navigate to "Security > Credentials".
Choose New Credential from the context-menu.
Select Example in the Partition field and JDBC in the Protocol field.
Fill example and someSillyPassword into the Real User and Password fields, respectively, retype someSillyPassword into the Retype field.
In the Endpoint field, fill JDBC_Oracle12c_DB.
Choose Save & Close.
Navigate to Environment > Databases.
Choose New Database from the context-menu.
Select Example in the Partition field and fill JDBC_Oracle12c_DB into the Name field.
Select Oracle Thin in the Jdbc Driver Class Name field.
Select Jdbc Url > Advanced and fill jdbc:oracle:thin:@ora12.example.com:1521/orcl into the field.
Select example@JDBC_Oracle12c_DB in the Connection User field.
Choose Save & Close.
Navigate to "Environment > Process Servers".
Choose New Process Server from the context-menu; select Manual Setup and choose Next.
Select Example in the Partition field and fill JDBC_Oracle12c_ProcessServer into the Name field.
Choose Add service and select JDBCService.
Choose Save & Close.
Navigate to "Scripting > Shell" and fill the following code into the Shell field and choose Submit command.

Code to issue in the Shell:

{
  Partition p = jcsSession.getPartitionByName("Example");
  Database db = jcsSession.getDatabaseByName(p, "JDBC_Oracle12c_DB");
  ProcessServer ps = jcsSession.getProcessServerByName(p, "JDBC_Oracle12c_ProcessServer");
  ObjectReference objRef = ps.createObjectReference(db);
  objRef.setName(Database.OBJECT_REFERENCE_NAME_JDBC_PROCESSSERVER);
  jcsSession.persist();
}

Connecting to Oracle 12c with JDBC Driver in Library

You want to connect to an Oracle database with the ojdbc7.jar driver.

The driver ships with the following info:

Name: Oracle Thin Client
Database URL: jdbc:oracle:thin:@<host>:<port>:<SID>
Alternate database URL: jdbc:oracle:thin:@<host>:<port>/<service_name>
Driver Name: oracle.jdbc.OracleDriver

Navigate to "Security > Credentials".
Choose New Credential from the context-menu.
Select Example in the Partition field and JDBC in the Protocol field.
Fill example and someSillyPassword into the Real User and Password fields, respectively, retype someSillyPassword into the Retype field.
In the Endpoint field, fill JDBC_Oracle12cLib_DB.
Choose Save & Close.
Navigate to "Scripting > Libraries".
Choose Edit from the context-menu of the Custom_Driver library; if the library does not exist, create it.
On the JAR Files tab, choose Add, fill in a name into the Name field and choose upload to locate the JAR file on your system.
Choose Save and Close.
Navigate to Environment > Databases.
Choose New Database from the context-menu.
Select Example in the Partition field and fill JDBC_Oracle12cLib_DB into the Name field.
Select Custom_Driver in the Library field.
Fill oracle.jdbc.OracleDriver into the Jdbc Driver Class Name field.
Select Jdbc Url > Advanced and fill jdbc:oracle:thin:@ora12.example.com:1521/orcl into the field.
Select example@JDBC_Oracle12cLib_DB in the Connection User field.
Choose Save & Close.
Navigate to "Environment > Process Servers".
Choose New Process Server from the context-menu; select Manual Setup and choose Next.
Select Example in the Partition field and fill JDBC_Oracle12cLib_ProcessServer into the name field.
Choose Add service and select JDBCService.
Choose Save & Close.
Navigate to "Scripting > Shell" and fill the following code into the Shell field and choose Submit command.