To migrate tables

The procedure in this section describes how to copy tables from one location to another. If you want to migrate only certain rows and/or columns of a table, see Migrating Certain Rows and Columns.

Before you begin, review the following:

• If you are migrating tables to/from the mainframe, make sure XDB Link is installed and operating properly.
• If you are migrating tables between servers, make sure the SYSLUNAMES and SYSLOCATIONS tables on the server to which you are connected are configured for all locations to which you need access. See your XDB Server Administration Guide or consult your XDB Server Administrator for information about setting up server-to-server communications.
• Make sure you have SELECTprivileges on the tables you are migrating and on the following system catalog tables:

  	SYSIBM.DATABASE	SYSIBM.SYSRELS
  	SYSIBM.SYSCOLUMNS	SYSIBM.SYSSYNONYMS
  	SYSIBM.SYSFOREIGNKEYS	SYSIBM.SYSTABLES
  	SYSIBM.SYSINDEXES	SYSXDB.SYSLOCALS
  	SYSIBM.SYSKEYS	SYSXDB.SYSPROMPTQUERY
  	SYSIBM.SYSLOCATIONS	SYSXDB.SYSQUERY

  For more information about system catalog tables, see the SQL Reference or consult your XDB Server Administrator.

  Make sure the Catalog Browser and the Schema Viewer are open.

  
  
  Open the Browser by selecting the Catalog Browser button on the toolbar. Open the Schema Viewer by selecting New from the File menu.

  Note:

  The location to which you want to migrate a table must exist. If it does not, you must first create it using a database management tool such as SQLWizard.

1. In the Catalog Browser, locate and highlight the table you want to migrate.
   • Click the + buttons to expand the hierarchy and show subordinate objects.
   • Tables can be selected and migrated. Databases and locations cannot.

   For more information about using the Catalog Browser, see The Catalog Browser.

2. Drag the highlighted table to the Schema Viewer. When you release the mouse button in the Schema Viewer, the table's schema displays in the viewer.
   Note:

   • You can double-click the table name to place it in the Schema Viewer.
   • You can hold the Shift or Ctrl key while you click the tables in the Catalog Browser to select multiple tables.
3. If you want to migrate tables that are dependent on or referenced by a particular table (a referential integrity constraint), click the table in the Schema Viewer (its title bar will be highlighted indicating that it is "selected") and select the Show Dependent or Show Referenced command in the Object menu.

   When you select Show Dependent or Show Referenced, dependent and referenced tables (respectively) are automatically placed in the Schema Viewer along with arrowed lines showing their foreign key/primary key relationships.

   If all the rows of the referenced table are migrated, Migrate processes the DDL and DML so that the referential integrity constraints are not violated. However, if the user intends to restrict the domain of data being processed by Migrate, the domain (subset of the rows) must be defined by using the Define Query command on the Object menu. Queries can then be constructed against the referenced and dependent tables to ensure that only the rows matching the search criteria will be migrated. This will guarantee that foreign key values in the referenced and the dependent tables are consistent with each other. See Migrating Certain Rows and Columns for more information.

   Note:

   You can select the Show Dependent and Show Referenced commands from the control menu. To access this menu, click the Control-menu button in the upper-left corner of the table's schema window.

4. Click the Table/Query Options button in the toolbar or select the Table/Query Options command on the Object menu.

   In this field...	Specify the following...
   AuthID	The name to be used as the creator of the destination table. If you want Migrate to use the source table's Auth ID by default, select the Default To Source checkbox next to the Auth ID text box in the Default Options dialog box before you select Table Options; Migrate will then automatically populate this field with the proper default value. Otherwise, you will need to specify an Auth ID by typing it in text box.
   Table	The name of the table to which you will be migrating the data. If this table doesn't exist, Migrate will create it for you.
   Tbl Space	The table space name for the target table to which you will be migrating the data. If you want Migrate to use the name of the source table space by default, select the Default To Source checkbox next to the Tbl Space text box in the Default Options dialog box before you select Table Options; Migrate will then automatically populate this field with the proper default value. Otherwise, you will need to specify a table space name by typing it in this text box.
   Database	The database name for the target table where you will be migrating the data. If you want Migrate to use the name of the source database from which the source table is selected by default, select the Default To Source checkbox next to the Database text box in the Default Options dialog box before you select Table Options; Migrate will then automatically populate this field with the proper default value. Otherwise, you will need to specify a database name by typing it in this text box. If this field is left blank, the default database name "DSNDB04" is used.

5. In the Migrate group box, select the database objects you want to copy with the table. You can migrate primary keys, unique keys, indexes, foreign keys, synonyms, comments, privileges and distinct types.
   CAUTION:
   If you do not migrate primary keys, unique keys, and foreign keys, applications that are dependent on the database to implement these business rules may not work.
6. In the If Table Already Exists group box, specify the action you want Migrate to take if the target table already exists.

   Select...	If you want Migrate to...
   Abort	End the migration.
   Append Data	Append the migrated records to the existing table.
   Replace Data	Replace all records in the existing table with the migrated records (all data in the existing table will be deleted).
   Replace Table	Drop the existing table and create a new table with the migrated records.

   For information about the Update No Insert, Update Insert, and Delete Match options, see the instructions for Replicating Data.

7. In the Limit Data To group box, specify how much data you want Migrate to copy.

   Select...	If you want Migrate to...
   All Rows	Copy all records from the source table to the destination.
   No Data	Copy no records from the source table to the destination. You might use this option to create an "empty" destination table (which will have a structure identical to the source) into which you can selectively copy records using Migrate's query feature. The behavior of this option depends on the following: If the destination table does not exist, an "empty" table is created. If the destination table exists, and the Replace Data or Replace Table option is used, the destination table's existing records are deleted and no records are copied (an "empty" table is created). If the destination table exists, and the Append Data option is used, no records are copied to it.
   First N Rows	Copy the first N records from the source table to the destination.
   Every Nth Row	Copy every N records from the source table to the destination.

8. In the Commit Work After group box, specify the number of records Migrate should accrue before permanently writing them to disk (i.e., issuing a COMMIT statement). High Commit Work After values produce a faster migration but result in fewer records successfully written should the migration be interrupted.
9. Click OK to confirm the settings.
10. Repeat steps 1 through 9 for each table you want to migrate. If you want to remove a table that you have inadvertently added to the Schema Viewer, select the table in the Schema Viewer, then select the Remove command in the Object menu.
11. Once all the tables you want to migrate are in the Schema Viewer, open the To drop-down list on the toolbar and select the location to which you want the tables copied.
    Attention: If a location to which you want to migrate records is not listed in the drop-down list, check that you are connected to the correct server and verify that the server has access to the location you need. See your XDB Server Administration Guide or consult your XDB Server Administrator for information about server-to-server communications.
12. When you are ready to begin the migration, click the Run () button in the toolbar, or from the Commands menu, select Run.
13. In the Run dialog box, make sure the Execute Migrate checkbox is selected. If this box is not selected, Migrate will not copy any records to the destination. This option can be disabled if you want to generate SQL without performing a migration. See Creating an SQL Script File of the Migration.
14. In the Migrate Options group box, select the kind of reports and SQL scripts you want Migrate to produce when it migrates your data.

    Select this option...	To request...
    Generate SQL	A copy of the SQL script used to execute the migration. (This script contains the DDL statements used to define the destination table, but does not contain the individual INSERT statements for each migrated record.) In the text-entry field next to the checkbox, type the name of the file to which you want the script written. If this file does not exist, Migrate creates it. If it exists, Migrate overwrites it with the new SQL script.
    Post Execution Report	A report that lists the tables that were migrated. In the text-entry field next to the checkbox, type the name of the file to which you want the report written. If this file does not exist, Migrate creates it. If it exists, Migrate overwrites it with the new report. You can specify the same file name for the Post Execution Report and the Error File to combine these two reports into one file.
    Error File	A report that lists any errors encountered during the migration. If there are no errors during the migration, this error file will be empty. In the text box, type the name of the file to which you want the errors written. If this file does not exist, Migrate creates it. If it exists, Migrate overwrites it with the new report. You can specify the same file name for the Error File and the Post Execution Report to combine these two reports into one file.

    Tip: If you want to produce a report describing the effect of the specified migration, select the Impact Analysis Report. This option gives you a report describing the schemas of the currently selected source and destination tables, without performing the migration. See Generating Reports for more information about this and other reports.
15. Click OK to start the migration. The migration's progress is reported in the Status bar at the bottom of the window.
16. When the migration is finished, click the Catalog Browser () button to refresh the contents of the Catalog Browser. The destination tables, if they are new, do not appear in the Browser until you do this.
    Tip: You can save your migration as a MIG file to save time recreating it later. See Saving Your Migration Specifications.