CREATE SERVER statement - Examples of the JDBC wrapper

Use the CREATE SERVER statement to register server definitions for the JDBC wrapper. This example provides the required parameters for simplified and traditional case, and an example with additional server parameters.

The following example shows how to register a server definition for a PostgreSQL data source by issuing the CREATE SERVER statement:

--New simplifed syntax
CREATE SERVER jdbc_server1 
   TYPE JDBC 
   VERSION 3.0 
   OPTIONS (
   URL ‘jdbc:db2://server.example.com:50471/testdb');

--Old traditional syntax
CREATE SERVER jdbc_server1 
   TYPE JDBC 
   VERSION 3.0 
   WRAPPER jdbc_wrapper1 
   OPTIONS (
      DRIVER_PACKAGE '/path1/file1.jar: /path2/file2.jar', 
      DRIVER_CLASS ‘com.ibm.db2.jcc.DB2Driver', 
      URL ‘jdbc:db2://server.example.com:50471/testdb');

Parameter values

jdbc_server1: Specifies a name that you assign to the JDBC data source server. Duplicate server definition names are not allowed.
TYPE JDBC: Specifies the type of data source server that you want to access. This parameter is optional.
VERSION 3.0: Specifies the version of the JDBC data source that you want to access. This parameter is optional.
WRAPPER jdbc_wrapper1: Specifies the wrapper name that you specified in the CREATE WRAPPER statement.
DRIVER_PACKAGE '/home/My_LIB/JDBC_driver/postgresql.jar': Specifies the JDBC driver packages. This parameter is required for Db2® v11.5.7 or earlier.
DRIVER_CLASS ‘org.postgresql.Driver': Specifies the JDBC driver library. This parameter is required for Db2 v11.5.7 or earlier.
URL ‘jdbc:postgresql://server.example.com:5432/testdb': Specifies the JDBC connection string of the remote server. This parameter is required.

Server parameters

When you create the server definition, you can specify additional server options in the CREATE SERVER statement. The server options can include both general server options and JDBC-specific server parameters.

In general, the default settings for the server parameters have limited functionality. You can use the server parameters to optimize your configuration.

To access JDBC data sources, you must specify the DRIVER_CLASS, DRIVER_PACKAGE and URL server parameters in the CREATE SERVER statement. The general server options are optional. In the JDBC-specific server option list, JDBC_LOG parameter is optional too.

OPTIONS (
   DRIVER_PACKAGE '/path1/file1.jar: /path2/file2.jar', 
   DRIVER_CLASS 'com.ibm.db2.jcc.DB2Driver', 
   URL 'jdbc:db2://server.example.com:50471/testdb',
   JDBC_LOG 'Y');

Parameters

DRIVER_PACKAGE '/path1/file1.jar: /path2/file2.jar'

Specifies the JDBC driver packages.

DRIVER_CLASS 'com.ibm.db2.jcc.DB2Driver'

Specifies the Db2 JDBC driver library.

URL 'jdbc:db2://server.example.com:50471/testdb'

Specifies the JDBC connection string that consists of three parts that are all separated by a colon:

The database protocol
The database type name or connectivity driver name
The database identity through an alias or sub-name

JDBC_LOG 'Y'

Specifies to create log files for error tracing. The default value of this server option is N.

Examples

For Teradata data source with driver downloaded by user: Currently, the Teradata JDBC driver must be downloaded by the user from the Teradata vendor. For more information, see Preparing the federated server to access data sources through JDBC of type 1.

CREATE SERVER jdbc_td 
   TYPE JDBC 
   VERSION 3.0 
   WRAPPER jdbc_wrapper1 
   OPTIONS (
      DRIVER_PACKAGE '/path1/tdgssconfig.jar:/path2/terajdbc4.jar', 
      DRIVER_CLASS ‘com.teradata.jdbc.TeraDriver’, 
      URL ‘jdbc:teradata://server.example.com /’, 
      JDBC_LOG 'Y');

For SalesForce data source with driver installed by default: Currently, the SalesForce JDBC driver is installed by default. For more information, see Preparing the federated server to access data sources through JDBC of type 2.

CREATE SERVER jdbc_salesforce  
   TYPE JDBC 
   VERSION 3.0 
   WRAPPER jdbc_wrapper1 
   OPTIONS (
      DRIVER_PACKAGE '$INSTANCE_HOME /sqllib/federation/jdbc/lib/FOsforce.jar',  
      DRIVER_CLASS ‘com.ibm.fluidquery.jdbc.sforce.SForceDriver’,  
      URL ‘jdbc:ibm:sforce://login.salesforce.com;SecurityToken=XXXX’, 
      JDBC_LOG 'Y');

The ‘XXXX’ in the URL parameter is the ‘SecurityToken’ of your account in SalesForce. For more information, see here.

For Amazon Athena data source with a driver that you download: Currently, the Athena JDBC driver must be downloaded from an Amazon Athena vendor. For more information, see Preparing the federated server to access data sources through JDBC of type 1.

CREATE SERVER jdbc_athena
   TYPE JDBC 
   WRAPPER jdbc_wrapper1
   OPTIONS (
      DRIVER_PACKAGE '/path/AthenaJDBC42_2.0.9.jar',  
      DRIVER_CLASS 'com.simba.athena.jdbc.Driver',  
      URL 'jdbc:awsathena://S3OutputLocation=s3://athena/out;AwsRegion=us-east-2;Schema=testdbu', 
      JDBC_LOG 'Y');

For more information about connection properties in the URL parameter for Amazon Athena, see Simba Athena JDBC Driver with SQL Connector: Installation and Configuration Guide.