The Digital Divide

I just finished reading "The Digital Divide: Arguments for and against Facebook, Google, Texting and the Age of Networking" by Mark Bauerlein. As I was reading the book, I kept coming back to the digital divide that is so prevalent in corporate IT where archaic processes are still preferred simply because "They work" or "It's how we do things here" or "Why bother with something new? That will involve learning and training". It amazes me that in this day and age "tried and tested" triumphs over "new and improved", even if it means slower turn-around time, more resources and consequently more dependence on those resources. It certainly makes those resources feel indispensable, but only until the process changes to be less resource intensive thus rendering them useless. As Thomas Friedman and Michael Mandelbaum wrote in "That Used to Be Us", skilled folks doing routine tasks can and will always be replaced by cheaper labor, automation or both. It's an interesting book, worth a read. 


Every business organization is challenged with integrating data residing in different database systems and every organization uses a different approach to overcoming this challenge. The "tried and tested" approach invariably involves extracting data from the source database, creating an ascii file, transferring the flat file to the target database server, and loading/transforming the flat file into the target database. There are several "new and improved" solutions that Oracle has available to handle data integration between a non-Oracle database and an Oracle database, such as Oracle Streams, Golden Gate, Oracle Data Integrator and Oracle Database Gateways. In this blog post, I dig into synchronous data integration using database gateways and heterogeneous services.


Oracle Database Gateways address the needs of disparate data access. In a heterogeneously distributed environment, gateways make it possible to integrate with any number of non-Oracle systems from an Oracle application.
DB gateways and the Heterogeneous Services (HS) component of Oracle DB, enable transparent data access to non-Oracle DBs using familiar tools (TOAD, SQL Developer, OAF, etc) to run queries against non-Oracle DBs. In most cases Oracle SQL can be used for querying non-Oracle DBs - very rarely will you need to use the HS passthrough feature to communicate with the remote DB in its own language. Applications can be developed  using a consistent Oracle interface for both Oracle and non-Oracle data sources.


How it works:

1. The client application sends a query using Oracle Net to Oracle Database.
   
2. Heterogeneous Services and the gateway converts the SQL statement into a SQL statement understood by the non-Oracle database system.
   
3. Oracle Database sends the query to the gateway using Oracle Net.
   
4. For the first transaction in a session, the gateway logs in to non-Oracle database system using a user name and password that is valid in the non-Oracle system.
   
5. The gateway retrieves data using non-Oracle database system SQL statements.
   
6. The gateway converts retrieved data into a format compatible with Oracle Database.
   
7. The gateway returns query results to Oracle Database, again using Oracle Net Services.
   
8. Oracle Database passes the query results to the client application using Oracle Net. The database link remains open until the gateway session is finished, or the database link is explicitly closed.

Oracle Database Gateway for ODBC (DG4ODBC) gives you the ability to connect to any non-Oracle ODBC data source. DG4ODBC, as the name suggests, works with an ODBC driver. The ODBC driver must be installed on the same machine as the gateway. Once you install and  configure the ODBC driver and the DG4ODBC gateway, you will need to modify the tnsnames.ora file on the Oracle DB to be able to connect to the ODBC data source. For more details on installation and configuration, RTFM - Oracle Database Gateway Installation and Configuration Guide, Oracle Database Gateway for ODBC User's Guide and Oracle Database Heterogeneous Connectivity User's Guide.


With DG4ODBC (and a robust design and lots of testing) , the "new and improved" approach will eliminate the need to create an ascii file, transfer the flat file to the target database server, and will speed up the loading/transforming the data into the target database. 


Make "new and improved" the new "tried and tested"!