Creating JDBC Process Servers using the Wizard

You use JDBC Process Servers to connect to remote databases using the standard JDBC drivers. To be able to configure the Process Server, you must know the configuration details of JDBC in Java. Check the documentation that ships with your jdbc driver before you continue. JDBC setup is database vendor-specific.

JDBC stores the URL with the driver class name in Database object along with the library name that contains your JDBC driver jar. The ObjectReference determines the Database object that will be used.

The driver must reside on the classpath of the application server or uploaded into a Library; the library can be specified in the JDBC Process Server wizard.

Connection Pool

You enable connection pooling to increase performance of JDBC-related REL expressions and JDBC processes. Careful considerations need to be made when you enable connection pooling, as dirty connections will be used from the pool where variables may no longer hold their initial value. Also, database locks are not automatically released when a connection is put back into the pool after a process using it has reached a final state. Other connection-related assumptions should be verified prior to using connection pooling for JDBC processes.

See Connecting to Remote Databases with JDBC for more information on configuring connection pooling.

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.

Prerequisites

• DB2 9.5.2 to 11.5, Oracle 11g to 19c or MS SQL 2000 to 2019.
  • See Creating JDBC Process Server Manually
• username/password and connection string for the database you want to access
• a free slot in your ProcessServerService.JDBC.limit license key
• Connectivity from the central Redwood server to the host and port of the Oracle database
• Connectivity from the Secure Gateway to the host and port of the Oracle database

Procedure

Tip: If the Process Server unexpectedly reaches status Shutdown, inspect the Operator Messages ("Monitoring > Operator Messages"). Process Servers will create Operator Messages when errors are encountered.

Creating a JDBC Process Server using the Wizard.

1. Navigate to "Environment > Process Servers".
2. Choose New Process Server from the context menu.
3. Select JDBC.
4. Select the appropriate JDBC driver in the list, if there is none for your database, upload the driver jar to a library and reference the library in the Library field (you might have to reopen the JDBC Process Server wizard). Choose Next. Note that the wizard only supports database versions as listed in the prerequisites; if your database is not listed, follow the instructions are outlined in the Creating JDBC Process Servers topic.
5. Fill all fields, refer to the below table for more information. Choose Next.
6. At this moment, Redwood Server will have created a Database object in Environment > Databases with the prefix System_Internal.
7. Choose Refresh, if the status of the Process Server is Running you have successfully completed the wizard, choose Finish. In the case of an error, the Process Server will have status Shutdown and an Operator Message will have been created with the details.

Creating a Credential

Note: The JDBC wizard creates a credential for you.

1. Navigate to "Security > Credentials".
2. Choose New Credential from the context menu, select JDBC as credential type.
3. Ensure the Partition matches the Partition of the JDBC Process Server.
4. Fill-in the name of the Database object created earlier into the Endpoint field.
5. Fill-in a username (must be the same as the Process Server parameter) and its password.
6. Optionally, specify a virtual username.

Example

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

1. Navigate to "Scripting > Libraries" and choose Edit from the Custom_JDBC library. Create the library if it does not already exist.
2. On the JAR Files tab, choose Add, fill in a Name, choose Upload and locate the JAR file you wish to import.
3. Click Save & Close to exit the dialog.
4. Navigate to "Environment > Process Servers".
5. Choose New process Server from the context menu.
6. Select JDBC.
7. Fill a name into the Name field, specify Custom_Driver as the library and specify oracle.jdbc.OracleDriver in the Jdbc Driver Class Name field.
8. Fill the FQDN hostname of the database server, in this case ora6.example.com, and the Database, in this case example.
9. Fill the User Name and Password fields under Connection User From New Credential with the Oracle user and password. In this case Scott and tiger.
10. Choose Test Connection, the connection attempt must be successful, choose Next.
11. The initial status of the Process Server will be Created, you choose the Refresh button and the Process Server should reach status Running.
12. Choose Finish.

See Also

• Connecting to Remote Databases with JDBC
• JDBC Drivers
• Creating JDBC Process Servers Manually
• JDBC Process Definition Wizard
• JDBC Definition Types
• JDBC Definition Type