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
      • 1. Re: Connecting Oracle 10g and Informix through DG4IFMX
        Kgronau-Oracle
        When you look at the gateway trace, the gateway is looking for a table called "ENC_GRID".
        Commonly the Informix tables are case sensitive so please check out your select statement, write the column/table names as they are defined in the Informix datbase and surround table/column names by double quotes.

        For example the table enc_grid contains only lower cases letters at your Informix database, then type: select * from "enc_grid"@dblink;

        Edited by: kgronau on May 4, 2011 12:56 PM
        • 2. Re: Connecting Oracle 10g and Informix through DG4IFMX
          858320
          I am writing with lower case letters as u said, but still in the error it is shown as upper case letters.

          Could it be that there is some Initialization Parameter that is processig the name of the table with upper case, and therefore the error is occured?

          Did u look at the trace file? There is some error during SQLDriverConnect, something about the ArraySize.

          Thanks for the immediate reply
          • 3. Re: Connecting Oracle 10g and Informix through DG4IFMX
            858320
            Thank you kgronau,

            the double quotes did the trick, i thought i have tried it, but i rechecked and it succeeded.

            Thanks again