3 Replies Latest reply on May 4, 2011 11:24 AM by 858320

    Connecting Oracle 10g and Informix through DG4IFMX

    858320
      Dear Colleagues,

      I am an Oracle newbie and i am trying to connect my Oracle 10g to an Informix Server v.11.
      I did everything as it is said in the manual (although many things are not well expained or even not included e.g. the informix database must have a transaction log...) and still i have some issues with the connecting.

      I am using the oracle database gateway to informix (dg4ifmx).

      First of all when i run select * from DUAL@dblink i dont get an error at all, i just get a result X, which as i understood means that everything is well configured in tsnnames, listener and initdg4ifmx.ora and in the database link.

      But when i write an existing table name like select * from enc_grid@dblink i get this error:


      ORA-00942: table or view does not exist
      [Oracle][ODBC Informix Wire Protocol driver][Informix]The specified table (ENC_GRID) is not in the database. {42S02,NativeErr = -206}
      ORA-02063: preceding 2 lines from LINK_INFORMIX

      The table exists for sure!!

      Then i opened the trace and i noticed some strange thing. There is an error occuring : error ptr FILE:hgocont.c LINE:2686 ID:SQLDriverConnect
      -----------------------------------------------------------------------------------------------------
      This is the entire trace:


      Oracle Corporation --- WEDNESDAY MAY 04 2011 12:34:55.062


      Heterogeneous Agent Release
      11.2.0.1.0


      Oracle Corporation --- WEDNESDAY MAY 04 2011 12:34:55.062

      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
      HOSGIP returned value of "RECOVER" for HS_FDS_RECOVERY_ACCOUNT
      HOSGIP returned a value for HS_FDS_RECOVERY_PWD
      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"
      setting HS_NLS_NCHAR to default of "UCS2"
      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"
      HOSGIP returned value of "1000" for HS_FDS_FETCH_ROWS
      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 "FALSE"
      setting HS_FDS_SUPPORT_STATISTICS to default of "TRUE"
      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"
      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 "dg4ifmx"
      Product-Info:
      Port Rls/Upd:1/0 PrdStat:0
      Agent:Oracle Database Gateway for INFORMIX
      Facility:hsa
      Class:IFMX, ClassVsn:11.2.0.1.0_0008, Instance:dg4ifmx
      Exiting hgogprd, rc=0
      hostmstr: 2056146944:      HOA After hoagprd
      hostmstr: 2056146944:      HOA Before hoainit
      Entered hgoinit
      HOCXU_COMP_CSET=1
      HOCXU_DRV_CSET=171
      HOCXU_DRV_NCHAR=1000
      HOCXU_DB_CSET=171
      HOCXU_SEM_VER=102000
      Entered hgolofn at 2011/05/04-12:34:55
      RC=-1 from HOSGIP for "PATH"
      PATH from environment is "C:\OraHome_1\bin;C:\oracle\product\10.2.0\db_1\bin;C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;C:\WINDOWS\system32\WindowsPowerShell\v1.0;C:\Program Files\Microsoft SQL Server\80\Tools\Binn\;C:\Program Files\Microsoft SQL Server\90\Tools\binn\;C:\Program Files\Microsoft SQL Server\90\DTS\Binn\;C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\;C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\;C:\Informix\Client-SDK\bin;C:\Program Files\ibm\gsk7\bin;C:\Program Files\ibm\gsk7\lib"
      Setting PATH to "C:\OraHome_1\dg4ifmx\driver\lib;C:\OraHome_1\bin;C:\oracle\product\10.2.0\db_1\bin;C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;C:\WINDOWS\system32\WindowsPowerShell\v1.0;C:\Program Files\Microsoft SQL Server\80\Tools\Binn\;C:\Program Files\Microsoft SQL Server\90\Tools\binn\;C:\Program Files\Microsoft SQL Server\90\DTS\Binn\;C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\;C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\;C:\Informix\Client-SDK\bin;C:\Program Files\ibm\gsk7\bin;C:\Program Files\ibm\gsk7\lib"
      Exiting hgolofn, rc=0 at 2011/05/04-12:34:55
      HOSGIP for "HS_OPEN_CURSORS" returned "50"
      HOSGIP for "HS_FDS_FETCH_ROWS" returned "1000"
      HOSGIP for "HS_LONG_PIECE_TRANSFER_SIZE" returned "65536"
      HOSGIP for "HS_NLS_NUMERIC_CHARACTER" returned ".,"
      HOSGIP for "HS_KEEP_REMOTE_COLUMN_SIZE" returned "OFF"
      HOSGIP for "HS_FDS_DELAYED_OPEN" returned "TRUE"
      HOSGIP for "HS_FDS_WORKAROUNDS" returned "0"
      HOSGIP for "HS_FDS_MBCS_TO_GRAPHIC" returned "FALSE"
      HOSGIP for "HS_FDS_GRAPHIC_TO_MBCS" returned "FALSE"
      treat_SQLLEN_as_compiled = 1
      Exiting hgoinit, rc=0 at 2011/05/04-12:34:55
      hostmstr: 2056146944:      HOA After hoainit
      hostmstr: 2056146944:      HOA Before hoalgon
      Entered hgolgon at 2011/05/04-12:34:55
      reco:0, name:informix, tflag:0
      Entered hgosuec at 2011/05/04-12:34:55
      uencoding=UTF16
      Entered shgosuec at 2011/05/04-12:34:55
      Exiting shgosuec, rc=0 at 2011/05/04-12:34:55
      shgosuec() returned rc=0
      Exiting hgosuec, rc=0 at 2011/05/04-12:34:55
      HOSGIP for "HS_FDS_RECOVERY_ACCOUNT" returned "RECOVER"
      HOSGIP for "HS_FDS_TRANSACTION_LOG" returned "HS_TRANSACTION_LOG"
      HOSGIP for "HS_FDS_TIMESTAMP_MAPPING" returned "DATE"
      HOSGIP for "HS_FDS_DATE_MAPPING" returned "DATE"
      HOSGIP for "HS_FDS_CHARACTER_SEMANTICS" returned "FALSE"
      HOSGIP for "HS_FDS_MAP_NCHAR" returned "TRUE"
      HOSGIP for "HS_FDS_RESULTSET_SUPPORT" returned "FALSE"
      HOSGIP for "HS_FDS_RSET_RETURN_ROWCOUNT" returned "FALSE"
      HOSGIP for "HS_FDS_PROC_IS_FUNC" returned "FALSE"
      HOSGIP for "HS_FDS_REPORT_REAL_AS_DOUBLE" returned "FALSE"
      using informix as default value for "HS_FDS_DEFAULT_OWNER"
      HOSGIP for "HS_SQL_HANDLE_STMT_REUSE" returned "FALSE"
      Entered hgocont at 2011/05/04-12:34:55
      HS_FDS_CONNECT_INFO = "[10.1.10.6]:9088/idsenclxsoc/enc3"
      RC=-1 from HOSGIP for "HS_FDS_CONNECT_STRING"
      Entered hgogenconstr at 2011/05/04-12:34:55
      dsn:[10.1.10.6]:9088/idsenclxsoc/enc3, name:informix
      optn:
      Entered hgocip at 2011/05/04-12:34:55
      dsn:[10.1.10.6]:9088/idsenclxsoc/enc3
      Exiting hgocip, rc=0 at 2011/05/04-12:34:55
      Entered shgogohn at 2011/05/04-12:34:55
      ohn is 'OUIHome1'
      Exiting shgogohn, rc=0 at 2011/05/04-12:34:55
      Entered hgocont_OracleCsidToIANA at 2011/05/04-12:34:55
      Returning 2251
      Exiting hgocont_OracleCsidToIANA at 2011/05/04-12:34:55
      ##>Connect Parameters (len=317)<##
      ## DRIVER=Oracle 11g dg4ifmx-OUIHome1;
      ## HostName=10.1.10.6;
      ## PortNumber=9088;
      ## ServerName=idsenclxsoc;
      ## Database=enc3;
      #! UID=informix;
      #! PWD=*
      ## ApplicationUsingThreads=1;
      ## CancelDetectInterval=0;
      ## TrimBlankFromIndexName=1;
      ## IANAAppCodePage=2251;
      ## OctetSizeCalculation=1;
      ## ArraySize=1000;
      ## UseDelimitedIdentifier=1;
      ## TruncateOversizedColumnValues=0;
      Exiting hgogenconstr, rc=0 at 2011/05/04-12:34:55
      Entered hgopoer at 2011/05/04-12:34:55
      hgopoer, line 233: got native error 0 and sqlstate 01S00; message follows...
      [Oracle][ODBC Informix Wire Protocol driver]Invalid attribute in connection string: ArraySize. {01S00}
      Exiting hgopoer, rc=0 at 2011/05/04-12:34:55
      hgocont, line 2686: calling SqlDriverConnect got sqlstate 01S00
      Entered hgolosf at 2011/05/04-12:34:55
      ODBC Function-Available-Array 0xFFFE 0x01FF 0xFF00 0xFBFF 0x03FF 0x0000
      0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
      0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
      0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
      0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
      0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
      0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
      0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
      0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
      0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
      0x0000 0x0000 0xFE00 0x3F5F
      Exiting hgolosf, rc=0 at 2011/05/04-12:34:55
      DriverName:HGifcl23.dll, DriverVer:06.00.0053 (b0050, U0047)
      DBMS Name:Informix, DBMS Version:09.52.UC3 .0000
      Exiting hgocont, rc=0 at 2011/05/04-12:34:55 with error ptr FILE:hgocont.c LINE:2686 ID:SQLDriverConnect
      SQLGetInfo returns Y for SQL_CATALOG_NAME
      SQLGetInfo returns 80 for SQL_MAX_CATALOG_NAME_LEN
      Exiting hgolgon, rc=0 at 2011/05/04-12:34:55
      hostmstr: 2040471552:      HOA After hoalgon
      RPC Calling nscontrol(0), rc=0
      hostmstr: 2040471552: RPC Before Upload Caps
      hostmstr: 2040471552:      HOA Before hoaulcp
      Entered hgoulcp at 2011/05/04-12:34:55
      Entered hgowlst at 2011/05/04-12:34:55
      Exiting hgowlst, rc=1 at 2011/05/04-12:34:55
      SQLGetInfo returns Y for SQL_PROCEDURES
      SQLGetInfo returns 0x1f for SQL_OWNER_USAGE
      TXN Capable:2, Isolation Option:0xb
      SQLGetInfo returns 32 for SQL_MAX_SCHEMA_NAME_LEN
      SQLGetInfo returns 128 for SQL_MAX_TABLE_NAME_LEN
      SQLGetInfo returns 128 for SQL_MAX_PROCEDURE_NAME_LEN
      13 instance capabilities will be uploaded
      capno:3007, context:0x00000000, add-info: -3, translation:"36"
      capno:3042, context:0x00000000, add-info: 0, translation:"42"
      capno:3047, context:0x00000000, add-info: 0, translation:"57"
      capno:3049, context:0x00000000, add-info: 0, translation:"59"
      capno:3050, context:0x00000000, add-info: 0, translation:"60"
      capno:3066, context:0x00000000, add-info: 0
      capno:3067, context:0x00000000, add-info: 0
      capno:3068, context:0x00000000, add-info: 0
      capno:3069, context:0x00000000, add-info: 0
      capno:3500, context:0x00000001, add-info: 91, translation:"42"
      capno:3501, context:0x00000001, add-info: 93, translation:"57"
      capno:3502, context:0x00000001, add-info: 107, translation:"59"
      capno:3503, context:0x00000001, add-info: 110, translation:"60"
      Exiting hgoulcp, rc=0 at 2011/05/04-12:34:55
      hostmstr: 2040471552:      HOA After hoaulcp
      hostmstr: 2040471552: RPC After Upload Caps
      hostmstr: 2040471552: RPC Before Upload DDTR
      hostmstr: 2040471552:      HOA Before hoauldt
      Entered hgouldt at 2011/05/04-12:34:56
      NO instance DD translations were uploaded
      Exiting hgouldt, rc=0 at 2011/05/04-12:34:56
      hostmstr: 2040471552:      HOA After hoauldt
      hostmstr: 2040471552: RPC After Upload DDTR
      hostmstr: 2040471552: RPC Before Begin Trans
      hostmstr: 2040471552:      HOA Before hoabegn
      Entered hgobegn at 2011/05/04-12:34:56
      tflag:0 , initial:1
      hoi:0x12f094, ttid (len 59) is ...
      00: 504F5354 42414E4B 2E524547 52455353 [POSTBANK.REGRESS]
      10: 2E524442 4D532E44 45562E55 532E4F52 [.RDBMS.DEV.US.OR]
      20: 41434C45 2E434F4D 2E383962 37656636 [ACLE.COM.89b7ef6]
      30: 322E382E 332E3139 303438 [2.8.3.19048]
      tbid (len 10) is ...
      0: 08000300 684A0000 0104 [....hJ....]
      Exiting hgobegn, rc=0 at 2011/05/04-12:34:56
      hostmstr: 2040471552:      HOA After hoabegn
      hostmstr: 2040471552: RPC After Begin Trans
      hostmstr: 2040471552: RPC Before Describe Table
      hostmstr: 2040471552:      HOA Before hoadtab
      Entered hgodtab at 2011/05/04-12:34:56
      count:1
      table: ENC_GRID
      Entered hgopdsc at 2011/05/04-12:34:56
      Describing procedure informix.ENC_GRID
      Output hoada
      hgopdsc, line 1426: NO hoada to print
      Exiting hgopdsc, rc=942 at 2011/05/04-12:34:56
      The hoada for table ENC_GRID follows...
      hgodtab, line 904: NO hoada to print
      Exiting hgodtab, rc=0 at 2011/05/04-12:34:56
      hostmstr: 2040471552:      HOA After hoadtab
      hostmstr: 2040471552: RPC After Describe Table
      hostmstr: 2040471552: RPC Before SQL Bundling
      hostmstr: 2040471552:      HOA Before hoxpars
      Entered hgopars, cursor id 1 at 2011/05/04-12:34:56
      type:0
      SQL text from hgopars, id=1, len=24 ...
      00: 53454C45 4354202A 2046524F 4D202245 [SELECT * FROM "E]
      10: 4E435F47 52494422 [NC_GRID"]
      Entered hgopoer at 2011/05/04-12:34:56
      hgopoer, line 233: got native error -206 and sqlstate 42S02; message follows...
      [Oracle][ODBC Informix Wire Protocol driver][Informix]The specified table (ENC_GRID) is not in the database. {42S02,NativeErr = -206}
      Exiting hgopoer, rc=0 at 2011/05/04-12:34:56
      hgopars, line 457: calling SQLPrepare got sqlstate 42S02
      Exiting hgopars, rc=942 at 2011/05/04-12:34:56
      hostmstr: 2040471552:      HOA After hoxpars
      hostmstr: 2040471552: RPC After SQL Bundling
      hostmstr: 2040471552: RPC Before Rollback Trans
      hostmstr: 2040471552:      HOA Before hoaroll
      Entered hgoroll at 2011/05/04-12:34:56
      tflag:1 , cmt(0):
      hoi:0x12f098, ttid (len 59) is ...
      00: 504F5354 42414E4B 2E524547 52455353 [POSTBANK.REGRESS]
      10: 2E524442 4D532E44 45562E55 532E4F52 [.RDBMS.DEV.US.OR]
      20: 41434C45 2E434F4D 2E383962 37656636 [ACLE.COM.89b7ef6]
      30: 322E382E 332E3139 303438 [2.8.3.19048]
      tbid (len 10) is ...
      0: 08000300 684A0000 0104 [....hJ....]
      Entered hgocpctx at 2011/05/04-12:34:56
      Exiting hgocpctx, rc=0 at 2011/05/04-12:34:56
      Exiting hgoroll, rc=0 at 2011/05/04-12:34:56
      hostmstr: 2040471552:      HOA After hoaroll
      hostmstr: 2040471552: RPC After Rollback Trans

      ----------------------------------------------------------

      Here is the initdg4ifmx.ora, just in case:

      # This is a customized agent init file that contains the HS parameters
      # that are needed for the Database Gateway for Informix

      #
      # HS init parameters
      #
      HS_FDS_CONNECT_INFO=[10.1.10.6]:9088/idsenclxsoc/enc3
      HS_FDS_TRACE_LEVEL=255
      HS_FDS_RECOVERY_ACCOUNT=RECOVER
      HS_FDS_RECOVERY_PWD=RECOVER


      Thank you for any help in advance,

      Branko