Suck that data out! Connect from Oracle to MaxDB

In one of my former jobs, I had been in charge of a rather small project tracking tool for the engineering department. As buying equipment and ordering services were two of the duties of these engineers these tasks had also been modelled into the tool. Of course, the orders had to be transported into the central financial application. Therefore a meeting with the consultants for that ERP system (no, not ours… they had got the wrong one there… ;-)) was set up.

After I explained what data our tool can deliver and offered to create some database views for that purpose, I asked for what information the ERP system would need to be able to process the data correctly. The consultant smiled and told me “Don’t bother – just give us direct access to the database and we will suck the data out!”

It’s unnecessary to tell that this interface was never brought fully productive…

Anyhow: connecting systems is one of the major problems bigger IT-landscapes face. SAP Netweaver it a tool specifically designed for this task. With it, it’s possible to define system interfaces that support each of the attached systems to “grow” and develop further and still being able to use the interface. (XI would be the technology in charge).

But there is the downside of complexity. Sometimes you just want to get some data from one database of a system into the one of a different system without having to define a messaging infrastructure first.

With the db-links of Oracle this is quite easy. Unfortunately these links only seem to work with Oracle databases… but only at first sight!

Let’s assume you’ve got a MaxDB and an Oracle Database and want some data from that MaxDB in the Oracle Database. That can be done by either exporting the data from MaxDB into flat files and then import it into Oracle (for which you would have to have some 3rd party tool) or you access the data in the MaxDB directly from Oracle.

Let’s see how this is done.

Oracle comes with a functionality called “Generic Connectivity” (GC). GC enables Oracle to access data via DB-Links that can be reached through OLE DB or a ODBC connection.

In this example, I will set up an ODBC connection to my MaxDB database and perform the necessary steps to access the data out of Oracle. I assume that both databases are already correctly installed and that there is the ODBC driver for MaxDB installed on the Oracle Server.

1. Create a ODBC connection

START -> Programs -> Administrative Tools -> Data Sources (ODBC)
or
START -> Settings -> Control Panel -> Administrative Tools -> Data Sources (ODBC)

Change to “System DSN” and click “Add…”

In the “Create New Data Source” dialogue choose the “MaxDB” ODBC driver and click on “Finish”.

In the upcoming dialogue I enter the following:

Data Source Name:  SDB
Server:            <hostname of MaxDB Server>
Database:          SDB

Then click on “Settings …”  and select the ORACLE sqlmode. Close the dialogue windows by clicking “OK”.

Now a new ODBC data source has been created.

2. Modify the Oracle Net setup

Create a file named init<SID>.ora in the %ORACLE_HOME%/hs/admin-Folder. In my case the file is named initSDB.ora If you look into that folder you’ll find also templates for this file, but the one I use just contains these two lines:

HS_FDS_CONNECT_INFO = SDB
HS_FDS_TRACE_LEVEL = OFF

Basically, this will tell Oracle later on, which ODBC data source should be used. So make sure that the <SID> is correct here.

The next thing is to add an entry into the tnsnames.ora file like this one:

SDB.WORLD =
      (DESCRIPTION=
        (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1512))
        (CONNECT_DATA=(SID=SDB))
        (HS=OK)
      )

The (HS=OK) part is the key point here! Make sure to check if the Listener port is correct for your system.

As the last step of configuring the listener configuration has to be made aware that it should listen to the new entry. So add this to the SID_LIST_LISTENER list in the listener.ora-file:

(SID_DESC=
      (SID_NAME=SDB)
       (ORACLE_HOME=c:\\oracle\\tdb\\102)
      (PROGRAM=hsodbc)
    )

With that, the Oracle listener will start up a kind of adapter program when a connection to this SID is made. This adapter program then will translate the SQL*NET requests into ODBC calls.

Now restart the Oracle listener:

lsnrctl reload

3. Creating a DB-Link inside the Oracle database

The finishing step to access data in the MaxDB database out of Oracle is to create a db-link that points to the new tnsnames.ora-entry:

sqlplus /
create database link sdb.world connect to lars identified by lars using ‘SDB.WORLD’;

Of course, you will have to put in your username and password in here, but I guess you get how this should work.

4. The first query:

Now we’re already ready to get data out from the MaxDB:

select * from user_catalog@sdb.world;

TABLE_NAME      TABLE_TYPE
--------------- -----------
TEST            TABLE

If you now get a error message like this:

ERROR at line 1:
ORA-02085: database link SDB.WORLD connects to HO.WORLD

Then this is just because the Oracle parameter GLOBAL_NAME is currently set to TRUE. After a

alter system set global_names=FALSE scope=both;

this error will be gone.

So we’ve got an easy way to get data from a MaxDB into an Oracle database. Actually, this will work with any database that you can reach over ODBC.

For further reading have a look at: Oracle® Database Heterogeneous Connectivity Administrator’s Guide 10g Release 2 (10.2) 7 Generic Connectivity (http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14232/gencon.htm)

A Warning

I just presented an easy rather quick and dirty way two make up an interface between to systems, by just accessing the data in the databases of the systems. Please don’t consider this to be the proper way to implement an interface! This is nothing but pure data shovelling and should not even be thought as the right layer on which the system interface is designed. What happens when the data definition in on of the databases change? Do you want to change the interface, too? Every time? Surely not! How do you keep track of problems in the interface? Write a logging facility yourself?

Therefore: Use what you’ve paid for! Use Netweaver! It’s already there and it’s designed just for this kind of tasks.

Hope you have some fun with this technical option anyhow.

Best regards,
Lars

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.