ORACLE heterogeneous connection 란

ORACLE heterogeneous connection 란 ?

오라클은 DBMS간의 연결을 OCI  Oracle Common Interface를 통해서 DB링크나 원격 프로그램들이 접속 하도록 하였다.

그러나 Mysql, MS-SQL, PostgreSQL 등 이 메커니즘을 제공하지 않는 이기종 DB와의 연결을 위해서

HSODBC라는 것을 통해서 커넥션을 할수 있게 하고 있다.

hsodbc는 오라클 9~10에 사용되었고 11g 이후는 db4odbc로 명령이 바뀌었다.

따라서 hsodbc의 구동 메커니즘은  db4odbc와 유사하고 환경 설정도 동일 하다.

Configure heterogeneous connection from Oracle DB to PostgreSQL DB

[http://docs.oracle.com/cd/B19306_01/server.102/b14232/tgvsgc.htm#sthref36]

Heterogeneous Services agent

The Heterogeneous Services component in the Oracle database server talks to a Heterogeneous Services agent 
process which, in turn, talks to the non-Oracle system. We can conceptually divide the code into three parts:

1. The Heterogeneous Services component in the Oracle database server.

Most of the processing related to heterogeneous connectivity is done in this module.

2. Agent generic code.

This is code in the agent that is generic to all Heterogeneous Services products. This consists, 
for the most part, of code to communicate with the database and multithreading support.

3. The driver.

This is the module that communicates with the non-Oracle system. It is used to map calls from the
Heterogeneous Services onto the native API of the non-Oracle system and it is non-Oracle system specific.

Heterogeneous Services Agents

Configuring Heterogeneous Services

In the previous section, we described what the different heterogeneous components do. 
These components consist entirely of generic code and, in order to work with so many different non-Oracle systems, 
their behavior has to be configured. Each gateway has configuration information stored in the driver module and 
this information is uploaded to the server immediately after the connection to the gateway has been established.
 We can divide this configuration information into three parts:

@
1. Data Dictionary Translations : http://docs.oracle.com/cd/B14117_01/server.101/b10764/tgvsgc.htm#1006046
2. Heterogeneous Services Initialization Parameters : http://docs.oracle.com/cd/B14117_01/server.101/b10764/tgvsgc.htm#1006058
3. Capabilities  :http://docs.oracle.com/cd/B14117_01/server.101/b10764/tgvsgc.htm#1006064
@

Gateway Process Flow

 

 typical gateway process flow. The steps explain the sequence of events that occur when a client 
application queries the non-Oracle database system database through the gateway.

 

운용 프로세스

1. The client application sends a query over Oracle Net to the Oracle database server.

2. Heterogeneous Services and the gateway converts the Oracle SQL statement into a SQL statement
  understood by the non-Oracle database system.

3. The Oracle database server sends the query over to the gateway using Oracle Net.

4. For the first transaction in a session, the gateway logs into non-Oracle database system 
   using a username 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 the Oracle database server.