Accessing Data From Multiple Heterogeneous Distributed Database Systems

Accessing Data From Multiple Heterogeneous Distributed Database Systems

Shefali Trushit Naik (Ahmedabad University, India)
DOI: 10.4018/978-1-5225-8295-3.ch008


This chapter describes the method to retrieve data from multiple heterogeneous distributed relational database management systems such as MySQL, PostgreSQL, MS SQL Server, MS Access, etc. into Oracle RDBMS using Oracle's Heterogeneous Gateway Services. The complete process starting from downloading and installation of required software, creation of data source names using open database connectivity, modification of system parameter files, checking connections, creation of synonyms for tables of remote databases into oracle, creation of database links and accessing data from non-oracle databases using database links is explained in great detail. Apart from this, data manipulation in remote databases from Oracle and execution of PL/SQL procedures to manipulate data residing on remote databases is discussed with examples. Troubleshooting common errors during this process is also discussed.
Chapter Preview

1. Introduction

In current era, people of all the ages use database in one or the other way directly or indirectly. Computerized Database has made our life very easy and comfortable. We can search any place, product, area, thing, etc. with the help of stored data in a fraction of seconds. People widely use e-commerce websites for sales, purchase, booking, and many other things. Massive amount of data are processed daily over networks. It is impossible to keep and process these data on a single site. Therefore, the data need to be stored at different sites, which user should be allowed to access from anywhere. To implement this functionality, the distribution of data is required. Using network topologies, operating systems, database management systems and computers, it is possible to access data stored at different sites geographically. These types of databases are called Distributed Databases.

Distributed database is a concept in which the physical database (tables) is distributed on different locations or computers known as sites. All the computers are connected through communication network with each other. The distribution of data could be full, partial or not at all. Before distribution, tables are partitioned and if needed replicas of these partitions are created and stored on different locations. There are three dimensions namely Autonomy, Distribution and Heterogeneity; which decide the strength of distributed database (Özsu & Valduriez, 2011). Presence or absence of any of these three dimensions defines the database type. There are three well-known architectures of distributed databases-client/server, peer to peer and multi-database (Breitbart, Morales, Silberschatz, & Thompson, 1990). The systems which have full distribution capacity are called peer-to-peer systems. These systems are fully distributed in nature which shares all their data with other sites to execute transaction and to manipulate the data. The sites which have partial distribution capacity are called client-server systems. This distribution is the most popular. In client-server system, the application resides on client, the data is managed on server and the communication is done by both client and server (Özsu & Valduriez, 2011). Multi-database systems are fully autonomous. These are the system in which the database is installed on each machine and they are interconnected. In DDBMS, the processing can be done at any site (Beraka, Mathkour, & Gannouni, 2011). Users sitting on these sites can access and manipulate local and remote data. On different sites, hardware and software used may be uniform or different. If they are uniform, the type of distributed database system is called Homogeneous Distributed Database System, but if different it is called Heterogeneous Distributed Database System. For ex., the database which is distributed on four different sites (computers), each having different database management systems namely Oracle, MySQL, PostgreSQL and MS SQL Server, the distributed database system known as Heterogeneous Distributed Database. To retrieve, manipulate and store data from these type of multiple heterogeneous distributed database systems; on each site some mechanism is required which will allow to do so. Many RDBMS provide Replication to fetch and manipulate data from different databases which are created within that same RDBMS (homogeneous databases). The comparison is given in the following section. None of the RDBMS except Oracle supports data retrieval and manipulation from different RDBMS (heterogeneous databases). Oracle RDBMS provides Heterogeneous Gateway Service using which data from different non-oracle RDBMS could be retrieved, manipulated and stored back to that database. The implementation of heterogeneous distributed database system is quite challenging which is described step-wise in great detail in this chapter.

Complete Chapter List

Search this Book: