Generic JDBC connector (DataStage)

Use the Generic JDBC connector to create jobs that read and write data from JDBC data sources.

Note: To use the Generic JDBC connector on IBM Cloud, you must set up a remote runtime engine with DataStage as a Service Anywhere. For more information, see DataStage as a Service Anywhere.

Prerequisites

Upload the required JAR file. See Importing JDBC drivers for the procedure and required permissions to upload the JAR file to Cloud Pak for Data.
Connect to the data source with the Generic JDBC connection:
- See Generic JDBC connection for the connection details.
- See Connecting to a data source in DataStage for information about how to create the connection in DataStage.

Important: Consult the driver vendor's documentation for the required properties, supported properties, and supported data types.

DataStage properties

In the Stage tab Properties section, select Use DataStage properties to access properties that are specific for DataStage. These properties provide more features and granular control of the flow execution, similar to the "optimized" connectors.

These properties do not apply when Use DataStage properties is selected:

Batch size
Fetch size
Case-sensitivity
Row limit support
Row limit prefix
Row limit suffix
Table type

Performance when connecting to a MySQL data source

When you use the Generic JDBC connector to connect to a MySQL data source, for improved performance with the Insert write mode, append the rewriteBatchedStatements parameter with its value set to true to the JDBC connection URL.

For example:

jdbc:mysql://<HOST>:<PORT>/<DATABASE>?rewriteBatchedStatements=true

SSL properties

SSL details: To connect to a database that uses an SSL port, set the EncryptionMethod property as SSL and the ValidateServerCertificate property as false in the connection URL.

You have two options to provide the SSL details:

Paste the self-signed certificate into the SSL Certificate box. The certificate will be imported into a temporary truststore and secured with a password. You must reference the truststore and password in the JDBC connection URL by specifying the connection properties in the JDBC properties box. For example:
```
ssl=true
sslTrustStoreLocation=${truststore_file}
sslTrustStorePassword=${truststore_password}
```
Provide the SSL properties in the connection URL. DataStage will create a truststore from the certificate and use the truststore in the runtime. Include the sslTrustStoreLocation and the sslTrustStorePassword properties as variables. Do not include the values. Example URL with SSL properties:
```
jdbc:db2://192.0.2.0:8030/DD1A:sslConnection=true;sslTrustStoreLocation=${truststore_file};
sslTrustStorePassword=${truststore_password};EncryptionMethod=SSL;ValidateServerCertificate=false;
```
Consult the JDBC driver vendor's documentation for which properties to use.

Restrictions

The CREATE statement is not supported for connecting to a MongoDB database.

For connections to MongoDB or to IBM Cloud® Databases for MongoDB, DataStage supports only the Append table action.