Use second-pass replication

Set up second-pass replication as audit

Second-pass replication creates a copy of the source table in the second-pass database and, inserts a record into this table each time there is a change in the source table (for UPDATE, DELETE, INSERT).

A new record is created in the target (or staging) table every time there is a change in the table, regardless of whether the change happens to the same field. The replicated table holds a new record for every change in the source table. A new property, SECOND_PASS, is added to the predefs.i file. This property supports second-pass replication and the default value of this parameter is set to NO. To start Second Pass Replication for a setup, this property should be set to YES for that specific setup.

To set up second-pass replication:

1. Create a copy of the Pro2 setup directory and rename it, appending it with SecondPass. For example, if your setup is in the folder Pro2, copy paste that folder and rename it to Pro2_SecondPass.
   Note: Copy all folders from the Pro2 setup directory to the SecondPass folder except for the db and Pro2Web(PAS Instance)folders.
2. Create a new folder named db in the newly created SecondPass folder. This db folder is created for the SecondPass target database.
3. Open a Proenv window and enter cd C:\Pro2_SecondPass\db to navigate to the db folder, for example, cd C:\Pro2_SecondPass\db
4. Create an empty target database from the Proenv window. Enter prodb target database name empty, for example, prodb secondtgt empty.
5. Start the target database from the Proenv window. Enter proserve target database name -S port number for example, proserve secondtgt -S 6677.
6. Load the schema generated from first pass into the target database. In the Proenv window enter prowin target database name, for example, prowin secondtgt.

   The Procedure Editor window appears.

7. Navigate to Tools > Data Administration.

   The Data Administration window appears.

8. Navigate to Admin > Load Data and Definitions > Data Definitions (.df file).

   The Load Data Definitions window appears.

9. Select the schema file as the input file, for example, Sports-tgt.df. Click OK.

   A success message appears after the load is complete.

10. Navigate to C:\Pro2 second pass folder\bprepl\Scripts folder, for example, C:\Pro2_SecondPass\bprepl\Scripts.
11. Right-click the Pro2 - Editor tool and select Properties.

    The Pro2 - Editor Properties window appears.

12. On the Shortcut tab, edit the Start in field to reflect the Pro2 second pass folder, for example, C:\Pro2_SecondPass.
    Note: You can use the same steps for the runbulkloader utility if the you need to do second pass bulk load.
13. Edit the Pro2_env.bat file to set the value of the Pro2 setup location to the C:\Pro2_SecondPass directory.
14. Open the replProc.pf file from C:/Pro2_SecondPass/bprepl/scripts folder and ensure that the rep and Pro2 database section to point to the first pass setup.
15. Save the contents of the replProc.pf file.
16. Edit your database's .pf file, and modify the target databases section to point to the database in the SecondPass directory. Save the contents of the .pf file. For example:
    
    
    
    Note: If you use a Microsoft SQL Server or Oracle database, the name of the second pass schema holder is the same as the first pass and should reference a schema holder database that points to the new SecondPass target database.
17. From C:/Pro2_SecondPass, open the predefs.i file and change the value for SECOND_PASS to YES.
18. Set the first pass replication thread's disposition to mark as applied.
    Note: This preserves the queue records so that the can be replicated on the second pass. As a consequence, you must run manual purges on the replqueue table.
19. From C:/Pro2_SecondPass/bprepl/repl_tmpl, rename the process library template name in second-pass setup directory.
    • For a WAN setup, rename the file tmpl_replproc_4audit.pto tmpl_asTgtDb.p.
    • For a LAN setup, rename the file tmpl_replproc_4SECOND_PASS.p to tmpl_replproc.p or current replication template.
20. Navigate to the Pro2 web interface and select Properties. Edit the WRI_TRIG_TEMPLATE to reflect tplt_repltrig-raw.p as Value. Click Save.
21. Navigate back to the Properties tab. Edit the DEL_TRIG_TEMPLATE to reflect tplt_repltrig-raw.p as the Value. Click Save.
22. From the \Pro2_secondpass\bprepl\scripts folder, double click the batchgen_GEN_PROCS.bat file to generate code.
23. Create and start the second pass replication threads from the c:\Pro2_SecondPass\bprepl\scripts folder.
    Note: Alternatively, you can create second pass replication threads and control information by loading the replbasev610-2ndPass.ini file present in the Pro2 folder.

Set up second-pass replication non-audit

To setup second pass replication as non-audit, you follow similar steps as first pass replication. The second pass functions exactly the same as first pass in that when the records on the source database are modified, the changes are replicated to the first pass target, and then replicated to the second pass target. This method of replication always overwrites the existing record instead of creating new records.

Additionally, you can reuse the same template from the first pass replication for the second pass as well for the replication write/delete trigger, and bulk copy/processor library templates. To bulk load the records to the second pass target, use the C:\Pro2_secondpass\bprepl\scripts\RunBulkLoader shortcut.