Just some time back i had to test the setup DataDirect ODBC connection between Oracle to Sql server. here is the complete documentation on how to setup
1) Basic Overview
2) Installing the data dictionary
tables and views for Heterogeneous services.
3) Installing DataDirect Connect for
ODBC Driver on the Server.
4) Configuring ODBC data source in
the odbc.ini file.
5) Configuring tnsnames.ora and
listener.ora files.
6) Creating a user in Oracle and in
SQL Server side.
7) Test the Connectivity by
(.example) provided by the vendor.
8) Creating the initialization file
and configuring it.
9) Creating a Database link.
10) Testing the Connectivity from the
oracle to SQL Server with a sample query.
1) Overview:-
Generic
Connectivity provides Oracle Users, the ability to access and integrate non-Oracle
data sources, by providing a large range of flexibility in a multi-database environment.
This
Document explains the Installation and the use of DataDirect Connect for ODBC with Oracle on an AIX
system using Oracle 11g and DG4odbc. Below diagram shows DataDirect Connect 6.1
ODBC drivers to connect Oracle to Microsoft SQL Server or Heterogeneous
Services.
2) Installing the
data dictionary tables and views for Heterogeneous services:
First,
select Oracle database and login in to the SqlPlus as sys and try to run the
following script which is located in $ORACLE_HOME/rdbms/admin/caths.sql.
For
example:
|
SQL>@ORACLE_HOME/rdbms/admin/caths.sql;
|
3) Installing
DataDirect Connect for ODBC Driver on the Server:
(i)
Check the permissions: Log in as a user with full r/w/x
permissions recursively on the
entire DataDirect Connect for
ODBC installation directory.
(i)
Install the DataDirect Connect for ODBC
Driver on the
server.
From on the options choose SQL Server Wire Protocol.
(ii)
Set the environment variables for the
ODBC.
4) Configuring
ODBC data source in the odbc.ini file:
After
installing the ODBC Driver we need to configure odbc.ini file in $ODBC_HOME by
setting up some of the parameters.
(i)
Change the ODBC Data source name
Ex: natodbc =DataDirect
6.1 SQL Server Wire Protocol
(ii)
Parameters to be looked in order to make connectivity:
Driver=
Database=<name
of the target database SQL DB’s>
disguisewide=1
HostName=
<Host name of the SQL Server>
PortNumber=
5) Configuring tnsnames.ora and listener.ora
files:
Edit the listener.ora and
tnsnames.ora in $ORACLE_HOME/network/admin
something like this Ex:
(i)
Listener.ora:
(SID_DESC=
(SID_NAME=natodbc)
(ORACLE_HOME=/prog/app/oracle/product/11.1.0/
db_2)
(PROGRAM=dg4odbc) (ENVS=LIBPATH=/var/tmp/opt/Progress/DataDirect/ Connect64_for_ODBC_61/lib:$ORACLE_HOME/lib)
)
(ii)
Tnsnames.ora:
dg4odbc =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = xx.x.x.x.)
(PORT = 1521)
)
(CONNECT_DATA = (SID = natodbc)
)
(HS=OK)
)
IMP:
Before restarting the listener, make sure the SID named in the listener.ora and
tnsnames.ora are the same and make sure the ODBC
LIBPATH directory is added to the listener.ora).
6) Creating a user
in Oracle and in SQL Server:
We
need to create a user in Oracle and SQL Server in order to connect from Oracle
to SQL Server. Grant the user the required privileges to connect.
7) Test the
Connectivity by (.example) provided by the vendor:
After
editing all the configuration files and restarting the listener we need to test
the connectivity by executing the .example provided by the software
(/$ODBC_HOME/samples/example). Test run the .example by giving appropriate
values as input and check for the connectivity. Once if it connected, you can
query a table from SQL Server for our reference. Ex:-
/var/tmp/opt/Progress/DataDirect/Connect64_for_ODBC_61/samples/example>./example
./example DataDirect Technologies, Inc. ODBC Example
Application.
Enter the data source name : natodbc
Enter the user name
: OracleDevNational
Enter the password
: <password>
sqlGetInfo (17)
sqlGetInfo (18)
sqlGetInfo (6)
sqlGetInfo (7)
sqlGetInfo (26)
Enter SQL statements (Press ENTER to QUIT)
Enter SQL statements (Press ENTER to QUIT)
SQL> select count(*) from dbo.table_name;
COUNT(*)
----------
9
8) Creating the
initialization file and configuring it:-
Create an initialization file in
$ORACLE_HOME/hs/admin with the same name that you used in the odbc.ini file
(name of the Data source) like initnatodbc.ora. Configure the initialization
file by adding following lines.
HS_FDS_CONNECT_INFO
= natodbc
HS_FDS_TRACE_LEVEL = 4
HS_FDS_TRACE_FILE_NAME=/var/tmp/opt/Progress/DataDire ct/Connect64_for_ODBC_61/dg04trace.log
HS_FDS_SHAREABLE_NAME=/var/tmp/opt/Progress/DataDire ct/Connect64_for_ODBC_61/lib/odbc.so
set
ODBCINI=/var/tmp/opt/Progress/DataDirect/Connect64_for_
ODBC_61/odbc.ini
9) Creating a
Database link:
After
configuring all the configuration files. Now we need to create a Database link
to access the Target Database from Oracle.
Ex: create database
link <database link name>
Connect to <username>
Identified by <password>
using
‘tnsnames.ora’;
where username:
created in step 6.
Tnsnames.ora:- created in step 5
(ii).
10) Testing the
Connectivity from the Oracle to SQL Server with a sample query:
In
order to test you’re the connectivity from Oracle to SQL Server. Try to execute
a simple query (select a table from the SQL Server database) and see
Note:
If you are getting any errors try to
check whether you missed out any steps in the process.
Hope this Helps....
No comments:
Post a Comment