Problem:

How can we import data into Mainframe Express? The only options available appear to be either to create a new table (create table) or replace data with the new imported data (Replace Data). I know we can specify Replace = Y to replace all data or we can specify Replace = N to append data to the existing table. Can we update an existing record on the table using the import feature? If import does not provide this feature, what process can we use if we want to update existing records in the table?

Resolution:

You cannot use the Import utility to import the data, but you can use the Migrate utility. The following is a simple working example that demonstrates the process. We recommend that you work through this example to become familiar with the process:

1. Using the following SQL, build a table on the mainframe:

   CREATE TABLE MFIREC.RDDMIG
   (
       F1                        CHAR(5) NOT NULL,
       F2                        CHAR(5)
   );
   INSERT INTO MFIREC.RDDMIG VALUES
   (
       'A    ',
       '1    '
   );
   INSERT INTO MFIREC.RDDMIG VALUES
   (
       'B    ',
       '2    '
   );
   COMMIT WORK;

2. From the mainframe location in the Migrate catalog browser, drag MFIREC.RDDMIG into the Source Location dialog box.
   Note: If you haven't changed the location listed in the From drop-down list, Migrate does it for you.
3. From the To drop-down list, click the XDB Server location name.
4. Click Commands > Run.
5. Check Execute Migrate; then click OK. The Execution Information window appears showing information similar to the following:

   Processing Table RDDMIG ...
   Creating Insert SQL INSERT INTO 
   LOCATIONNAME.MFIREC.RDDMIG (F1, F2) VALUES (?, ?) ...
   Execution Successful ...
   
   

   Note: You can do this step more than once. However, because you create the table in this step, you should first change the If Table Already Exists table option to Replace Table or Replace Data before executing the migrate a second time. To set table options, click on the dash in the upper left-hand corner of the Source Location dialog box.
6. To update the second row, start SQL Wizard and execute a script similar to the following:

   SET LOCATION TO UDB_81_OS390 (this would be your mainframe location name);
   UPDATE MFIREC.RDDMIG 
   SET F2 = "200"
   WHERE F1 = "B";
   COMMIT WORK;
   
   

7. In the Migrate utility, close the Execution Information window and the Source Location dialog box.
8. Click Object > Define Query.
9. Type the table name into the Table field.
10. Type the table creator's name into the Authid field.
11. In the bottom half of the Define Query dialog box, type a select statement that specifies only the row you want to migrate. For example:

    SELECT * FROM MFIREC.RDDMIG WHERE F1='B';

12. Click Object > Table/Query Options.
13. Choose one of the following options under If Table Already Exists:

    Replace Data	Migrate replaces all existing records in the destination table with records from the source table. All data in the existing table is deleted.
    Replace Table	Migrate drops the destination table and creates a new table containing the result of this migration.
    Update No Insert	If the row with they key already exists, Migrate updates data only.
    Update Insert	If the row does not exist, Migrate updates existing data or inserts the row.
    Delete Match	Migrate deletes an existing matching row.

14. Click OK.
15. In the Query dialog box, click Update Rule.

    The Query Update Rules dialog box appears showing Field Name, Key, and Update Rule columns. In this example, the F1 field is the key for the update and the F2 field is the key for data to update.

16. On the drop-down list in the Key column on the row for the F1 field, click TRUE.

    This tells Migrate to do its comparison based upon the contents of the F1 column. You can select multiple columns to help you determine when to update and when to insert. The WHERE clause in the original queries that brought us to this point regulates which rows are processed. In this example, WHERE F1='B'.

17. On the Query Update Rules dialog box, click OK.
18. Click Commands > Run SQL.
19. On the Run SQL dialog box, check Run SQL; then click OK.

    The Execution Information appears showing details of the processing. You have now successfully migrated a single row based upon the query originally given.

Incident Number: 2259690