Oracle HS (heterogeneous Services) in-depth analysis and collaborative Gateway Workflow (RPM)

Heterogeneous data sources interact with Oracle database.

Figure 1

is an Oracle heterogeneous Connection processing architecture diagram where we can see that the main non-data source modules include the HS (heterogeneous Service), Agent. There is also a remote database, which we assume is a heterogeneous data source.

As you can see, Oracle is not able to interact directly with these heterogeneous data sources, and Oracle needs to interact with heterogeneous data sources through the Heterogeneous Services collaboration Agent .

Heterogeneous Services Component

HS is a component of Oracle database that is used for data interaction with the non-oracle system.

HS's role: It tells the agent that it wants to interact with heterogeneous data sources and let him know what data it wants, what kind of data, how it handles data (such as type, method, etc.).

The HS consists mainly of two modules:

A. Transaction services (Transaction service)

The Transaction service enables heterogeneous data sources to be integrated into transactions and sessions in the Oracle database server as much as possible. So when you access a heterogeneous data source, you use the current session of the Oracle user to establish the appropriate authentication session for the heterogeneous data source through the database link (the authentication information that accesses the heterogeneous data source is provided by the user or database link has been specified). Similarly, when the user closes the session of the current Oracle, the corresponding session on the heterogeneous data source is closed. Oracle also supports Oracle distributed transactions that access heterogeneous data sources.

B. Structured Query Language service service (SQL service)

SQL service is used to handle all SQL-related operations, including:

A) map Oracle internal SQL-related calls to the HS-driven API. These are once driven by client APIs that map to heterogeneous data sources.

b) Translate the SQL statement. SQL statements that translate Oracle's SQL statements into related heterogeneous data sources.

c) Translate the query statement that references the related table of Oracle data dictionary to the query statement that can get the required information from data dictionary of the Heterogeneous data source

d) Convert data types between heterogeneous data sources and Oracle databases

e) make up for the non-existent function method in the heterogeneous data source, so that it becomes a number of production-required statements to obtain the corresponding results.

HS components have many common encodings and are configured to work together with many different heterogeneous data sources. Each gateway has these configuration information and is present in the appropriate driver module (Driver module). This information will be uploaded to the Oracle server immediately after the HS and gateway/agent connections are established. These configuration information includes the following:

A) Data Dictionary translation views

A data Dictionary conversion view is a data dictionary table that exists as a view in a heterogeneous data source. It helps the HS to consume a data dictionary of heterogeneous data sources as it would with Oracle's Data dictionary table.

b) Heterogeneous Services initialization Parameters

The HS initialization parameter mainly provides two functions:

1) provides a good way to tune the gateway to enable the performance and memory usage of the gateway and HS components to be optimized.

2) Configure information for heterogeneous data sources. For example, how many heterogeneous data sources are configured, what languages they are running, and what properties they have.

Information about the settings of these parameters for the current session can be obtained by querying the table V$hs_parameter. You can make settings changes in the gateway's initialization file.

c) Capabilities

Functionality is to tell the HS heterogeneous data source which types of SQL statements are not supported, how to convert these SQL expressions into a heterogeneous data source.

HS Data Dictionary

Above we mentioned, configuration information. The configuration information is immediately uploaded by the agent to the HS component after the HS and agent have established a connection. Then after uploading, they are stored in the HS Data dictionary table. And until the agent changes, the information will be overwritten and modified accordingly.

Below we explain the information about these data dictionary:

With HS, you can access multiple heterogeneous data sources from the same Oracle database, as shown in:

Figure 2

Two agents will upload the configuration information, respectively, into the HS Data Dictionary table.

So how does this information come to be summed up? HS points This information at two levels:

A) Class

Class is divided according to the relevant type of product. For example, Sybase database systems, the SQL Server databases system is divided into one category.

b) Instance

Instances are defined based on class. For example, there are multiple instances of the SQL Server database class.

So a class is shared by multiple, specific instance. So assuming that the Oracle database accesses 5 instances of SQL Server and 2 instances of MySQL, then the Oracle database requires two class definitions to visit them. One for defining SQL Server and one for defining MySQL. Similarly, the definitions of 5 and 2 instance are defined in these two classes respectively. It is also important to note that both the capacity of the instance layer and the data dictionary information are session information, so they cannot exist in the HS data dictionary in the Oracle database. However, the initialization parameters of instance can exist in the database. Information is included in the HS data dictionary view (Dictionary views):

n the names of instances and classes that are uploaded to Oracle data dictionary

n functionality, including SQL transactions defined for each class and instance

n a data dictionary conversion relationship defined for each class and instance

n the initialization parameters defined for each class and instance

These views are divided into three main categories:

N General View

n Transaction Service View

n SQL Service View

Agent Component

Agent component includes:

A. Agent Generic Code

Agent Generic code is used to perform data manipulation or retrieval on heterogeneous data sources. It is responsible for receiving commands, executing commands, and obtaining execution results.

B. Driver

Driver is a bridge between agent Generic Code and command execution. Without it, Agent Generic code cannot interact with heterogeneous data sources.

Agent Generic code is like a person who wants to work on the other side of the river, driver is like a transport to the other side, such as a bridge.

From Figure 1, we might think of whether the agent can be placed in an environment alone? Of course it's possible,

A) It can be installed on the same computer as a heterogeneous data source

b) It can be installed on the same computer as Oracle

c) It can be separated from both Oracle and heterogeneous data sources and installed on a separate computer

Oracle provides many types of agents because different database vendors use the same standards, which are mainly packaged in the following two products through Oracle Products:

A. Oracle Database

In Oracle database, the ODBC Agent is installed by default. Used to interact with ODBC for the current system. Thus the system is responsible for driver. The corresponding agent Generic code is dg4odbc.exe, this program can be found in the oracle_home/bin directory.

B. Oracle Database Gateways

Optional installation by Oracle Database gateways product agent for different database products (Agent Generic Code, Driver). So Driver also and Oracle database Install together when gateways is installed. For example, if you have installed the components of the gateway for MS SQL Server, you can find the agent Generic Code:dg4msql.exe under the oracle_home/bin file, in Oracle_ The HOME/dg4msql folder finds the relevant agent's driver, which is also stored in some configuration-related dg4msql configuration files.

Let's take a holistic look at how HS and gateway work in a systematic way:

Figure 3

(1) The client requests data from Oracle database. The user sends an Oracle-standard SQL statement that creates a session on the Oracle database.

(2) HS and gateway work together, and HS translates Oracle's SQL statements into statements that the Non-oracle database can run correctly.

A) HS and gateway establish connections

b) HS Gets the configuration information for the relevant non-oracle system, such as what type of database product it is (SQL Server or MySQL, Sybase), etc. properties

c) HS uses the data Dictionary view to identify various properties such as the non-oracle system's functional characteristics, and convert the Oracle standard SQL statements into Non-oracle system-compatible statements

(3) The Oracle database sends the converted SQL statements through Oracle NET to the gateway.

(4) gateway uses the relevant username and password to log in to the non-oracle system to establish a connection and execute the SQL statement.

A) HS connects to the Non-oracle system via the gateway to create a temporary database link.

b) Gateway uses the user name and password specified by the database link to login to the non-oracle system

c) The gateway's successful login creates a session between the gateway and the Non-oracle system, and the database Link Open

d) Gateway sends SQL statements through established sessions to non-oracle system execution

(5) Non-oracle system Returns the result of running the SQL statement to the gateway.

(6) Gateway translates/formats The returned results to be compatible with the Oracle database.

(7) Return the results of the gateway to the Oracle database via Oracle Net service.

(8) The Oracle database server returns the results to the client through Oracle Net. and the database link of the databases is opened until the end of the session of the gateway or explicitly closed.

In the above step, step (2), the gateway uses the local configuration to invoke the relevant HS service program and drive to interact with heterogeneous data sources. For example:

A) in the configuration of Initxxx.ora, it is mainly filled with the address information of heterogeneous data sources, such as host name/address, port, instance name, database and so on.

b) in the configuration of Listener.ora, it is recorded in the need to use what Agent Generic code program, need to use what to drive. For example, to access MS SQL Server, the agent Generic code program that needs to be used is dg4msql, which is driven under the oracle_gateway_home\dg4msql\driver\lib folder. (If the gateway is installed under Windows, you do not need to specify the path to driver by default)

Oracle HS (heterogeneous Services) in-depth analysis and collaborative Gateway Workflow (RPM)