Welcome to All Things Warehouse Builder

  • ETL
    January 3, 2008

11g Heterogeneous Agent

David Allan

There are quite a few nice changes in the 11g Gateways that are worth checking out.... from performance to standalone agent to legacy integration. As with a lot of things in life, when you start scratching the surface you find lots of cool and interesting stuff - and in amongst a few things you don't want to find too of course! Here's a few initial findings on setup....

The software firstly for 11g is under the 'See All' section next to the database platform download.


When you click on 'See All' further down the page you will find the Gateway zip, this is a standalone install that has the Gateway Heterogeneous Agent and also the legacy connect and designer utilities amongst others.


A change in 11g is that the agent can be installed on its own node or wherever you decide which is great, it can also be used with existing 10.2 database releases which is even better. See the certification matrices below for connection details:

The install and configuration is generally better, there are a few differences such as having to run some scripts on the source system (if you are reverse engineering) - see the SQLServer example for details, and various other gotchas.

Firstly lets check the SQLServer Database Gateway for example...

A lot of people blast on without reading the manual, I am one of those people too. There are some changes to how the Gateway is configured that you should be aware of. In the installation and configuration guide check the section 'Create SQL Server Views for Data Dictionary Support',  these are needed in order to reverse engineer from SQLServer, the views are used by the database's transparent gateway views used by OWB in the import/reverse engineering process - so remember and define them on your source! After this it was fairly straightforward.

Looking at a simple example..
Firstly I thought I'd start off simple using a simple Excel document. With 11g Gateways I can now have an agent on my windows box and use my Linux 11g database to read the document. On my windows host under the gateway Oracle home I create a file initMYEXCEL.ora under hs/admin with the following content:


The trace setting is INVALUABLE whilst you are setting this up, the trace in this case will be written to hs/trace. In the gateway Oracle home's listener.ora file I added a listener for the service:

On my database home where I wish to connect to the agent I add an entry in the tnsnames.ora to connect to my Heterogeneous agent:

I can then create a database link that uses this to test:
create database link MYEXCEL_DBL 'MYEXCEL_TNS';

Now I can query the catalog for this schema and also query the tables in the Excel document;

If this fails (with 'Optional feature not implemented' message) rerun the query, this is a bug (6701400). With 11g this seems to happen with 1st query after the link has been created, every other use is OK. Also this is specific to ODBC Gateway reading from a read-only Excel data source. I was able to use this 11g agent on Windows  with a 10gR2 database on Linux and transparently read the Excel document. So lots of potential once this bug is resolved, also Gateway bug 6883247 is an issue (this was raised after bug 6701461 was raised on OWB, it is actually a database/gateway bug).

This is a quick run through of some initial findings time, permitting I'd like to write up some more of these features that can be leveraged from OWB, the following areas jump to mind;
  • Integrating legacy data using the 11g Studio for legacy integration
  • Parallelizing loading of stage tables using Gateway

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.