Forum Stats

  • 3,768,190 Users
  • 2,252,758 Discussions
  • 7,874,486 Comments

Discussions

Configure Heterogeneous Connectivity between SQL Server x Oracle

124

Comments

  • DenisGomes
    DenisGomes Member Posts: 77
    edited Nov 30, 2011 1:01PM
    Hi Mike,
    I tested this parameter, but didn't work.

    HS_FDS_CONNECT_INFO = CAPTA_CONT
    HS_FDS_TRACE_FILE_NAME = /tmp/hsodbcsql.trc
    HS_FDS_SHAREABLE_NAME =/usr/local/easysoft/unixODBC/lib/libodbc.so
    HS_FDS_TRACE_LEVEL=debug

    I checked the hsodbcsql.trc file, it's empty.
    I'm using EasySoft Sql Server ODBC Driver v1.4.

    Aix 6.1
    Oracle 10g
    Oracle Database Gateway 11g
    Windows Server 2003
    Sql Server 2005

    Thanks
    Denis
  • Kgronau-Oracle
    Kgronau-Oracle Posts: 2,550 Employee
    Denis,
    did you open a new session once you changed the trace parameter? The gateway init file is only read at the beginning when the gateway process is spawned from the listener. When yo now have opened a gateway process (even when it failed) there might be a gateway process in the memory that belongs to your session and when you now reuse the statement by executing the failing statement again, then the trace parameter isn't read and executed.

    So please make sure no dg4odbc process is running, then retry the select. Also remove the parameter HS_FDS_TRACE_FILE_NAME = /tmp/hsodbcsql.trc.
    Make sure that the $ORACLE_HOME/hs/log directory is empty - then start a new session and execute your statement.
  • DenisGomes
    DenisGomes Member Posts: 77
    edited Dec 1, 2011 6:15AM
    Hi Kgronau,
    I tried this, changed the parameter and opened a new session by SQL Plus.
    Now, I removed the HS_FDS_TRACE_FILE_NAME parameter, restarted the machine, started the two listeners, started up the instance and executed my statement. I checked the directory $ORACLE_HOME/hs/log and $ORACLE_HOME/hs/trace and anything created.
    How can i check the dg4odbc process?

    Thanks again,
    Denis
  • Mkirtley-Oracle
    Mkirtley-Oracle Member Posts: 1,859
    Denis,
    When you run the select from SQLPLUS then what error do you see ? Is it the same one you reported earlier -

    ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
    ORA-02063: preceding line from CAPTA
    28500. 00000 - "connection from ORACLE to a non-Oracle system returned this message:"
    *Cause: The cause is explained in the forwarded message.
    *Action: See the non-Oracle system's documentation of the forwarded
    message.
    Error at Line: 1 Column: 28

    If so, this indicates the dg4odbc executable is being called so it should have created a trace file.
    According to the listener.ora file you posted earlier the gateway is installed in the directory -

    /oracle/TSA/112_64

    so the trace should be in -

    /oracle/TSA/112_64/hs/log

    To check the executable itself go to -

    /oracle/TSA/112_64/bin

    and do -

    ./dg4odbc

    the output should be similar to -

    Oracle Corporation --- THURSDAY DEC 01 2011 12:24:35.396

    Heterogeneous Agent Release 11.2.0.1.0 - 64bit Production Built with
    Oracle Database Gateway for ODBC

    If the executable runs okay then it post the most recent versions of the following files so we can check the setup again -

    - inithscapta.ora
    - gateway listener.ora

    Regards,
    Mike
  • DenisGomes
    DenisGomes Member Posts: 77
    Hi Mike,
    Thanks for your help.
    I checked the /oracle/TSA/112_64/hs/log directory and the trace files were there.
    I cheched the last file created.

    #ls -ltr

    The output:

    [email protected]:/oracle/TSA/112_64/hs/log:#more hscapta_agt_13828262.trc


    Oracle Corporation --- THURSDAY DEC 01 2011 09:05:35.270


    Heterogeneous Agent Release
    +11.2.0.1.0+


    Unable to open init file inithscapta.ora in directory /oracle/TSA/112_64/hs/admin

    HS Gateway: NULL connection context at exit

    So, I copied the inithscapta.ora from /oracle/TSA/102_64/hs/admin to /oracle/TSA/112_64/hs/admin.

    Now, follow the new output.

    [email protected]:/oracle/TSA/112_64/hs/log:#more hscapta_agt_4325414.trc


    Oracle Corporation --- THURSDAY DEC 01 2011 09:48:53.047


    Heterogeneous Agent Release
    +11.2.0.1.0+




    Oracle Corporation --- THURSDAY DEC 01 2011 09:48:53.046

    Version 11.2.0.1.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_IDLE_TIMEOUT to default of 0
    setting HS_FDS_TRANSACTION_ISOLATION to default of "READ_COMMITTED"
    setting HS_NLS_NCHAR to default of "AL32UTF8"
    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$:SQLGet
    Info"
    setting HS_FDS_DELAYED_OPEN to default of "TRUE"
    setting HS_FDS_WORKAROUNDS to default of "0"
    Exiting hgosdip, rc=0
    ORACLE_SID is "hscapta"
    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:hscapta
    Exiting hgogprd, rc=0
    Entered hgoinit
    HOCXU_COMP_CSET=1
    HOCXU_DRV_CSET=2
    HOCXU_DRV_NCHAR=873
    HOCXU_DB_CSET=2
    HOCXU_SEM_VER=102000
    Entered hgolofn at 2011/12/01-09:48:53
    HOSGIP for "HS_FDS_SHAREABLE_NAME" returned "/usr/local/easysoft/unixODBC/lib/libodbc.so"
    Entered hgolofns at 2011/12/01-09:48:53
    hoaerr:28500
    Exiting hgolofns at 2011/12/01-09:48:53
    Failed to load ODBC library symbol: /usr/local/easysoft/unixODBC/lib/libodbc.so(SQLAllocHandle)
    Exiting hgolofn, rc=28500 at 2011/12/01-09:48:53
    Exiting hgoinit, rc=28500 with error ptr FILE:hgoinit.c LINE:337 ID:Loading ODBC aray of function ptrs
    Entered hgoexit
    HS Gateway: NULL connection context at exit
    Exiting hgoexit, rc=0 with error ptr FILE:hgoexit.c LINE:108 ID:Connection context

    Thanks,
    Denis
  • Mkirtley-Oracle
    Mkirtley-Oracle Member Posts: 1,859
    Denis,
    Thanks for following up and resolving the problems so far.
    The problem is with the libodbc.so driver manager -

    Failed to load ODBC library symbol: /usr/local/easysoft/unixODBC/lib/libodbc.so(SQLAllocHandle)

    I'll check and get back to you.

    Regards,
    Mike
  • Mkirtley-Oracle
    Mkirtley-Oracle Member Posts: 1,859
    Denis,
    Can you check the following -

    1. The gateway listener.ora entry for the gateway still has the correct LD_LIBRARY_PATH entry ? it should have -

    (ENVS=LD_LIBRARY_PATH = /usr/local/easysoft/unixODBC/lib:/usr/local/easysoft/lib)

    2. The libodbc.so is a 64-bit version -

    cd /usr/local/easysoft/unixODBC/lib

    file libodbc.so

    If it reports a symbolic link then issue 'file' against that.

    3. That the file has the correct entries -

    cd /usr/local/easysoft/unixODBC/lib

    nm libodbc.so | grep SQLA

    It should report something like -

    000000000002fb2e T SQLAllocConnect
    000000000002fa14 T SQLAllocEnv
    000000000002f6b0 T SQLAllocHandle
    000000000002fc5c T SQLAllocStmt
    0000000000060c5c t _ZN5DMEnv15SQLAllocConnectEPPv

    4. Make sure the Easysoft driver is ODBC v3 compliant. You'll have to check with the supplier for that.

    Regards,
    Mike
  • DenisGomes
    DenisGomes Member Posts: 77
    edited Dec 1, 2011 8:05AM
    Mike,
    Thanks for your help.
    I found the solution here http://www.easysoft.com/applications/oracle/database-gateway-dg4odbc.html#config.

    Follow step by step.
    unixODBC on AIX
    On AIX, the unixODBC driver manager shared object is inserted into libodbc.a as libodbc.so.1. The following ar command output shows a unixODBC archive that >contains libodbc.so.1.
    $ ar -X32_64 -tv libodbc.a
    rwxr-xr-x 201/1 636475 Aug 20 09:11 2004 libodbc.so.1
    libtool creates the driver manager like this on AIX.
    Unfortunately, DG4ODBC is built and linked against libodbc.so (no version). To work around this:
    cd to the directory where unixODBC’s libraries are installed. For example:
    cd /usr/local/easysoft/unixODBC/lib

    Extract the shared object from the archive:
    ar -X32_64 -xv libodbc.a
    x - libodbc.so.1

    Rename libodbc.so.1 to libodbc.so:
    mv libodbc.so.1 libodbc.so

    Point DG4ODBC directly at the new shared object by amending the HS_FDS_SHAREABLE_NAME value in your init*.ora file to be:
    HS_FDS_SHAREABLE_NAME = /usr/local/easysoft/unixODBC/lib/libodbc.so

    Add this line to your .profile file:
    LIBPATH=$LIBPATH:/usr/local/easysoft/lib:/usr/local/easysoft/unixODBC/lib

    Restart your Oracle listener. Your database link should now work.
    Now, I have another error:

    select count(*) from [email protected]"CAPTA.WORLD"
    *
    ERROR at line 1:
    ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
    +[unixODBC][Driver Manager]Can't open lib+
    +'/usr/local/easysoft/sqlserver/lib/libessqlsrv.a' : file not found {01000}+
    ORA-02063: preceding 2 lines from CAPTA

    Trace file
    Oracle Corporation --- THURSDAY DEC 01 2011 10:46:02.941


    Heterogeneous Agent Release
    11.2.0.1.0




    Oracle Corporation --- THURSDAY DEC 01 2011 10:46:02.940

    Version 11.2.0.1.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_IDLE_TIMEOUT to default of 0
    setting HS_FDS_TRANSACTION_ISOLATION to default of "READ_COMMITTED"
    setting HS_NLS_NCHAR to default of "AL32UTF8"
    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 "hscapta"
    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:hscapta
    Exiting hgogprd, rc=0
    Entered hgoinit
    HOCXU_COMP_CSET=1
    HOCXU_DRV_CSET=2
    HOCXU_DRV_NCHAR=873
    HOCXU_DB_CSET=2
    HOCXU_SEM_VER=102000
    Entered hgolofn at 2011/12/01-10:46:02
    HOSGIP for "HS_FDS_SHAREABLE_NAME" returned "/usr/local/easysoft/unixODBC/lib/libodbc.so"
    Entered hgolofns at 2011/12/01-10:46:02
    symbol_peflctx=0xa09df658
    hoaerr:0
    Exiting hgolofns at 2011/12/01-10:46:02
    Entered hgolofns at 2011/12/01-10:46:02
    symbol_peflctx=0xa09df6d0
    hoaerr:0
    Exiting hgolofns at 2011/12/01-10:46:02
    Entered hgolofns at 2011/12/01-10:46:02
    symbol_peflctx=0xa09df700
    hoaerr:0
    Exiting hgolofns at 2011/12/01-10:46:02
    Entered hgolofns at 2011/12/01-10:46:02
    symbol_peflctx=0xa09df7a8
    hoaerr:0
    Exiting hgolofns at 2011/12/01-10:46:02
    Exiting hgolofn, rc=0 at 2011/12/01-10:46:02
    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 ".,"
    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"
    Invalid value of 64 given for HS_FDS_SQLLEN_INTERPRETATION
    treat_SQLLEN_as_compiled = 1
    Exiting hgoinit, rc=0 at 2011/12/01-10:46:02
    Entered hgolgon at 2011/12/01-10:46:02
    reco:0, name:sa, tflag:0
    Entered hgosuec at 2011/12/01-10:46:02
    Exiting hgosuec, rc=0 at 2011/12/01-10:46:02
    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 sa as default value for "HS_FDS_DEFAULT_OWNER"
    HOSGIP for "HS_SQL_HANDLE_STMT_REUSE" returned "FALSE"
    Entered hgocont at 2011/12/01-10:46:02
    HS_FDS_CONNECT_INFO = "CAPTA_CONT"
    RC=-1 from HOSGIP for "HS_FDS_CONNECT_STRING"
    Entered hgogenconstr at 2011/12/01-10:46:02
    dsn:CAPTA_CONT, name:sa
    optn:
    Entered hgocip at 2011/12/01-10:46:02
    dsn:CAPTA_CONT
    Exiting hgocip, rc=0 at 2011/12/01-10:46:02
    Exiting hgogenconstr, rc=0 at 2011/12/01-10:46:02
    Entered hgopoer at 2011/12/01-10:46:02
    hgopoer, line 233: got native error 0 and sqlstate 01000; message follows...
    [unixODBC][Driver Manager]Can't open lib '/usr/local/easysoft/sqlserver/lib/libessqlsrv.a' : file not found {01000}
    Exiting hgopoer, rc=0 at 2011/12/01-10:46:02
    hgocont, line 2753: calling SqlDriverConnect got sqlstate 01000
    Exiting hgocont, rc=28500 at 2011/12/01-10:46:02 with error ptr FILE:hgocont.c LINE:2772 ID:Something other than invalid authorization
    Exiting hgolgon, rc=28500 at 2011/12/01-10:46:02 with error ptr FILE:hgolgon.c LINE:781 ID:Calling hgocont
    Entered hgoexit at 2011/12/01-10:46:02
    Exiting hgoexit, rc=0


    I already checked this file at this directory and it exists. I changed the permissions to oratsa:dba too, but didn't work.

    Thanks by support for all gurus.

    Denis
  • Mkirtley-Oracle
    Mkirtley-Oracle Member Posts: 1,859
    Denis,
    As you see the error is coming from the driver manager.
    I checked the configuration files again and for AIX the ENVS setting should use LIBPATH instead of LD_LIBRARY_PATH so could you change the listener.ora entry from -

    (ENVS=LD_LIBRARY_PATH = /usr/local/easysoft/unixODBC/lib:/usr/local/easysoft/lib:/oracle/TSA/112_64/lib:/oracle/TSA/112_64/bin)

    tp

    (ENVS=LIBPATH=/usr/local/easysoft/unixODBC/lib:/usr/local/easysoft/lib:/oracle/TSA/112_64/lib:/oracle/TSA/112_64/bin)

    if you still have problems then add the directory -

    /usr/local/easysoft/sqlserver/lib

    to the LIBPATH in the listener.ora.

    In both cases stop and start he gateway listener and select from a new SQLPLUS session.

    You could also try the select without quotes round the db link name -

    select count(*) from [email protected] ;

    Also, if SQL*Server is case sensitive for table names then you may need to do -

    select count(*) from "sefcli"@CAPTA.WORLD ;

    or perhaps also with the owner -

    select count(*) from "owner"."sefcli"@CAPTA.WORLD ;

    Regards,
    Mike
  • DenisGomes
    DenisGomes Member Posts: 77
    Hi Mike,
    Thanks a lot. I changed this parameter and worked.
    Now, i would want to know... Do i need to adjust the types at any file? For instance, i tried to execute a select at the SAP and it returned a error null column.

    Thanks Kgronau and Mike.

    Denis
This discussion has been closed.