Generic JDBC connection

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

If you want to connect to multiple instances of the data source in a consistent way, a Cloud Pak for Data administrator can create a Custom JDBC connector for the data source. The connection will be available on the New connection page with the name, details, and properties that are defined by the administrator who creates the connector.

Supported JDBC versions

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

Prerequisites

  • 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.

  • Upload the JDBC driver (JAR file) for the data source to Cloud Pak for Data. See Importing JDBC drivers in the IBM Software Hub documentation for the procedure and required permissions to upload the JAR file to Cloud Pak for Data. By default, uploading JDBC driver files is disabled and users cannot view the list of JDBC drivers in the web client. An administrator must enable users to upload or view JDBC drivers in the IBM Software Hub documentation.

Connection details

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

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 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 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:

ssl=true
sslTrustStoreLocation=${truststore_file}
sslTrustStorePassword=${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.
Connector uses catalog structure Optional Some data sources use catalogs instead of schemas to structure data. Select this option after you consult the driver vendor's documentation to confirm that the database uses a catalog structure.
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}
Important: Do not manually replace the {row_limit} value with a number. This is part of the SQL command and the value will be inserted by the application running the command. The SQL criteria used in the appropriate fields may vary and is dependent on the database langauge.
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
Important: Do not manually replace the {row_limit} value with a number. This is part of the SQL command and the value will be inserted by the application running the command. The SQL criteria used in the appropriate fields may vary and is dependent on the database langauge.

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 Assets > New asset > Prepare data > Connect to a data source. See Adding a connection to a project.
In a deployment space
Click Import assets > Data access > Connection. See Adding data assets 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)

Federal Information Processing Standards (FIPS) compliance

This connection can be used on a FIPS-enabled cluster (FIPS tolerant); however, it is not FIPS-compliant. Test the connection to confirm that it works.

Example JDBC URLs

Important: Follow the driver vendor's documentation to confirm what additional properties to include.
Databricks
Driver: com.simba.spark.jdbc.Driver

jdbc:spark://<DatabricksHost>:443/default;transportMode=http;ssl=1;AuthMech=3; httpPath=<Databricks_compute_resources_URL>;connCatalog=<WKC_catalog>;UID=token; PWD=<password>?EnableArrow=

For more information, see Building the connection URL for the legacy Spark driver.


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

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

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

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

Required properties for the Microsoft SQL Server driver:

  • 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

With a SID:
jdbc:oracle:thin:@<Hostname>:<Port>:<ORACLE_SID>

With a SERVICE_NAME:
jdbc:oracle:thin:@//<Hostname>:<Port>/<ORACLE_SERVICE_NAME>

For an SSL connection to an Oracle database, you must provide a certificate in the connection properties and include this code for the URL to pass the certificate to the driver:

?javax.net.ssl.trustStore=${truststore_file}&javax.net.ssl.trustStorePassword=${truststore_password}

Example formats for an SSL connection to an Oracle database:

With a SID:
jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=<hostname>)(PORT=<port>))(CONNECT_DATA=(SID=<ORACLE_SID>)))?javax.net.ssl.trustStore=${truststore_file}&javax.net.ssl.trustStorePassword=${truststore_password}

With a SERVICE_NAME:
jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=<hostname>)(PORT=<port>))(CONNECT_DATA=(SERVICE_NAME=<ORACLE_SERVICE_NAME>)))?javax.net.ssl.trustStore=${truststore_file}&javax.net.ssl.trustStorePassword=${truststore_password}

Parent topic: Supported connections