Skip to content
≡

Administrator

Developer

Tools

External Tools

5. Migration Center Internals#

This chapter describes the main steps of Migration Center, namely the Build, Reconcile, Run, and Data Validation steps. This chapter consists of the following sections:

  • Build Step
  • Reconcile Step
  • Run Step
  • Data Validation Step

Build Step#

Purpose#

The Build step not only collects information about database objects from the source and destination databases, but also provides a build report to make migration easier for the user. The report lists migratable database objects in the source and destination databases with byte size information. This informs the user of the scale of the migration project, and helps the user to estimate the necessary data volume and time.

The information gathered in this step is used throughout the entire migration process and must reflect the current status of the source and destination databases. If changes are made to the database objects in the source database after the Build step, all steps must be re-executed.

Output#

  • Build reports
    Several data volume analysis reports based on the current status of the source and destination databases are output in HTML format and stored in the project folder.
  • SQL Data Definition Language (DDL) script
    A file containing database object creation statements (DDLs) collected from the source database, whether or not Migration Center is supported, created in the project folder and named SrcDbObj_Create.sql. This file is intended for user reference only and is not used at any stage of the Migration Center.
  • BuildReport4Unsupported.html
    It is one of the files summarizing the build results and shows the CREATE statements of objects not automatically migrated by the Migration Center. Objects that are not supported by Migration Center must be manually converted by the user, and this file can be referenced for conversion operations.
    Non-automatic migration object types depend on the source database and can be found at Appendix B. Migratable Database Objects. In case of Oracle to Altibase migration, this file is not created because all of Oracle database object types defined in the appendix table are supported for automatic migration. On the other hand, in case of MySQL to Altibase migration, if the source database has any database object type such as stored procedures, stored functions, views, and trigger objects, the CREATE statement of the unsupported database object is recorded at this file for user's reference.

Internal Activities#

This step consists of two internal activities; collecting information about database objects from both databases, and generating a Build report based on that information.

The collecting information can be started as either "Build User" or "Build Table".

  • Build User
    This collects all migratable object information of the user connected to the source database
  • Build Table
    This configures a list of tables to be migrated among the tables of the user connected to the source database. Plus, this collects object information of the selected tables, and constraints and indexes dependent on the selected tables.

For more information on the object types that can be migrated, refer to the "Appendix B: Migratable Database Objects".

When the user starts the build step, the "Table Counting Method" dialog box appears. The user can choose one of the following options:

  • Approximate Counting Method
    This retrieves the number of table records by referring to the statistical value of the source database. This value is affected by the accuracy of the statistical value.
  • Exact Counting Method
    This retrieves the exact number of table records by executing the COUNT function on every table in the source database.

Of the two methods, the approximate counting method performs faster whereas, the exact counting method yields more accurate results. The method chosen does not affect the database schema and data migration. It only affects the accuracy of the data migration progress percentage provided at the Run step in GUI mode. This is because the data migration progress is displayed as the time elapsed and the percentage of (number of migrated records / total number of records). With this, the user can estimate the total amount of time required to complete data migration.

For more detailed information about how to execute the Build step, please refer to Building the Project.


Reconcile Step#

Purpose#

The Reconcile step builds a migration plan. Migration Center users should have a plan on how to migrate the database objects of each database. Migration Center cannot migrate all database objects to Altibase databases. However, it makes migration easier by allowing users full control over migration.

Altibase offers high performance of an in-memory database and high capacity of on-disk database in a single database. Altibase generally stores frequently used and low-latency data in memory tablespaces, and the rest of the data in disk tablespaces.

For more detailed information about tablespaces in Altibase databases, please refer to the Administrator's Manual.

Output#

  • Reconcile reports: Several reports that specify which database objects to migrate and how to migrate them in the project folder.
  • SQL Data Definition Language (DDL) scripts: Sample SQL files to create and drop database objects in the destination database are provided in the project folder for user convenience. However, these files are not used in any step.
  • DbObj_Create.sql: A SQL script file for creating database objects to be migrated.
  • DbObj_Drop.sql: A SQL script file for dropping database objects having the same names as those to be migrated.
  • PL/SQL conversion reports: Several reports generated by the PL/SQL Converter.
  • sqlconv.html: A HTML report that compares the difference between source and converted PL/SQL.
  • sqlconv_src.sql: A report that contains the input PL/SQL statements to be converted in text format.
  • sqlconv_dest.sql: A report that contains converted PL/SQL statements and comments listing the applied conversion rules in text format.

Internal Activities#

Although the Reconcile step is very important and can get complicated, the wizard is as easy to use as a UI. For further information about how to start the Reconcile step, please refer to Reconciling the Project in Chapter 3.

Reconcile Wizard Dialog Box#

Users can check and modify the default settings in wizard dialog box. The wizard guides the user step by step, but the user can also go to any step by selecting it in the left pane.

"Data Type Mapping" Step#

The "Data Type Mapping" step maps data types between heterogeneous databases. Please note that small differences between data types can cause unexpected data loss and truncation during data migration. For more detailed information, please refer to Data Type Mapping in Appendix C.

"PSM Data Type Mapping" Step#

The "PSM Data Type Mapping" step maps PSM data types between heterogeneous databases. This step is only activated for Oracle to Altibase PSM Migration or TimesTen to Altibase PSM Migration. All configurations specified in this step are reflected to target DDL in the "SQL Editing" step later on.

"Tablespace to Tablespace Mapping" Step#

The "Tablespace to Tablespace Mapping" step maps tablespaces between the source and destination databases. When tablespace mapping is set, contents of the tablespace are also mapped to the selected tablespace. This step creates a default tablespace map, and this map can be altered with the "Object to Tablespace Mapping" submenu.

"Object to Tablespace Mapping" Step#

The "Object to Tablespace Mapping" step maps each table and index by dragging and dropping them to tablespaces in the destination tablespace. Whenever a map changes, the necessary total storage sizes for tablespaces in the destination database are recalculated. Internally, a database object size is accurately retained in bytes, but is rounded to megabytes in the dialog box. Therefore, the total size of a tablespace may not be equivalent to the sum of its contents.

"Select Editing" Step#

The "Select Editing" step enables the user to edit the SELECT statement for fetching data from a table in the source database. The user can add hints or WHERE clauses, and immediately check the edited statement. Click the "Restore" button to undo changes.

The name of modified table in the SELECT statement is recorded in pairs with WHERE clause in the TableCondition.properties file. This file is automatically generated during the final stage of Reconcile, and it can be edited by the user.

TableCondition.properties#

To selectively migrate specific data from tables in the source database, the "TableCondition.properties" file is provided. Users can input conditions in this file to filter data for migration. Tables not included in this file will have all data migrated from the source to the target tables.

The file is structured with "Source Database Table Name"=WHERE clause pairs, and it is automatically generated after completing the Reconcile step. The WHERE clauses in this file are added to SELECT statements when retrieving data from the source tables. Additionally, the same WHERE clause is used when verifying the number of migrated records in the target table after Execution step. The number of migrated data can be viewed in the RunReport4Summary.html file.

Conditions can be modified during the Reconcile step using "Select Editing" or manually edited after Reconcile step is complete, with the following constraints:

  • The WHERE clause must be written on a single line.
  • If there are SQL syntax differences between the source and target databases, the [DEST] section should include the WHERE clause for the target database table.
  • Example
    DATE_TEST=WHERE C2 > DATE'2023-12-02'
...
[DEST]
DATE_TEST=WHERE C2 > TO_DATE('2023-12-02', 'YYYY-MM-DD');

Refer to the guidance at the top of the file for more details on editing and restrictions.

"Unacceptable Name" Step#

The "Unacceptable Name" step shows objects that violate the unquoted object name rules in the target database. This includes objects with special characters or spaces in the name, and creation fails during the run step. If the user selects the "Use Double-quoted Identifier" check box, the user can avoid creating objects by wrapping only the problem names in double quotes("").

"SQL Editing" Step#

The "SQL Editing" step allows the user to view and edit the DDL statements for schema migration. The user can refer to the source DDL, and directly edit the DDL statements that Migration Center will apply on the destination database. SQL statements for creating procedures, functions, triggers, and views are all displayed in PSM type.

Tick the check box to select which object type within the PSM category to edit. These objects are displayed in the "Done" or "To-Do" list pane. If an object requires user confirmation, it is displayed in the todo list; otherwise, it is displayed in the done list. Click an object name in a list to view its source and destination DDL. Edit the DDL statement of an object belonging to the to-do list, then click the "Save" button, and the object will move to the done list.

The migration of an object in the done list can unexpectedly fail in the Run step. In this case, the user must check the "Missing Cause" in the Run report, find the error cause, and manually migrate the object.

DDL statements of PSM objects can be output as a file for users who prefer text editors. This functionality and instructions on its use are provided in the offline pane of the PSM object type. For Oracle or TimesTen to Altibase migration, the PL/SQL Converter converts the DDL statement of the PSM type object to an Altibase-compatible format.

Please note that although most of the syntax is converted, statements containing semantic logic are not converted.

Migration Center generates a dependency graph between objects in the source and destination databases in the Build step. If the user changes this dependency while editing destination DDL statements, migration cannot be guaranteed for the object and related objects.


Run Step#

Purpose#

The Run step copies database objects in the source database to the destination database or external files, depending on migration options.

After the Run step is complete, Run reports that contain results of this step are generated. These results may be the most important to the user. The RunReport4Summary.html report file outputs the overall result by comparing the number of database objects and table records in the source and destination databases. The RunReport4Missing.html report file outputs in detail any failure.

Data that failed to be migrated is collected in the "db2db" or "db2file" folder in the project folder. These two folders store data files and form files available for use with iLoader (the command-line data import/export tool for Altibase databases), and also scripts to easily import the generated data files to the database with iLoader. "iLoaderIn.sh" is the main script that executes the "iLoaderIn.number.sh" script which runs iLoader on other script files and tables.

Output#

  • RunReport4Summary.html: A summary report file that outputs the overall result of migration.
  • RunReport4Missing.html: A missing report file that outputs information about data that failed to be migrated, and the error cause.
  • DbObj_Failed.sql: A file that outputs a list of failed SQL statements and the error cause.
  • The db2db folder: A subfolder in the project directory, where data that failed to be migrated is saved. This folder is only available if the "DB to DB" option is selected for "Migration Type" and "No" is selected for "Batch Execution."
  • The db2file folder: A subfolder in the project directory, where all output is saved if the "DB to File" option is selected for "Migration Type".

Internal Activities#

The user can execute the Run step with a single mouse click in GUI mode, or a single command in CLI mode. For further information, please refer to Running the Project in Chapter 3 or Chapter 4.

Internally, this process consists of three steps to avoid database object dependencies: Initialization, the PreSchema, Table & Data, and PostSchema steps. For example, index objects are migrated in the PostSchema step, after the Table & Data step is completed. This is because it is usually faster to insert data without an index, than with an index.

Each step performs the following:

  1. Initialization: To the source database, performs validation of the WHERE clauses recorded in the TableCondition.properties file.
  2. PreSchema: Migrates sequence objects
  3. Table & Data: Migrates table objects and data
  4. PostSchema:
  5. Queue: Migrates queue objects
  6. Constraints: Migrates constraints, namely unique, primary key, foreign key, and check constraints
  7. Index: Migrates index objects
  8. Synonym: Migrates private synonym objects
  9. Procedures, functions, materialized views, views, typesets and triggers: Depends on the database management system and its version


Data Validation Step#

Purpose#

The Data Validation step checks whether data migration was properly performed in the Run step. At completion, the user is provided with a Data Validation report for post processing. This report offers information about the source and destination databases, a list of successfully validated tables, and the number of occurrences of data accordance and difference.

The user can decide whether to re-execute the Run step or use filesync. The user is recommended to use filesync when the occurrence of data difference is low, but re-execute the Run step when the occurrence of data difference is high.

Restrictions#

  • The Data Validation step can only be performed on a table with a primary key constraint.
  • LOB columns are excluded as targets for data comparison.

Output#

  • Data Validation report: A report containing a summary with information, namely the list of validated tables and the occurrences of data accordance and difference is output to the project directory.
  • Validation directory: A subdirectory in the project directory that stores different data. This directory is valid only if "YES" is selected for the "Write to CSV" option in the "Data Validation Options" submenu.

Internal Activities#

The Data Validation step can be executed with a single click in GUI mode, or a single command in CLI mode. For further information, please refer to Validating the Project or Executing the Run and Data Validation Steps in CLI Mode.

Internally, the Data Validation step is executed as below.

The data to be validated is fetched from the source and destination databases and compared. If a difference in data is detected and "Write to CSV" in the "Data Validation Options" submenu is set to "Yes", the different data is stored in CSV format files in the validation folder. Regardless of the option, summary information is always output to the Data Validation report.