Generic JDBC connection

Use the Generic JDBC connection to connect to a data source that has no connection defined for Cloud Pak for Data.

Supported JDBC versions
Prerequisites
Connection details
Where you can use this connection
DataStage properties
Example JDBC URLs

Supported JDBC versions

The JDBC JAR file must be compatible with JDK 8, JDBC version 4.2 or earlier.

Prerequisites

Upload the JDBC driver for the data source to the web client.

Cloud Pak for Data common core services must be installed. The common core services are automatically installed by services that rely on them. If you don't see the Platform connections page, it's because none of the services that are installed in your environment rely on the common core services.
The upload step must be done by a Cloud Pak for Data administrator with the Administer platform permission:
Log in to Cloud Pak for Data.
From the navigation menu, select Data > Platform connections.
Open the JDBC drivers tab.
Browse for the JAR file or files that are required to connect to the data source.
Click Upload.

Connection details

To create the Generic JDBC connection, you must have the following information about the data source.

Table 1. Connection details for Generic JDBC
Information	Status	Description
Required JAR files	Required	The JAR files required to connect to the data source. If a JDBC connection requires multiple JAR files, ensure that you know which JAR files are needed.
JDBC driver class	Required (Except for DataStage)	The name of the JDBC driver class. This name is specified by the JDBC driver provider. For details, refer to the JDBC driver documentation.
JDBC URL	Required	The JDBC URL used to connect to the database. Refer to the JDBC driver documentation for information about the syntax of the URL.
SSL details	Conditional For DataStage, see DataStage properties.	If the port that you specified in the JDBC URL is configured to accept SSL connections, ensure that you select The port is configured to accept SSL connections. If your data source uses chained certificates, you can specify the contents of multiple certificates. If you 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: `key1=${truststore_file}` `key2=${truststore_password}`
Batch size	Optional	The maximum number of rows to send to the database. The default is 2000.
Fetch size	Optional	The maximum number of rows to fetch from the database for each call. The default is 2000.
Case-sensitivity	Conditional	If the IDs in the database are case-sensitive, select Indicates whether ID values for this database are case sensitive.
JDBC properties	Optional	The JDBC properties to use to connect to the database. Specify the properties with this format: `key1=value1;key2=value2;key3=value3` Refer to the JDBC driver documentation for information about supported properties.
Row limit support	Optional	Indicates whether the driver supports a row limit in a SELECT statement. You can specify None, Prefix, or Suffix.
Row limit prefix	Conditional	If you set Row limit support to Prefix, specify the prefix that is used to indicate a row limit in a SELECT statement. Use $`{row_limit}` to indicate where to put the row limit value. For example: `TOP ${row_limit}`
Row limit suffix	Conditional	If you set Row limit support to Suffix, specify the suffix that is used to indicate a row limit in a SELECT statement. Use $`{row_limit}` to indicate where to put the row limit value. For example: `LIMIT ${row_limit} ROWS`

For Credentials and Certificates, you can use secrets if a vault is configured for the platform and the service supports vaults. For information, see Using secrets from vaults in connections.

Choose the method for creating a connection based on where you are in the platform

In a project Click Add to project > Connection. See Adding a connection to a project.

In a catalog Click Add to catalog > Connection. See Adding a connection asset to a catalog.

In a deployment space Click Add to space > Connection. See Adding connections to a deployment space.

In the Platform assets catalog Click New connection. See Adding platform connections.

Next step: Add data assets from the connection (Optional)

Where you can use this connection

You can use Generic JDBC connections in the following workspaces and tools:

Analytics projects

AutoAI (Watson Machine Learning)
DataStage (DataStage service). See DataStage properties.
Metadata import (Watson Knowledge Catalog)
SPSS Modeler (SPSS Modeler service)

Catalogs

Platform assets catalog
Other catalogs (Watson Knowledge Catalog)

Data Virtualization service You can connect to this data source from Data Virtualization.

DataStage properties

These properties do not apply when the use datastage properties flag in the connector's Details card in the DataStage canvas is set to true:

SSL details: To connect to a database that uses an SSL port, provide the EncryptionMethod attribute as SSL and the ValidateServerCertificate attribute as false in the connection URL.
Batch size
Fetch size
Case-sensitivity
Row limit support
Row limit prefix
Row limit suffix
Table type

DataStage restrictions

The CREATE statement is not supported for connecting to a MongoDB database.
For connections to MongoDB or to Databases for MongoDB, DataStage only supports the Append table operation.

Example JDBC URLs

Important: Follow the driver vendor's documentation to confirm what additional properties to include.

Db2 Driver: com.ibm.db2.jcc.DB2Driver

jdbc:db2://<Hostname>:<Port>/<DatabaseName>

Microsoft SQL Server Driver: com.microsoft.sqlserver.jdbc.SQLServerDriver

This driver works for Windows authentication. Download the latest Microsoft JDBC Driver for SQL Server.

Required properties:

server_address
authenticationScheme=NTLM
integratedSecurity=true

jdbc:sqlserver://<server_address>:<port><\your_instance>;database=<DatabaseName>;authenticationScheme=NTLM;integratedSecurity=true;domain=<your_domain>

MongoDB Driver: com.ddtek.jdbc.mongodb.MongoDBDriver and Port is SSL-Enabled selected.

jdbc:datadirect:mongodb://<MongoDBHost>:<MongoDBPort>;DatabaseName=<DatabaseName>;SchemaMap=/tmp/mongodb.config;EncryptionMethod=SSL;ValidateServerCertificate=false;ReadOnly=false;

MySQL Driver: com.mysql.cj.jdbc.Driver

jdbc:mysql://<MySQLHost>:<MySQLPort>/<DatabaseName>?useSSL=false

Oracle Driver: oracle.jdbc.OracleDriver

jdbc:oracle:thin:<Username>/<Password>@<Hostname>:<Port>:<ORACLE_SID>

Parent topic: Supported connections