Replication Using Direct SQL Calls - Forum - OpenEdge Pro2 - Progress Community

Replication Using Direct SQL Calls

 Forum

Replication Using Direct SQL Calls

This question is answered

Hello,

Pro2Oracle v4.6.7

In the Pro2 Replication User Guide says that instead of 4GL calls we have the ability to make direct SQL calls from Pro2 for significantly improve replication performance for tables with extremely high transaction activivty.

To do this the SQL_GEN_PROC property must be set to YES + some additional properties (SQL_INC_DIR, SQL_MASS_LOAD_DIR, SQL_MASSLOAD_TEMPLATE, SQL_PROC_DIR, SQL_PROC_TEMPLATE).

I have added all these properties and regenerate the replication processor library and bulk-copy processor library.

But nothing happened - this libraries for direct SQL calls are not created. Instead are created a standard libraries for 4GL calls.

I test this feature on the sports database as a Source and Oracle database as the Target.

Where I made a mistake and how to solve this problem?

Regards,
Valeriy

Verified Answer
  • Hi Dmitry,

    as to me said in the PTS, that functionality has been disabled, since the results of using the direct SQL procedures reported by Pro2 customers is mixed and that the generated SQL queries required further manual optimization to make them perform at an acceptable rate.

    They registered bug# 63971 that the functionality was removed from the product, but the documentation still details it and needs to be updated.

    Regards,

    Valeriy

All Replies
  • I opened a Case in PTS (#00384643).

  • Valeriy,

    Any updates on Direct SQL Calls ?

    We have pro2sql v4.6.7 and it is not lightning fast. So Direct SQL Calls would be interesting to explore.

    Dmitri Levin

    Alphabroder

  • Hi Dmitry,

    as to me said in the PTS, that functionality has been disabled, since the results of using the direct SQL procedures reported by Pro2 customers is mixed and that the generated SQL queries required further manual optimization to make them perform at an acceptable rate.

    They registered bug# 63971 that the functionality was removed from the product, but the documentation still details it and needs to be updated.

    Regards,

    Valeriy

  • Dmitri Levin

    We have pro2sql v4.6.7 and it is not lightning fast. 

    how you tested it?
    In my tests I use ATM and it shows me a decrease performance by 40-50 %%  from 1200 tps to ~700 tps, and after end of test,  synchronization between Source and Target performs over 40 minutes.
    But I think that the ATM is not a good choice for testing Pro2, as it generates a very high transactional activity that does not correspond to the real application.
  • Hi Valeriy, Thanks for bringing this to our notice. The documentation will be corrected.

    Please also note that there are plans to support Replication using DIRECT SQL calls in the 5.x (x>0) product roadmap.

  • Valeriy, we are running pro2sql in production. So it is not a test.

    The replication process works fine, though we mostly replicate very small tables. Some are medium.

    When I wrote "Not lightning fast" I mean the initial Buck Load process when we have to replicate a table with a couple million records takes many hours. We have a dozen tables with 100 million records, I do not think they are possible to replicate because it will be days or months for Bulk load. Fortunately I do not have a requirement to replicate big tables as of now.

    Dmitri Levin

    Alphabroder