Oracle
Introduction to ORACLE 8i common connectivity Technology
====================================
Welcome everyone to exchange with me: small white enhydra_boy@tom.com
Welcome to reprint, please keep this statement, thank you!
====================================
The concept of common connectivity technology introduced in Oracle 8.1.6 (Generic connectivity), a connectivity solution that addresses data access requirements for many heterogeneous database storage without the need to install a transparent gateway for Oracle (Oracle Transparent Gateway). This feature allows transparent connections to be established using industry standard ODBC and OLE DB.
What is a normal connection (Generic connectivity)
The common connection is a low-end data integration solution for Oracle, with the goal of providing Oracle 8i the ability to connect to a non-Oracle database. It is implemented through heterogeneous service agents (heterogeneous services).
Types of heterogeneous Service agents:
ODBC Agent for accessing the ODBC data Providersole DB Agent for accessing OLE DB data providers that support SQL processing-- Sometimes referred to as OLE DB (SQL) ODBC agent for Acceng OLE DB data providers without SQL processing support--sometimes Referred to as OLE DB (FS)
System structure diagram for common connections
The process of accessing a non-Oracle database by an Oracle client program: The client program connects the Oracle server via Sql*net, and the access to the heterogeneous database is delivered to the HS agent.
The HS agent is implemented through the following functional components: The ODBC Administrator->ODBC driver-> The network client of the database-> the target database.
If the heterogeneous library and the Oracle database are on the same server, the driver is generally able to communicate directly with the local database, which eliminates the network client layer of the database.
Conversion of data types
Oracle automates the conversion of ODBC and OLE DB data types to Oracle data types, and the specific corresponding transformations can refer to Oracle's online help http://download-west.oracle.com/docs/cd/A87860_01 /doc/server.817/a76960/datatype.htm.
Restrictions on normal connections
1 A table containing a BLOB column must have a primary key field;
2 Blob/clob data not directly through the pass-through query method;
3 Updates or deletes statements that include functions in the WHERE clause are not allowed;
4 does not support calls to stored procedures;
The HS agent (ODBC/OLEDB) does not support distributed transactions (distributed transactions) and supports only single point transactions (Single-site transactions).
Configuration of common connection agents
Let me introduce you to the ODBC configuration with normal connections. Use the HS ODBC proxy to connect to SQL SERVER 2000.
The first step initialization file creation
First, you have to create the initialization file. ORACLE provides sample initialization files, the name init<agent>.ora,<agent> may be hsodbc,hsoledb,hsolefs, representing three types of proxies, located in $oracle_home/ Hs/admin under.
Copy the corresponding file and rename it to Init
Copy and create a Inithssql2k.ora file, edit the Inithssql2k.ora file. Parameters are:
# This is a sample agent init file, contains the HS parameters that are
# needed for an ODBC Agent.
#
# HS init parameters
#
#HS_FDS_CONNECT_INFO = <odbc data_source_name>
#HS_FDS_TRACE_LEVEL = <trace_level>
Hs_fds_connect_info = SQL2K
Hs_fds_trace_level = On
Hs_autoregister = TRUE
#
# Environment variables required for the non-oracle system
#
#set <envvar>=<value>
The second step is to create a data dictionary of heterogeneous Service agents
Perform $oracle_home/rdbms/admin/caths.sql to create related system tables and views.
Step three creating a working environment for heterogeneous service agents
Add a hssql2k listening service, $ORACLE _home/network/admin/listener.ora
Sid_desc=
(SID_NAME=HSSQL2K)
(oracle_home=d:\oracle\ora81)
(PROGRAM=HSODBC)
)
Modify $oracle_home/network/admin/tnsnames.ora to add a network service name
SQL2K, which will be used when you create a database join later.
SQL2K =
(description=
(Address= (PROTOCOL=TCP) (Host=localhost) (port=1521))
(Connect_data= (SID=HSSQL2K))
(Hs=ok)
)
Reboot listening and view service status
Lsnrctl for 32-bit windows:version 8.1.7.0.0-production on 02-jul-2003 12:49:
30
(c) Copyright 1998 Oracle Corporation. All rights reserved.
Welcome to Lsnrctl, type ' help ' for information.
lsnrctl> status
Connecting to (description= address= (protocol=tcp) (Host=sam) (port=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version Tnslsnr for 32-bit windows:version 8.1.7.0.0-produ
tion
Start Date 02-jul-2003 11:08:31
Uptime 0 days 1 hr. 2 sec
Trace level off
Security off
SNMP off
Listener Parameter File D:\oracle\ora81\network\admin\listener.ora
Listener Log File D:\oracle\ora81\network\log\listener.log
Services Summary ...
ORCL has 1 service handler (s)
ORCL has 3 service handler (s)
Plsextproc has 1 service handler (s)
HSSQL2K has 1 service handler (s)
The command completed successfully
Lsnrctl>
The HSSQL2K listening service is already functioning properly.
Step fourth Modify the database startup parameter file
Modify the database startup parameter file, set Global_names=false, and if set to True (default), the database connection name and global database name are the same, which can cause ORA-02085 errors.
Step fifth Create a connection to access the Non-oracle database (db link)
In Sqlplus, execute the CREATE DATABASE link command. Connect to specifies the username and password for connecting to SQL Server.
Sql>create Public Database link SQL2K connect to SA identified by manager using ' SQL2K ';
Database Link created.
Sixth Step Test
Oracle8i Enterprise Edition Release 8.1.7.0.0-production
With the partitioning option
Jserver Release 8.1.7.0.0-production
Sql> Connect Scott/tiger;
Connected.
Sql> Select COUNT (*) from SYSOBJECTS@SQL2K;
COUNT (*)
----------
135
Oracle now has access to the SQL Server database. All above, I refer to Oracle's online help, and the actual test pass. The author also found that the use of HSODBC is not very stable, and some SQL statements will cause HSODB agent fatal error, at the same time there are many restrictions, the use of transparent gateway should be able to achieve very good results.
However, this feature of Oracle 8i allows the installation of additional transparent gateway products. Therefore, as long as the application of data access requirements can be met, the adoption of generic connectivity is also a fast, economical method.
The author's test environment: Windows2000 server+oracle 8i (8.1.7)
