1 Reply Latest reply: Aug 28, 2012 10:23 AM by Mkirtley-Oracle RSS

    Connection from Oracle to non-oracle using DG4ODBC

    911172
      Hi All,

      I have encountered below error message after creating dblink and trying to access data from SQL server.


      ______________________
      ERROR at line 1:
      ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
      ORA-02063: preceding line from ODBC1.

      ------------------------------------------------------------------------Additional Info---------
      $ cat odbc.ini
      [ODBC Data Sources]
      DB2 Wire Protocol=DataDirect 5.1 DB2 Wire Protocol
      Informix Wire Protocol=DataDirect 5.1 Informix Wire Protocol
      Oracle Wire Protocol=DataDirect 5.0 Oracle Wire Protocol
      Oracle=DataDirect 5.1 Oracle
      SQLServer Wire Protocol=DataDirect 5.1 SQL Server Wire Protocol
      Sybase Wire Protocol=DataDirect 5.1 Sybase Wire Protocol
      mssqlserver=MS SQL Server 2008

      [DB2 Wire Protocol]
      Driver=/home2/alesio/odbc64v51/lib/dddb221.so
      Description=DataDirect 5.1 DB2 Wire Protocol
      AddStringToCreateTable=
      AlternateID=
      AlternateServers=
      ApplicationUsingThreads=1
      CatalogSchema=
      CharsetFor65535=0
      #Collection applies to OS/390 and AS/400 only
      Collection=
      ConnectionRetryCount=0
      ConnectionRetryDelay=3
      #Database applies to DB2 UDB only
      Database=<database_name>
      DynamicSections=200
      GrantAuthid=PUBLIC
      GrantExecute=1
      IpAddress=<DB2_server_host>
      LoadBalancing=0
      #Location applies to OS/390 and AS/400 only
      Location=<location_name>
      LogonID=
      Password=
      PackageOwner=
      ReportCodePageConversionErrors=0
      SecurityMechanism=0
      TcpPort=<DB2_server_port>
      UseCurrentSchema=1
      WithHold=1

      [Informix Wire Protocol]
      Driver=/home2/alesio/odbc64v51/lib/ddifcl21.so
      Description=DataDirect 5.1 Informix Wire Protocol
      AlternateServers=
      ApplicationUsingThreads=1
      CancelDetectInterval=0
      ConnectionRetryCount=0
      ConnectionRetryDelay=3
      Database=<database_name>
      HostName=<Informix_host>
      LoadBalancing=0
      LogonID=
      Password=
      PortNumber=<Informix_server_port>
      ReportCodePageConversionErrors=0
      ServerName=<Informix_server>
      TrimBlankFromIndexName=1

      [Oracle Wire Protocol]
      Driver=/home2/alesio/odbc64v51/lib/ddora21.so
      Description=DataDirect 5.1 Oracle Wire Protocol
      AlternateServers=
      ApplicationUsingThreads=1
      ArraySize=60000
      CachedCursorLimit=32
      CachedDescLimit=0
      CatalogIncludesSynonyms=1
      CatalogOptions=0
      ConnectionRetryCount=0
      ConnectionRetryDelay=3
      DefaultLongDataBuffLen=1024
      DescribeAtPrepare=0
      EnableDescribeParam=0
      EnableNcharSupport=0
      EnableScrollableCursors=1
      EnableStaticCursorsForLongData=0
      EnableTimestampWithTimeZone=0
      HostName=<Oracle_server>
      LoadBalancing=0
      LocalTimeZoneOffset=
      LockTimeOut=-1
      LogonID=
      Password=
      PortNumber=1521
      ProcedureRetResults=0
      ReportCodePageConversionErrors=0
      ServiceType=0
      ServiceName=
      SID=<Oracle_SID>
      TimeEscapeMapping=0
      UseCurrentSchema=1

      [Oracle]
      Driver=/home2/alesio/odbc64v51/lib/ddor821.so
      Description=DataDirect 5.1 Oracle
      AlternateServers=
      ApplicationUsingThreads=1
      ArraySize=60000
      CatalogIncludesSynonyms=1
      CatalogOptions=0
      ClientVersion=9iR2
      ConnectionRetryCount=0
      ConnectionRetryDelay=3
      DefaultLongDataBuffLen=1024
      DescribeAtPrepare=0
      EnableDescribeParam=0
      EnableNcharSupport=0
      EnableScrollableCursors=1
      EnableStaticCursorsForLongData=0
      EnableTimestampWithTimeZone=0
      LoadBalancing=0
      LocalTimeZoneOffset=
      LockTimeOut=-1
      LogonID=
      OptimizeLongPerformance=0
      Password=
      ProcedureRetResults=0
      ReportCodePageConversionErrors=0
      ServerName=<Oracle_server>
      TimestampEscapeMapping=0
      UseCurrentSchema=1

      [SQLServer Wire Protocol]
      Driver=/home2/alesio/odbc64v51/lib/ddmsss21.so
      Description=DataDirect 5.1 SQL Server Wire Protocol
      Address=<SQLServer_host, SQLServer_server_port>
      AlternateServers=
      AnsiNPW=Yes
      ConnectionRetryCount=0
      ConnectionRetryDelay=3
      Database=<database_name>
      LoadBalancing=0
      LogonID=
      Password=
      QuotedId=No
      ReportCodePageConversionErrors=0

      [Sybase Wire Protocol]
      Driver=/home2/alesio/odbc64v51/lib/ddase21.so
      Description=DataDirect 5.1 Sybase Wire Protocol
      AlternateServers=
      ApplicationName=
      ApplicationUsingThreads=1
      ArraySize=50
      Charset=
      ConnectionRetryCount=0
      ConnectionRetryDelay=3
      CursorCacheSize=1
      Database=<database_name>
      DefaultLongDataBuffLen=1024
      EnableDescribeParam=0
      EnableQuotedIdentifiers=0
      InitializationString=
      Language=
      LoadBalancing=0
      LogonID=
      NetworkAddress=<Sybase_host, Sybase_server_port>
      OptimizePrepare=1
      PacketSize=0
      Password=
      RaiseErrorPositionBehavior=0
      ReportCodePageConversionErrors=0
      SelectMethod=0
      TruncateTimeTypeFractions=0
      WorkStationID=

      [mssqlserver]
      Driver=/usr/opt/ibm/WSII/odbc/ddl/lib/FRmsss21.so
      Description=MS SQL Server Driver for AIX
      Database=TraceTest
      LogonID=TraceAdmin
      Password=sqltest@123$
      Address=10.10.1.92\MSSQL2008
      QuotedId=YES
      QEWSD=41143
      AnsiNPW=YES

      [ODBC]
      IANAAppCodePage=4
      InstallDir=/usr/opt/ibm/WSII/odbc/ddl
      Trace=0
      TraceDll=/usr/opt/ibm/WSII/odbc/dd/lib/odbctrac.so
      TraceFile=odbctrace.out
      UseCursorLib=0
      $
      ****************************************************************
      $ cat initdg4odbc.ora
      # This is a sample agent init file that contains the HS parameters that are
      # needed for the Database Gateway for ODBC

      #
      # HS init parameters
      #
      #HS_FDS_CONNECT_INFO = dg4odbc
      HS_FDS_CONNECT_INFO=mssqlserver
      #HS_FDS_CONNECT_INFO=10.10.1.92\MSSQL2008:80
      HS_FDS_TRACE_LEVEL = off
      #HS_FDS_SHAREABLE_NAME = /usr/opt/ibm/WSII/odbc/ddl/lib/libodbc.a
      #HS_FDS_SHAREABLE_NAME = /home/orauat/iiv/lib/odbc.so

      #
      # ODBC specific environment variables
      #
      set ODBCINI=/usr/opt/ibm/WSII/odbc/ddl/odbc.ini
      #set ODBCINST=/usr/opt/ibm/WSII/odbc/ddl/odbcinst.so
      #
      # Environment variables required for the non-Oracle system
      #
      #set <envvar>=<value>
      ==============================

      Please advise.

      Best Regards,
        • 1. Re: Connection from Oracle to non-oracle using DG4ODBC
          Mkirtley-Oracle
          Hi,
          In the initdg4odbc.ora you have commented out the HS_FDS_SHAREABLE_NAME. This should point to an ODBC driver manager unless the ODBC driver can handle all the functions.
          What happens if you set the parameter to point to an ODBC driver manager ?
          If you still have problems then setting DG4ODBC tracing at level 255 mau show aht is causing the problem.
          To do this in initdg4odbc.ora set -

          HS_FDS_TRACE_LEVEL = 255

          and run a select from a new SQLPLUS session, then end the session.
          The tarce will be in the gateway $ORACLE_HOME/hs/log directory.

          Coudl you also post the gateway listener.ora file.

          Regards,
          Mike