9 Replies Latest reply on May 13, 2013 3:40 PM by Mkirtley-Oracle

    Accessing Postgres from Oracle using DG4ODBC and psqlodbc very slow

    cm*362129*oz
      I have set up DG4ODBC on a Windows 2003 32-bit 11g Oracle database to access a Windows 2008 64-bit 9.1 Postgres database, using the psqlodbc driver (32-bit).

      I am able to issue queries, but they are very slow. The reason is that the where clause of the query is being removed somewhere along the line (I have seen the actual queries run by Postgres using pgAdmin server status screen), and all the rows in the table are retrieved from Postgres. Naturally with tables bigger than a few rows that means doing a full table scan in Postgres, as well as transferring lots of bytes through the network.

      For instance, if I query from Oracle like this: select * from "dps_user"@pg where "id" = '2423', the sql run at Postgres is this: select * from dps_user, which retrieves all rows on the table! When I get the query result in Oracle, I only get the row with id 2423, so I assume that Oracle gets the whole table, and does the filtering at its end.

      In a 32-bit Windows XP separate machine I have set up the same psqlodbc driver, and I have run the same query using Microsoft Query. In this case, the query run in Postgres contains the where clause, and therefore it is much faster because a unique index scan is performed in Postgres, and only one row is transferred back to Microsoft Query.

      Could you help me, please?

      Thanks a lot,

      Carlos
        • 1. Re: Accessing Postgres from Oracle using DG4ODBC and psqlodbc very slow
          Mkirtley-Oracle
          Carlos,
          DG4ODBC is designed as a generic gateway so does not have as much functionality as a dedicated gateway so 'where' claues can be dropped if they contain something that is not supported.
          However, I would expect a simple 'where' such as you have posted -

          select * from "dps_user"@pg where "id" = '2423'@dg4odbc ;

          to be passed to Postgres.
          Could you post the gateway init<dg4odbc>.ora file and set up 255 level tracing -

          HS_FDS_TRACE_LEVEL=255

          in the gateway init file and post the section that shows the SQL passed to Postgres ?
          What is the full version of DG4ODBC being used ? If it is not 11.2.0.3 then I suggest you download and install that version in a separate ORACLE_HOME from existing installs and see if the problem happens with that version.
          It is available from My Oracle Support as -

          Patch 10404530: 11.2.0.3.0 PATCH SET FOR ORACLE DATABASE SERVER

          - login to My Oracle Support
          - click on 'Patches and Updates'
          - search for patch number 10404530
          - you may need to press the '+' button to see the platform list
          - scroll down the list to and click on theyour platform
          - on the next screen click on the 'Download' option
          - from the list of download files choose -
          p10404530_112030_platform_4of6.zip
          - this is the Gateway media pack and has everything needed for a standalone gateway install.
          - unzip the file and run the installer and choose the gateway you want.

          Regards,
          Mike
          1 person found this helpful
          • 2. Re: Accessing Postgres from Oracle using DG4ODBC and psqlodbc very slow
            cm*362129*oz
            Hi Mike,

            Thanks a lot for your help. The gateway version must be 11.2.0.3 since the Oracle database has patchset 11.2.0.3 applied.

            This is the ini file once I changed the trace level:

            HS_FDS_CONNECT_INFO = pg
            HS_FDS_TRACE_LEVEL = 255

            I tried this query:

            select *
            from dps_user
            where id = 'W106606';

            Where dps_user is a view on the Oracle database which translates to this:

            CREATE OR REPLACE FORCE VIEW dps_user (ID,
            login,
            first_name,
            last_name,
            lastactivity_date,
            registration_date,
            email,
            email_status,
            receive_email
            )
            AS
            SELECT "id", "login", "first_name", "last_name", "lastactivity_date",
            "registration_date", "email", "email_status", "receive_email"
            FROM "dynamo_web"."dps_user"@pg.es.lladro.com;



            The generated trace file is:

            Oracle Corporation --- JUEVES MAY 09 2013 18:13:35.968


            Heterogeneous Agent Release
            11.2.0.3.0




            Oracle Corporation --- JUEVES MAY 09 2013 18:13:35.968

            Version 11.2.0.3.0

            Entered hgogprd
            HOSGIP for "HS_FDS_TRACE_LEVEL" returned "255"
            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 "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"
            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_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_FDS_QUOTE_IDENTIFIER to default of "TRUE"
            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 32 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 "PG"
            Product-Info:
            Port Rls/Upd:3/0 PrdStat:0
            Agent:Oracle Database Gateway for ODBC
            Facility:hsa
            Class:ODBC, ClassVsn:11.2.0.3.0_0011, Instance:PG
            Exiting hgogprd, rc=0
            hostmstr: 2051219456:      HOA After hoagprd
            hostmstr: 2051219456:      HOA Before hoainit
            Entered hgoinit
            HOCXU_COMP_CSET=1
            HOCXU_DRV_CSET=178
            HOCXU_DRV_NCHAR=1000
            HOCXU_DB_CSET=873
            HS_LANGUAGE not specified
            rc=1000 attempting to get LANG environment variable.
            HOCXU_SEM_VER=112000
            Entered hgolofn at 2013/05/09-18:13:36
            Exiting hgolofn, rc=0 at 2013/05/09-18:13:36
            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 32 given for HS_FDS_SQLLEN_INTERPRETATION
            treat_SQLLEN_as_compiled = 1
            Exiting hgoinit, rc=0 at 2013/05/09-18:13:36
            hostmstr: 2051219456:      HOA After hoainit
            hostmstr: 2051219456:      HOA Before hoalgon
            Entered hgolgon at 2013/05/09-18:13:36
            reco:0, name:dynamoselect, tflag:0
            Entered hgosuec at 2013/05/09-18:13:36
            Exiting hgosuec, rc=0 at 2013/05/09-18:13:36
            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_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 dynamoselect as default value for "HS_FDS_DEFAULT_OWNER"
            HOSGIP for "HS_SQL_HANDLE_STMT_REUSE" returned "FALSE"
            Entered hgocont at 2013/05/09-18:13:36
            HS_FDS_CONNECT_INFO = "pg"
            RC=-1 from HOSGIP for "HS_FDS_CONNECT_STRING"
            Entered hgogenconstr at 2013/05/09-18:13:36
            dsn:pg, name:dynamoselect
            optn:
            Entered hgocip at 2013/05/09-18:13:36
            dsn:pg
            Exiting hgocip, rc=0 at 2013/05/09-18:13:36
            ##>Connect Parameters (len=40)<##
            ## DSN=pg;
            #! UID=dynamoselect;
            #! PWD=*
            Exiting hgogenconstr, rc=0 at 2013/05/09-18:13:36
            Entered hgolosf at 2013/05/09-18:13:36
            ODBC Function-Available-Array 0xFFFE 0x01FF 0xFF00 0xFA7F 0x03DF 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 0x3B5C
            Exiting hgolosf, rc=0 at 2013/05/09-18:13:36
            DriverName:PSQLODBC35W.DLL, DriverVer:09.01.0100
            DBMS Name:PostgreSQL, DBMS Version:9.1.7
            Exiting hgocont, rc=0 at 2013/05/09-18:13:36
            SQLGetInfo returns Y for SQL_CATALOG_NAME
            SQLGetInfo returns 0 for SQL_MAX_CATALOG_NAME_LEN
            Exiting hgolgon, rc=0 at 2013/05/09-18:13:36
            hostmstr: 2049228800:      HOA After hoalgon
            RPC Calling nscontrol(0), rc=0
            hostmstr: 2049228800: RPC Before Upload Caps
            hostmstr: 2049228800:      HOA Before hoaulcp
            Entered hgoulcp at 2013/05/09-18:13:36
            Entered hgowlst at 2013/05/09-18:13:36
            Exiting hgowlst, rc=0 at 2013/05/09-18:13:36
            SQLGetInfo returns 0x1d for SQL_OWNER_USAGE
            TXN Capable:2, Isolation Option:0xa
            SQLGetInfo returns 64 for SQL_MAX_SCHEMA_NAME_LEN
            SQLGetInfo returns 64 for SQL_MAX_TABLE_NAME_LEN
            SQLGetInfo returns 0 for SQL_MAX_PROCEDURE_NAME_LEN
            HOSGIP returned value of "TRUE" for HS_FDS_QUOTE_IDENTIFIER
            SQLGetInfo returns " (0x22) for SQL_IDENTIFIER_QUOTE_CHAR
            2 instance capabilities will be uploaded
            capno:1989, context:0x00000000, add-info: 0
            capno:1992, context:0x0001ffff, add-info: 0
            Exiting hgoulcp, rc=0 at 2013/05/09-18:13:36
            hostmstr: 2049228800:      HOA After hoaulcp
            hostmstr: 2049228800: RPC After Upload Caps
            hostmstr: 2049228800: RPC Before Upload DDTR
            hostmstr: 2049228800:      HOA Before hoauldt
            Entered hgouldt at 2013/05/09-18:13:36
            NO instance DD translations were uploaded
            Exiting hgouldt, rc=0 at 2013/05/09-18:13:36
            hostmstr: 2049228800:      HOA After hoauldt
            hostmstr: 2049228800: RPC After Upload DDTR
            hostmstr: 2049228800: RPC Before Begin Trans
            hostmstr: 2049228800:      HOA Before hoabegn
            Entered hgobegn at 2013/05/09-18:13:36
            tflag:0 , initial:1
            hoi:0x12f090, ttid (len 39) is ...
            00: 44455341 322E4553 2E4C4C41 44524F2E [DESA2.ES.LLADRO.]
            10: 434F4D2E 65336530 30323865 2E31312E [COM.e3e0028e.11.]
            20: 332E3133 303836 [3.13086]
            tbid (len 36) is ...
            00: 44455341 322E4553 2E4C4C41 44524F2E [DESA2.ES.LLADRO.]
            10: 434F4D5B 31312E33 2E313330 38365D5B [COM[11.3.13086][]
            20: 312E345D [1.4]]
            Exiting hgobegn, rc=0 at 2013/05/09-18:13:36
            hostmstr: 2049228800:      HOA After hoabegn
            hostmstr: 2049228800: RPC After Begin Trans
            hostmstr: 2049228800: RPC Before Describe Table
            hostmstr: 2049228800:      HOA Before hoadtab
            Entered hgodtab at 2013/05/09-18:13:36
            count:1
            table: dynamo_web.dps_user
            Allocate hoada[0] @ 033A88D4
            Entered hgopcda at 2013/05/09-18:13:36
            Column:1(id): dtype:-9 (WVARCHAR), prc/scl:40/0, nullbl:0, octet:80, sign:1, radix:0
            Exiting hgopcda, rc=0 at 2013/05/09-18:13:36
            Entered hgopcda at 2013/05/09-18:13:36
            Column:2(login): dtype:-9 (WVARCHAR), prc/scl:100/0, nullbl:0, octet:200, sign:1, radix:0
            Exiting hgopcda, rc=0 at 2013/05/09-18:13:36
            Entered hgopcda at 2013/05/09-18:13:36
            Column:3(auto_login): dtype:12 (VARCHAR), prc/scl:5/0, nullbl:1, octet:-1, sign:1, radix:0
            Exiting hgopcda, rc=0 at 2013/05/09-18:13:36
            Entered hgopcda at 2013/05/09-18:13:36
            Column:4(password): dtype:-9 (WVARCHAR), prc/scl:35/0, nullbl:1, octet:70, sign:1, radix:0
            Exiting hgopcda, rc=0 at 2013/05/09-18:13:36
            Entered hgopcda at 2013/05/09-18:13:36
            Column:5(member): dtype:12 (VARCHAR), prc/scl:5/0, nullbl:1, octet:-1, sign:1, radix:0
            Exiting hgopcda, rc=0 at 2013/05/09-18:13:36
            Entered hgopcda at 2013/05/09-18:13:36
            Column:6(first_name): dtype:-9 (WVARCHAR), prc/scl:60/0, nullbl:1, octet:120, sign:1, radix:0
            Exiting hgopcda, rc=0 at 2013/05/09-18:13:36
            Entered hgopcda at 2013/05/09-18:13:36
            Column:7(middle_name): dtype:-9 (WVARCHAR), prc/scl:40/0, nullbl:1, octet:80, sign:1, radix:0
            Exiting hgopcda, rc=0 at 2013/05/09-18:13:36
            Entered hgopcda at 2013/05/09-18:13:36
            Column:8(last_name): dtype:-9 (WVARCHAR), prc/scl:60/0, nullbl:1, octet:120, sign:1, radix:0
            Exiting hgopcda, rc=0 at 2013/05/09-18:13:36
            Entered hgopcda at 2013/05/09-18:13:36
            Column:9(user_type): dtype:-5 (BIGINT), prc/scl:19/0, nullbl:1, octet:-1, sign:1, radix:10
            Exiting hgopcda, rc=0 at 2013/05/09-18:13:36
            Entered hgopcda at 2013/05/09-18:13:36
            Column:10(locale): dtype:-5 (BIGINT), prc/scl:19/0, nullbl:1, octet:-1, sign:1, radix:10
            Exiting hgopcda, rc=0 at 2013/05/09-18:13:36
            Entered hgopcda at 2013/05/09-18:13:36
            Column:11(lastactivity_date): dtype:93 (TIMESTAMP), prc/scl:26/6, nullbl:1, octet:-1, sign:1, radix:10
            Exiting hgopcda, rc=0 at 2013/05/09-18:13:36
            Entered hgopcda at 2013/05/09-18:13:36
            Column:12(registration_date): dtype:93 (TIMESTAMP), prc/scl:26/6, nullbl:1, octet:-1, sign:1, radix:10
            Exiting hgopcda, rc=0 at 2013/05/09-18:13:36
            Entered hgopcda at 2013/05/09-18:13:36
            Column:13(email): dtype:-9 (WVARCHAR), prc/scl:100/6, nullbl:1, octet:200, sign:1, radix:10
            Exiting hgopcda, rc=0 at 2013/05/09-18:13:36
            Entered hgopcda at 2013/05/09-18:13:36
            Column:14(email_status): dtype:-5 (BIGINT), prc/scl:19/0, nullbl:1, octet:-1, sign:1, radix:10
            Exiting hgopcda, rc=0 at 2013/05/09-18:13:36
            Entered hgopcda at 2013/05/09-18:13:36
            Column:15(receive_email): dtype:-5 (BIGINT), prc/scl:19/0, nullbl:1, octet:-1, sign:1, radix:10
            Exiting hgopcda, rc=0 at 2013/05/09-18:13:36
            Entered hgopcda at 2013/05/09-18:13:36
            Column:16(gender): dtype:-5 (BIGINT), prc/scl:19/0, nullbl:1, octet:-1, sign:1, radix:10
            Exiting hgopcda, rc=0 at 2013/05/09-18:13:36
            Entered hgopcda at 2013/05/09-18:13:36
            Column:17(date_of_birth): dtype:93 (TIMESTAMP), prc/scl:26/6, nullbl:1, octet:-1, sign:1, radix:10
            Exiting hgopcda, rc=0 at 2013/05/09-18:13:36
            Entered hgopcda at 2013/05/09-18:13:36
            Column:18(securitystatus): dtype:-5 (BIGINT), prc/scl:19/0, nullbl:1, octet:-1, sign:1, radix:10
            Exiting hgopcda, rc=0 at 2013/05/09-18:13:36
            The hoada for table dynamo_web.dps_user follows...
            hgodtab, line 1092: Printing hoada @ 033A88D4
            MAX:18, ACTUAL:18, BRC:1, WHT=6 (TABLE_DESCRIBE)
            hoadaMOD bit-values found (0x40:TREAT_AS_NCHAR,0x400:UNICODE_COLUMN,0x20:NEGATIVE_HOADADTY,0x200:TREAT_AS_CHAR)
            DTY NULL-OK LEN MAXBUFLEN PR/SC CST IND MOD NAME
            12 VARCHAR N 80 80 128/ 40 1000 0 440 id
            12 VARCHAR N 200 200 128/100 1000 0 440 login
            -1 LONGVARCHAR Y 0 0 0/ 0 0 0 220 auto_login
            12 VARCHAR Y 70 70 128/ 35 1000 0 440 password
            -1 LONGVARCHAR Y 0 0 0/ 0 0 0 220 member
            12 VARCHAR Y 120 120 128/ 60 1000 0 440 first_name
            12 VARCHAR Y 80 80 128/ 40 1000 0 440 middle_name
            12 VARCHAR Y 120 120 128/ 60 1000 0 440 last_name
            -5 BIGINT Y 8 8 0/ 0 0 0 20 user_type
            -5 BIGINT Y 8 8 0/ 0 0 0 20 locale
            91 DATE Y 16 16 0/ 0 0 0 0 lastactivity_date
            91 DATE Y 16 16 0/ 0 0 0 0 registration_date
            12 VARCHAR Y 200 200 128/100 1000 0 440 email
            -5 BIGINT Y 8 8 0/ 0 0 0 20 email_status
            -5 BIGINT Y 8 8 0/ 0 0 0 20 receive_email
            -5 BIGINT Y 8 8 0/ 0 0 0 20 gender
            91 DATE Y 16 16 0/ 0 0 0 0 date_of_birth
            -5 BIGINT Y 8 8 0/ 0 0 0 20 securitystatus
            Exiting hgodtab, rc=0 at 2013/05/09-18:13:36
            hostmstr: 2048180224:      HOA After hoadtab
            hostmstr: 2048180224:      HOA Before hoadafr
            Entered hgodafr, cursor id 0 at 2013/05/09-18:13:36
            Free hoada @ 033A88D4
            Exiting hgodafr, rc=0 at 2013/05/09-18:13:36
            hostmstr: 2048180224:      HOA After hoadafr
            hostmstr: 2048180224: RPC After Describe Table
            hostmstr: 2048180224: RPC Before SQL Bundling
            hostmstr: 2048180224:      HOA Before hoxpars
            Entered hgopars, cursor id 1 at 2013/05/09-18:13:36
            type:0
            SQL text from hgopars, id=1, len=152 ...
            00: 53454C45 43542022 6964222C 226C6F67 [SELECT "id","log]
            10: 696E222C 22666972 73745F6E 616D6522 [in","first_name"]
            20: 2C226C61 73745F6E 616D6522 2C226C61 [,"last_name","la]
            30: 73746163 74697669 74795F64 61746522 [stactivity_date"]
            40: 2C227265 67697374 72617469 6F6E5F64 [,"registration_d]
            50: 61746522 2C22656D 61696C22 2C22656D [ate","email","em]
            60: 61696C5F 73746174 7573222C 22726563 [ail_status","rec]
            70: 65697665 5F656D61 696C2220 46524F4D [eive_email" FROM]
            80: 20226479 6E616D6F 5F776562 222E2264 [ "dynamo_web"."d]
            90: 70735F75 73657222 [ps_user"]
            Exiting hgopars, rc=0 at 2013/05/09-18:13:56
            hostmstr: 2001973248:      HOA After hoxpars
            hostmstr: 2001973248:      HOA Before hoxopen
            Entered hgoopen, cursor id 1 at 2013/05/09-18:13:56
            hgoopen, line 87: NO hoada to print
            Deferred open until first fetch.
            Exiting hgoopen, rc=0 at 2013/05/09-18:13:56
            hostmstr: 2001973248:      HOA After hoxopen
            hostmstr: 2001973248:      HOA Before hoxdscr
            Entered hgodscr, cursor id 1 at 2013/05/09-18:13:56
            Allocate hoada @ 033A88BC
            Entered hgodscr_process_sellist_description at 2013/05/09-18:13:56
            Entered hgopcda at 2013/05/09-18:13:56
            Column:1(id): dtype:-9 (WVARCHAR), prc/scl:40/0, nullbl:0, octet:80, sign:1, radix:0
            Exiting hgopcda, rc=0 at 2013/05/09-18:13:56
            Entered hgopcda at 2013/05/09-18:13:56
            Column:2(login): dtype:-9 (WVARCHAR), prc/scl:100/0, nullbl:0, octet:200, sign:1, radix:0
            Exiting hgopcda, rc=0 at 2013/05/09-18:13:56
            Entered hgopcda at 2013/05/09-18:13:57
            Column:3(first_name): dtype:-9 (WVARCHAR), prc/scl:60/0, nullbl:1, octet:120, sign:1, radix:0
            Exiting hgopcda, rc=0 at 2013/05/09-18:13:57
            Entered hgopcda at 2013/05/09-18:13:57
            Column:4(last_name): dtype:-9 (WVARCHAR), prc/scl:60/0, nullbl:1, octet:120, sign:1, radix:0
            Exiting hgopcda, rc=0 at 2013/05/09-18:13:57
            Entered hgopcda at 2013/05/09-18:13:57
            Column:5(lastactivity_date): dtype:93 (TIMESTAMP), prc/scl:26/6, nullbl:1, octet:120, sign:1, radix:0
            Exiting hgopcda, rc=0 at 2013/05/09-18:13:57
            Entered hgopcda at 2013/05/09-18:13:57
            Column:6(registration_date): dtype:93 (TIMESTAMP), prc/scl:26/6, nullbl:1, octet:120, sign:1, radix:0
            Exiting hgopcda, rc=0 at 2013/05/09-18:13:57
            Entered hgopcda at 2013/05/09-18:13:57
            Column:7(email): dtype:-9 (WVARCHAR), prc/scl:100/0, nullbl:1, octet:200, sign:1, radix:0
            Exiting hgopcda, rc=0 at 2013/05/09-18:13:57
            Entered hgopcda at 2013/05/09-18:13:57
            Column:8(email_status): dtype:-5 (BIGINT), prc/scl:19/0, nullbl:1, octet:200, sign:1, radix:0
            Exiting hgopcda, rc=0 at 2013/05/09-18:13:57
            Entered hgopcda at 2013/05/09-18:13:57
            Column:9(receive_email): dtype:-5 (BIGINT), prc/scl:19/0, nullbl:1, octet:200, sign:1, radix:0
            Exiting hgopcda, rc=0 at 2013/05/09-18:13:57
            hgodscr, line 464: Printing hoada @ 033A88BC
            MAX:9, ACTUAL:9, BRC:100, WHT=5 (SELECT_LIST)
            hoadaMOD bit-values found (0x40:TREAT_AS_NCHAR,0x400:UNICODE_COLUMN,0x20:NEGATIVE_HOADADTY)
            DTY NULL-OK LEN MAXBUFLEN PR/SC CST IND MOD NAME
            12 VARCHAR N 80 80 128/ 40 1000 0 440 id
            12 VARCHAR N 200 200 128/100 1000 0 440 login
            12 VARCHAR Y 120 120 128/ 60 1000 0 440 first_name
            12 VARCHAR Y 120 120 128/ 60 1000 0 440 last_name
            91 DATE Y 16 16 0/ 0 0 0 0 lastactivity_date
            91 DATE Y 16 16 0/ 0 0 0 0 registration_date
            12 VARCHAR Y 200 200 128/100 1000 0 440 email
            -5 BIGINT Y 8 8 0/ 0 0 0 20 email_status
            -5 BIGINT Y 8 8 0/ 0 0 0 20 receive_email
            Exiting hgodscr, rc=0 at 2013/05/09-18:13:57
            hostmstr: 2001973248:      HOA After hoxdscr
            hostmstr: 2001973248: RPC After SQL Bundling
            hostmstr: 2001973248: RPC Before Fetch Row
            hostmstr: 2001973248:      HOA Before hoaftch
            Entered hgoftch, cursor id 1 at 2013/05/09-18:13:57
            hgoftch, line 133: Printing hoada @ 033A88BC
            MAX:9, ACTUAL:9, BRC:100, WHT=5 (SELECT_LIST)
            hoadaMOD bit-values found (0x40:TREAT_AS_NCHAR,0x400:UNICODE_COLUMN,0x20:NEGATIVE_HOADADTY)
            DTY NULL-OK LEN MAXBUFLEN PR/SC CST IND MOD NAME
            12 VARCHAR N 80 80 128/ 40 1000 0 440 id
            12 VARCHAR N 200 200 128/100 1000 0 440 login
            12 VARCHAR Y 120 120 128/ 60 1000 0 440 first_name
            12 VARCHAR Y 120 120 128/ 60 1000 0 440 last_name
            91 DATE Y 16 16 0/ 0 0 0 0 lastactivity_date
            91 DATE Y 16 16 0/ 0 0 0 0 registration_date
            12 VARCHAR Y 200 200 128/100 1000 0 440 email
            -5 BIGINT Y 8 8 0/ 0 0 0 20 email_status
            -5 BIGINT Y 8 8 0/ 0 0 0 20 receive_email
            Performing delayed open.
            SQLBindCol: column 1, cdatatype: -8, bflsz: 82
            SQLBindCol: column 2, cdatatype: -8, bflsz: 202
            SQLBindCol: column 3, cdatatype: -8, bflsz: 122
            SQLBindCol: column 4, cdatatype: -8, bflsz: 122
            SQLBindCol: column 5, cdatatype: 93, bflsz: 16
            SQLBindCol: column 6, cdatatype: 93, bflsz: 16
            SQLBindCol: column 7, cdatatype: -8, bflsz: 202
            SQLBindCol: column 8, cdatatype: -25, bflsz: 8
            SQLBindCol: column 9, cdatatype: -25, bflsz: 8
            SQLFetch: row: 1, column 1, bflsz: 82, bflar: 14
            SQLFetch: row: 1, column 1, bflsz: 82, bflar: 14, (bfl: 80, mbl: 80)
            SQLFetch: row: 1, column 2, bflsz: 202, bflar: 54
            SQLFetch: row: 1, column 2, bflsz: 202, bflar: 54, (bfl: 200, mbl: 200)
            SQLFetch: row: 1, column 3, bflsz: 122, bflar: 10
            SQLFetch: row: 1, column 3, bflsz: 122, bflar: 10, (bfl: 120, mbl: 120)
            SQLFetch: row: 1, column 4, bflsz: 122, bflar: 8
            SQLFetch: row: 1, column 4, bflsz: 122, bflar: 8, (bfl: 120, mbl: 120)
            SQLFetch: row: 1, column 5, bflsz: 16, bflar: -1
            SQLFetch: row: 1, column 5, bflsz: 16, bflar: SQL_NULL_DATA
            SQLFetch: row: 1, column 6, bflsz: 16, bflar: 16
            SQLFetch: row: 1, column 6, bflsz: 16, bflar: 16, (bfl: 16, mbl: 16)
            SQLFetch: row: 1, column 7, bflsz: 202, bflar: 44
            SQLFetch: row: 1, column 7, bflsz: 202, bflar: 44, (bfl: 200, mbl: 200)
            SQLFetch: row: 1, column 8, bflsz: 8, bflar: 8
            SQLFetch: row: 1, column 8, bflsz: 8, bflar: 8, (bfl: 8, mbl: 8)
            SQLFetch: row: 1, column 9, bflsz: 8, bflar: 8
            SQLFetch: row: 1, column 9, bflsz: 8, bflar: 8, (bfl: 8, mbl: 8)
            SQLFetch: row: 2, column 1, bflsz: 82, bflar: 14
            SQLFetch: row: 2, column 1, bflsz: 82, bflar: 14, (bfl: 0, mbl: 80)
            .....

            (I have clipped it because it is huge, since all rows from the table are brought into Oracle. In fact, the query did not finish yet because the process dg4odbc.exe is busy writing the trace file, I may have to kill it ).

            Thanks a lot,

            Carlos
            • 3. Re: Accessing Postgres from Oracle using DG4ODBC and psqlodbc very slow
              cm*362129*oz
              Hello,

              I have been doing some more testing, and I have discovered something interesting. The where clause is not dropped when the column in the where clause is an number or a date, but it is dropped when it is a varchar (in Postgres the varchar type is actually called "character varying").

              I wonder if the character set has anything to do, although it should not. Both Oracle and Postgres databases are UTF-8.

              Thanks,

              Carlos
              • 4. Re: Accessing Postgres from Oracle using DG4ODBC and psqlodbc very slow
                cm*362129*oz
                Hi,

                I have been digging further into the Unicode stuff, and it looks like that is the reason why varchar columns are being dropped from the where clause.

                First I created a test Postgres database with WIN1252 encoding instead of UTF8. I changed the ODBC connection to use the ANSI Postgres ODBC driver, and bingo, varchar columns where not dropped anymore from the where clause.
                Secondly, I tried to access the original UTF8 database but using the ANSI Postgres ODBC driver instead of the Unicode one, and surprisingly it also works fine, so the problem is the relationship between DG4ODBC and the Unicode Postgres ODBC driver.

                Sadly, I need to use the Unicode one because with the ANSI driver I get errors when retrieving data in Russian or Japanese.

                I have tried to change the HS_NLS_NCHAR init parameter from its default value, UCS2 to SPANISH_SPAIN.AL32UTF8, but I could not. It is still using UC2, and to tell the truth, I do not know whether it should be USC2 or something else. I also changed HS_LANGUAGE to SPANISH_SPAIN.AL32UTF8, but to no effect.

                Thanks,
                Carlos
                • 5. Re: Accessing Postgres from Oracle using DG4ODBC and psqlodbc very slow
                  Mkirtley-Oracle
                  Carlos,
                  Thanks for posting the results of your testing. I'll check further.
                  However, what are the entries in the odbc.ini file for the Postgres connection ? Can you try and set a characterset parameter in that ?

                  Regards,
                  Mike
                  • 6. Re: Accessing Postgres from Oracle using DG4ODBC and psqlodbc very slow
                    cm*362129*oz
                    Hello Mike,

                    Thanks for following up my problem.

                    I have checked the odbc.ini, and there are no parameters for the Postgres DSN. In addition I have checked all the parameters available, and as far as I can see, there is no parameter for the charset.

                    I am glad to tell you that I have found a way to make it work, although I don't like it too much. Yesterday I noticed that varchar columns in Postgres are returned by the gateway+psqlodbc unicode driver as nvarchar. I had already seen that two days ago when I created some views to hide the database links and the need to use double quotes around column names and table names. All varchar2 columns on the views were indeed nvarchar2 columns.

                    I came up now with the idea of trying this:

                    select id, first_name, last_name
                    from dps_user
                    where id = cast('W114077' as nvarchar2(40));

                    And it works. When casting the literal to nvarchar2, the where clause is not dropped and it reaches the Postgres database, where the unique index access is done, and only one row is returned to Oracle.

                    The problem is that having to use a cast for all character literals in where clauses is a pain, specially if the field length has to be specified (we can always specified a maximum, such as 2000). I have checked all the postgres driver parameters, and I do not see one that controls this. Do you know of any in DG4ODBC that could? I do not exactly know what the parameter HS_FDS_MAP_NCHAR is for, but I tried changing it to false (true is the default), and it did not help.

                    By the way, I also tried this, but no joy:

                    create view prueba(id) as
                    select cast("id" as varchar2(40))
                    from "dynamo_web"."dps_user"@pg.es.lladro.com;

                    select *
                    from prueba
                    where id = 'W114077';

                    Thanks a lot for your help,

                    Carlos
                    • 7. Re: Accessing Postgres from Oracle using DG4ODBC and psqlodbc very slow
                      Mkirtley-Oracle
                      Carlos,
                      Thanks for the testing and the information on your workround.
                      Some drivers allow a setting such as -
                      characterset = xxxx
                      but perhaps this is not applicable for Postgres.

                      I'll investigate further and get back to you.

                      Regards,
                      Mike
                      • 8. Re: Accessing Postgres from Oracle using DG4ODBC and psqlodbc very slow
                        cm*362129*oz
                        Hi Mike,

                        I opened a service request with Oracle, and they solved my problem. In fact, the technician's name was Miki. He's not you by any chance, is he?

                        The problem was related to note 1468941.1 in Oracle Support. The cause was a bug on the gateway in the conversion from nchar to char and back, if I understood correctly. I have been told that it will be solved in patchset 11.2.0.4, but for 11.2.0.3 there is the following workaround:

                        1. Please get your DG4ODBC class using this select statement executed as sysdba:

                        select fds_class_name from HS_FDS_CLASS;


                        => you should see a class like: ODBC11.2.0.2.0_0008

                        2. When you now check out the to_nchar capability you'll see it is turned off (=0)

                        select * from hs_class_caps where upper(CAP_DESCRIPTION) like '%NCHAR%' and FDS_CLASS_NAME ='ODBC11.2.0.2.0_0008';


                        => relevant to_char capability is 564:

                        564 TO_NCHAR(op1) 0


                        3. We now need to modify this capability and turn it on:

                        exec DBMS_HS.ALTER_CLASS_CAPS('ODBC11.2.0.2.0_0008' , 564, 'ODBC11.2.0.2.0_0008', 564,131071, NULL, NULL);
                        commit;


                        SIDE NOTE: Please make sure you did this capability update as sysdba user and please do not forget the commit.
                        4. When executing the select from setp 2 again you now should get for the context_translation 131071 instead of 0:

                        564 TO_NCHAR(op1) 131071

                        - change "ODBC11.2.0.2.0_0008" to whatever is returned by your query.

                        The note then says to set these values in the gateway init file -

                        HS_NLS_NCHAR=UCS2 => should already be in the gateway init file
                        HS_LANGUAGE=american_america.al32utf8 => needs to be changed as only in unicode mode the gateway will be able to handle the nchar columns correctly

                        An that's it. The cast() is not necessary anymore.

                        Thanks for your help.

                        Carlos
                        • 9. Re: Accessing Postgres from Oracle using DG4ODBC and psqlodbc very slow
                          Mkirtley-Oracle
                          Hi Carlos,
                          Yes - it was me handled the SR ;-)
                          Sometimes having a bit more time and information to investigate can throw up the answer.

                          Regards,
                          Mike