16 Migrating ODI Flows from AIA Foundation Pack 2.4 and 2.5 to AIA Foundation Pack 11g Release 1

This chapter discusses how to migrate Oracle Data Integrator (ODI) flows. After you have set up the environment, the ODI 11g server, and the Oracle 11g database, you will be ready to migrate ODI flows.

This chapter includes the following sections:

• Section 16.1, "Overview of ODI Flow Migration Tasks"

• Section 16.2, "Describing the Source 10g ODI Server"

• Section 16.3, "Describing the Target 11g ODI Server"

• Section 16.4, "Migrating ODI"

• Section 16.5, "Performing Post-Migration Steps"

• Section 16.6, "Handling Cross-Reference Schema Changes"

• Section 16.7, "Importing the Latest Knowledge Module for Cross-References"

• Section 16.8, "Changing the Model and Interfaces That Involve Cross-References"

16.1 Overview of ODI Flow Migration Tasks

To migrate ODI flows:

1. Set up the environment.

2. Export and move master repository and work repository schemas from the Oracle 10g database to the Oracle 11g database.

3. Import the source code Oracle 11g database master repository and work repository schemas.

4. Run upgrade assistant.

Figure 16-1 illustrates the overall ODI migration task flow.

Figure 16-1 ODI Migration Task Flow

AIA Foundation Pack 11g Release 1 provides infrastructure utilities that enable you to carry out the ODI flow migration process. Migration strategy details and corresponding utilities are discussed in this chapter.

For information about the environment setup, see Chapter 2, "Preparing to Migrate AIA Artifacts to AIA 11g Release 1."

16.2 Describing the Source 10g ODI Server

• The ODI code that you want to migrate should be up and running in ODI 10g repositories (in database).

• Ensure that the master repository is associated with only one work repository.

• Compile the following information about the source ODI system:

  • Host login user name and password.

  • Database installation location.

  • ODI repository details: master repository database user name and password and work repository database user name and password.

  • ODI user name and password.

16.3 Describing the Target 11g ODI Server

Compile the following information about the target ODI system:

• Host login user name and password.

• Database installation location.

• Database administrator user name and password.

• ODI repository details: master repository database user name and password and work repository database user name and password.

  If these users are not present in the database, create the users and grant them session, connect, and resource privileges. Use the following Oracle database commands:

  create user <User name> identified by <Password>
  grant create session, connect, resource to <User name>
  

• ODI installation location.

  Gather connection details for data servers in the ODI master repository (source database, target database, cross-reference database, DVM XML file location, AIAConfiguration.Properties file location, and so forth).

16.4 Migrating ODI

To migrate ODI:

1. Export the master repository and work repository schemas from the ODI 10g database.

   To export the database schema, run the following commands from the bin folder of the Oracle database installation:

   exp <master db username of 10g>/< master db password of 10g> 
   FILE=<location>/master.dmp
   
   exp < work db username of 10g >/< work db password of 10g> 
   FILE=<location>/work.dmp
   

   Note:

   You must source the environment correctly for these commands to run correctly.

2. Move the master.dmp and work.dmp files to the server where the database for ODI 11g is installed.

3. Import the master and work repositories to the 11g database.

   To import the database schema run the following commands from the bin folder of the Oracle database installation:

   imp <MASTER REP DB USERNAME for 11g>/<MASTER REP DB PASSWORD for 11g> 
   FROMUSER=<MASTER REP DB USERNAME of 10 g> TOUSER=<MASTER REP DB USERNAME for 
   11g> FILE=< location >\master.dmp
   
   imp <WORK REP DB USERNAME for 11g>/<WORK REP DB PASSWORD for 11g> 
   FROMUSER=<WORK REP DB USERNAME for 10g> TOUSER=<WORK REP DB USERNAME for 11g> 
   FILE=<location>\work.dmp
   

4. Run the ODI upgrade operation of the Oracle Fusion Middleware Upgrade Assistant from the bin folder under ODI 11g installation home.

   For Windows, run ua.bat.

   For Linux, run ua.

   1. The Oracle Fusion Middleware Upgrade Assistant Welcome page displays. Click Next.

   2. The Specify Operation page displays, as shown in Figure 16-2. Select the Upgrade Oracle Data Integrator Repositories option. Upgrade the ODI database repositories only. Click Next.

      Figure 16-2 Specify Operation

      
      

   3. The Prerequisites page displays, as shown in Figure 16-3. Verify that prerequisites have been met. Click Next.

      Figure 16-3 Prerequisites

      
      

   4. The Specify Target Database page displays, as shown in Figure 16-4. Enter the details of the master repository to be migrated. Click Next.

      Figure 16-4 Specify Target Database

      
      

   5. The Specify Work Repositories User Name page displays, as shown in Figure 16-5. Enter the details of the work repository to be migrated. Click Next.

      Figure 16-5 Specify Work Repositories User Name

      
      

   6. The Examining Components page displays, as shown in Figure 16-6. Enable the Upgrade Assistant to complete its component examination. Click Next.

      Figure 16-6 Examining Components

      
      

   7. The Upgrade Summary page displays, as shown in Figure 16-7. Verify that the upgrade information displayed is correct. Click Upgrade.

      Figure 16-7 Upgrade Summary

      
      

   8. The Upgrading Components page displays, as shown in Figure 16-8. The page displays the progress of the upgrade.

      Figure 16-8 Upgrading Components

      
      

   9. After the upgrade process is complete, the Upgrade Complete page displays, as shown in Figure 16-9.

      Figure 16-9 Upgrade Complete

      
      

16.5 Performing Post-Migration Steps

1. After the ODI upgrade has been completed, connect to repositories using the ODI Studio and update the connections in the physical topology to point to the data servers to be used for development.

2. If the ODI projects use OdiInvokeWebService to invoke a web service, as with AIA Error Handling, for example, then reenter the password for all of the web service callouts in the projects.

   Also, update the web service URL to conform to AIA 11g Release 1 standards. Note that the password is encrypted using the ODI 11g OdiInvokeWebService command.

3. If ODI projects use cross-references, follow the steps specified in Section 16.6, "Handling Cross-Reference Schema Changes."

4. After all post-ODI upgrade changes are complete, regenerate all scenarios and test the flows.

16.6 Handling Cross-Reference Schema Changes

Oracle SOA Suite 11g Release 1 includes the following changes to the cross-reference schema (XREF_DATA table):

• The LAST_ACCESSED column in the XREF_DATA table has been removed.

• The data type of the LAST_MODIFIED column in the XREF_DATA table has been changed from varchar2 to timestamp.

• The XREF_TABLE _NAME value is the fully qualified name of the cross-reference in Oracle Metadata Services repository (MDS).

  In 10g, the XREF name was populated as the name of the cross-reference. In 11g Release 1, it is populated as MDS_XREF_PATH + XREF NAME.

  For example, oramds:/apps/AIAMetaData/xref/RESOURCE_ID.xref.

To enable ODI code to work with the Oracle SOA Suite 11g Release 1 cross-reference schema:

1. Import the latest Knowledge Module for cross-references.

   This is delivered by ODI.

   For more information, see Section 16.7, "Importing the Latest Knowledge Module for Cross-References."

2. Change the ODI models and interfaces that involve cross-references.

   For more information, see Section 16.8, "Changing the Model and Interfaces That Involve Cross-References."

16.7 Importing the Latest Knowledge Module for Cross-References

To import the latest Knowledge Module for cross-references:

1. Import the latest IKM SQL Control Append (ESB XREF) into the project using INSERT_UPDATE mode.

2. In all interfaces that use this KM, update the XREF_TABLE_NAME option with the fully qualified name of the cross-reference table, as shown in Figure 16-10.

Figure 16-10 Update XREF_TABLE_NAME

Note:

Any customizations made to this KM for a Process Integration Pack (PIP) must be recreated manually.

16.8 Changing the Model and Interfaces That Involve Cross-References

Because the XREF_DATA table has undergone data model changes, the model in ODI must also be changed accordingly.

Note:

We assume that the XREF_DATA table is used only as the target of the interfaces in the project. The cross-reference view, which is created per PIP, is used as the source of interface.

To update the model and interfaces:

1. Log in to ODI and access the Designer tab.

2. Open the LAST_ACCESSED column of the XREF_DATA table in the model.

3. Access the Control tab and clear the Mandatory option, as shown in Figure 16-11.

   Figure 16-11 Mandatory Option Cleared for the LAST_ACCESSED Column

   
   

4. Open the interface that uses the XREF_DATA table as the target and access the Mapping tab.

5. Clear the Active Mapping option for the LAST_ACCESSED column on the target. Also delete any code in the Implementation area, as shown in Figure 16-12.

   Figure 16-12 Active Mapping Option Cleared for the LAST_ACCESSED Column

   
   

6. Perform steps 4 and 5 for all interfaces that use the XREF_DATA table.

7. Save the interface and ignore any warnings displayed while saving.

8. Regenerate all scenarios.

9. Disconnect from and reconnect to the repository. This is required.

10. Delete the LAST_ACCESSED column of the XREF_DATA table in the model using the Delete option in the context menu of the LAST_ACCESSED column, as shown in Figure 16-13.

    Figure 16-13 Delete Option in the Context Menu

    
    

11. Open the LAST_MODIFIED column of the XREF_DATA table in the model and change the Datatype field value to TIMESTAMP. Clear the Length and Precision fields in the Logical Format area, as shown in Figure 16-14.

    Figure 16-14 Edits to the LAST_MODIFIED Column

    
    

12. Open the interface that uses the XREF_DATA table in the target and access the Mapping tab.

13. On the Implementation tab, change the value to SYSTIMESTAMP. Save the implementation code and interface. Ensure that the Execute on option is set to Target, as shown in Figure 16-15.

    Figure 16-15 Implementation Tab Value Set to SYSTIMESTAMP

    
    

14. Perform steps 12 and 13 for all interfaces that use XREF_DATA.

15. Change the Mapping value of XREF_TABLE _NAME to the fully qualified name of the cross-reference, as shown in Figure 16-16.

    Figure 16-16 Mapping Value Set to the Fully Qualified Name of the Cross-Reference

    
    

16. Update the physical data source to connect to the new 11g XREF database.

17. Update the physical schema to the cross-reference schema on the 11g database, as shown in Figure 16-17.

    Figure 16-17 Update of the Physical Schema to the Cross-Reference Schema

    
    

18. In the Logical connection of the cross-reference schema, change the physical connection to point to the new cross-reference physical schema.

19. Regenerate all scenarios.

20. Test the execution of the code.