Pro2 - How to consolidate tables from multiple databases into a single target table? - Progress Community

Loading

Skip to Main Content

Pro2 - How to consolidate tables from multiple databases into a single target table?

Is it possible to consolidate the data from multiple source databases into identical databases in the target database while being able to identify from which source database the data originated?

May 27, 2020
Knowledge

Title

Pro2 - How to consolidate tables from multiple databases into a single target table?

URL Name

pro2-how-to-consolidate-tables-from-multiple-databases-into-a-single-target-table

Article Number

000155808

Environment

Product: OpenEdge Pro2 Version: 4.x, 5.x, 6.x OS: All Supported Platforms

Question/Problem Description

Is it possible to consolidate the data from multiple source databases into identical databases in the target database while being able to identify from which source database the data originated?

For instance, let's say the users need to be able to query data from all regions, yet be able to filter out specific regions.

Steps to Reproduce

Clarifying Information

Error Message

Defect Number

Enhancement Number

Cause

Resolution

There are a couple ways to consolidate data from multiple source databases into one target table.

The PRROWID_ADD_DB Property:

The PRROWID_ADD_DB property, once implemented allows editing of the templates for the processor libraries that carry out replication for both bulk loads and regular replication.

You simply change the value of the PRROWID_ADD_DB property (on the properties tab of Pro2 Administration Tool or Pro2Web) and the feature will automatically prepend the source database name to the ROWID value stored in the prrowid field of the target record.

You can also customize the string that is prepended to the ROWID by editing the templates for the bulk load and replication processor libraries (below).

Templates:

repl_tmpl/mreplproc.p
repl_tmpl/replproc.p

To do this, simply search for PRROWID_ADD_DB in each of these programs and modify the statement after the "THEN" to indicate what string you would like to prepend to the rowid in order to identify the source of the data.

While this procedure is running you are connected to the source and target databases, so you can make use of your own logic to identify the source to customize the below statement in both templates:

ASSIGN strRecordID = (IF "<<PRROWID_ADD_DB>>" = "YES"
                          THEN UPPER(PDBNAME("<<SOURCE_DB>>")) ELSE "") + 
                       STRING(ROWID(bfrSrc<<SRC_TABLE25>>)).

NOTE: After enabling this feature you MUST:

Re-generate (and recompile if you're running compiled processor libraries - Best Practice) the Processor Libraries

To generate processor libraries

From Pro2 Admin tool, select "Tools >> Generate Code >> Processor Libraries"
From Pro2Web,

Select Manage Replication
Select the source database
On the Edit Replication dialog click Next
Select Generate Code, then de-select "Replication Triggers" and click "Generate Code"

Truncate the target SQL target tables
Run a Bulk-Copy for the initial creation of the records in the target using the new prrowid values

Changing Table Ownership:

Another way to accomplish this is to create the target table with a different owner for each source database.

e.g.

Instead of dbo.customerInfo...

usa.customerInfo
eng.customerInfo
hyd.customerInfo

Then the users can create queries qualified with the owner.

Workaround

Notes

References to Other Documentation:

Pro2 Documentation:
https://docs.progress.com/bundle/openedge-pro2-web-ui/page/Properties.html

Keyword Phrase

Last Modified Date

11/13/2020 6:50 AM

Drop Files

Upload FilesOr drop files

Disclaimer

The origins of the information on this site may be internal or external to Progress Software Corporation (“Progress”). Progress Software Corporation makes all reasonable efforts to verify this information. However, the information provided is for your information only. Progress Software Corporation makes no explicit or implied claims to the validity of this information.

Any sample code provided on this site is not supported under any Progress support program or service. The sample code is provided on an "AS IS" basis. Progress makes no warranties, express or implied, and disclaims all implied warranties including, without limitation, the implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample code is borne by the user. In no event shall Progress, its employees, or anyone else involved in the creation, production, or delivery of the code be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample code, even if Progress has been advised of the possibility of such damages.

Loading

Pro2 - How to consolidate tables from multiple databases into a single target table? - Progress Community