nuodb-migrator

nuodb-migrator is a command-line tool that helps database administrators migrate schemas and existing data to a NuoDB database. It interfaces with a source database over a JDBC-compliant driver and is designed to support all major RDBMSs. It also interfaces with the NuoDB target database using the NuoDB JDBC driver. nuodb-migrator can also be used to perform backup and restore of NuoDB databases.

nuodb-migrator is included in the Client Package. To execute nuodb-migrator commands after installation, ensure that the location of the NuoDB JDBC driver jar (nuodbjdbc.jar) has been added to your CLASSPATH. For example:

export CLASSPATH=nuodbjdbc.jar:${CLASSPATH}

For more information, see Client Package.

Pre-built versions of the nuodb-migrator command line tool can be downloaded from GitHub.

Syntax

nuodb-migrator --help [dump | load | schema]

The nuodb-migrator command line tool supports three commands for migrating database schema objects and data from a source (NuoDB or third-party) database to a target NuoDB database. The nuodb-migrator tool commands include:

Command Description

nuodb-migrator dump

nuodb-migrator dump generates a CSV or XML-formatted data dump for each table it finds and also creates a schema metadata information file for a source NuoDB or third-party database.

For more information, see nuodb-migrator dump.

nuodb-migrator load

nuodb-migrator load reads the data files generated by nuodb-migrator dump and loads the data into a target NuoDB database.

For more information, see nuodb-migrator load.

nuodb-migrator schema

nuodb-migrator schema generates DDL SQL statement scripts to generate objects defined in the source NuoDB or third-party database. Optionally, nuodb-migrator schema executes the generated DDL SQL object create statements directly in the target NuoDB database, or writes them to a specified file.

For more information, see nuodb-migrator schema.

Databases Supported by nuodb-migrator

nuodb-migrator supports migration of data from the following databases:

Database Version

MySQL

5.5

PostgreSQL

9.2

Oracle

11.2, 12.2.0.1

SQL Server

11.0

DB2

10 (DSN10015)

Database Sequences

If there are database sequences used in the source schema, nuodb-migrator will migrate them with the correct starting value if the dump/load method is used to also create the target schema. This dumps the data and the DDL to create the sequence in one step, which makes sure the sequence will start with a value that is greater than the data.

If the schema objects and data are not migrated in one step (nuodb-migrator schema is used to generate the DDL SQL object create statements; nuodb-migrator load and nuod-migrator dump are used to load the data) then it is possible data can be inserted into the target database (outside of the data migration process) once the target schema is created. This can result in the sequence "start with value" stored in the migrator dump file to be lower than the current target database value when the data is loaded. This will cause an error during the execution of nuodb-migrator load if an attempt to add a row with a the sequence value that already exists.

Foreign Keys or Referential Integrity Constraints

For source schemas that use Foreign Keys or referential integrity constraints, either:

  • Defer index creation in the target NuoDB schema until after the execution of nuodb-migrator load has completed. This can be done by generating the table and index create statements separately during nuodb-migrator schema. Only execute the table create statements prior to the execution of nuodb-migrator load. For more information on using this method, see the nuodb-migrator schema section and the use of the --meta.data.value switch.

  • Create both table and indexes during the NuoDB target schema create process (the default). However, when running nuodb-migrator load, change the database isolation level to READ_COMMITTED. For this option, changing the default isolation level is required to process the table and index create statements correctly. You can set this option by including the following --target.properties switch when running the nuodb-migrator load command.

    --target.properties="isolation=read_committed"

Known Limitations

  1. For Oracle, since sequences and tables are not associated, nuodb-migrator will migrate the sequence and set its start value to the current value in the source Oracle database.

  2. Database VIEW objects are not migrated. The recommendation is to create your VIEWs in NuoDB using the CREATE VIEW <view-name> AS SELECT <query> command based on the view syntax from your source database.