RE: Oracle Transparent Gateway

From: David Kurtz <info2_at_go-faster.co.uk>
Date: Thu, 29 Sep 2005 18:06:21 +0100
Message-ID: <CKEAJBMGFEOCDBFILPJDAEELGNAA.info2@go-faster.co.uk>

MessageI have used Generic Connectivirt in the past. At the time we were connecting from a PeopleSoft running on Oracle 8 on a Unix server (can't remember the flavour) to a Tandem(?) mainframe. We had an ODBC driver for windows only. So we installed Oracle on a windows server and created an Oracle listener (no need for a database) and created an HS service in the listener that connected to an ODBC data source. Then we created a TNS entry on the Oracle database server that pointed at this listener. We could then create a database link in the usual way to the Tandem mainframe and reference objects via a normal database link.

We buried the db link syntax inside database views in the Oracle database, because it was easier to manage from within PeopleTools. However, it also enabled us to control which columns were visible and what they were called in PeopleSoft. We had reasons for changing column names to match the PeopleSoft naming convention.

We found that we transactional control on the mainframe from the Oracle session. If we updated a data value we took out a lock until we committed the Oracle session.
Although I think that behaviour might also depend on the ODBC driver. You can also get some strange error messages coming back in your Oracle session. It looks really odd when you get standard Microsoft error messages in a SQL*Plus session.

Watch out for date formatting. A date column on the remote database may arrive as a charater string in a default format. That also can depend on the ODBC driver and its settings. In some cases I have reformatted the dates in the views that I create in the Oracle database. This kind of thing can also prevent you updating remote data - but you probably shouldn't be doing that with GC.

Oracle says "Generic Connectivity is a free feature of Oracle9i. It is available with the database license. "

They also say "Generic Connectivity is a low-end solution based on ODBC/OLEDB standards and it has some restrictions such as no distributed updates possible. Transparent Gateways are tailored for the non-Oracle system. They offer more functionality such as full 2PC and provide better performance than Generic Connectivity. "

I suppose that's why you pay $15000 per computer for Gateway Technonogy - see http://www.oracle.com/gateways/gateway_bundles/faq.html

regards

David Kurtz
Go-Faster Consultancy Ltd.

tel: +44 (0)7771 760660
fax: +44 (0)7092 348865
web: www.go-faster.co.uk

mailto:david.kurtz_at_go-faster.co.uk
Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com Next Go-Faster Seminar: PeopleSoft for the DBA, London UK, October PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba   -----Original Message-----
  From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Subbiah, Nagarajan   Sent: 29 September 2005 17:38
  To: 'oracle-l_at_freelists.org'
  Subject: Oracle Transparent Gateway

Hi,

Is the Oracle Transparent Gateway a licensable product? We have a requirement to access the SQL Server from the Oracle using the DB link or Synonyms of the SQL Server object in the Oracle using the dblink.

In the Heterogeneous connectivity document, it says either you could use the Generic connectivity or the Transparent Gateway. The transparent gateway can use the SQL Server database as a another Oracle database so we could use the Database link. How about the Generic connectivity? I wasn't clear that the Generic connectivity has the same feature of using the db link. Also, Does Oracle support the SQL Server connectivity from the Unix (HP-UX)?

Thanks,
Raja

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Sep 29 2005 - 12:11:03 CDT