4 Replies Latest reply: Oct 16, 2012 10:00 AM by tx103108 RSS

    ODBC Gateway on Linux error - connects ok with Sybase SQL Anywhere

    tx103108
      OS: CentOS 5.8 64-bit
      DB: Oracle XE 11gR1 64-bit
      Gateway: Oracle Gateway for ODBC 64-bit
      Database and gateway reside on same Linux Server.
      Connecting to remote Sybase SQL Anywhere 10 server on WindowsXP.
      Using SQL Anywhere 11 odbc driver and unixODBC driver manager on Linux server.
      isql tool connects without any problems.
      One LISTENER, service for db and gateway on same port.
      ===================================
      Via SQL*Plus, the following error occurs....

      SQL> select * from dual@dblink;
      select * from dual@dblink
      *
      ERROR at line 1:
      ORA-28513: internal error in heterogeneous remote agent
      ORA-02063: preceding line from dblink

      ===================================
      sqlnet.ora
      # sqlnet.ora Network Configuration File: /u01/app/oracle/product/11.2.0/xe/network/admin/sqlnet.ora
      # Generated by Oracle configuration tools.

      NAMES.DIRECTORY_PATH=(TNSNAMES)

      ADR_BASE = /u01/app/oracle/product/11.2.0/xe
      ==================================
      tnsnames.ora
      # tnsnames.ora Network Configuration File:

      XE =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = mysystem)(PORT = 1521))
      (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
      )
      )

      dblink =
      (DESCRIPTION =
      (ADDRESS =
      (PROTOCOL = TCP)(HOST = mysystem)(PORT = 1521)
      )
      (CONNECT_DATA =
      (SID = dblink))
      (HS=OK))

      EXTPROC_CONNECTION_DATA =
      (DESCRIPTION =
      (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
      )
      (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
      )
      )

      =======================
      listener.ora
      # listener.ora Network Configuration File:

      SID_LIST_LISTENER =
      (SID_LIST =
      (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
      (PROGRAM = extproc)
      )
      (SID_DESC =
      (SID_NAME = dblink)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/gtw)
      (PROGRAM = dg4odbc)
      (ENVS=LD_LIBRARY_PATH=/opt/sqlanywhere11/lib64:/u01/app/oracle/product/11.2.0/gtw/lib)
      )
      )

      LISTENER =
      (DESCRIPTION_LIST =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
      (ADDRESS = (PROTOCOL = TCP)(HOST = mysystem)(PORT = 1521))
      )
      )

      DEFAULT_SERVICE_LISTENER = (XE)

      ========================
      initdblink.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=dblink
      HS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.so
      HS_FDS_TRACE_LEVEL=255

      HS_LANGUAGE=american_america.we8iso8859p1
      HS_NLS_NCHAR=UTF-8

      #
      # ODBC specific environment variables
      #
      set ODBCINI=/etc/odbc.ini

      ========================


      Oracle Corporation --- MONDAY OCT 15 2012 17:23:39.915


      Heterogeneous Agent Release
      11.2.0.1.0




      Oracle Corporation --- MONDAY OCT 15 2012 17:23:39.914

      Version 11.2.0.1.0

      Entered hgogprd
      HOSGIP for "HS_FDS_TRACE_LEVEL" returned "255"
      Entered hgosdip
      setting HS_OPEN_CURSORS to default of 50
      setting HS_FDS_RECOVERY_ACCOUNT to default of "RECOVER"
      setting HS_FDS_RECOVERY_PWD to default value
      setting HS_FDS_TRANSACTION_LOG to default of HS_TRANSACTION_LOG
      setting HS_IDLE_TIMEOUT to default of 0
      setting HS_FDS_TRANSACTION_ISOLATION to default of "READ_COMMITTED"
      HOSGIP returned value of "UTF-8" for HS_NLS_NCHAR
      setting HS_FDS_TIMESTAMP_MAPPING to default of "DATE"
      setting HS_FDS_DATE_MAPPING to default of "DATE"
      setting HS_RPC_FETCH_REBLOCKING to default of "ON"
      setting HS_FDS_FETCH_ROWS to default of "100"
      setting HS_FDS_RESULTSET_SUPPORT to default of "FALSE"
      setting HS_FDS_RSET_RETURN_ROWCOUNT to default of "FALSE"
      setting HS_FDS_PROC_IS_FUNC to default of "FALSE"
      setting HS_FDS_CHARACTER_SEMANTICS to default of "FALSE"
      setting HS_FDS_MAP_NCHAR to default of "TRUE"
      setting HS_NLS_DATE_FORMAT to default of "YYYY-MM-DD HH24:MI:SS"
      setting HS_FDS_REPORT_REAL_AS_DOUBLE to default of "FALSE"
      setting HS_LONG_PIECE_TRANSFER_SIZE to default of "65536"
      setting HS_SQL_HANDLE_STMT_REUSE to default of "FALSE"
      setting HS_FDS_QUERY_DRIVER to default of "TRUE"
      setting HS_FDS_SUPPORT_STATISTICS to default of "FALSE"
      Parameter HS_FDS_QUOTE_IDENTIFIER is not set
      setting HS_KEEP_REMOTE_COLUMN_SIZE to default of "OFF"
      setting HS_FDS_GRAPHIC_TO_MBCS to default of "FALSE"
      setting HS_FDS_MBCS_TO_GRAPHIC to default of "FALSE"
      Default value of 64 assumed for HS_FDS_SQLLEN_INTERPRETATION
      setting HS_CALL_NAME_ISP to "gtw$:SQLTables;gtw$:SQLColumns;gtw$:SQLPrimaryKeys;gtw$:SQLForeignKeys;gtw$:SQLProcedures;gtw$:SQLStatistics;gtw$:SQLGetInfo"
      setting HS_FDS_DELAYED_OPEN to default of "TRUE"
      setting HS_FDS_WORKAROUNDS to default of "0"
      Exiting hgosdip, rc=0
      ORACLE_SID is "dblink"
      Product-Info:
      Port Rls/Upd:1/0 PrdStat:0
      Agent:Oracle Database Gateway for ODBC
      Facility:hsa
      Class:ODBC, ClassVsn:11.2.0.1.0_0008, Instance:dblink
      Exiting hgogprd, rc=0
      hostmstr: 0: HOA After hoagprd
      Invalid HS_NLS_NCHAR value UTF-8 specified
      Setting HS_NLS_NCHAR to Server Default 2000
      Invalid HS_NLS_NCHAR value UTF-8 specified
      Setting HS_NLS_NCHAR to Server Default 2000
      hostmstr: 0: HOA Before hoainit
      Entered hgoinit
      HOCXU_COMP_CSET=1
      HOCXU_DRV_CSET=31
      HOCXU_DRV_NCHAR=2000
      HOCXU_DB_CSET=873
      HOCXU_SEM_VER=112000
      Exiting hgoinit, rc=28513
      hostmstr: 0: HOA After hoainit
      RPC Calling nscontrol(0), rc=0
      hostmstr: 0: RPC Before Exit Agent
      hostmstr: 0: HOA Before hoaexit
      Entered hgoexit
      HS Gateway: NULL connection context at exit
      Exiting hgoexit, rc=0 with error ptr FILE:hgoexit.c LINE:108 FUNCTION:hgoexit() ID:Connection context
      hostmstr: 0: HOA After hoaexit
      hostmstr: 0: RPC After Exit Agent
      =============================

      Also, does anyone know how to determine the location (full path) of the unixODBC driver manager ?
      Please assist if you can...thx.

      Edited by: user601798 on Oct 15, 2012 3:37 PM
        • 1. Re: ODBC Gateway on Linux error - connects ok with Sybase SQL Anywhere
          Kgronau-Oracle
          Let me start with the unixODBC driver:
          There's no generic location as it depends which package you've used to install the UnixODBC driver manager or how you compiled it from scratch. I commonly prefer to compile it from scratch as this offers me to install it to any location I like.

          If you're not sure where it is located, you could perform a search on your machine looking for libodbc.so. Then cross check it with "file <location>/libodbc.so" to see the lib is a 64bit library.
          So let's assume in the /usr/lib64 you found the libodbc.so library, then the setting
          HS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.so
          is correct. Else you need to adopt the parameter.

          Next step is to check out which unixODBC version is installed. That's commonly done by calling "odbcinst -j".

          Looking now ta the gateway init file there's a parameter HS_NLS_NCHAR set to UTF-8. UnixODBC is commonly compiled with UCS2 support, so please change it to HS_NLS_NCHAR=UCS2


          Could you also please post the content of the odbc.ini file "more /etc/odbc.ini"?

          Thanks,
          Klaus
          • 2. Re: ODBC Gateway on Linux error - connects ok with Sybase SQL Anywhere
            tx103108
            Changed the initdblink.ora file from UTF-8 to UCS2:

            #
            # HS init parameters
            #
            HS_FDS_CONNECT_INFO=txeis
            HS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.so
            HS_FDS_TRACE_LEVEL=255

            HS_LANGUAGE=american_america.we8iso8859p1
            HS_NLS_NCHAR=UCS2

            and I get a connection. But now I only get the column headings back with no values except (it seems) to columns with a number value??

            odbc.ini file:
            =======
            [ODBC Data Sources]
            dblink = Sybase

            [dblink]
            Driver = SYBASE
            Description = Sybase
            CommLinks=tcpip(MyIP=10.10.1.100;IP=10.10.1.200:2638;)
            ServerName=dblink
            DBN=mydatabase

            [Default]
            Driver = /opt/sqlanywhere11/lib64/libdbodbc11.so

            Please advise...we are almost there ...

            Edited by: user601798 on Oct 16, 2012 7:06 AM
            • 3. Re: ODBC Gateway on Linux error - connects ok with Sybase SQL Anywhere
              Mkirtley-Oracle
              Hi,
              What is the Sybase create table statement fo rthe table giving the problem ? We need to compare the datatypes with how they are handled by the ODBC driver and how those datatypes are handled by DG4ODBC.
              The following note available in My Oracle Supprot has some help on this -

              How to determine why certain foreign datatypes are not displayed within a select using DG4ODBC or HSODBC (Doc ID 252548.1)

              It would be useful to see -

              - Sybase create staement
              - an ODBC trace
              - a DG4ODBC trace

              from a select giving problems.

              Regards,
              Mike
              • 4. Re: ODBC Gateway on Linux error - connects ok with Sybase SQL Anywhere
                tx103108
                Actually, even though the data does not always appear correctly via SQL*Plus, it does come up correctly in APEX via the gateway. (go figure?!).
                So that is really all we need it to do.
                So I'll go with this setup and address any further issues as they arise.
                Thanks for the help.