Oracle Application Integration Architecture Foundation Pack XREF Migration Utility

Foundation Pack’s XREF Migration Utility is a tool to perform a one-time migration of cross reference (XREF) data from the existing single XREF_DATA table to new multi-tables, with one table per XREF artifact.

If you work with large XREF tables, splitting up the single XREF_DATA table into one table per XREF artifact improves cross reference lookup performance. The XREF Migration Utility updates the metadata in the XREF artifacts for this multi-table use case, as well as generates a set of scripts that create a new table for each artifact and migrate the corresponding XREF data to them.

This document includes instructions related specifically to the Foundation Pack XREF Migration Utility. For more details about the multi-table use case and the utility’s various components, refer to the documentation available within the XREFMigrationUtility directory of the extracted zip file.

XREF Migration Utility Requirements

XREF Migration Utility requires that the Java classpath and Ant home are set prior to running.

If AIA Foundation Pack is installed, the environment variables can be set via the aiaenv.sh (on Windows, aiaenv.bat) script.

1. For Linux (bash shell)
   source <AIA_HOME>/aia_instances/<aia_instance>/bin/aiaenv.sh
2. For Windows
   Navigate to aiaenv.bat located in <AIA_HOME>\aia_instances\<aia_instance>\bin
   Run aiaenv.bat

If AIA Foundation Pack is not installed, the following environment variables need to be set:

1. JAVA_HOME: your Java environment  
2. ANT_HOME: the Ant directory  
3. PATH: append ${ANT_HOME}/bin (Unix) or %ANT_HOME%/bin (Windows)

Running the XREF Migration Utility

1. Extract the XREF Migration Utility zip.
2. Backup XREF files, update XREFs with multi-table metadata, and generate migration SQL scripts using setupMigration.xml.
    
   • Edit SOAFP_XREFMigrationUtility/XREFMigrationUtility/setupMigration.properties to set paths.
     
     

     Property	Description
     xref.utility	Path to XREFMigrationUtility directory
     xref.directory	Path to directory containing XREF artifacts
     xref.output	Output directory to hold the generated scripts

   
   
   • Optional: edit migration and MDS-related parameters
     
     

     Property	Description
     xref.track.rows	Specify “false” to not track the number of migrated rows during migration; by default, tracking is on
     xref.bulk.collect.size	Bulk collect chunk size; default is 100
     xref.commit.freq	Frequency of commits (i.e. commit after migrating N chunks); default is 10
     xref.oramds.loc	Mapping of XREF name to full XREF_TABLE_NAME in XREF_DATA; by default, XREF_NAME maps to oramds:/apps/AIAMetaData/xref/XREF_NAME.xref

     
     Advanced usage: the XREFMigrationUtility.java source code and XREFMigrationUtility.jws project are also provided, if further customization is necessary.
   
   
   • Run setupMigration.xml.
     ant –f SOAFP_XREFMigrationUtility/XREFMigrationUtility/setupMigration.xml

3. Optional: modify/customize the generated SQL scripts
   • Change the table names of the multi-tables that will be created
     • Modify createXREFTables.sql, xrefTableNameMapping.sql, the corresponding multi-table migration scripts, and createXREFIndexes.sql
     • See documentation for example
   • Modify createXREFTables.sql to add storage clauses, partitioning features, etc prior to the tables being created
4. Run the generated script XREF_MIGRATION.sql, a wrapper script that invokes the following scripts in order:

5. Track progress of migration via XREF_MONITORING table:

6. After migration, check summary output to see if all tables have been migrated
   • If not, look at HAD_EXCEPTION column in XREF_MONITORING
   • Re-run individual migration scripts for tables that had exceptions

7. Run xrefMigrationCleanup.sql