6 Replies Latest reply: Feb 28, 2012 3:18 AM by 919587 RSS

    Create a dblink oracle to postgres with dg4odbc

    919587
      Hi,

      I'm trying to create a dblink from a database Oracle (10.2.0.3) to PostgreSQL database (8.3.11) with DG4ODBC (11.2.0.1.0).

      I've installed the postgres driver 64 bit :
      file /usr/lib64/psqlodbc.so
      /usr/lib64/psqlodbc.so: ELF 64-bit LSB shared object, AMD x86-64, version 1 (SYSV), stripped
      I'm configuring the /oracle/.odbc.ini :
      [record]
      Driver=/usr/lib64/psqlodbc.so
      Description  = PostgreSQL ODBC
      Servername       = cdxlan035
      Port         = 5435
      Username         = rec_lct
      Password     = rec_lct
      Database     = REC_TEST
      I'm configuring the /oracle/product/DG4ODBC/hs/admin/initrecord.ora like this :
      #
      # HS init parameters
      #
      HS_FDS_CONNECT_INFO = record
      HS_FDS_TRACE_LEVEL = 4
      HS_FDS_TRACE_FILE_NAME = /oracle/product/DG4ODBC/hs/log/hsodbc.trc
      HS_FDS_SHAREABLE_NAME = /usr/lib64/psqlodbc.so
      
      #
      # ODBC specific environment variables
      #
      set ODBCINI=/oracle/.odbc.ini
      I'm configuring the /oracle/product/DG4ODBC/network/admin/listener.ora
      LISTENER_HS =
        (DESCRIPTION_LIST =
          (DESCRIPTION =
            (ADDRESS = (PROTOCOL = TCP)(HOST = cdxlan036.douane)(PORT = 1525))
          )
        )
      
      ADR_BASE_LISTENER_HS = /oracle/product/DG4ODBC
      
      SID_LIST_LISTENER_HS =
        (SID_LIST =
          (SID_DESC =
            (PROGRAM = dg4odbc)
            (ORACLE_HOME = /oracle/product/DG4ODBC)
            (SID_NAME = record)
            (ENVS=LD_LIBRARY_PATH=/usr/lib64:/oracle/product/DG4ODBC/lib:/oracle/product/10.2.0/lib)
          )
        )
      I'm updating my /oracle/product/10.2.0/network/admin/tnsnames.ora :
      RECORD2 =
        (DESCRIPTION =
          (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = TCP)(HOST = cdxlan036)(PORT = 1525))
          )
          (CONNECT_DATA =
            (SID = record)
          )
          (HS=OK)
         )
      In last i'm creating the dblink :
      CREATE  DATABASE LINK "RECORD"  CONNECT TO "rec_lct" IDENTIFIED BY "rec_lct"  USING 'RECORD2';
      When i'm trying to select my postgres database, that doesn't work. Here the /oracle/product/DG4ODBC/hs/log/record.log :
      [oracle@cdxlan036 log]$ cat record_agt_30445.trc
      
      
      Oracle Corporation --- MONDAY    FEB 27 2012 17:11:31.204
      
      
      Heterogeneous Agent Release
      11.2.0.1.0
      
      
      
      
      Oracle Corporation --- MONDAY    FEB 27 2012 17:11:31.204
      
          Version 11.2.0.1.0
      
      Entered hgogprd
      HOSGIP for "HS_FDS_TRACE_LEVEL" returned "4"
      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 "record"
       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:record
      Exiting hgogprd, rc=0
      Entered hgoinit
      HOCXU_COMP_CSET=1
      HOCXU_DRV_CSET=31
      HOCXU_DRV_NCHAR=873
      HOCXU_DB_CSET=31
      HOCXU_SEM_VER=102000
      Entered hgolofn at 2012/02/27-17:11:31
      HOSGIP for "HS_FDS_SHAREABLE_NAME" returned "/usr/lib64/psqlodbc.so"
      Entered hgolofns at 2012/02/27-17:11:31
       libname=/usr/lib64/psqlodbc.so, funcname=SQLAllocHandle
       peflerr=6521, libname=/usr/lib64/psqlodbc.so, funcname=SQLAllocHandle
       hoaerr:28500
      Exiting hgolofns at 2012/02/27-17:11:31
      Failed to load ODBC library symbol: /usr/lib64/psqlodbc.so(SQLAllocHandle)
      Exiting hgolofn, rc=28500 at 2012/02/27-17:11:31
      Exiting hgoinit, rc=28500 with error ptr FILE:hgoinit.c LINE:337 FUNCTION:hgoinit() 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 FUNCTION:hgoexit() ID:Connection context
      What's wrong with my configuration?

      thanks for your help

      regards,

      Mathieu
        • 1. Re: Create a dblink oracle to postgres with dg4odbc
          Mkirtley-Oracle
          Mathieu,
          The error causing the problem is this -

          Failed to load ODBC library symbol: /usr/lib64/psqlodbc.so(SQLAllocHandle)

          Which implies the psqlodbc.so file does not contain sqlallochandle symbol.
          However, your gateway listener has the following env setting -

          (ENVS=LD_LIBRARY_PATH=/usr/lib64:/oracle/product/DG4ODBC/lib:/oracle/product/10.2.0/lib)


          so can you remove the entry -

          /oracle/product/10.2.0/lib

          and stop and start the listener.
          Can you then check the psqlodbc.so file -

          cd usr/lib64
          nm psqlodbc.so | grep SQLAllocHandle

          or

          nm -D psqlodbc.so | grep SQLAllocHandle

          and check it exists in the file.

          Regards,
          Mike
          • 2. Re: Create a dblink oracle to postgres with dg4odbc
            919587
            Hi Mike

            thanks a lot for your reply

            see below the reply of command nm :
            oracle@cdxlan036 log]$ cd /usr/lib64/
            [oracle@cdxlan036 lib64]$ nm psqlodbc.so | grep SQLAllocHandle
            nm: psqlodbc.so: aucun symbole
            [oracle@cdxlan036 lib64]$
            [oracle@cdxlan036 lib64]$ nm -D psqlodbc.so | grep SQLAllocHandle
            [oracle@cdxlan036 lib64]$
            should i guess my driver odbc doesn't work correctly?

            regards
            Mathieu
            • 3. Re: Create a dblink oracle to postgres with dg4odbc
              Kgronau-Oracle
              HS_FDS_SHAREABLE_NAME needs to point to the ODBC driver manager. Commonly the ODBC driver manager is a separate library (common name is libodbc.so). It can be downloaded for example from unixodbc.org. Only a few rare ODBC drivers (some ODBC drivers from MySQL and also some IBM DB2 drivers have a built in ODBC driver manager into the ODBC driver itself, all other require a stand alone ODBC driver manager).

              Background info:
              In general the ODBC set up consists of 2 parts, an ODBC driver manager and an ODBC driver itself. The ODBC driver manager is providing generic ODBC functions and it is also responsible to load the driver specified in the odbc.ini file. The ODBC driver itself is then dedicated for a foreign database and provides specific foreign database functions.
              • 4. Re: Create a dblink oracle to postgres with dg4odbc
                919587
                Hi kgronau,

                thanks for your reply. Unfortunately, i'm not sure to understand what you explain.
                In fact i should have a driver manager and a driver, thats it?

                I've download and install this rpm : postgresql-odbc-7.3-8.RHEL4.1.x86_64.rpm.

                it provides :

                /usr/lib64/psqlodbc.la
                /usr/lib64/psqlodbc.so
                /usr/share/psqlodbc/odbc-drop.sql
                /usr/share/psqlodbc/odbc.sql

                I've supposed thats the /usr/lib64/psqlodbc.so file must be the drver manager. So i've pointed HS_FDS_SHAREABLE_NAME to this file.

                But where is the driver? Driver in my odbc.ini must point to the driver file, that's it?


                regards

                Mathieu
                • 5. Re: Create a dblink oracle to postgres with dg4odbc
                  Kgronau-Oracle
                  Yes, you need to have an ODBC driver (psqlodbc.so) and an ODBC driver manager (very common name is libodbc.so). So you're missing the ODBC driver manager which you can for example get from unixodbc.org and once you installed it you need to correct HS_FDS_SHAREABLE_NAME to refer to the driver manager. The driver manager will check out the odbc.ini file and depending on the specified DSN it will load your ODBC driver.
                  • 6. Re: Create a dblink oracle to postgres with dg4odbc
                    919587
                    Hi

                    thanks a lot for your help.

                    i've another problem now with my dblink, but i prefer create a new thread.

                    thanks

                    Mathieu