Pro2 and Bulk load

Posted by goo on 15-May-2019 13:10

11.7

I wonder if this is a correct way of upgrade from 10 to 11 using Pro2:

I have done a setup of Pro2 with latest version, and done a setup of the repl database. I have setup the SQL and connection to that, I have done a testrun of everything, and it seems to work without any errors. So the new system is ready to receive the production database and to do the bulk load.

What I wonder, is : when the restore is done and I have started the databases, can I then start the bulk load and simultanious start the Repl (... -p RunReplProc.p...) at the same time, and leting the users in to the system  as in normal production?

1. shutdown database servers (old system)

2. probkup of the databases (old system)

3. 102b05_dbutil.exe prorest  xxxx xxx.bck

4. 102b05_dbutil.exe xxxx -C truncate bi

5. proutil xxx -C conv1011

6. probkup xxx xxx_new.bck

7. start database servers 

8. start Pro2 process for replication

9. let the users into the system

10. start Pro2 bulkload

I think the backup process and the restore process will be done within an hour, but the bulk-load to SQL will take some hours extra, but if I can start the system while doing the bulk load, it will be ok.

Could it be some locking issues doing like this? 

Should I wait starting the Pro2 process till the bulk load is finished?

All Replies

Posted by gus bjorklund on 15-May-2019 13:42

you did a conv1011

why not do a conv1112 and then you are done

Posted by Valeriy Bashkatov on 15-May-2019 14:05

Hi goo,

Why do you need to update SQL database doing Bulk-Copy again? SQL is a new database?

Posted by Valeriy Bashkatov on 15-May-2019 14:58

[quote user="goo"]

Could it be some locking issues doing like this? 

Should I wait starting the Pro2 process till the bulk load is finished?

[/quote]
I just looked at the templates for MS SQL. Apparently, there are checks for the existence of records in SQL. And it should work without conflict. However, this will not work for Oracle.

Posted by goo on 15-May-2019 15:44

Sorry, yes it is :-) we do all new, even SQL, so that we can have a safe going-back.... if needed

Posted by goo on 15-May-2019 15:46

Gus, we will wait till we know it would be safe... better safe than sorry

Posted by goo on 15-May-2019 15:47

Thanks Valeriy, so then it would be ok doing what I do? Any better solution?

Posted by Valeriy Bashkatov on 16-May-2019 07:40

I think yes. Basically this scenario OK.

For Pro2, it depends from the amount of data. How big are your tables, how many tables, how many records are there? Which version of Pro2SQL are you using?

In Pro2 v5.x there is an аlternative Bulk-Load Option for primary synchronization using a Microsoft SQL Server Linked Server. This option is applicable only for SQL Server database. You can generate SQL script files containing INSERT INTO statements to bulk load data from the source OpenEdge database. This is a faster alternative to running bulk procedures. But as far as I understand, it will not allow to start Pro2 replication processes at the same time.

I haven't personally tried this feature yet, but it looks like it will speed up the initial synchronization process as much as possible. As I said before, it all depends on the amount of data and your availability requirements.

P.S.
FYI. I used another alternative solution but for Oracle, Pentaho Data Integration. It worked many times faster than standard Bulk-Copy in Pro2, and it took from three to ten seconds to process one hundred thousand records. But unfortunately, Pentaho couldn't handle the big data. For example, it fail with an Overflow error when it processed more than two billion records for a single table.

Posted by goo on 16-May-2019 15:08

It’s around 70 tables in each database and it is 2 databases. Around 500 000 to 15 million records in each of 10 tables. The load has been running for two days on the biggest tables... as long as I can run production while doing the load, and that the Pro2 process both load and new, I am ok ...

Sendt fra min iPad

16. mai 2019 kl. 09:42 skrev Valeriy Bashkatov <bounce-Arelav2@community.progress.com>:

Update from Progress Community
Valeriy Bashkatov

I think yes. Basically this scenario OK.

For Pro2, it depends from the amount of data. How big are your tables, how many tables, how many records are there? Which version of Pro2SQL are you using?

In Pro2 v5.x there is an аlternative Bulk-Load Option for primary synchronization using a Microsoft SQL Server Linked Server. This option is applicable only for SQL Server database. You can generate SQL script files containing INSERT INTO statements to bulk load data from the source OpenEdge database. This is a faster alternative to running bulk procedures. But as far as I understand, it will not allow to start Pro2 replication processes at the same time.

I haven't personally tried this feature yet, but it looks like it will speed up the initial synchronization process as much as possible. As I said before, it all depends on the amount of data and your availability requirements.

P.S.
FYI. I used another alternative solution but for Oracle, Pentaho Data Integration. It worked many times faster than standard Bulk-Copy in Pro2, and it took from three to ten seconds to process one hundred thousand records. But unfortunately, Pentaho couldn't handle the big data. For example, it fail with an Overflow error when it processed more than two billion records for a single table.

View online

 

You received this notification because you subscribed to the forum.  To unsubscribe from only this thread, go here.

Flag this post as spam/abuse.

This thread is closed