4 Connecting to Non-Oracle Data Sources Through Gateways

Gateways provide a transparent connection between Oracle and non-Oracle databases. Once a database is connected through a gateway, you can access it just as you access an Oracle database. As a result, you can use any of the transformation operators available in Warehouse Builder to transform the data before loading it into a target database.

Using Oracle Database Gateways, you can connect to different non-Oracle databases such as SQL Server, Sybase, Informix, Teradata, and DRDA. DRDA connectivity enables connection to DB2 database. Gateways provide a specific connection agent for each of these databases. For example, for a Sybase data source, the agent is a Sybase-specific gateway. You must install and configure this agent to support communication between the two systems. After creating a gateway connection to the database, you can import metadata into Warehouse Builder.

Note:

Before attempting to import metadata from a non-Oracle database through a gateway, ensure that the specific gateway for that database is installed on the system. See Oracle Database Heterogeneous Connectivity Administrator's Guide for more information about Oracle Database Gateways.

Connecting to a DB2 Database

To create a gateway connection to a DB2 database, ensure that Oracle Database Gateway for DRDA is installed. Once the gateway connection is established, you can access the DB2 database just as you access an Oracle database.

Creating a DB2 Module

To import metadata from a DB2 database, create a module in the DRDA node. The DRDA node is available under the Databases node in the Projects Navigator. To create a DRDA module:

Right-click DRDA and select New DRDA Module.

The Create Module Wizard is displayed.
In the Name and Description page, provide a name, description (optional), and select a module status.
In the Connection Information page, either select an existing location or click Edit to open the Edit Non-Oracle Location Dialog Box and create a new location.
In the Summary page, verify the details and click Finish.

The newly created DB2 module is now available under the DRDA node.

Edit Non-Oracle Location Dialog Box

Select the Connection Type to be one of Host:Port:Service, Database Link, or SQL*Net.

Host:Port:Service

If you selected Host:Port:Service, then provide the following connection details:

User Name/Password: User name and password to access the database.
Host: Machine on which the database is hosted.
Port: SQL port number of the database. The default port number to access DB2 is set to 1111.
Service Name: The service name of the database.
Schema: Browse to select a schema to import.

Database Link

If you selected Database Link, then provide the following connection details:

From Location: An existing location where the database link is defined
Database Link Name: The object name of the database link
Schema: The schema where the source data is stored or the target objects will be deployed.

SQL*Net

For SQL*Net, provide the following connection details:

User Name: The database user credential that has permission to access the schema location.

When connecting to a database that does not have user names, enter any text as a mock user name.
Password: The password associated with user name.

When connecting to a database that does not require a password, enter any text as a mock password.
Net Service Name: The name of the predefined connection.
Use Global Name: The unique name of the database, which is composed of the database name and the domain in the form database_name.database_domain. For example, db2.us.example.com identifies the db2 database in the us.example.com domain. Select this option when connecting to a database in a different network.

Click Test Connection to verify that you have provided the correct details and click OK to set the connection details.

Connecting to a SQL Server Database

To create a gateway connection to a SQL Server database, ensure that Oracle Database Gateway for SQL Server is installed.

Creating a SQL Server Module

The SQL Server node is available under the Databases node in the Projects Navigator. To create a SQL Server module:

Right-click SQL Server and select New SQL Server Module.

The Create Module Wizard is displayed.
In the Name and Description page, provide a name, description (optional), and select a module status. Select SQL Server Gateway as the access method.
In the Connection Information page, either select an existing location or click Edit to open the Edit Non-Oracle Location Dialog Box and create a new location.
In the Summary page, verify the details and click Finish.

The newly created SQL Server module is now available under the SQL Server node.

Edit Non-Oracle Location Dialog Box

Select the Connection Type to be one of Host:Port:Service, Database Link, or SQL*Net.

Host:Port:Service

If you selected Host:Port:Service, then provide the following connection details:

User Name/Password: User name and password to access the database.
Host: Machine on which the database is hosted.
Port: SQL port number of the database. The default port number to access SQL Server is set to 1526.
Service Name: The service name of the database.
Schema: Browse to select a schema to import.

Database Link

If you selected Database Link, then provide the following connection details:

From Location: An existing location where the database link is defined
Database Link Name: The object name of the database link
Schema: The schema where the source data is stored or the target objects will be deployed.

SQL*Net

For SQL*Net, provide the following connection details:

User Name: The database user credential that has permission to access the schema location.

When connecting to a database that does not have user names, enter any text as a mock user name.
Password: The password associated with user name.

When connecting to a database that does not require password, enter any text as a mock password.
Net Service Name: The name of the predefined connection.
Use Global Name: The unique name of the database, which is composed of the database name and the domain in the form database_name.database_domain. For example, sqlserver.us.example.com identifies the SQL Server database in the us.example.com domain. Select this option when connecting to a database in a different network.

Click Test Connection to verify that you have provided the correct details and click OK to set the connection details.

Connecting to an ODBC Data Source

Oracle Warehouse Builder can access any data source that supports open database connectivity (ODBC). This is possible using the ODBC support provided by Oracle Database. You can use ODBC access for any data source not supported by a specific gateway agent.

Setting up ODBC Connectivity

While ODBC connectivity is widely available, it is most commonly used to connect to data sources on Windows platforms. The Oracle Database configuration process should be substantially similar for connecting to any ODBC source. ODBC setup on the source system can vary greatly in different environments. The following discussion describes the common case of setting up ODBC connectivity to a source on a Windows platform using a System DSN. If you are on a non-Windows platform, consult the documentation from your vendor on ODBC setup.

Installing ODBC Driver

To enable ODBC connectivity to a data source, you must first install the ODBC driver for that particular data source.

Creating a System DSN

After installing the driver, create a system DSN using the Microsoft ODBC Administrator. To do this,

Select Start, Settings, Control Panel, Administrative Tools, Data Sources (ODBC).

This opens the ODBC Data Source Administrator dialog box.
Navigate to the System DSN tab and click Add to open the Create New Data Source dialog box.
Select the driver for which you want to set up the data source.

Click Finish to open the ODBC Setup dialog box.
Specify a name for the data source, and other relevant connection details for the data source. The connection details that you provide here will differ for each type of data source.

Configuring the Database

Next, you must configure Oracle Database to connect to the data source. Warehouse Builder can then use this configuration to extract metadata from the data source. This involves:

Creating a Heterogeneous Services Initialization File
Configuring the listener.ora File

Creating a Heterogeneous Services Initialization File

To configure an ODBC connection agent, you must create a heterogeneous services initialization file for the agent. Create this file in the ORACLE_HOME\hs\admin directory of the database.

The file name should start with init and have the extension .ora. It should be of the format initSID.ora, where SID is the system identifier for the ODBC agent. The value to be entered in this file is:

HS_FDS_CONNECT_INFO = DSN_NAME

DSN_NAME is the data source name that you provide while creating the system DSN. Other relevant data that needs to be provided in this file depends on the data source you are connecting to.

Configuring the listener.ora File

Add a new SID description in the listener.ora file. This file is available in the ORACLE_HOME\network\admin directory.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = SID)
      (ORACLE_HOME = oraclehome)
      (PROGRAM = program)
    )
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = oraclehome)
      (PROGRAM = program)
      )
  )

The SID_NAME parameter must contain the name of the configuration file you created in the previous step. However, it must not contain the init prefix. For example, if the configuration file you created in the previous step was initdb2.ora, then the value of the SID_NAME parameter should be db2.

ORACLE_HOME must point to the Oracle home location of your database installation.

The value associated with the PROGRAM keyword defines the name of the executable agent, and will differ for each type of data source.

Restart the listener service after making these modifications.

The steps involved in setting up the ODBC connectivity are similar for all data sources. However, the data to be provided while Creating a System DSN, Creating a Heterogeneous Services Initialization File, and Configuring the listener.ora File differs for each data source.

See Chapter 5, "Connecting to Microsoft Data Sources Through ODBC Connection" for specific examples of connecting to an Excel worksheet and SQL Server database.

Creating an ODBC Module

After you set up the ODBC connectivity to a data source, you must create an ODBC module to import the metadata from this data source. The ODBC node is available under the Databases node in the Projects Navigator. To create an ODBC module:

Right-click ODBC and select New ODBC Module.

The Create Module Wizard is displayed.
In the Name and Description page, provide a name, description (optional), and select a module status.
In the Connection Information page, either select an existing location or click Edit to open the Edit Non-Oracle Location Dialog Box and provide the connection details.
In the Summary page, verify the details and click Finish.

The newly created ODBC module is now available under the ODBC node.

Edit Non-Oracle Location Dialog Box

Select the Connection Type to be one of Host:Port:Service, Database Link, or SQL*Net.

Host:Port:Service

If you selected Host:Port:Service, then provide the following connection details:

User Name/Password: You can provide a dummy user name and password as you are not connecting to an Oracle database.
Host: Machine on which the database is hosted.
Port: SQL port number of the database.
Service Name: The SID_NAME that you specify in the listener.ora file.
Schema: You can leave this field empty if you are not importing from a schema.

Database Link

If you selected Database Link, then provide the following connection details:

From Location: An existing location where the database link is defined
Database Link Name: The object name of the database link
Schema: The schema where the source data is stored or the target objects will be deployed.

SQL*Net

For SQL*Net, provide the following connection details:

User Name: The database user credential that has permission to access the schema location.

When connecting to a database that does not have user names, enter any text as a mock user name.
Password: The password associated with user name.

When connecting to a database that does not require password, enter any text as a mock password.
Net Service Name: The name of the predefined connection.
Use Global Name: The unique name of the database, which is composed of the database name and the domain in the form database_name.database_domain. Select this option when connecting to a database in a different network.

Click Test Connection to verify that you have provided the correct details and click OK to set the connection details.

Importing Database Objects

After establishing connection to a DB2, SQL Server, or any other generic ODBC database, you can import metadata from the data objects in the database. The database objects that you can import are tables, views, and sequences. To import database objects:

Right-click the newly created module, and select Import, Database Object.

The Import Metadata Wizard is displayed.
In the Filter Information page, select the object types to be imported. You can also narrow down the selection of objects by specifying a string pattern.
In the Object Selected page, move the required objects from the Available list to the Selected list. Use the Find Objects button to search for objects in the Available list. Also specify whether dependent objects need to be imported.
In the Summary and Import page, verify the objects to be imported. Click Advanced Import Options to open the Advanced Import Options dialog box. By default, the following options are selected:
- Preserve workspace added column
- Preserve workspace added constraints
- Import descriptions
Retain or deselect any of the options.

You can now use any of the imported objects in a mapping to load data.