4 Capturing a Source Database

Overview

Before migrating a third-party database, you must extract information from the database. This information is a representation of the structure of the source database called the Source Model. The process of extracting the information from the database is called capturing the source database.

There are two methods of capturing the source database:

• During the online capture method, you use Migration Workbench to connect to the source database, extract the database structure, and populate the Source Model. If you want to perform an online capture, proceed to "Using the Capture Wizard". If you are migrating from a MySQL, IBM DB2 UDB or IBM DB2/400 database, you must use the online capture method.

• During the offline capture method, you use Migration Workbench capture scripts to connect to the source database and create a set of files that describe the database structure. You can then use Migration Workbench to populate the Source Model from these files. If you want to perform an offline capture, you must complete the procedure described in "Creating Offline Capture Files" before using the Capture wizard.

Use the offline capture method if:

• You cannot connect to the source database from the computer running Migration Workbench.

• You are migrating a complex database that includes many stored procedures. Offline capture provides better performance than online capture when you are migrating a complex database that includes many stored procedures.

After capturing the source database, you can view the source database information in the Source Model in Migration Workbench. You can modify the Source Model as described in Customizing the Source Model.

The following figure shows this process in the context of the overall migration process:

Starting Migration Workbench on Windows

To start Migration Workbench on Windows:

1. From the Start option, select Run.

   The Run dialog box appears.

2. Run the startup script by typing a command similar to the following:

   OMWB_install_dir\Omwb\bin\omwb.bat
   
   

   In the previous command, OMWB_install_dir is the absolute directory where you installed Migration Workbench.

   The Oracle Migration Workbench Repository Login dialog box appears.

3. Click OK.

4. Select the Workbench repository that you want to use for the migration.

   For information about the Workbench repository, see "Workbench Repository".

   
   

   Note: Oracle recommends that you specify an Oracle Database 10g, Oracle9i, or Oracle8i database to use as the Workbench repository. If you select the default Workbench repository, you must perform an online capture.

   
   

   Before you can capture the Source Model, Migration Workbench prompts you to specify the Workbench repository you want to use:

   • To select the default Workbench repository, click Default Repository, then click OK.

     The default Workbench repository is an Oracle9i Lite database.

   • To select an Oracle database (Oracle Database 10g, Oracle9i, or Oracle8i), enter the user name, password, host, port, and SID for that database, then click OK.

     To list the connection details for the database you want to use as the Workbench repository, see "Determining Connection Information for an Oracle Database".

     You must create a specific user account (the REPUSER user) on the database you want to use as the Workbench repository. For more information about creating the REPUSER user, see "Creating a Database User for the Workbench Repository".

     If a Workbench repository does not exist in the REPUSER schema on the Oracle database that you select, you are prompted to create one. Click OK.

   The Select Migration Source screen appears.

5. Select the plug-in that you want to use from the list of installed plug-ins, then click OK.

   When you change plug-in, if there is data in the current Oracle Model, it is deleted from the Workbench repository.

   If this is the first time that you are using the current Workbench repository, the Capture wizard appears. Otherwise, the main Migration Workbench screen appears.

Starting Migration Workbench on Linux x86

To start Migration Workbench on Linux x86:

1. Set the JAVA_HOME environment variable to specify the path of the JRE.

   • Bourne, Bash, and Korn shell:

     prompt> JAVA_HOME=jre_directory
     prompt> PATH=jre_directory/bin:$PATH
     prompt> export PATH JAVA_HOME
     
     

   • C shell:

     prompt> setenv JAVA_HOME jre_directory
     prompt> setenv PATH jre_directory/bin:$PATH 
     
     

2. Run the startup script by typing a command similar to the following:

   cd OMWB_install_dir/Omwb/bin
   ./omwb.sh
   
   

   In the previous command, OMWB_install_dir is the absolute directory where you installed Migration Workbench.

3. Enter the user account and connection information for the Oracle database.

   To list the connection details for the database you want to use as the Workbench repository, see "Determining Connection Information for an Oracle Database".

   You must create a specific user account (the REPUSER user) on the database you want to use as the Workbench repository. For more information about creating the REPUSER user, see "Creating a Database User for the Workbench Repository".

   If a Workbench repository does not exist in the REPUSER schema on the Oracle database you choose, you are prompted to create one. Click OK.

   The Select Migration Source screen appears.

4. Select the plug-in that you want to use from the list of installed plug-ins.

   When you change plug-in, the data in the current Oracle Model, if it exists, is deleted from the Workbench repository.

   If this is the first time you are using the current Workbench repository, the Capture wizard appears. Otherwise, the main Migration Workbench screen appears.

Creating Offline Capture Files

To perform an offline capture, you must create offline capture files from the source database, then use the Capture wizard to populate the Source Model with the information from the offline capture files.

Depending on the database you want to migrate, use the appropriate procedure to create the offline capture files:

• Creating Microsoft SQL Server or Sybase Adaptive Server Offline Capture Files

• Creating Informix Dynamic Server Offline Capture Files

If there is not a section for the plug-in you are using, then the offline capture method is not supported for the database you are migrating.

Using the Capture Wizard

To use the Capture wizard:

1. If necessary, start Migration Workbench as described in "Starting Migration Workbench on Windows".

2. To start the Capture wizard, from the Action menu, select Capture Source Database.

   The Welcome page appears.

3. Click Next.

   If you are using the MySQL, IBM DB2 UDB or IBM DB2/400 plug-ins, the Source Database Details page appears. Proceed to step 6.

   If you are using the Microsoft SQL Server, Sybase Adaptive Server or Informix Dynamic Server plug-ins, the Source Model Load Details page appears.

   • If you want to perform an online capture, select the Online Source Model Load option, then click Next. The Source Database Details page appears. Proceed to step 6.

   • If you want to perform an offline capture, select the Offline Source Model Load option, then click Next. The Select Source Model Metadata Files page appears. Proceed to step 5.

   If you are using the Microsoft Access plug-in, the Select Databases to Capture page appears. Proceed to step 4.

4. If you are using the Microsoft Access plug-in, select the database to capture as follows:

   1. Click Add XML File.

      A file selection dialog box appears.

   2. Select the XML file that you created by completing the procedure "Creating Microsoft Access XML Files", then click OK.

      The file name appears in the Microsoft Access XML File list. To remove the file, select the file name, then click Remove XML File.

   3. Click Next.

      The Data Type Mapping page appears. Proceed to step 7.

5. If you are performing an offline capture, select the capture files:

   1. Click Load.

      The Select Root Directory dialog box appears.

   2. Browse to the directory containing the capture files, then click OK.

      Each capture file is listed on the Select Source Model Metadata Files page.

   3. Click Next.

      The Data Type Mapping page appears. Proceed to step 7.

6. If you are performing an online capture, select the databases you want to capture:

   1. Specify the source database details.

      Enter the information required to connect to the databases that you want to capture. The information you enter is used by the connection driver to allow Migration Workbench to connect to the source database. The information required is different for each plug-in, as follows:

      Plug-in	Information Required
      IBM DB2/400	Username - any user with database administrative privileges Password - password for the user above Hostname - the name of the computer
      IBM DB2 UDB	User ID - any user with database administrative privileges Password - password for the user above Database Names - A comma separated list of names of the databases you want to migrate.
      Informix Dynamic Server	User name - Defaults to informix. This user should have full DBA privileges. You cannot change this default. Password - password for the user above Host name - the name of the computer Port number - the port number of the database. The default port number is 1526. Database server - the name of the database
      Microsoft Access	Not applicable
      Microsoft SQL Server and Sybase Adaptive Server (Migration Workbench for Windows only)	Login ID - defaults to the Microsoft SQL Server or Sybase Adaptive Server login sa. You cannot change this default. Password - password for the user above. ODBC DSN - The name of the ODBC data source.
      MySQL	User name - any user with database administrative privileges Password - password for the user above Host name - the name of the computer Port number - the port number of the database. The default port number is 3306.
      Sybase Adaptive Server (Migration Workbench for Linux x86 only)	Login ID - defaults to the Sybase Adaptive Server login sa. You cannot change this default. Password - password for the user above. Host name - the name of the computer Port number - the port number of the database. The default port number is 5000. Note: The default delimiters for Sybase Adaptive Server for Linux x86 do not work using the UTF-8 encoding file. Therefore, if your LANG variable is set to en_US.UTF-8, you must set the LANG variable to en_US to default to ISO-8859-1.

      
      

   2. Click Next.

      The Select Databases to Capture page appears.

   3. Select the databases that you want to capture.

      The databases are displayed alphabetically in the Available Databases list.

   4. Click Next.

      The Data Type Mappings page appears.

7. Review the data type mappings, then click Next.

   The source data types are mapped to similar data types in the Oracle database. Oracle recommends that you do not change these mappings unless you are an experienced Oracle database administrator. For information about these mappings, see "Customizing Data Type Mappings".

   The Create Oracle Model page appears.

8. Choose whether to automatically create the Oracle Model:

   • To automatically create the Oracle Model, choose Yes.

   • To create the Oracle Model at a later stage, choose No.

     Choose No if you want to customize the Source Model before creating the Oracle Model, for example, if you want to delete schema objects from the Source Model. For information about creating the Oracle Model at a later stage, see Chapter 5, "Creating and Customizing the Oracle Model".

9. Click Next.

   If you are using the Microsoft Access plug-in the Destination Database page appears:

   1. Choose the type of Oracle database that you want as the destination database. The options are:

      • Oracle8i/Oracle9i/Oracle Database 10g

      • Oracle9i Lite

        
        

        Note: To migrate to an Oracle9i Lite database, the Oracle9i Lite database file must be available for selection.

        
        

   2. Click Next. The Summary page appears.

   If you are using any plug-in other than Microsoft Access, the Summary page appears.

10. Review the summary information:

    • If you need to change a selection, click Back to return to a previous page.

    • Click Finish to start the capture.

      A progress screen appears displaying information about the capture in progress. The capture process may take a long time with complex databases.

11. Filter the messages to show only error and warning messages:

    1. With the progress screen highlighted, from the Edit menu, select Preference.

    2. Deselect all options except Error Messages and Warning Messages.

    3. Click OK.

12. Take the following actions, depending on the type of message displayed:

    • If an error message appears, fix the error and start the Capture wizard again.

    • If error messages with the prefix Parse Exception are listed in the progress screen, use the procedure described in "Correcting Errors in the Oracle Model" to correct these errors.

    • If a warning message appears, consider the problem described in the message and decide whether any action is required.

13. Click OK to dismiss the progress screen.

    If you chose to automatically create the Oracle Model, the Creating Oracle Model screen appears displaying the total number of errors and warnings.

    If you chose not to automatically create the Oracle Model, the Loading Source Model screen appears displaying the total number of errors and warnings.

    For more information about analyzing errors and warnings, see Chapter 9, "Using Reports".

14. Click OK to return to the main Migration Workbench screen.

Capturing Informix ESQL/C Source Files

If you are migrating an Informix Dynamic Server database, you can also migrate ESQL/C source files. Migration Workbench migrates ESQL/C code to Pro*C code. You can migrate ESQL/C source files without migrating the Informix Dynamic Server database, that is, you can migrate ESQL/C code as a separate process from the rest of the migration.

Before migrating ESQL/C code you must capture the ESQL/C source files:

1. Copy all the directories containing ESQL/C files to a directory available from the system where you installed Migration Workbench.

2. From the Action menu, select Capture ESQL/C Source Files.

   The Select Source Root Directory screen appears.

3. Choose the ESQL/C directory that you used in step 1, then click OK.

The ESQL/C file appears as objects in the Source Model of Migration Workbench.

Customizing the Source Model

You can edit the properties of some schema objects, such as stored procedures, triggers, views, check constraints and default values before you create the Oracle Model.

To modify schema objects from the Source Model:

1. Expand the Databases(n) node of the Source Model tree.

   The number in parentheses, n, indicates the number of databases in the Source Model.

2. Expand the schema object type node of the Source Model tree.

   The number in parentheses, indicates the number of those schema objects in the database, for example Primary Keys (23) indicates that there are 23 primary keys in the database.

3. Select the schema object that you want to modify.

4. Edit the schema object property that you want to modify.

   For a detailed description about each schema object and the properties associated with it, see the online help.

5. To delete the schema object:

   1. From the Object menu, select Delete.

      A confirmation dialog box appears.

   2. Click Yes to confirm the deletion of the schema object.

      All data dictionary items associated with that schema object are also deleted. For example, if you delete a table, any indexes on columns in that table are deleted from the indexes section of the model.

There may be errors when you create the Oracle Model. To correct these errors you must customize the Source Model as described in "Correcting Errors in the Oracle Model".

Customizing Data Type Mappings

Migration Workbench allows you to customize some data type mappings by allowing you to change the following:

• clob data type to map to varchar2

• char data type to map to varchar2

• precision and scale of number data types