This content has been marked as final. Show 3 replies
in general they are doing the same, they allow you to connect to an SQL Server from your Oracle database.
But there are differences in the licensing and in the capability of each gateway.
As you mentioned some notes here I assume you have access to the My Oracle Support portal and I will first give you 2 other notes:
Database Gateway and Generic Connectivity (DG4ODBC) Licensing Considerations
Functional Differences Between Database Gateway for ODBC (DG4ODBC) and Specific Database Gateways (DG4MSQL, dg4sybase, dg4informix, dg4teradata)
These notes describe the major differences between both products. DG40dbc is for free but would require a 3rd party ODBC driver which might cause additional costs when you purchase it from a commercial ODBC vendor like DataDirect. DG4MSQL comes with everything you need and is ready to use once you installed and configured it.
Now the major functional difference: DG4ODBC is the free product and works with every suitable 3rd party ODBC driver. You can connect to text files or to other foreign databases like DB2 or MS SQL Server. So DG4ODBC is a generic connectivity and it only knows a couple of functions it can directly map to foreign database functions. All other functions will cause a post processing which means that all records from the remote table(s) will be fetched into the Oracle database and the result is then processed locally. So you see its capability is limited.
DG4MSQL is designed for MS SQl Server connections. It knows how to map a lot of Oracle functions to the SQL Server equivalents and it allows you also to participate in distributed transactions as well as calling remote SQL Server stored procedures.
So best would be first to check what you need to do; for just selecting data from a MS SQl Server both gateways would work. Then you have to check if there are available ODBC drivers for free or if you have to purchase them from a 3rd party vendor for DG4ODBC (for example when you install DG4ODBC on MS Windows or Linux x86-64bit) you could use the ODBC drivers from Microsoft. If you have to purchase a driver for your Solaris platform you might compare the pricing of DG4MSQL and the 3rd party ODBC driver license.
Your question is similar to the post: Oracle 11gr2 connection to Sql Server using dg4msql problem
Please visit: http://stackoverflow.com/questions/4658942/oracle-11gr2-connection-to-sql-server-using-dg4msql-problem
Answer on the website:
You seem to be using the Gateway for MySQL set-up rather than the Heterogeneous Gateway (for ODBC connections). Here is an overview of the process
On SQL Server create a database user and give it read access to the database/tables you want to read via the Oracle database link.
In the gateway home each SQL Server database you want to access should have an init.ora located in $OH/dg4msql/admin in the form initsid.ora where sid is the name of the database to be used in the link (e.g. initbob.ora), so create one
You must now add the new sid to the listener.ora in the gateway home using an additional SID_DESC section inside the existing SID_LIST, for example
You should now stop and restart the gateway listener so that the new sid becomes active. NB a reload is not enough.
You must now add the new sid in the tnsnames.ora file for the listener of each database in which you will create a link. You don't need to do this in the gateway home unless it is also a database home in which you will create a database link.
(ADDRESS = (PROTOCOL = TCP)(HOST = severname.example.com)(PORT = 1690))
(CONNECT_DATA = (SID = bob))
(HS = OK)
NB: The host and port are for the gateway not for the SQL Server database
In each database that requires a link to the MS-SQL database you should create a database link to your new gateway sid.
CREATE PUBLIC DATABASE LINK bob
CONNECT TO "ms_user" IDENTIFIED BY "ms-passwd" USING 'bob';
where ms-user and ms-password are the SQL Server user you created right at the start.
Now you can test the new database link
SELECT COUNT(*) FROM "Table_Name"@bob;
Once you have this working you can alter the initsid.ora file to add parameters to suit your connection. If you do it this way you can easily add and manage many different databases via the gateway.
Edited by: 1000595 on 04:58 17-04-2013