Using Oracle Heterogeneous Services in an Operational Data Store or Data Warehouse

By John Adolph Palinski

I recently received a user request to bring information into the data warehouse for use in combination with other data warehouse tables. On the surface this is a routine task. After all, I often bring transaction processing data into the warehouse using materialized views and other tools such as the Merge statement. However, the database for this request was a non-Oracle database. This created a small problem. I could no longer use the normal Oracle tools to bring this data into the warehouse. I would need a different device.

In the past, whenever I needed to bring non-Oracle data into the warehouse, I required the production data to be placed into a flat file and I loaded the file into Oracle using SQL*Loader. Creating the source system extraction process, the SQL*Loader script, and the batch processes that move and execute the files required quite a bit of work. In the past, we used this technique for single files. However, the current request was to move an entire production system schema into the warehouse for transformation and loading. This would require SQL*Loader and batch files for three hundred and seventy tables. I really did not want to do this much work so I began to look for an easier method.

Investigating various sources I identified Oracle's Hetergeneous Services. This Oracle feature allows you to set up an ODBC connection inside the Oracle database. This was the answer to my problem. With a little configuration, Oracle can read the non-Oracle database files directly. No batch programs, flat files, or SQL*Loader files. Before setting this up I researched the topic in a variety of sources. Some of them were incorrect, some were not. However, none of them discussed setting up hetergenous services using a remote database. All of the articles discussed setting using Oracle on a local Windows server that had an Oracle installation and the ODBC drivers installed on the server. However, our data warehouse was a Unix server and the ODBC drivers could not be installed on this server. I really needed instructions that discussed how to connect from a Unix platform using Oracle to ODBC drivers located on a Windows server. This article describes the steps needed to set up heterogeneous services on a Unix data warehouse server that reads a Sybase database on another server. These same steps can be applied to any non-Oracle database that has ODBC connectivity.

Infrastructure

The infrastructure is illustrated in Figure 1. The following bullets describe the structure:

- The target system is a Sybase database residing on Windows XP server X.
- Server X has Sybase ODBC drivers installed.
- Server X has an Oracle server or code tree installed. Server X does not have the Oracle database installed.
- The data warehouse resides in an Oracle database on Unix server Z.
- The users will access the Sybase database through the Oracle installation on server Z.



Problem

The data warehouse residing on the Oracle installation on server Z needs to communicate and query the Sybase database on server X.

Setup Tasks

Below is a series of steps that are needed to set up the hetergeneous services. The actual code will be discussed following these steps.

1. Install ODBC drivers on the server (server X) that is to actually access the Sybase database.
2. Setup the ODBC connection on server X using the Windows ODBC Data Source Administrator
3. Test the ODBC drivers to ensure that connectivity is made to the non-Oracle database.
4. Ensure the Global_names parameter is set to False.
5. Install an Oracle server (or code tree) or an Oracle database on the same server (server X) as the ODBC drivers.
6. Configure the Hetergeneous services. This consists of creating an initodbc.ora file within the Oracle installation on server X.
7. Modify the Listener.ora file on server X used to connect to the Oracle instance and ODBC drivers.
8. Modify the Tnsnames.ora file on server Z . This is the Oracle database installation accessed by the users to connect to the ODBC drivers
9. Reload the listener on server X
10. Create a database link on the Oracle installation (server Z) that accesses the heterogeneous connection.
11. Run a Select statement for the Oracle installation (server Z) using the database link.


Step 1

The first step in setting up hetergeneous services is to install the ODBC drivers on the Windows server (server X) that is to directly access the non-Oracle database. In the case of Sybase, this is Adaptive Server Anywhere 9.0. You can identify the current installed drivers on the ODBC Data Source Administrator. Launch this dialog box using this path: Start/Settings/Control Panel/Administrative Tools/Data Sources. Figure 2 depicts the dialog box. The System DSN tab page is shown. This tab page shows the System data sources defined and the associated drivers. You will need to define a DSN for the non-Oracle Sybase database.

Steps 2 and 3 - Seting up and testing the ODBC

The first task in setting up the ODBC driver is to configure the ODBC. This requires you to set up a System DSN (or data source name). Perform the following:

- Launch the Create New Data Source dialog. On the System DSN tab page (Figure 2), press the Add button. This opens the Create New Data Source dialog box (Figure 3).

- Select the appropriate driver and press the Finish button. This launches the ODBC Configuration Dialog box displayed in Figure 3.

The ODBC Configuration dialog box (Figure 4) is a five tab dialog box and settings will be needed on the first four tabs. The tabs and modifications are:

ODBC - Enter a name for the remote data source (mandatory). This can be a custom name. For our example the name will be Cas30c. You may also enter a description for the remote data source (optional). This can be the actual network sever name or a TCP/IP alias
Login - Enter a remote data source user id and password or use the integrated login
Database - Enter the remote server name. This is the System DSN created on the remote database. Enter the actual remote database name.
Network - Select the appropriate network protocols. For example, check TCP/IP and IP=Network_server_name. Network_server_name is the actual network server name.

- Return to the ODBC tab. Press the Test Connection button. If it returns a success message you are ready to begin setting up the Heterogeneous services in Oracle.

Step 4 - Ensure the Global_names parameter is set to false

Execute the following query to determine the parameter setting:

Select *
from v$parameter
where name like 'global_names%'

If the parameter is set to True, you must modify the Init.ora file and restart the database.

Step 5 - Install an Oracle server on server X

In this step you must install an Oracle server on the same server as the ODBC drivers. This installation can be a database or simply the code tree. If the users will not access Oracle directly from this server the code tree is perfectly acceptable.


Step 6 - Setup the Heterogeneous Services file on server x

You will need to document the parameters that Oracle needs to locate and execute the ODBC drivers. Follow these instructions:

- Locate the Oracle_home/hs/admin/initsodbc.ora file.
- Save a copy of this file renaming it per the following template:

initDSNNAME.ora (i.e. initcas30c.ora)
DSNNAME is the DSN value set up on server X containing the ODBC (Figure 4).
- Open the init.ora file.

Locate the line "HS_FDS_TRACE_LEVEL = ". Change to OFF
Locate the line "HS_FDS_CONNECT_INFO = ". Change to the data source name.
# This is a sample agent init file that contains the HS parameters
# that are needed for an ODBC Agent.

#
# HS init parameters
#
HS_FDS_CONNECT_INFO = Cas30C
HS_FDS_TRACE_LEVEL = off

#
# Environment variables required for the non-Oracle system
#
#set =

Step 7 - Configuring the Listener

The Listener on server X accessing the Oracle database must be configured to point to the Heterogeneous Services entry created in step 6. Create an entry in the SID _LIST similar to the one below. The bolded part is the actual entry. Note the following:

SID_NAME is the DSN for the remote database.
ORACLE_HOME is the actual Oracle home file path.
PROGRAM tells Oracle to use heterogeneous services. This particular entry tell Oracle to look for the initcas30c.ora file defined in step 5.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME=Cas30C) -- Enter the DSN on this line
(ORACLE_HOME = c:\oracle10gdb) -- Enter your Oracle home on this line
(PROGRAM = hsodbc)
)
)

Step 8 - Configuring Tnsnames.ora

Oracle needs to know where to look for the remote database when it is called. This requires an entry in the Tnsnames.ora file in every Oracle instance that will be calling the remote database. In the case of our article, the Tnsnames.ora file entry must be on server Z. The Oracle installation on server X does not require this modification since this Oracle installation will not be directly used by a user. The server X Oracle installation is simply the path from Oracle's listener to the ODBC drivers. The following example can be followed:

Cas30c.world = -- This name can be customized
(DESCRIPTION=
(ADDRESS_LIST=
(Address=(PROTOCOL=TCP)
(HOST=
-- (Server x)
(PORT=1521))) -- Enter the port on which the server x Oracle installation
-- is listening
(CONNECT_DATA=(SID=Cas30c)) - Enter the DSN name
(HS=OK) -- Enter this value. It tells Oracle to use hetergeneous
-- services
)

Step 9 - Reloading the listener

At this point the infrastructure is in place. You will need to reload the Listener.ora settings you defined in Step 7. They must be in place on server X. There are several methods. Execute one of the following from the command line:

Lsnrctl reload

or

Lsnrctl stop
Lsnrctl start

After completing this step, you should check whether the listener and Tnsnames.ora file are configured correctly. On the server accessing the data (server Z) issue a Tnsping command from the command prompt. If Oracle can contact the server X Oracle installation, the configuration is correct and a response is seen. The following exemplifies the command.

Tnsping cas30c

Step 10 - Create a database link

The last step in the procedure is to create a database link on each installation that will view the non-Oracle data (Server Z). In order to acccess the non-Oracle database, each Oracle ID must know how to contact the database. The database link is placed after the non-Oracle data source reference. The database link contains the Tnsnames reference (Cas30c) along with the remote data user id and password. When the SQL statement containing the link is executed, Oracle reviews the Tnsnames.ora file to locate the server and listener port for the ODBC database. A message is then sent to that server. The listener identifies the signal, locates the hetergeneous services entry, and uses the ODBC drivers to perform the work. The results are sent back through this channel.

There are a variety of database link options. This example is one that contains the remote database userid and password.

Note:  it's important that the username and password must be in double quotes.

create database link ODBC connect to "sa" identified by "pencil" using 'hsodbc'.

create [public] database link link_name connect to "user_name" identified by "password" using 'cas30c';

You have now completed the configuration. You can test the configuration by executing a Select statement against the non-Oracle database.

Select * from table_name@link_name;

If you need to have data outside the Oracle database, you will find this a great tool for making it transparent to the Oracle user.