1 2 3 Previous Next 41 Replies Latest reply: Jun 24, 2014 6:31 AM by mxallen-Oracle RSS

    DG4ODBC connectivety to DB2

    Santosh_DBA

      Hi All,

      I am totally new to Oracle and i need help.

      I configured dg4odbc to connect to db2 zos. i am able to run query against db2 table using sqlplus and i can see the results. However, when the odbc application is invoking the results are not populatng on their screen. The application seems to be hunged, however, when I set the trace level to debug I notice that multiple queries are being run and rows returned but these results are not being returned to the application.

       

      i used the following HS parameters, let me know if i am missing something or you need more information to help me.

       

      HS_FDS_CONNECT_INFO = DB2DUD1

      HS_FDS_TRACE_LEVEL = DEBUG

      HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so

      HS_LANGUAGE = AMERICAN_AMERICA.US7ASCII

      HS_FDS_TIMESTAMP_MAPPING =CHAR

      HS_FDS_SQLLEN_INTERPRETATION=32

      HS_NLS_DATE_FORMAT=YYYY-MM-DD

      HS_RPC_FETCH_REBLOCKING=OFF

      HS_FDS_DELAYED_OPEN=TRUE

      HS_RPC_FETCH_SIZE=40000

      HS_FDS_FETCH_ROWS=20000

       

      Thanks

      Santosh

        • 1. Re: DG4ODBC connectivety to DB2
          Mkirtley-Oracle

          Hi Santosh,

              Could you give an example of the type of query you are running ?   If you are using a 'where' clause or functions against columns then it may be that all the data needs to be pulled back to Oracle for post-processing which is a usual cause of queries appearing to hang.  It is just the time taken for the data to be returned and processed on the Oracle side which can be quite long for large tables.

          Do you see the same problem if you set -

          HS_FDS_DELAYED_OPEN=FALSE

           

          Which gateway version are you using and on which platform is it running ? What is the application ?  To start could you try from a SQLPLUS session and check what happens there ?

           

          Regards,

          Mike

          • 2. Re: DG4ODBC connectivety to DB2
            Santosh_DBA

            Hi Mike,

             

            I am using DG4ODBC on Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Linux. The application works fine when its using transparent gateway, but when i switch to use the dg4odbc the application screen hangs.

             

            previously i had HS_FDS_DELAYED_OPEN=TRUE as default, but i changed it later to FALSE based on few articles, but no luck so far.

             

            The application is a VB application that connects to an Oracle database that has synonyms pointing to DB2 and local Oracle tables

             

            The query i used through sqlplus was the one captured in the trace. i get the results populated when running through sqlplus and trace also indicates the query ran and the same number of records fetched, but it does not populate the results leading to a hunged screen. These tables are also pretty small, few thousands and the count on the below query is about 19K

             

            SELECT A1.ORG_ID,A1.ORG_FROM_DT,A1.ORG_THRU_DT,A1.ORG_SHORT_NAME,A1.ORG_LONG_NAME,

            A1.ORG_STABLE_NAME,A1.ORG_TYPE_ID,A1.PARENT_ORG_ID,A1.OFFICE_ID,A1.ELAS_DIVISION_CD,

            A1.ELAS_AGENCY_CD,A1.ELAS_DISTRICT_CD,A1.ORG_MANAGER_IND,A1.CENTER_CD,A1.ORG_START_DT,

            A1.COMMENTS_TXT,A1.ORG_CLOSE_DT,A1.ASU_NUMERIC_CD,A1.ASU_ALPHA_CD,A1.ORG_HQ_CD,A1.CREATION_TSTP,

            A1.UPDATE_TSTP,A1.UPDATE_ID,A1.EQUIPMENT_CODE,A1.ORG_ID,A1.ORG_TYPE_ID,A1.ORG_FROM_DT,

            A1.ORG_THRU_DT,A1.ORG_THRU_DT,A1.ORG_MANAGER_IND,A2.ORG_TYPE_LEVEL_CD,A2.ORG_TYPE_ID

            FROM HROCTST.ORG A1,HROCTST.ORG_HIERARCHY_CODE A2

            WHERE A1.ORG_TYPE_ID=A2.ORG_TYPE_ID AND A1.ORG_ID<>0 AND A1.ORG_FROM_DT<='04/01/2014'

            • 3. Re: DG4ODBC connectivety to DB2
              Kgronau-Oracle

              You might hit post processing. When using the transparent gateway it is/was designed for a dedicated foreign database so it knows how to map certain functions to foreign database equivalents. DG4ODBC instead is the free product which can be used with a suitable ODBC driver to connect to any foreign data store which includes MS Excel files but also DB2 or SQL Server databases. Dur to that wide variety of foreign data stores DG4ODBC only has a limited amount of functions it is able to map to foreign database equivalents. In those cases all records from the remote database will be fetched into the Oracle database and the result is then processed locally in the Oracle database. Depending now on the amount of data in your foreign database as well as the trace level you have set in the gateway that will take a while.

              Especially your statement that you get trace files that grow indicate that there's a lot of data being fetched using DG4ODBC.

               

              Best would be to get a trace from the old gateway and the new gateway to check the select that was executed using the old transparent gateway as well as the new DG4ODBC (explain plan of that statement might work but is less reliable in older Oracle database releases when used with a gateway).

              - Klaus

              • 4. Re: DG4ODBC connectivety to DB2
                Santosh_DBA

                Hi Klaus,

                 

                I had a trace set up for the old gateway and i compared it with DG4ODBC. i noticed that in old gateway the HS_LANGUAGE is AMERICAN_AMERICA.AL32UTF8 where as in DG4ODBC its AMERICAN_AMERICA.US7ASCII.

                 

                when i checked the Oracle database for the NLS parameters i noticed this

                NLS_CHARACTERSET

                US7ASCII

                 

                Will this make any difference?

                 

                I still have to allow the new gateway to run for long to see data returned to the VB application

                 

                Thanks

                Santosh

                • 5. Re: DG4ODBC connectivety to DB2
                  Kgronau-Oracle

                  The HS_LANGUAGE needs to be set to reflect the NLS setting of the foreign database. Depending on the data type the remote columns are mapped to that setting has an impact - so it is worth to change that setting (matching your previous configuration  HS_LANGUAGE=AMERICAN_AMERICA.AL32UTF8) and to test it.

                   

                  - Klaus

                  • 6. Re: DG4ODBC connectivety to DB2
                    Santosh_DBA

                    Hi Klaus,

                    I can't change the HS_Language at this point, however, i have the developer run the query only on a debug tool. what was noticed that even when i had the HS_FDS_TIMESTAMP_MAPPING=CHAR, the ODBC application returned date. when i do a sqlplus, i see the whole timestamp value as CHAR(26) as expected. why is the ODBC returning only date value? i tried to read through the documentation and it talks about data type conversion  "the ODBC data type SQL_TIMESTAMP are converted to Oracle's DATE data type"

                    How do i prevent the conversion or make SQL_TIMESTAMP return timestamp?

                     

                    Thanks

                    Santosh

                    • 7. Re: DG4ODBC connectivety to DB2
                      Kgronau-Oracle

                      Santosh,

                      there is no way to change the data type mapping (except of specifying HS_FDS_TIMESTAMP_MAPPING=CHAR or DATE). The way how ODBC data types are mapped is described in the users guide:

                      Oracle® Database Gateway for ODBC

                      User’s Guide

                      11g Release 2 (11.2)

                      E12070-03

                       

                      Table A–1 (Cont.) Mapping ODBC Data Types to Oracle Data Types

                       

                      and it is the agent that loads this capability into the Oracle database when it registers at the Oracle db. There is no way to change that.

                      - Klaus

                      • 8. Re: DG4ODBC connectivety to DB2
                        Santosh_DBA

                        Thanks Klaus,

                         

                        I will have the application team start a VB trace to determine why they see it as Date even when HS_FDS_TIMESTAMP_MAPPING=CHAR.

                        Also, when i try to make HS_FDS_DATE_MAPPING=CHAR, it still returns DATE. Can I not set it to CHAR?

                         

                        Thanks

                        Santosh

                        • 9. Re: DG4ODBC connectivety to DB2
                          Santosh_DBA

                          Today i had the developer rerun the VB application and i turned on the trace. After 30 mins, there was no more entry in the log file with the below message as the last "Exiting hgoftch, rc=1403 at 2014/05/02-08:34:56" does it mean some error? the application was still hanging.

                          What can i check to know why the application was hung?

                           

                          Thanks

                          Santosh

                          • 10. Re: DG4ODBC connectivety to DB2
                            Kgronau-Oracle

                            Looks like you have set the trace level to ON only. To get an idea what is happening set it to DEBUG - here you will also get more details about the fetches (trace level ON will only collect basic information like the statements and their return codes handled by the gateway).

                            - Klaus

                            • 11. Re: DG4ODBC connectivety to DB2
                              Santosh_DBA

                              Hi Klaus,

                               

                              The trace was run using DEBUG mode. the fetches seem to have run fine and after a while i saw that final entry in the log, but, the application still continued to hang. Any input on what that message means? "Exiting hgoftch, rc=1403 at 2014/05/02-08:34:56"

                              also, can you let me know on setting HS_FDS_DATE_MAPPING=CHAR? i tried this and even after loggin back in, it still reflect DATE.

                               

                              Thanks

                              Santosh

                              • 12. Re: DG4ODBC connectivety to DB2
                                Kgronau-Oracle

                                Santosh,

                                The message you see here "Exiting hgoftch, rc=1403 at 2014/05/02-08:34:56" just means the gateway has finished fetching all rows from your remote table and passed that information to the Oracle database. So there is something wrong in the application or the Oracle database.

                                 

                                Regarding the date/timestamp mapping: When you do a describe of your remote table in SQL*Plus, what's the output?

                                • 13. Re: DG4ODBC connectivety to DB2
                                  1351428

                                  Santosh,

                                   

                                  Can you share how you configured the DG4ODBC to connect to the DB2 database/?

                                   

                                  Thanks,

                                  -Hari

                                  • 14. Re: DG4ODBC connectivety to DB2
                                    Mkirtley-Oracle

                                    Santosh,

                                         What is the SQL shown in the trace just before it starts fetching the data ?  Does it include the any 'where' clauses ? It should be the SQL shown in the last entry "Entered hgopars" before the first fetch statements that begin "Entered hgoftch".
                                    Is there more than one set of fetches ? It may be that all the data is being returned to Oracle and then being post-processed to satisfy the 'where' conditions in the SQL issued in Oracle.
                                    Are the traces the same for a select run from SQLPLUS and from the VB application ?  If they are the same then it looks like an application problem of some sort.

                                     

                                    Regards,

                                    Mike

                                    1 2 3 Previous Next