To migrate certain rows and columns

Restriction: This topic applies to Windows environments only.

You can copy certain columns or certain rows from one table to another using Migrate's query feature. You can also use this feature to combine data from different tables into one result.

See Replicating Data if you wish to update, insert, or delete specific rows and columns.

Note:

Migrate's query feature is designed to migrate tables. It is not an all-purpose SQL processor. Do not use it to perform any SQL other than a single SELECT statement; for example, do not try to CREATE or DROP a location. If you need to include SQL with your migration, use prologue and epilogue scripts. See Adding SQL to a Migration.

The procedure in this section describes how to create a query and migrate its result. Before you begin, review the following prerequisites:

Note:

The location to which you want to migrate the results of your query must already exist. If it does not, you must first create it using a database management tool such as SQLWizard.

  1. In the From drop-down list, select the location containing the tables you want to query.
  2. In the To drop-down list, select the location to which you want the query result migrated.
    Note:

    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.

  3. Select the Define Query command on the Object menu to display an empty Query window.
  4. In the upper pane of the Query window, enter a Table and AuthID for the result of the query.
    Note:

    If this table does not exist already, Migrate will create it for you. If it does exist, the result of your query must match the existing table's schema exactly. That is, it must produce the same number of columns, of the same size and type, and in the same order as the destination table.

  5. If you want the result stored to a specific database and table space, type their names in the Database and Tablespace fields.
  6. In the lower pane, type a single SELECT statement. If you want to open a saved query that you have created using SQLWizard, double-click its name in the Catalog Browser's Query tab. For more information about recalling a saved query, see The Query Tab.

    You can enter any valid SELECT statement, even those containing joins and unions.

    Note:

    To ensure a successful query, specify your tables using fully-qualified (i.e., three-part) names.

    The following are examples of valid query statements: 

    SELECT *
		FROM tutorial.tutorial.employee
		WHERE city = 'miami'
		SELECT e_no, lname, city
		FROM tutorial.tutorial.employee
		SELECT pname, qty
		FROM tutorial.tutorial.part p1,
		tutorial.tutorial.partsupp p2 
		WHERE p1.pno = p2.pno
  7. After you have entered a SELECT statement, click the Table/Query Options (
    *
    ) button in the toolbar or select the Table/Query Options command on the Object menu.
  8. In the Query Options dialog box, specify how you want the results of this query written, then click OK. You can specify what action Migrate should take if the destination table already exists, and how many total records should be copied.

    If you need specific information about an option in this dialog box, click Help or see Query Options.

  9. If you want to process multiple queries, repeat steps 1 - 8. Each SELECT statement must use a separate Query window (you can minimize a Query window to reduce screen clutter).
  10. When you have finished specifying the query(s), click the Run
    *
    button in the toolbar, or select the Run SQL command on the Commands menu.
  11. In the Run dialog box, make sure the Execute Migrate check box 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.
  12. In the Migrate Options group box, select the kind of reports and 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.

    Note:

    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 that describes 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.

  13. Click OK to start the migration. The migration's progress is reported in the Status bar at the bottom of the window and in a progress window.

    When a migration is processed, queries are processed after any tables in the Schema Viewer. When multiple queries are specified, they are processed in window-number order.

  14. 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.
    Note:

    You can save your migration as a MIG file to save time recreating it later. See Saving Your Migration Specifications.

Parent topic: Migrate Utility