Replication Using Direct SQL Calls

Posted by Valeriy Bashkatov on 24-Jan-2017 06:53

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

OpenEdge Pro2 - Forum
Posted by Valeriy Bashkatov on 28-Feb-2017 05:56

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

Posted by Valeriy Bashkatov on 25-Jan-2017 01:58

I opened a Case in PTS (#00384643).

Posted by Dmitri Levin on 27-Feb-2017 09:33

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.

Posted by Valeriy Bashkatov on 28-Feb-2017 05:56

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

Posted by Valeriy Bashkatov on 28-Feb-2017 06:08

[quote user="Dmitri Levin"]

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

[/quote]
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.

Posted by rkumar on 19-Apr-2017 06:33

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.

Posted by Dmitri Levin on 24-Apr-2017 13:46

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.

This thread is closed