Configure and Register Data Gateway for Reporting

Perform these optional steps to enable remote connections from reporting dashboards and analyses.

If you have a visualization-only deployment (for example, Oracle Analytics Cloud Standard Edition), you don't need to follow these steps.
Before you start, follow the configuration steps in the previous topic Configure and Register Data Gateway for Visualization.
  1. On the machine where you've installed a Data Gateway agent, obtain the machine name and port number.
    In a server deployment:
    1. Execute the command /<Data Gateway install folder>/domain/bin/status.sh.
    2. In the command output, note the machine name contained within the URL displayed under Data Gateway Status, and note the Data Gateway Jetty HTTP Port value.
    In a personal deployment:
    1. Open the file: %localappdata%\Temp\DataGateway\ports.properties.
    2. Note the machine name and port number.
  2. Start the Data Gateway agent.
  3. In Developer Client Tool, load the Java data source metadata:
    1. From the File menu, click Open, then In the Cloud, and use the Open in the Cloud dialog to specify the details of your Cloud data model.
    2. From the File menu, click Load Java Data Sources.
    3. At the Connect to Java Datasource Server dialog:
      • In the Hostname field, enter the machine name that you noted in Step 1. Fully qualify the hostname. For example, if you noted machine in Step 1, you might specify machine.us.example.com.
      • In the Port field, enter the port that you noted in Step 1. For example, 51811.
      • In the Username and Password fields, enter dummy or any string (these credentials aren't validated because this is a public call to discover the capabilities advertised by the Data Gateway).
  4. In Developer Client Tool, set up a physical database connection:
    1. In the Physical layer, create a local (not remote) connection to your data source using the standard call interface appropriate for your data source, and model the data as required.
    2. When you are happy with the data model and you are ready to make the connection remote and publish back to the cloud, edit the connection that you created.
    3. On the General tab, in the Call interface field select JDBC (Direct Driver), and in the Connection String field, specify the JDBC string and credentials in the RPD connection. See JDBC and JNDI Templates and Examples below for a list of supported JDBC strings and driver classes.
    4. On the Miscellaneous tab, in the Use SQL Over HTTP field enter true, and in the RDC Version field enter 2, and specify the JDBC driver class.
    5. Publish the data model to the cloud.
You're now ready to test your deployment by connecting remotely to an on-premises database.

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.

JDBC String Patterns and Driver Classes
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\"])))              
Apache Hive
    Driver Class: com.oracle.bi.jdbc.hive.HiveDriver
    jdbc:oracle:hive://[\"host-name\"]:[\"port\"];EncryptionMethod=SSL;ValidateServerCertificate=false
DB2
   com.oracle.bi.jdbc.db2.DB2Driver
   jdbc:oracle:db2://[\"host-name\"]:[\"port\"];DatabaseName=[\"service-name\"]
Impala
   com.oracle.bi.jdbc.impala.ImpalaDriver
   jdbc:oracle:impala://[\"host-name\"]:[\"port\"];EncryptionMethod=SSL;ValidateServerCertificate=false
MySQL
   com.mysql.cj.jdbc.Driver
   jdbc:mysql://[\"host-name\"]:[\"port\"]"[/database][?properties]
SQL Server
   com.oracle.bi.jdbc.sqlserver.SQLServerDriver
   jdbc:oracle:sqlserver://[\"host-name\"]:[\"port\"];DatabaseName=[\"service-name\"]
Teradata
   com.teradata.jdbc.TeraDriver
   jdbc:teradata://[\"host-name\"]/DBS_PORT=[\"port\"]  
JNDI Templates for Native Drivers
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://10.246.64.217"               
      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" />
JNDI Templates for DD Drivers
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" 
/>