Creating the Study Design Replication Packages

The following table lists the scripts that you must run if you are using:

• Standard replication to replicate study-specific designs

• Symmetric replication to replicate data related to study design, but not specific to a study

These scripts, which are located in the RXC_INSTALL directory, create the study design replication packages required by standard and symmetric replication.

Table 12-4 Scripts Required to Use Standard Replication for Replicating Study Design

Script Name	You Run this Script …
RXASRALL.SQL	At every location/for every location. The RXASRALL.SQL script automatically calls the following scripts: RXAOCLRP.SQL RXARELRP.SQL RXASTMRP.SQL
RXASRAVW_CUSTOM.SQL	At every location/for every location; rename to RXASRAVW.SQL.
RXASVSRA.SQL	At each location.
DYNA_RXAPKIRP.SQL	At each location, for each remote location from which data will be retrieved.

For more information , see:

• Using the rxasrall.sql Script
  
• Creating the Views and Synonyms Required to Replicate Study Design
  
• Creating the Package for Replicating Investigators and Sites
  

Using the rxasrall.sql Script

The rxasrall.sql script is the driver script that automatically calls the following three scripts to create the study design replication packages:

• rxaoclrp.sql

• rxarelrp.sql

• rxastmrp.sql

The rxasrall.sql script accepts the name of the remote (that is, shared) database and the name of the database link to that database, and then passes the information to the other three scripts. The study design replication packages replicate all the study-specific design details for the selected study as well as study design supporting information.

For more information , see:

• About Running the rxasrall.sql Script for Each Location
  
• Running the rxasrall.sql Script
  

About Running the rxasrall.sql Script for Each Location

You run the rxasrall.sql script in each location, once for each of the remote (shared) locations in the installation. For example, if you have databases A, B, and C, you need to run the rxasrall.sql script six times:

• First, you run the script on database A passing in the database name and database link for database B, and then you run the script again on database A passing in the database name and database link for database C.

• Next, you run the script on database B passing in the database name and database link for database A, and then you run the script again on database B passing in the database name and database link for database C.

• Finally, you run the script on database C passing in the database name and database link for database A, and then you run the script again on database C passing in the database name and database link for database B.

Running the rxasrall.sql Script

To run the rxasrall.sql script:

1. Change to the RXC_INSTALL directory.

2. Connect to SQL*Plus as RXA_DES.

3. Run the rxasrall.sql script in each location, once for each of the shared locations in the installation:

   start rxasrall
   Name of the Remote Oracle Clinical Database: location_code
   Name of db Link to Remote Oracle Clinical Database Name: db_link
   

   where:

   • location_code is the identifying code stored for this database in the SOURCE LOCATION CODE reference codelist. If the location_code has spaces, replace them with underscores.

   • db_link is the database link stored for this database in the PUBLIC_DB_LINKS codelist.

4. Exit from SQL*Plus.

Creating the Views and Synonyms Required to Replicate Study Design

The rxasravw_custom.sql script creates the ALL_name views on the design tables, and the AVAILABLE_CLINICAL_STUDIES view. The rxasvsra.sql script creates synonyms to all the views.

You need to run these scripts if you are using:

• Standard replication to replicate study-specific designs

• Symmetric replication to replicate data related to study design, but not specific to a study

To create the views and synonyms required for study design replication:

1. Copy the rxasravw_custom.sql script to the rxasravw.sql script, overwriting the default version. (The default version is for non-replicated installations only.) The custom version creates the views required for design replication.

   The rxasravw.sql script assumes there are two locations remote from any given installation (total of three locations in your replication installation).

   • If you have three or fewer locations, you can use the script as is. Copy it to each of the other one or two locations and proceed to Step 2.

   • If you have more than three locations edit rxasravw.sql, adding a clause for each remote location. If your installation has n locations, your script should contain n-1 clauses. Copy your edited script to each location.

   Note:

   If you have more than one environment in the same code area, save more than one copy of rxasravw.sql with a name that indicates the environment to which it applies. For example, rxasravw_prod for your production database.

2. Connect to SQL*Plus as RXA_DES.

3. Run the rxasravw.sql script at each location. For example:

   Name of the Current Oracle Clinical Database: example_mexico
   Name of Remote Oracle Clinical Database 1: example1_jersey
   Name of db Link to Remote Oracle Clinical Database 1: to_example1
   Name of Remote Oracle Clinical Database 2: example2_york
   Name of db Link to Remote Oracle Clinical Database 2: to_example2
   

   You do not need to replace spaces in the location codes with underscores.

4. Run the rxasvsra.sql script at each location. This script creates synonyms to all the views created by the rxasravw.sql script.

Creating the Package for Replicating Investigators and Sites

The dyna_rxapkirp.sql script creates the REPLINVSITE_remote_location package, which replicates the investigators and sites used in a study from the specified source location.

Only standard replication uses the REPLINVSITE_remote_location package. Therefore, you run the dyna_rxapkirp.sql script only if you are using standard replication.

As RXC_REP, you need to run the dyna_rxapkirp.sql script at each location, for each remote location from which data will be retrieved.

To run the dyna_rxapkirp.sql script:

1. Change to the RXC_INSTALL directory.

2. Connect to SQL*Plus as RXC_REP.

3. Run the dyna_rxapkirp.sql script at each location, once for each of the source locations in the installation:

   start dyna_rxapkirp.sql

   The script prompts for the following information:

   Enter value for source_location: location_code Enter value for link: database_link

   where:

   • location_code is the code for the remote location from which data will be retrieved to the current database. The value you specify should match the identifying code stored for this database in the SOURCE LOCATION CODE reference codelist. Be sure to replace any spaces in the location_code with underscores.

   • database_link is the name of the database link to the source database. The value you specify should match a record in the DB_LINKS reference codelist.

4. Exit from SQL*Plus.