Oracle Application Integration Architecture Foundation Pack XREF Migration Utility

About the 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 (on Windows, aiaenv.bat) script.

  1. For Linux (bash shell)
    source <AIA_HOME>/aia_instances/<aia_instance>/bin/
  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. (Updated 28-MAY-2015, includes Patch 19161682).  MD5 Hash
  2. Backup XREF files, update XREFs with multi-table metadata, and generate migration SQL scripts using setupMigration.xml.
    • Edit SOAFP_XREFMigrationUtility/XREFMigrationUtility/ to set paths.

      Property Description
      xref.utility Path to XREFMigrationUtility 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 source code and XREFMigrationUtility.jws project are also provided, if further customization is necessary.

    • Run setupMigration.xml.
      ant –f SOAFP_XREFMigrationUtility/XREFMigrationUtility/setupMigration.xml
  1. 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
  2. Run the generated script XREF_MIGRATION.sql, a wrapper script that invokes the following scripts in order:
Script Purpose
createXREFTables.sql Creates multi-tables as well as XREF_TABLE_LKP, a lookup table mapping XREF names to their (shortened) database table names
xrefTableNameMapping.sql Populates XREF_TABLE_LKP table with seed data
xrefMigrationSetup.sql Creates XREF_DATA_ANOMALIES, a table for holding 1-to-many rows during migration; XREF_MIGRATED_TABLES, for tracking successfully migrated tables; XREF_MONITORING, for monitoring progress during migration; XREF_TABLE_NAME_IDX1, a temporary index on the XREF_TABLE_NAME column of XREF_DATA table
xrefMonitoringSeedData.sql Seeds XREF_MONITORING table with table names
dataMigrationProcedures.sql PL/SQL procedures to help with migration
Individual migration scripts for each XREF, e.g. 48_migrate_ITEM_ID.sql Migrates data from XREF_DATA to the corresponding multi-table
createXREFIndexes.sql Creates indexes for each multi-table

Verify that XREF_DATA and the multi-tables have the same number of distinct row numbers, post-migration

xrefMigrationSummary.sql Migration summary

  1. Track progress of migration via XREF_MONITORING table:
Column name  Purpose
XREF_TABLE_NAME XREF artifact name
TOTAL_NUM_REGULAR_ROWS Number of rows in XREF_DATA for this artifact that do not fall into the 1-to-many case
NUM_MIGRATED_REGULAR_ROWS Number of "regular" rows that have already been migrated to the corresponding multi-table
TOTAL_NUM_MULTI_ROWS Number of rows in XREF_DATA for this artifact that fall under the 1-to-many case
NUM_MIGRATED_MULTI_ROWS Number of 1-to-many rows that have already been migrated to the corresponding multi-table
STARTED_PROCESSING Times at which migration for this artifact began
FETCH_ANOMALIES_START Time at which the query for 1-to-many rows began
FETCH_ANOMALIES Time it took to query for 1-to-many rows
BULK_MIGRATE_START Time at which migration of "regular" rows began
BULK_MIGRATE Time it took to migrate "regular" rows
PROCESS_ANOMALIES_START Time at which migration of 1-to-many rows began
PROCESS_ANOMALIES Time it took to migrate 1-to-many rows
HAD_EXCEPTION Non-null if exception occurred while processing the migration of this artifact

  1. 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
  1. Run xrefMigrationCleanup.sql
Left Curve
Popular Downloads
Right Curve
Untitled Document
Left Curve
More Middleware Downloads
Right Curve