The “Multi-Table Migration Wizard” in the DBMS edition of IRI NextForm software moves data from one database to another, multiple tables at a time. IRI Voracity platform users can also make use of this feature.

Following is an example of migrating data from two MySQL tables into Oracle. The tables do not have to exist in the target location; however, in this example they do as empty tables. The target table ORDERS_NF will have a masked “TOTAL” column.

Here are the tables as they exist in the source MySQL database’ test Schema, as shown in the IRI Workbench GUI¹ for NextForm:

The wizard starts by asking which DB sourcing and targeting method will be used. NextForm supports both IRI FACT (Fast Extract) and ODBC for data acquisition. Data population can be through bulk loads (for which NextForm will write the load utility’s control file) or ODBC. If you use flat files and have a Voracity (or IRI CoSort) licenses, that load file can be pre-sorted on the primary index key, by-passing the loader’s sort function and making the load faster.

On the second screen, select the source tables you will migrate. There is a second table selected down in the non-visible section of the scroll box:

You can use various field rules to transform your data before it is loaded into the target tables. In this case, the TOTAL column will be masked with the number “1”:

After selecting the target database and schema, the summary page shows the information entered in the wizard pages, along with any matched rules and existing matched tables:

The “new batch” folder shows the scripts that were created by the wizard. Because IRI products run on the command-line, the only file that needs to be executed is the batch file. It bundles the execution of all the scripts automatically.

In the editing window shown below are the two scripts that extract the data from the source file, and transform it before saving it to two text files (CUSTOMERS_NF.out and ORDERS_NF.out). The highlighted line shows the transformation that will take place on the “TOTAL” field.

After the batch file is executed, the data is now visible in the target tables. The “TOTAL” column now displays 1’s where actual data used to be. This is helpful when setting up new database systems when you don’t want your production data visible during testing.

If for some reason your migration wasn’t successful, the log files that are created during the execution of the batch file are helpful in determining why. For instance, if you had data in the wrong data type or format, the log file will display that.

In the following case, the transfer was successful:

NextForm includes this easy-to-follow migration wizard for your multi-table data migration projects. Once your rules are set-up, it is also quite easy to apply those mapping rules during your migration to save steps later.

Email nextform@iri.com if you have any questions; be sure to specify that you are writing about the DBMS edition of NextForm. For assistance in migrating complex constraints, triggers, SQL procedures and/or database applications, contact IRI Professional Services, or a third-party database expert.

 

---

^{1. From the same GUI, but with an IRI FieldShield or IRI CoSort (SortCL) license, your migrations can include data masking and data transformation functions, respectively. IRI RowGen, compatible with NextForm metadata, works with the same GUI’s Data Source Explorer to generate structurally and referentially correct test data in target tables (needing no production data).↩}