6 Replies Latest reply: Mar 13, 2008 2:15 AM by Kgronau-Oracle RSS

    All queries using database link to Sql Server hang!

    506350
      Hello,

      Here is my problem. Recently we have migrated our database from 9iR2 to 10gR2. We have a working connection to MS SQL Server database using tg4msql. However after my new configuration, all my queries using the database link hang. Moreover, I cannot stop the query unless I kill the tg4msql.exe process from the Task Manager.

      Here is my configuration:

      tg4msql\admin\initMYDB.ora
      -----------------------------------------
      HS_FDS_CONNECT_INFO="SERVER=MYWIN2003SERVER;DATABASE=SALES"
      HS_FDS_TRACE_LEVEL=ON
      HS_FDS_RECOVERY_ACCOUNT=RECOVER
      HS_FDS_RECOVERY_PWD=RECOVER


      NETWORK\ADMIN\listener.ora
      --------------------------------------------
      SID_LIST_LISTENER =
      (SID_LIST =
           (SID_DESC=
                (SID_NAME = MYDB)
                (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
                (PROGRAM = tg4msql)
           )
      )

      LISTENER =
      (DESCRIPTION_LIST =
      (DESCRIPTION =
                (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
                (ADDRESS = (PROTOCOL = TCP)(HOST = mywinxp)(PORT = 1521))
      )
      )


      NETWORK\ADMIN\tnsnames.ora
      ------------------------------------------------
      MYDB =
           (DESCRIPTION=
                (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = mywinxp) (PORT = 1521)))
                (CONNECT_DATA = (SID = MYDB))
                (HS=OK)
           )


      And the code to create the database link is:
      CREATE DATABASE LINK mydb CONNECT TO myuser IDENTIFIED BY mypass USING 'MYDB'


      The tnsping to the alias is successful:
      -----------------------------------------------------------
      Used TNSNAMES adapter to resolve the alias
      Attempting to contact (DESCRIPTION= (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
      (HOST = mywinxp) (PORT = 1521))) (CONNECT_DATA = (SID = mydb)) (HS=OK))
      OK (0 msec)


      Any help will be appreciated.

      Thanks in advance,
      Beroetz
        • 1. Re: All queries using database link to Sql Server hang!
          ebangma-Oracle
          Beroetz,

          As a start, activate a gateway debug trace by setting HS_FDS_TRACE_LEVEL=DEBUG in the initMYDB.ora file. Start a new SQL*Plus session and re-execute the query. The trace file will be by default generated at %ORACLE_HOME%\tg4msql\trace

          Maybe it shows at what point the gateway hangs or waits.

          Ed
          • 2. Re: All queries using database link to Sql Server hang!
            400726
            ebangma,
            I have the same problem. I am using 11g on Windows to connect to SQLServer 2000 using HS4ODBC.

            After running the query, it simply hangs. However, I see the connection on the SQLServer database end but the result is not relayed back to the Oracle database.

            I appreciate if you could guide me in this issue.

            Thanks!


            Here is the output from HS Trace in DEBUG mode:



            Oracle Corporation --- TUESDAY MAR 11 2008 14:25:17.315

            Heterogeneous Agent Release
            11.1.0.6.0




            Oracle Corporation --- TUESDAY MAR 11 2008 14:25:17.315

            Version 11.1.0.6.0

            Entered hgogprd
            HOSGIP for "HS_FDS_TRACE_LEVEL" returned "DEBUG"
            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_FDS_TRANSACTION_ISOLATION to default of "READ_COMMITTED"
            setting HS_NLS_NCHAR to default of "AL16UTF16"
            setting HS_FDS_TIMESTAMP_AS_DATE to default of "TRUE"
            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_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"
            setting HS_CALL_NAME_ISP to "gtw$:SQLTables;gtw$:SQLColumns;gtw$:SQLPrimaryKeys;gtw$:SQLForeignKeys;gtw$:SQLProcedures;gtw$:SQLStatistics"
            Exiting hgosdip, rc=0
            ORACLE_SID is "dg4odbc"
            Product-Info:
            Port Rls/Upd:6/0 PrdStat:0
            Agent:Oracle Database Gateway for ODBC
            Facility:hsa
            Class:ODBC, ClassVsn:11.1.0.6.0_0006, Instance:dg4odbc
            Exiting hgogprd, rc=0
            Entered hgoinit
            HOCXU_COMP_CSET=1
            HOCXU_DRV_CSET=178
            HOCXU_DRV_NCHAR=2000
            HOCXU_DB_CSET=178
            HOCXU_SEM_VER=110000
            Entered hgolofn at 2008/03/11-14:25:18
            Exiting hgolofn, rc=0 at 2008/03/11-14:25:18
            HOSGIP for "HS_OPEN_CURSORS" returned "50"
            HOSGIP for "HS_FDS_FETCH_ROWS" returned "100"
            HOSGIP for "HS_LONG_PIECE_TRANSFER_SIZE" returned "65536"
            HOSGIP for "HS_NLS_NUMERIC_CHARACTER" returned ".,"
            Exiting hgoinit, rc=0 at 2008/03/11-14:25:18
            Entered hgolgon at 2008/03/11-14:25:18
            reco:0, name:sqlserver_dsn, tflag:0
            Entered hgosuec at 2008/03/11-14:25:18
            Exiting hgosuec, rc=0 at 2008/03/11-14:25:18
            HOSGIP for "HS_FDS_RECOVERY_ACCOUNT" returned "RECOVER"
            HOSGIP for "HS_FDS_TRANSACTION_LOG" returned ""HS_TRANSACTION_LOG""
            HOSGIP for "HS_FDS_TIMESTAMP_AS_DATE" returned "TRUE"
            HOSGIP for "HS_FDS_CHARACTER_SEMANTICS" returned "FALSE"
            HOSGIP for "HS_FDS_MAP_NCHAR" returned "TRUE
            HOSGIP for "HS_FDS_RESULT_SET_SUPPORT" returned "FALSE"
            HOSGIP for "HS_FDS_PROC_IS_FUNC" returned "FALSE"
            HOSGIP for "HS_FDS_REPORT_REAL_AS_DOUBLE" returned "FALSE"
            using sqlserver_dsn as default value for "HS_FDS_DEFAULT_OWNER"
            HOSGIP for "HS_SQL_HANDLE_STMT_REUSE" returned "FALSE"
            Entered hgocont at 2008/03/11-14:25:19
            HS_FDS_CONNECT_INFO = "sqlserver_DSN"
            RC=-1 from HOSGIP for "HS_FDS_CONNECT_STRING"
            Entered hgogenconstr at 2008/03/11-14:25:19
            dsn:sqlserver_DSN, name:sqlserver_dsn
            optn:
            ##>Connect Parameters (len=48)<##
            ## DSN=sqlserver_DSN;
            #! UID=sqlserver_dsn;
            #! PWD=*
            Exiting hgogenconstr, rc=0 at 2008/03/11-14:25:19
            DriverName:SQLSRV32.DLL, DriverVer:03.85.1117
            DBMS Name:Microsoft SQL Server, DBMS Version:09.00.1399
            Exiting hgocont, rc=0 at 2008/03/11-14:25:20
            SQLGetInfo returns Y for SQL_CATALOG_NAME
            SQLGetInfo returns 128 for SQL_MAX_CATALOG_NAME_LEN
            Exiting hgolgon, rc=0 at 2008/03/11-14:25:20
            Entered hgoulcp at 2008/03/11-14:25:21
            Entered hgowlst at 2008/03/11-14:25:21
            Exiting hgowlst, rc=0 at 2008/03/11-14:25:21
            SQLGetInfo returns " for SQL_IDENTIFIER_QUOTE_CHAR
            SQLGetInfo returns Y for SQL_COLUMN_ALIAS
            Exiting hgoulcp, rc=0 at 2008/03/11-14:25:22
            Entered hgoulat 2008/03/11-14:25:22
            Exiting hgoul rc=0 at 2008/03/11-14:25:22
            Entered hgobegn at 2008/03/11-14:25:22
            tflag:0 , initial:1
            hoi:0x12f074, ttid (len 28) is ...
            00: 47474E45 524F572E 612E444C 31646362 [ENGG.WORLD.abcd1]
            10: 2E363535 2E312E33 31343133 [556.3.1.3141]
            tbid (len 25) is ...
            00: 47474E45 524F572E 335B444C 332E312E [ENGG.WORLD[3.1.3]
            10: 5D313431 342E315B 5D [141][1.4]]
            Exiting hgobegn, rc=0 at 2008/03/11-14:25:23
            Entered hgob at 2008/03/11-14:25:23
            count:1
            table: emp
            The hoada for table emp follows...
            hgob, line 577: NO hoada to print
            Exiting hgob, rc=0 at 2008/03/11-14:25:26
            Entered hgopars, cursor id 1 at 2008/03/11-14:25:26
            type:0
            SQL text from hgopars, id=1, len=19 ...
            00: 454C4553 2A205443 4F524620 6522204D [SELECT * FROM "e]
            10: 6D7022 [mp"]
            Exiting hgopars, rc=0 at 2008/03/11-14:25:27
            Entered hgoopen, cursor id 1 at 2008/03/11-14:25:27
            hgoopen, line 83: NO hoada to print
            Exiting hgoopen, rc=0 at 2008/03/11-14:25:27
            Entered hgodscr, cursor id 1 at 2008/03/11-14:25:27
            Entered hgopcda at 2008/03/11-14:25:27
            Column:1(ID): pe:6 (FLOAT), prc/scl:53/0, nullbl:0, octet:0, sign:1, radix:2
            Exiting hgopcda, rc=0 at 2008/03/11-14:25:27
            Entered hgopcda at 2008/03/11-14:25:27
            Column:2(CabRowLetters): pe:-9 (WVARCHAR), prc/scl:255/0, nullbl:1, octet:510, sign:1, radix:2
            Exiting hgopcda, rc=0 at 2008/03/11-14:25:28
            Entered hgopcda at 2008/03/11-14:25:28
            Column:3(CabinetNumber): pe:6 (FLOAT), prc/scl:53/0, nullbl:1, octet:510, sign:1, radix:2
            Exiting hgopcda, rc=0 at 2008/03/11-14:25:28
            Entered hgopcda at 2008/03/11-14:25:28
            Column:4(Panel1): pe:-9 (WVARCHAR), prc/scl:255/0, nullbl:1, octet:510, sign:1, radix:2
            Exiting hgopcda, rc=0 at 2008/03/11-14:25:28
            Entered hgopcda at 2008/03/11-14:25:28
            Column:5(Circuit1): pe:6 (FLOAT), prc/scl:53/0, nullbl:1, octet:510, sign:1, radix:2
            Exiting hgopcda, rc=0 at 2008/03/11-14:25:28
            Entered hgopcda at 2008/03/11-14:25:29
            Column:6(Panel2): pe:-9 (WVARCHAR), prc/scl:255/0, nullbl:1, octet:510, sign:1, radix:2
            Exiting hgopcda, rc=0 at 2008/03/11-14:25:29
            Entered hgopcda at 2008/03/11-14:25:29
            Column:7(Circuit2): pe:6 (FLOAT), prc/scl:53/0, nullbl:1, octet:510, sign:1, radix:2
            Exiting hgopcda, rc=0 at 2008/03/11-14:25:29
            Entered hgopcda at 2008/03/11-14:25:29
            Column:8(Panel3): pe:-9 (WVARCHAR), prc/scl:255/0, nullbl:1, octet:510, sign:1, radix:2
            Exiting hgopcda, rc=0 at 2008/03/11-14:25:30
            Entered hgopcda at 2008/03/11-14:25:30
            Column:9(Circuit3): pe:6 (FLOAT), prc/scl:53/0, nullbl:1, octet:510, sign:1, radix:2
            Exiting hgopcda, rc=0 at 2008/03/11-14:25:30
            Entered hgopcda at 2008/03/11-14:25:30
            Column:10(Panel4): pe:-9 (WVARCHAR), prc/scl:255/0, nullbl:1, octet:510, sign:1, radix:2
            Exiting hgopcda, rc=0 at 2008/03/11-14:25:30
            Entered hgopcda at 2008/03/11-14:25:30
            Column:11(Circuit4): pe:6 (FLOAT), prc/scl:53/0, nullbl:1, octet:510, sign:1, radix:2
            Exiting hgopcda, rc=0 at 2008/03/11-14:25:30
            hgodscr, line 521: Printing hoada @ 01ECA540
            MAX:11, ACTUAL:11, BRC:100, WHT=5
            DTY NULL-OK LEN MAXBUFLEN PR/SC CST IND MOD NAME
            8 DOUBLE N 8 8 53/ 0 0 0 0 ID
            12 VARCHAR Y 510 510 128/255 2000 0 40 CabRowLetters
            8 DOUBLE Y 8 8 53/ 0 0 0 0 CabinetNumber
            12 VARCHAR Y 510 510 128/255 2000 0 40 Panel1
            8 DOUBLE Y 8 8 53/ 0 0 0 0 Circuit1
            12 VARCHAR Y 510 510 128/255 2000 0 40 Panel2
            8 DOUBLE Y 8 8 53/ 0 0 0 0 Circuit2
            12 VARCHAR Y 510 510 128/255 2000 0 40 Panel3
            8 DOUBLE Y 8 8 53/ 0 0 0 0 Circuit3
            12 VARCHAR Y 510 510 128/255 2000 0 40 Panel4
            8 DOUBLE Y 8 8 53/ 0 0 0 0 Circuit4
            Exiting hgodscr, rc=0 at 2008/03/11-14:25:31
            Entered hgotcis at 2008/03/11-14:25:31
            Calling SQLStatistics for emp
            • 3. Re: All queries using database link to Sql Server hang!
              ebangma-Oracle
              As you see, the last statement in the DG4ODBC DEBUG trace is a call to get the statistics:
              "Calling SQLStatistics for emp"

              This is a known issue:
              Bug 6733545 - ANY SQL QUERY TO SQL SERVER USING DG4ODBC HANGS

              The solution is quite easy; just add the following parameter to your init<dg4odbc>.ora initialization file:
              HS_FDS_SUPPORT_STATISTICS=FALSE

              This will turn off the 'gather statistics' capability.

              Start a new SQL*Plus session and select using the DG4ODBC database link.

              Regards,
              Ed
              • 4. Re: All queries using database link to Sql Server hang!
                Kgronau-Oracle
                you are using DG4ODBC ... it is different from the first thread.

                The answer to your issue is the SQLStatistics of the SQL Server are causing a hung.
                Please set in your ini<dg4odbc>.ora located in the <ORACLE_HOME>/HS/ADMIN directory:
                HS_FDS_SUPPORT_STATISTICS=FALSE
                • 5. Re: All queries using database link to Sql Server hang!
                  400726
                  It helped me a little bit. I am now getting data but it is unreadable.

                  My Oracle setttings are :

                  From Oracle's Database_Properties :

                  NLS_CHARACTERSET WE8MSWIN1252 Character set

                  Sql Server Collation: latin1_general_cp1_cl_as


                  Any suggestions would be appreciated.

                  Thanks,
                  • 6. Re: All queries using database link to Sql Server hang!
                    Kgronau-Oracle
                    try setting HS_LANGUAGE.
                    For details see the manual. I would start with HS_LANGUAGE=american_america.we8iso8859P1 ad Latin-1 matches 8859P1 character set.