14 Replies Latest reply on Jan 31, 2012 5:56 AM by Kgronau-Oracle

    DBMS_HS_PARALLEL.LOAD_TABLE

    913153
      Hi

      I am trying to load a sybase table in oracle using DBMS_HS_PARALLEL.LOAD_TABLE, passing following script:

      declare
      row_count number;
      begin
      DBMS_HS_PARALLEL.LOAD_TABLE ('TABLE_A', 'TEST_DBLINK.WORLD', 'STG_TABLE_A', TRUE , 4, row_count);
      end;

      I am getting following error

      Error report:
      ORA-24278: remote table TABLE_A not found
      ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
      ORA-06512: at "SYS.DBMS_HS_PARALLEL_METADATA", line 52
      ORA-06512: at "SYS.DBMS_HS_PARALLEL", line 2261
      ORA-06512: at line 4

      However, following command works fine :

      select * from "TABLE_A"@TEST_DBLINK.WORLD;

      Please can some one help, where I am going wrong and where to look for problems?

      Thanks
        • 1. Re: DBMS_HS_PARALLEL.LOAD_TABLE
          Kgronau-Oracle
          you need to specify the owner of the table in front of the table name.

          Parallel load package is using a different method then the select is using. So assuming the table belongs to the TEST user then you need to use:
          DBMS_HS_PARALLEL.LOAD_TABLE ('TEST.TABLE_A', 'TEST_DBLINK.WORLD', 'STG_TABLE_A', TRUE , 4, row_count);

          As a cross check you can use:
          select * from "TEST"."TABLE_A"@TEST_DBLINK.WORLD;


          BTW,
          Which gateway are you using?
          • 2. Re: DBMS_HS_PARALLEL.LOAD_TABLE
            913153
            I tried using following as well but did not work:

            declare
            row_count number;
            begin
            DBMS_HS_PARALLEL.LOAD_TABLE ('dbo.TABLE_A', 'TEST_DBLINK.WORLD', 'STG_TABLE_A', TRUE , 4, row_count);
            end;



            I am using heterogeneous services Transparent Gateway for sybase.

            Thanks
            • 3. Re: DBMS_HS_PARALLEL.LOAD_TABLE
              Kgronau-Oracle
              Sybase has case sensitive names - so you have to surround the object names by double quotes to preserver the case like:

              DBMS_HS_PARALLEL.LOAD_TABLE ('"dbo".TABLE_A', 'TEST_DBLINK.WORLD', 'STG_TABLE_A', TRUE , 4, row_count);
              • 4. Re: DBMS_HS_PARALLEL.LOAD_TABLE
                913153
                Thanks for pointing that out, I put "dbo" now I get following:

                Error report:
                ORA-02050: transaction 1.10.43445 rolled back, some remote DBs may be in-doubt
                ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
                trying all tdps, ignoring failure
                ORA-02063: preceding 2 lines from TEST_DBLINK
                ORA-06512: at "SYS.DBMS_HS_PARALLEL", line 1043
                ORA-06512: at line 4
                02050. 00000 - "transaction %s rolled back, some remote DBs may be in-doubt"
                *Cause:    network or remote failure in 2PC.
                *Action:   Notify operations; remote DBs will automatically re-sync when the
                failure is repaired.

                strange thing is select * from "TABLE_A"@TEST_DBLINK.WORLD working fine... any clue pls?
                • 5. Re: DBMS_HS_PARALLEL.LOAD_TABLE
                  Kgronau-Oracle
                  To be able to use the parallel load option you need to make sure your gateway is configured to support distributed transactions. Configuring the gateway to be able to participate in distributed transactions requires additional steps on the Sybase database server. From the error message you posted it looks likethe gateway isn't configured correctly.

                  Have a look at this My Oracle Support note: How to Setup DG4SYBS to Work with Distributed Transactions          [Document 1370391.1]     
                  It describes how to create the recovery user and the HS_TRANSACTION_LOG table and also how to test the distributed transaction set up as well as how to get rid of pending transactions.
                  • 6. Re: DBMS_HS_PARALLEL.LOAD_TABLE
                    913153
                    The thing is I don't need to commit anything on sybase. I only need to fetch data from sybase and store in oracle.

                    gateway is working ok because it works fine in select. do you still think there might be problem with gateway setup?

                    where is that document which you pointed? pls could you give link?

                    Regards
                    • 7. Re: DBMS_HS_PARALLEL.LOAD_TABLE
                      Kgronau-Oracle
                      https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=HOWTO&id=1370391.1

                      The gateway needs the distributed set up when using it with parallel load - that's for example why it won't work with Database gateway for ODBC.

                      Edited by: kgronau on Jan 25, 2012 1:47 PM
                      • 8. Re: DBMS_HS_PARALLEL.LOAD_TABLE
                        913153
                        Jus now checked with DBAs, we have HS_TRANSACTION_LOG and recovery account created on sybase.
                        • 9. Re: DBMS_HS_PARALLEL.LOAD_TABLE
                          Kgronau-Oracle
                          in the note mentioned above there's also a short test to verify the 2PC configuration. Does it work?
                          • 10. Re: DBMS_HS_PARALLEL.LOAD_TABLE
                            913153
                            Can't access the link you provided: it says "Failure of server APACHE bridge:"
                            • 11. Re: DBMS_HS_PARALLEL.LOAD_TABLE
                              913153
                              Now I get following msg, what does that mean?

                              Error report:
                              ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
                              [Transparent gateway for SYBASE]
                              ORA-02063: preceding 2 lines from TEST_DBLINK
                              ORA-06512: at "SYS.DBMS_HS_PARALLEL", line 50
                              ORA-06512: at "SYS.DBMS_HS_PARALLEL", line 692
                              ORA-06512: at "SYS.DBMS_HS_PARALLEL", line 1198
                              ORA-06512: at line 4
                              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.

                              Edited by: 910150 on 25-Jan-2012 05:22
                              • 12. Re: DBMS_HS_PARALLEL.LOAD_TABLE
                                Kgronau-Oracle
                                need to see a gateway trace (or at least 4) because of the parallel load config. Please set HS_FDS_TRACE_LEVEL=25 in the gateway config file and then try reexecuting the parallel load.

                                Upload the gateway traces to http://dl.dropbox.com instead of uploading them to the thread.
                                • 13. Re: DBMS_HS_PARALLEL.LOAD_TABLE
                                  913153
                                  Hi kgronau

                                  I have set HS_FDS_TRACE_LEVEL = ON and run the load. However, I have not got trace copy yet by DBA team.

                                  I have noticed one thing when I run datapump on small tables it works fine and it fails when I try to load big tables having 5 million rows. are you able to figure out what it could be and when should I look?

                                  Thanks
                                  • 14. Re: DBMS_HS_PARALLEL.LOAD_TABLE
                                    Kgronau-Oracle
                                    to avoid trial and error approaches in your case it would be best to file a service request and to provide the testcase so that support can look into it and analyze the problem.