Using the Data Migration Wizard

Using the Data Migration Wizard

The Management Portal provides a wizard that you can use to migrate data from an external table or view.

When you migrate data from a table or view in an external source, the system generates a persistent class to store data of that table or view and then copies the data. This wizard assumes that the class should have the same name as the table or view from which it comes; similarly, the property names are the same as in the table or view. After the class has been generated, it does not have any connection to external data source.

• If you have not yet created an SQL Gateway connection to the external database, do so before you begin (see “Creating SQL Gateway Connections for External Sources”).

• From the Management Portal select System Explorer, then SQL. If necessary, change namespaces by clicking the current namespace displayed at the top of the page; this displays the list of available namespaces.

  At the top of the page, click the Wizards drop-down list, and select Data Migration.

• On the first page of the wizard, select the table or view, as follows:

  • Select a destination namespace — Select the InterSystems IRIS namespace to which the data will be copied.

  • Schema Filter — Specify a schema (class package) name that contains the table or view. You can specify a name with wildcards to return multiple schemas, or % to return all schemas. For example, C% will return all schemas in the namespace beginning with the letter C. Use of this filter is recommended, as it will shorten the return list of schemas to select from, and thus improve loading speed.

  • Table Filter — Specify a table or view name. You can specify a name with wildcards to return multiple tables and/or views, or % to return all tables/views.

  • Table type — Select TABLE, VIEW, SYSTEM TABLE, or ALL. The default is TABLE.

  • Select a SQL Gateway connection — Select the SQL Gateway connection to use.

• Click Next.

• On the next page, you can optionally specify the following information for each class:

  • New Schema — Specify the package to contain the class or classes. Be sure to follow the rules for ObjectScript identifiers, including length limits (see the section on Naming Conventions in Defining and Using Classes).

    Tip:

    To change the package name for all classes, type a value at the top of this column and then click Change all.

  • Copy Definition — Select this check box to generate this class, based on the table definition in the external source. If you have already generated the class, you can clear this check box.

  • Copy Data — Select this check box to copy the data for this class from the external source. When you copy data, the wizard overwrites any existing data in the InterSystems IRIS class.

• Click Next. The wizard displays the following optional settings:

  • Disable validation — If checked, data will be imported with %NOCHECK specified in the restriction parameter of the INSERT command.

  • Disable journaling for the importing process — If checked, journaling will be disabled for the process performing the data migration (not system-wide). This can make the migration faster, at the cost of potentially leaving the migrated data in an indeterminate state if the migration is interrupted by a system failure. Journaling is re-enabled at the end of the run, successful or not.

  • Defer indices — If checked, indices are built after the data is inserted. The wizard calls the class' %SortBegin() method prior to inserting the data in the table. This causes the index entries to be written to a temporary location for sorting. They are written to the actual index location when the wizard calls the %SortEnd() method after all rows have been inserted. Do not use Defer Indices if there are Unique indices defined in the table and you want the migration to catch any unique constraint violations. A unique constraint violation will not be caught if Defer Indices is used.

  • Disable triggers — If checked, data will be imported with %NOTRIGGER specified in the restriction parameter of the INSERT command.

  • Delete existing data from table before importing — If checked, existing data will be deleted rather than merged with the new data.

• Click Finish. The wizard opens a new window and displays the Background Jobs page with a link to the background tasks page. Click Close to start the import immediately, or click the given link to view the background tasks page. In either case, the wizard starts the import as a background task.

• In the Data Migration Wizard window, click Done to go back to the home page of the Management Portal.