6 Replies Latest reply: Aug 14, 2013 9:30 AM by 1033010 RSS

    Multiple Instance of HSODBC.EXE

    430267
      Hi,
      I'm using Oracle 8.1.7.4 (W2K) with heterogeneous services to connect via dblink to DB2/400.

      I can read successfully several tables from AS400; but a multiple instance of 'hsodbc.exe' ( i.e. one task for one interrogation) are created on task manager.

      If I try to end a hsodbc I can't.

      After several interrogation, I can't use again hsodbc and the system returns the following error:

      "
      ORA-28500 [Generic Connectivity Using ODBC] a dynamic link library (DLL) initialization routine failed; at FIND_IMAGE_SYMBOL

      cannot connect to shareable nvdbodbc. Using dummy function
      Initialization function ODBC not found.

      ORA-02063
      "

      Is there any particular configuration of heterogeneous services that avoid a multiple instance of 'hsodbc.exe' ?

      This is a serious problem for us.

      Regards
      Andrea
        • 1. Re: Multiple Instance of HSODBC.EXE
          309833
          Andrea,
          this normally indicates, that an established session is not correctly ended.

          In general you need to close database links after you do not need them anymore.
          A SQL*Plus session for example querying from the remote database will normally close the db link automatically while ending the SQL*Plus session.

          Sometimes it could happen, that you end SQLÃœPlus during long runnning queries; in this situaotion it might happen, that hsodbc will stay in the mmory. To get rid of those hsodbc processes, you may use pskill from www.sysinternals.com.

          If the hsodbc processes are spawned by frequentyl running job systems, you need to close the database link manually afterwards.
          alter session close database link <db link name>;
          • 2. Re: Multiple Instance of HSODBC.EXE
            430267
            Thanks for the trick, pskill is very usefull...

            Can you help me ? I try to use this command:
            "
            SELECT * FROM TableName@ExternalDB ;

            ALTER SESSION CLOSE DATABASE LINK ExternalDB;
            "

            The system returns this error

            ORA-02080 DBLink in use

            if I execute only the last command the system returns this

            ORA-02081 DBLink is not open
            • 3. Re: Multiple Instance of HSODBC.EXE
              309833
              Simply do a commit or rollback before closing the db link.
              The cause is: hsodbc disables the AUTOCOMMIT of ODBC drivers; so you need to MANUALLY commit/rollback your work;even if you only select records within the foreign database.
              • 4. Re: Multiple Instance of HSODBC.EXE
                430267
                OK, if iI use
                "select * from TableName@externalDB;
                commit;
                alter session close database link externalDB;
                "
                The system does't return any error.

                But hsodbc.exe still alive.
                • 5. Re: Multiple Instance of HSODBC.EXE
                  309833
                  Sure that this dedicated hsodbc.exe process you see here belongs to your SQL*Plus session?

                  You can only close the hsodbc.exe process belonging to your current session. If for example another user or another SQL*Plus session has opened also an hsodbc process, that you close ONLY the hsodbc process belonging to your session.

                  So make sure no hsodbc.exe exists, then select from the remote db, wait until the select finished, commit your work and close the db link.
                  The hsodbc.exe process will disappear.


                  The only way I could imagine that hsodbc is not removed is while you cancel the query result in sql*plus....
                  So wait until the query has finished.
                  • 6. Re: Multiple Instance of HSODBC.EXE
                    1033010

                    Hi,

                    I have the same problem.

                    I'm using  Oracle Database 10g Release 10.2.0.4.0 (Windows 2003 Server R2 EE SP2) with heterogeneous services to connect via dblink to SQL Server.

                     

                    I use this commands but I have multiple instances of HSODBC.EXE. Whenever I make a select, after I commit the transaction and close dblink I have a new instance of this process. It will never disappear.