A useful hint on notes

From time to time I get customers asking: ”When will the patch mentioned in note XYZ be available?”.

Most often the disappointing answer has to be: “The release date won’t be announced until the software is released.” The reasoning behind this policy of information can be disputed – anyhow it is how SAP and its partners currently handle it. We don’t promise dates since there might come up very good reasons to spend some more time on producing the bugfix.

So how can the customer then know when the requested software will be available? Actually, whenever a patch is released this is usually updated in the corresponding SAP note. So to know about the software release is to know about the changes of the note. Ok, but really nobody wants to check all the note all days just to find out one day: the note was modified…

To save much time and more nerves SAP provides a handy feature in the Service Marketplace: the note subscription.

When you display a note in the Service Marketplace you get this little link in the upper right corner named “SUBSCRIBE”. Click it and you’ll be asked if you really want to subscribe. A click on “YES” and the next time the note is changed you’ll get an e-mail notification about it.

To unsubscribe just redisplay the note in the Service Marketplace again and at the very same location where you found “SUBSCRIBE” now an “UNSUBSCRIBE” can be found.

Voilà! Notes tracking made easy.

For those of you that are further interested in the notes-search functionality in the SAP Service Marketplace there is a Learning Map available:

SAP Service Marketplace -> notes.

Just click “Learning Maps” on the left-hand menu and browse through the PowerPoint presentation.

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