Preparing the federated server to access data sources through JDBC of Type 2

For Type 2 JDBC data sources, no need to download JDBC driver which has been installed by default in folder $INSTANCE_HOME/sqllib/federation/jdbc/lib on federation server.

Before you begin

  • The federated server must be able to access JDBC data sources.
  • Check the federated parameter to ensure that federation is enabled.
Recommendation: For the following data sources, use the specialized native wrappers instead of JDBC wrapper:

Procedure

  1. Check the federated parameter to ensure that federation is enabled.
  2. Check the installed data source driver in $INSTANCE_HOME /sqllib/federation/jdbc/lib on Federation server.
  3. (Db2 and Db2 for z/OS® only) Check that the db2jcc4.jar and db2jcc_license_cisuz.jar files are located in the $INSTANCE_HOME/sqllib/java/ directory.
  4. Get the driver package information below which will be used when registering server definitions for JDBC data sources.
    Table 1. JDBC data sources categorization
    Data Source DRIVER_PACKAGE DRIVER_CLASS URL*
    Amazon RedShift FOredshift.jar com.ibm.fluidquery.jdbc.redshift.RedshiftDriver jdbc:ibm:redshift://<servername>:<port>; DatabaseName=<DatabaseName>DetailsExample
    Amazon Aurora PostgreSQL FOpostgresql.jar com.ibm.fluidquery.jdbc.postgresql.PostgreSQLDriver jdbc:ibm:postgresql://<server.example.com>:<DBPort>;DatabaseName=<DBName>
    Autonomous Restful Service FOautorest.jar com.ibm.fluidquery.jdbc.autorest.AutoRESTDriver jdbc:ibm:autorest:Config=/home/db2inst1/restful/buck.rest;createMap=forceNew;InsensitiveResultSetBufferSize=2048 Details Example
    Apache Hive FOhive.jar com.ibm.fluidquery.jdbc.hive.HiveDriver jdbc:ibm:hive://<erver.example.com>:<DBPort>;DatabaseName=<DatabaseName>Details
    Apache Spark SQL FOsparksql.jar com.ibm.fluidquery.jdbc.sparksql.SparkSQLDriver jdbc:ibm:sparksql://server.example.com:port;DatabaseName=dbname
    Db2 db2jcc4.jar; db2jcc_license_cisuz.jar com.ibm.db2.jcc.DB2Driver jdbc:db2://<server>:<DBPort>/<DatabaseName>
    Db2 for z/OS db2jcc4.jar; db2jcc_license_cisuz.jar com.ibm.db2.jcc.DB2Driver jdbc:db2://<server>:<DBPort>/<DatabaseName>
    IBM watsonx.data presto-jdbc-0.282.jar com.facebook.presto.jdbc.PrestoDriver jdbc:presto://<server>:<DBPort>
    Google BigQuery FOgooglebigquery.jar com.ibm.fluidquery.jdbc.googlebigquery.GoogleBigQueryDriver jdbc:ibm:googlebigquery:AuthenticationMethod=serviceaccount;Project=<projectId>;dataset=<datasetId>;ServiceAccountEmail=<Your_ServiceAccount_Mail>;ServiceAccountPrivateKey=<Your_ServiceAccount_Private_Key_File_Path>DetailsExample
    Microsoft SQL Server FOsqlserver.jar com.ibm.fluidquery.jdbc.sqlserver.SQLServerDriver jdbc:ibm:sqlserver://<server>:<server.example.com>:<DBPort>Details
    MongoDB mongo-jdbc.jar com.ibm.db2.federation.jdbc.mongo.driver.MongoDriver jdbc:mongo://<server>:<DBPort>/<DatabaseName>
    MySQL Enterprise Edition FOmysql.jar com.ibm.fluidquery.jdbc.mysql.MySQLDriver jdbc:ibm:mysql://<server.example.com>:<DBPort>;DatabaseName=<DatabaseName>Details
    Oracle FOoracle.jar com.ibm.fluidquery.jdbc.oracle.OracleDriver jdbc:ibm:oracle://<server.example.com>:<DBPort>;ServiceName=<DatabaseServiceName>Details
    Pivotal Greenplum FOgreenplum.jar com.ibm.fluidquery.jdbc.greenplum.GreenplumDriver jdbc:ibm:greenplum://<erver.example.com>:<DBPort>;DatabaseName=<DatabaseName>Details
    PostgreSQL FOpostgresql.jar com.ibm.fluidquery.jdbc.postgresql.PostgreSQLDriver jdbc:ibm:postgresql://<server.example.com>:<DBPort>;DatabaseName=<DBName>DetailsDetails
    Salesforce FOsforce.jar com.ibm.fluidquery.jdbc.sforce.SForceDriver jdbc:ibm:sforce://<server.example.com>:Securitytoken=<your_salesforce_account_securitytoken>DetailsExample

    *You can click the Details link to get the details of the URL connection string for each data source. And you can update all the information in the URL according to the descriptions in the link except for the prefix, such as jdbc:db2: (Db2 for z/OS).

Restrictions

Data Source Restrictions
Amazon RedShift

IUD and Transparent IUD are not supported.

Method 1 is not supported for NNSTAT.

COMMIT and ROLLBACK are not supported.

For other limitations that apply to all JDBC data sources, see Configuring access to JDBC data sources.
Autonomous Restful Service IUD and Transparent IUD are not supported.

Method 1 is not supported for NNSTAT.

COMMIT and ROLLBACK are not supported.

For other limitations that apply to all JDBC data sources, see Configuring access to JDBC data sources.

Db2

IUD and Transparent IUD are not supported.

Method 1 is not supported for NNSTAT.

COMMIT and ROLLBACK are not supported.

For other limitations that apply to all JDBC data sources, see Configuring access to JDBC data sources.
IBM watsonx.data

IUD, Transparent IUD and Transparent DDL are not supported.

COMMIT and ROLLBACK are not supported.

For other limitations that apply to all JDBC data sources, see Configuring access to JDBC data sources.
Google BigQuery

IUD and Transparent IUD are not supported.

Method 1 is not supported for NNSTAT.

COMMIT and ROLLBACK are not supported.

For other limitations that apply to all JDBC data sources, see Configuring access to JDBC data sources.
MongoDB

IUD and Transparent IUD are not supported.

Method 1 is not supported for NNSTAT.

COMMIT and ROLLBACK are not supported.

For other limitations that apply to all JDBC data sources, see Configuring access to JDBC data sources.
Oracle

IUD and Transparent IUD are not supported.

Method 1 is not supported for NNSTAT.

COMMIT and ROLLBACK are not supported.

For other limitations that apply to all JDBC data sources, see Configuring access to JDBC data sources.
Salesforce

IUD and Transparent IUD are not supported.

Method 1 is not supported for NNSTAT.

COMMIT and ROLLBACK are not supported.

For other limitations that apply to all JDBC data sources, see Configuring access to JDBC data sources.

  1. You are now ready to register the JDBC server.