Re: Hi,

the enterprise service bus has a database adapter. you point it at a table, and tell it to poll for changes to that table. you configure another adapter to insert that record into another database via another database adaptor.    

  I don't have to code the scheduling mechanism, the insert...select..., the transformation between record structures from source to target, etc. I configure stuff. semantics maybe.    

  some would say middleware adds complexity (now we have middleware involved). many places already use middleware, and are comfortable with it, so using it to solve this problem is a no-brainer for them. you configure through a wizard two adapters, one for the source, the other at the target, and than the polling begins.. when a new record is found, it is pushed to the target.    

  the polling (other than the interval) is a black box to the developer, the insert/transformation is also a black box of sorts.    

  oracle posts an example here:
  http://download.oracle.com/docs/cd/B31017_01/integrate.1013/b28212/usingendtoend.htm#CIHBBCGB       

this example though is a file to a database using the file adapter (at the source), and a db adapter at the target. for this example, dbas would write SQL*Loader or external table insert..select, they would write a file system polling mechanism, and they would call their script whenever the file showed up. there is nothing wrong with that. this is another approach that is less coding than that IMHO. In this SQL Server to Oracle scenario, instead of file adaptor to db adaptor, you would have db adaptor to db adaptor.    

  I shouldn't comment on the relative ease of development. It depends on the skill set of the team. but if you need real time integration, and don't want to code the polling mechanism, and the insert, use middleware adapters that already have this logic in place for simple gui configuration.    

  just another approach to integration that fits certain use cases. not bulk data movement really in my opinion, but folks use middleware to move thousands of messages a second from system to system, where a pure DBA would only consider db links/replication technology.    

  just another opinion on an approach that may be applicable. isn't real-time integration the thing now? (Streams, SOA, EDA, etc.)    

  Job   

Alexander Fatkulin <afatkulin_at_gmail.com> wrote:   Job,

> With the development tools in the middleware space, I can do this
> integration quite quickly from SQL Server to Oracle without requiring any
> DBA intervention and without coding.

Setting up TG is not a rocket since also... What coding do you mean?

> For real-time integration, this may be the ideal approach, instead of
> coding/scheduling the insert...select * from table where timestamp >
> last_time_i_checked.

Checking the timestamps is not the only approach. I don't see how any middleware can make this easier (besides adding another layer of complexity)?

On 7/16/07, Job Miller wrote:
> The ODBC Generic Gateway is free with the database. The Transparent Gateway
> for SQL Server is an add on option to the database that you may or may not
> need, depending on whether you can benefit from the capabilities the
> transparent gateway for SQL server provides.
>
> Dare I suggest middleware approaches to db-to-db integration on a dba
> mailing list?
> With the development tools in the middleware space, I can do this
> integration quite quickly from SQL Server to Oracle without requiring any
> DBA intervention and without coding.
>
> For real-time integration, this may be the ideal approach, instead of
> coding/scheduling the insert...select * from table where timestamp >
> last_time_i_checked.
>
> For bulk transfer/ETL, it may not be, but for consistent incremental
> changes, it may be.
>
> Job
>
>
> ________________________________
> Be a better Heartthrob. Get better relationship answers from someone who
> knows.
> Yahoo! Answers - Check it out.
>
>

-- 
Alex Fatkulin,
The Pythian Group,
http://www.pythian.com/blogs/author/alexf/


       
---------------------------------
Park yourself in front of a world of choices in alternative vehicles.
Visit the Yahoo! Auto Green Center.
--
http://www.freelists.org/webpage/oracle-l