Configure and Register Data Gateway for Reporting
Perform these optional steps to enable remote connectivity for Classic features such as analyses and dashboards.
JDBC and JNDI Templates and Examples
When you set up remote connectivity for analyses and dashboards, you might have to specify JDBC strings and driver classes, and JNDI connection details and context details.
Oracle:
Driver Class: oracle.jdbc.OracleDriver
jdbc string: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=[\"host-name\"])(PORT=[\"port\"]))(CONNECT_DATA=(SERVICE_NAME=[\"service-name\"])))
Amazon Redshift:
Driver Class: com.oracle.jdbc.redshift.RedshiftDriver
JDBC String: jdbc:oracle:redshift://[\"host-name\"]:[\"port\"];DatabaseName=[\"service-name\"];EncryptionMethod=SSL;ValidateServerCertificate=false
Apache Hive
Driver Class: com.oracle.bi.jdbc.hive.HiveDriver
JDBC String: jdbc:oracle:hive://[\"host-name\"]:[\"port\"];EncryptionMethod=SSL;ValidateServerCertificate=false
DB2
Driver Class: com.oracle.bi.jdbc.db2.DB2Driver
JDBC String: jdbc:oracle:db2://[\"host-name\"]:[\"port\"];DatabaseName=[\"service-name\"]
Impala
Driver Class: com.oracle.bi.jdbc.impala.ImpalaDriver
JDBC String: jdbc:oracle:impala://[\"host-name\"]:[\"port\"];EncryptionMethod=SSL;ValidateServerCertificate=false
MySQL
Driver Class: com.mysql.cj.jdbc.Driver
JDBC String: jdbc:mysql://[\"host-name\"]:[\"port\"]"[/database][?properties]
SQL Server
Driver Class: com.oracle.bi.jdbc.sqlserver.SQLServerDriver
JDBC String: jdbc:oracle:sqlserver://[\"host-name\"]:[\"port\"];DatabaseName=[\"service-name\"]
Teradata
Driver Class: com.teradata.jdbc.TeraDriver
JDBC String: jdbc:teradata://[\"host-name\"]/DBS_PORT=[\"port\"]
Oracle:
<Resource
name="jdbc/myoracle"
global="jdbc/myoracle"
auth="Container"
type="javax.sql.DataSource"
driverClassName="oracle.jdbc.OracleDriver"
url="jdbc:oracle:thin:@localhost:1521:orcl"
username="my_user"
password="my_password"
maxActive="15"
maxIdle="1"
maxWait="-1"
/>
<Resource
name="jdbc/oracleolap"
global="jdbc/oracleolap"
auth="Container"
type="javax.sql.DataSource"
driverClassName="oracle.jdbc.OracleDriver"
url="jdbc:oracle:thin:@localhost:1522:orcl112"
username="my_user"
password="my_password"
maxActive="15"
maxIdle="1"
maxWait="-1"
/>
<Resource
name="jdbc/oraclenorthwind"
global="jdbc/oraclenorthwind"
auth="Container"
type="javax.sql.DataSource"
driverClassName="oracle.jdbc.OracleDriver"
url="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=example.com)(PORT=1234))(CONNECT_DATA=(SERVICE_NAME=MATSDB.EXMAPLE.COM)))"
username="my_user"
password="my_password"
maxActive="15"
maxIdle="1"
maxWait="-1"
/>
DB2
<Resource
name="jdbc/db2northdb"
global="jdbc/db2northdb"
auth="Container"
type="javax.sql.DataSource"
driverClassName="com.ibm.db2.jcc.DB2Driver"
url="jdbc:db2://example.com:58263/NORTHDB"
username="my_user"
password="my_password"
maxActive="15"
maxIdle="1"
maxWait="-1" />
SQLServer:
<Resource
name="jdbc/sqlservernorthwind"
global="jdbc/sqlservernorthwind"
auth="Container"
type="javax.sql.DataSource"
driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
url="jdbc:sqlserver://example.com:61045;DatabaseName=Northwind"
username="my_user"
password="my_password"
maxActive="15"
maxIdle="1"
maxWait="-1" />
Teradata:
<Resource
name="jdbc/teranorthwind"
global="jdbc/teranorthwind"
auth="Container"
type="javax.sql.DataSource"
driverClassName="com.teradata.jdbc.TeraDriver"
url="jdbc:teradata://99.999.99.999"
username="my_user"
password="my_password"
maxActive="15"
maxIdle="1"
maxWait="-1" />
Mysql_community
<Resource
name="jdbc/CEmysql"
auth="Container"
type="com.mysql.jdbc.jdbc2.optional.MysqlDataSource"
factory="com.mysql.jdbc.jdbc2.optional.MysqlDataSourceFactory"
username="my_user"
password="my_password"
serverName="example.com"
portNumber="3306"
databaseName="my_database" />
The JNDI for DD Drivers.
SQLServer:
<Resource
name="jdbc/DDsqlserver"
auth="Container"
type="com.oracle.bi.jdbcx.sqlserver.SQLServerDataSource"
factory="com.oracle.bi.jdbcx.sqlserver.SQLServerDataSourceFactory"
user="my_user"
password="my_password"
serverName="example.com\MSSQLSERVER16"
portNumber="61045"
databaseName="my_database" />
DB2:
<Resource
name="jdbc/DDdb2"
auth="Container"
type="com.oracle.bi.jdbcx.db2.DB2DataSource"
factory="com.oracle.bi.jdbcx.db2.DB2DataSourceFactory"
user="my_user"
password="my_password"
serverName="example.com"
portNumber="58263"
databaseName="my_database"
/>
Impala:
<Resource
name="jdbc/DDimpala"
auth="Container"
type="com.oracle.bi.jdbcx.impala.ImpalaDataSource"
factory="com.oracle.bi.jdbcx.impala.ImpalaDataSourceFactory"
user="my_user"
password="my_password"
serverName="example.com"
portNumber="21050"
databaseName="my_database"
/>
Spark:
<Resource
name="jdbc/DDspark"
auth="Container"
type="com.oracle.bi.jdbcx.sparksql.SparkSQLDataSource"
factory="com.oracle.bi.jdbcx.sparksql.SparkSQLDataSourceFactory"
user="my_user"
password="my_password"
serverName="example.com"
portNumber="10000"
databaseName="my_database"
/>
HIVE:
<Resource
name="jdbc/DDhive"
auth="Container"
type="com.oracle.bi.jdbcx.hive.HiveDataSource"
factory="com.oracle.bi.jdbcx.hive.HiveDataSourceFactory"
user="my_user"
password="my_password"
serverName="example.com"
portNumber="10000"
databaseName="my_database"
/>
MySQL:
<Resource
name="jdbc/DDmysql"
auth="Container"
type="com.oracle.bi.jdbcx.mysql.MySQLDataSource"
factory="com.oracle.bi.jdbcx.mysql.MySQLDataSourceFactory"
user="my_user"
password="my_password"
serverName="example.com"
portNumber="3306"
databaseName="my_database"
/>
MYSQL:
<Resource
name="jdbc/DDmysql"
auth="Container"
type="com.oracle.bi.jdbcx.mysql.MySQLDataSource"
factory="com.oracle.bi.jdbcx.mysql.MySQLDataSourceFactory"
user="my_user"
password="my_password"
serverName="example.com"
portNumber="3306"
databaseName="my_database"
/>
Add a JDBC Driver to Data Gateway
Add a JDBC driver to your Data Gateway installation so that you can model data in an on-premises database.
DSN Formats for Specifying Data Sources
In Oracle Analytics you can model your on-premises data for many database types. Oracle Analytics supports direct access to some on-premises data sources through the semantic model. When you create the database connection using Model Administration Tool, in the Data source name field on the Connection Pool dialog (General tab) you use the appropriate DSN format for the database type you're connecting to.
Amazon Redshift:
DRIVER=Oracle 7.1 Amazon Redshift Wire Protocol;HOST=["host-name"];PORT=["port"];DB=["service-name"]
SSL: DRIVER=Oracle 7.1 Amazon Redshift Wire Protocol;HOST=["host-name"];PORT=["port"];DB=["service-name"];EM=6;CPV=TLSv1.2,TLSv1.1,TLSv1, SSLv3, SSLv2;VSC=0
Apache Drill:
DRIVER=MapR Drill ODBC Driver;Host=["host-name"];Port=["port"];CastAnyToVarchar=true;ExcludedSchemas=sys,INFORMATION_SCHEMA;AuthenticationType=Basic Authentication;ConnectionType=Direct
Aster:
DRIVER=Aster ODBC Driver;SERVER=["host-name"];PORT=["port"];DATABASE=["service-name"]
DB2:
DRIVER=Oracle 7.1 DB2 Wire Protocol;IpAddress=["host-name"];PORT=["port"];DB=["service-name"]
SSL: DRIVER=Oracle 7.1 DB2 Wire Protocol;IpAddress=["host-name"];PORT=["port"];DB=["service-name"];EM=1;VSC=0
Greenplum:
DRIVER=Oracle 7.1 Greenplum Wire Protocol;HOST=["host-name"];PORT=["port"];DB=["service-name"]
Hive:
DRIVER=Oracle 8.0 Apache Hive Wire Protocol;HOST=["host-name"];PORT=["port"]
SSL: DRIVER=Oracle 8.0 Apache Hive Wire Protocol;HOST=["host-name"];PORT=["port"];EM=1;VSC=0
Impala:
DRIVER=Oracle 7.1 Impala Wire Protocol;HOST=["host-name"];PORT=["port"]
SSL: DRIVER=Oracle 7.1 Impala Wire Protocol;HOST=["host-name"];PORT=["port"];EM=1;VSC=0
Informix:
DRIVER=Oracle 7.1 Informix Wire Protocol;HOSTNAME=["host-name"];PORTNUMBER=["port"];DATABASE=["service-name"]
MongoDB:
DRIVER=Oracle 8.0 MongoDB;HOST=["host-name"];PORT=["port"];DB=["service-name"]
MySQL:
DRIVER=Oracle 7.1 MySQL Wire Protocol;HOST=["host-name"];PORT=["port"];DB=["service-name"]
PostgresSql:
DRIVER=Oracle 7.1 PostgreSQL Wire Protocol;HOST=["host-name"];PORT=["port"];DB=["service-name"]
Spark:
DRIVER=Oracle 8.0 Apache Spark SQL;HOST=["host-name"];PORT=["port"]
SSL: DRIVER=Oracle 8.0 Apache Spark SQL;HOST=["host-name"];PORT=["port"];EM=1;VSC=0
SQL Server:
DRIVER=Oracle 7.1 SQL Server Wire Protocol;HOST=["host-name"];PORT=["port"];DB=["service-name"]
SSL: DRIVER=Oracle 7.1 SQL Server Wire Protocol;HOST=["host-name"];PORT=["port"];DB=["service-name"];EM=1;VSC=0;CryptoProtocolVersion=TLSv1.2,TLSv1.1,TLSv1,SSLv3,SSLv2
Sybase:
DRIVER=Oracle 7.1 Sybase Wire Protocol;NA=["host-name"], ["port"];DB=["service-name"]
Teradata:
DRIVER=Oracle 7.1 Teradata;DBCName=["host-name"];port_name=["port"]