Replicate with Pro2

Pro2 replication configuration process

1. Set up the global settings for the replication instance.
2. Add the source database details.
3. Add the target database details.
4. Generate target schema file.
5. Load the target database file.
6. Map the tables in the source database to the target database.
7. Generating the replication code based on your configuration.

Set up global settings for the replication instance

Use the following procedure to configure the global settings:

1. From the Pro2 web user interface, navigate to the Manage Replication Tab.
2. Click New. The Create Replication window appears.
3. Set up global settings for the replication instance. For example, Source DB Mode is set to Triggers and the Source DB Connection is set to LAN.
4. Click Next. The Select Source tab appears.

Add the source database details

As you might assume, the source database is where your replication will originate from. The replicated data flows from the source database to the target database, so it is absolutely essential to add your source database details correctly.

Use the following procedure to add source database details:

1. Enter Source DB Name (for example, sports).
2. Enter Host Name aslocalhost.
3. Enter the port number in the Host Port/Service box (for example, 2233).
4. (Optional) EnterUser Name and Password.
   Note: Enter user name and password only if it is required by your source database.
5. Select Test Connection. If the test is successful, move on to set the target database. Review your source database details to ensure they are correct.
6. Click Next. The Set Target tab appears.

Add the target database details

The target database, or databases, is where your replicated data flows to when a replication is initiated.

Use the following procedure to add target database details:

1. Enter the Target Database Name.
2. Select Target Database Type. Choose from either Microsoft SQL Server, Oracle, or OpenEdge databases.
3. Designate the DB Host Name as localhost.
4. Enter the port number in the DB Port/Service box (for example, 3344).
5. (Optional) Enter User Name and Password.
   Note: Enter user name and password only if it is required by your target database.
6. Click Next. The Generate Target Schema tab appears.

Generate target schema file

By generating the target schema file you load the target database with files necessary to build out the target database. Pro2 reads the source database schema and generates scripts that are then executed on the target database server to create the target schema. By default, all tables and fields are created in the target. The rest of the files (drop.sql, index.sql, trunc.sql, errors.log, and warnings.log) are created separately. You can execute these files as you see fit. Typically, tables which are not replicated remain unmapped.

Perform the following steps to generate target schema file:

1. Select one or more of the following checkboxes:
   Note: These options are available only when the target database is an OpenEdge database. In case, the target database is MSS SQL or Oracle, these options are not available.
   • Preserve Area: Preserves the source database data definition files and copy’s them to the target database.
   • Generate Pro2 Target Fields: Generates Pro2 specific target fields in your target database. Doing so avoids unnecessary replications of source schemas.
   • Generate Index as Inactive: Deactivates the existing active index.
2. Click Generate Target Schema. A table containing replication job appears.
   Note: This job is not be executed until you run the job runner file. A job runner batch file continuously processes replication jobs off the job queue. This file is placed in the Scripts folder.
3. Navigate to \bprepl\Scripts select the jobrunner.bat file. The Proenv window appears if the file is runs successfully.
4. The status of the replication job changes to complete in the Pro2 web user interface.
5. Click the Download under the Download Schema files column to download the target schema file (sports-tgt.df).

Schema trigger definitions

The following table is an example of the output from the Progress trigger report. This example is of a Customer table in the sports database after Pro2 triggers have been inserted:

Alternatively, these trigger definitions can be displayed by the replTrigDisp.p procedure (to screen) and the replTrigDisp2.p procedure (to file).

Load the target database

After the target schema file is downloaded (sports-tgt.df), load the target database with the appropriate tables required for replication.

Perform the following steps to load the target database:

1. Cut and Paste the target schema file (sports-tgt.df) in the db folder of Pro2.
2. Open Proenv window, type prowin -db target and click Enter.
3. In the Procedure Editor, select Tools > Data Administration.
4. Select Admin > Load Data and Definitions > Data Definitions (.df file)…
5. Add the target schema file (in this case, sports-tgt.df) as Input File and click OK.
6. Navigate to the Pro2 web UI and click Next. The Mapping Source to Target Tables tab appears.

Target side only tables and fields

Mapping information

You must map data fields between your source and target database so that data in the source can be replicated to the target. You can do this from the Mapping dashboard in the UI. The repl database contains all the mapping information for the replication of the databases, tables, and fields between the source and target. This information is stored in the ReplDBXref, ReplTableXref, and ReplFieldXref tables. Mapping information can be saved to and loaded from a text file.

Depending on the number of, schema, primary keys, and foreign keys of the databases, the data sources and the database mapping information has a varying degree of complexity.

Map source tables to target tables and generate code

After you load the target database, click Next, The Mapping Source to Target Tables tab appears.

Perform the following steps to map the tables:

1. Select the tables from the source database that you want to map with the target database. You can map the tables in the following ways:
   • Map—Choose the Map option if you want to map a single source table with a single target table.
   • Automap—Choose the Automap option if you want to map one or more source tables to the corresponding target tables.
   Note: You can also unmap the tables based on your need by the Unmap option.
2. Select the tables to map and click Automap.
3. Click Next. appears.
4. In the Advanced Configuration tab, change thread assignments and other optional control flags if required.
   Note: The Advanced Configuration tab is an optional settings tab. You can set same items on all selected tables in the list in theGlobal Controls section.
5. Click Next. The Generate Code tab appears.
6. Click Generate Code. You will be directed to the Pro2 web user interface Dashboard. Click Pending Jobs watch-box to see which replication jobs are running.
7. Run the replication file to execute the replication jobs. In Windows Explorer, navigate to \bprepl\Scripts.
8. Double click the Replbatch1.bat file. The corresponding replication thread starts running on the Dashboard.
   Note: In a CDC based replication, double click the CDCbatch.bat file.
9. Refresh the Pro2 web user interface to complete the replication configuration process.

Manual replications

Consider an example where you have assigned a city to a customer. You have made this change in the source database. This change needs to be replicated to the target database. Perform the following steps to use Pro2 for replication:

1. In Proenv , navigate to C:\Pro2v60\db.
2. Type prowin -db sports -db repl and click Enter. The Procedure Editor window appears.
3. Assign city to the first customer in source database.

   FIND FIRST customer
   ASSIGN city = “NY”
   

4. Open the Pro2 web UI and click the Total Threads watch-box. You will notice an event type in the Replication thread window. This event will show the recent change made to the source database.
   
   
   
5. Run the replication file by navigating to \bprepl\Scripts .
6. Double click the Replbatch1.bat file to run the replication thread. This action will remove the event type in the replication thread which implies that the replication record is processed by the replication thread and sent to the target database.
   
   
   

Pro2 replication FAQ

When do I need to regenerate code?

You must regenerate the replication code whenever there are mapping changes. However, triggers and bulk load processor code need to be generated only when a new table is added to replication. Replication processor library code needs to be regenerated whenever a table or field is added or removed.

When do I need to update table or field mapping?

The mapping of tables and fields does not update automatically when schema changes are made to either the source or target database. Once the changes are made to the target SQL schema and after the schema holder is updated, you need to update the mapping to map new tables/fields and to delete mapping of removed tables/fields.

When do I need to update the schema holder?

Anytime a change is made to the target SQL database schema the schema holder database needs to be rebuilt or updated.

How do I contact Pro2 Technical Support?

To report a new issue or update an existing issue, login to the Progress SupportLink application at http://progresslink.progress.com/supportlink.

If you do not have a SupportLink login, please register at http://progresslink.progress.com/, or if you need immediate assistance please call us directly.

See the support contact page at http://web.progress.com/en/support/contact-support.html

Replication procedure library

The Replication Procedure Library (ReplLib.p) contains internal procedures and functions that handle general utility functions such as reading from and writing to configuration files, map files, and the Replication Properties and Controls Tables. It is run persistently and added to either the session or procedure SUPER stack.