Replicate with Pro2

Main steps: 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 the target schema file.
5. Load the target database file.
6. Map the tables in the source database to the target database.
7. Generate the replication code based on your configuration.

Set up global settings for the replication instance

To configure the global settings:

1. From the Pro2 web 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

The source database is where your replication originates from. The replicated data flows from the source database to the target database, so it is essential to add your source database details correctly.

To add source database details:

1. Enter Source DB Name (for example, sports).
2. Enter Host Name as localhost.
3. Enter the port number in the Host Port/Service field (for example, 2233).
4. (Optional) Enter your User Name and Password.
   Note: Enter your 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 are where your replicated data flows to when a replication is initiated.

To add target database details:

1. Enter the Target Database Name.
2. Select Target Database Type. Choose Microsoft SQL Server, Oracle database, or OpenEdge databases.
3. Set the DB Host Name as localhost.
4. Enter the port number in the DB Port/Service field (for example, 3344).
5. (Optional) Enter your 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 the 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 necessary. Typically, tables that are not replicated remain unmapped.

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. If the target database is Microsoft SQL server, or Oracle database, these options are not available.
   • Preserve Area: Preserves the source database, data definition files and copies 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 that contains a replication job appears.
   Note: Do not execute this job until you run the job runner file. A job runner batch file continuously processes replication jobs from the job queue. This file is in the Scripts folder.
3. Navigate to \bprepl\Scripts and execute the jobrunner.bat file.

   The Proenv window appears if the file ran successfully. The status of the replication job changes to complete in the Pro2 web interface.

4. Click Download under the Download Schema files column, to download the target schema file (sports-tgt.df).

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.

To load the target database:

1. Cut and paste the target schema file (sports-tgt.df) in the db folder of Pro2.
2. Open a 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 the Input File and click OK.
6. Navigate to the Pro2 web interface 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 web interface. 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 schemas, primary keys, and foreign keys of the databases, the data sources and the database mapping information has varying degrees of complexity.

Map source tables to target tables and generate code

To map source tables to target tables and generate code:

1. On the Mapping Source to Target Tables tab, 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 needs using the Unmap option.
2. Select the tables to map, and click Automap click Next.
3. 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 the same items on all selected tables in the list in the Global Controls section.
4. Click Next.

   The Generate Code tab appears.

5. Click Generate Code. You are directed to the Pro2 web interface dashboard. Click the Pending Jobs watch-box to see which replication jobs are running.
6. Run the replication file to execute the replication jobs. In Windows Explorer, navigate to \bprepl\Scripts.
7. 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.
8. Refresh the Pro2 user interface to complete the replication configuration process.

Manual replications

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

1. In a Proenv window, 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 interface and click the Total Threads watch-box. Notice the 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 removes 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 must be generated only when a new table is added to the replication database. The replication processor library code must 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. After the changes are made to the target SQL schema and after the schema holder is updated, you must update the mapping to map new tables or fields and to delete mapping of removed tables or fields.

When do I need to update the schema holder?

Any time a change is made to the target SQL database schema, the schema holder database must be rebuilt or updated.

How do I contact Pro2 Technical Support?

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

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

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