Re: Can Oracle Access Informix Database?

In article <1994Apr2.051714.17167_at_schbbs.mot.com> Q10641_at_paccvm.corp.mot.com (Troy Tinnes) writes:

> I've inquired as well concerning this issue however from the posts
> I've read, everything seems to indicate that there is no easy way to
> communicate between the 2 products (not even a SQLPLUS COPY which implies
> use of Oracle's SQL*NET which Informix doesn't talk to). Its too bad

Oracle Open Gateway products allows you to access Informix databases r/w and even allow them to participate in distributed transactions even if your Informix db or whatever lacks 2PC and transaction memory. You have several options:

1. Buy a tailor made solution from one of our VARs or let us build one for you.
2. Build your own SQL-based transparent gateway with our developer's kit. We're talking about a day or two of C programming. We even include a demo gateway for Oracle built with embedded SQL, so you should be able to use this pretty much as is against Informix.
3. Build your own procedural gateway with our developer's kit. This GW differs in that it does not accept SQL, only PL/SQL function, procedure or package calls (RPC) from an Oracle7 DB, and maps these to your own C-functions (compiled into the gateway).

These products are being rolled out now. In my opinion the difficult part is not programming technicalities, but rather planning. You must carefully plan what type of functionality you want to provide.

> that the Informix and Oracle NET products couldn't be an industry
> standard protocol which all database systems could use ... however
> if this was implemented, it would no doubt work out in Informix's
> favour since Informix products are thousands of dollars cheaper.

Well, the interfaces are certainly opening up. We're allowing people to use any database with our development tools, and use any tool to access our database. I have my doubts about this working out in Informix's favour though...

>
> At any rate, I do have something implemented (a hack at best) that
> dumps informix data into Oracle daily. It works like this:
>
> 1. Every night a CRON job starts up on the Oracle system and does a
> 'rsh' to run a C executable on the informix host.
> 2. The C program produces an output of the data I want into a flat
> file ... the CRON job next runs 'rcp' to copy the flat file onto
> the Oracle host.
> 3. Next, SQL*LOADER is started on the Oracle host and the flat file
> is dumped into a temporary table on the database.
> 4. A checker program is executed to make sure the LOADER executed okay
> and that the data from the other database is valid (best not to
> trust any other programmer's validations especially if this data
> is eventually being dumped into your 'live' system :)
> 5. The data is transferred out of the temporary table (provided its ok
> and transfered into the final destination table.
>
> Steps one and two can be substantially simplified if you have the
> luxury to automount your Informix and Oracle systems together.
>
> Has anyone been able to write any 'C' tools to implement this directly?
> For instance, Oracle offers PRO*C and Informix has its own C. Has anyone
> been able to write a tool which can grab data out of Informix and put
> it into Oracle using the two C products essentially compiled together?

You could use our OCI library instead of Pro*C to avoid any conflicts with Informix embedded SQL. If you prefer PRO*C, you could write two C programs that exchange data through pipes, shared memory, sockets or whatever.

>
> I welcome any shortcuts or criticisms concernr.
 

>
> I welcome any shortcuts or criticisms concerning my 'hack' above and
> would like to hear from people who have written similar tools!
>
> Thanks,
>
> -----------------------------------------------------------------------
> J. Troy Tinnes Tel: 011-816-586-3937
> Database Administrator Fax: 011-816-586-3962
> Nippon Motorola Ltd.
> Osaka, JapanE-MAIL: tinnes_at_cig.nml.mot.com
> -------------------------------------------------------------------------
>
> We have Oracle7 running on one RS6000/AIX machine and Informix on another
> RS6000 machine. The two computers are networked via TCPIP. Is there a way
> to make Oracle be able to communicate with the Informix database, like
> possibly use the SQLPLUS COPY command to copy a table from a non-Oracle
> remote database? If this is even possible, what tools would need to be
> running on each machine to accomplish this?
>
> Thanks for any suggestions.
> -------------------------------------------------------------------------------
> Dagmar Murray, Senior System Engineer
> Space Management & Academic Scheduling
>
> We have Oracle7 running on one RS6000/AIX machine and Informix on another
> RS6000 machine. The two computers are networked via TCPIP. Is there a way
> to make Oracle be able to communicate with the Informix database, like
> possibly use the SQLPLUS COPY command to copy a table from a non-Oracle
> remote database? If this is even possible, what tools would need to be
> running on each machine to accomplish this?
>
> Thanks for any suggestions.

--

Magnus Lonnroth
Tech.Sales & Consultant
Oracle Sweden
Mail: mloennro_at_se.oracle.com