Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Posts
    1,636
    Provided Answers: 1

    Answered: How to export table into IXF file without exporting primary key definitions?

    Hi,
    on DB2 v10.5 I need move about 50 tables from one database to another database. I decided to do this with export. What I see is when export to ixf is performed primary key is also included in ixf file, so when importing primary key is created in target database.

    On target database I would like to do:
    1. import data into tables,
    2. create unique indexes on tables with include additional columns
    3. create primary keys on tables and DB2 will recognize index is already created with the same definition because of step 2.

    What I see now, is there any option to export/import table without primary key? If not I will be forced to create additional step between 1 and 2 to drop primary keys on targed database tables.
    Thanks

  2. Best Answer
    Posted by mark.bb

    "Hi,

    Use a table alias with export (which prevents saving pk info into the file) and a forcecreate modifier with import:
    Code:
    export to myfile.ixf of ixf select a.* from src_tab a;
    import from myfile.ixf of ixf modified by forcecreate create into tgt_tab ...;
    "


  3. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    579
    Provided Answers: 3
    EXPORT TO myfile.ixf OF IXF SELECT all_columns_except_PK FROM your_table
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  4. #3
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    294
    Provided Answers: 45
    Hi,

    Use a table alias with export (which prevents saving pk info into the file) and a forcecreate modifier with import:
    Code:
    export to myfile.ixf of ixf select a.* from src_tab a;
    import from myfile.ixf of ixf modified by forcecreate create into tgt_tab ...;
    Regards,
    Mark.

  5. #4
    Join Date
    Jan 2003
    Posts
    1,636
    Provided Answers: 1
    @aflorin27, I want to export columns of primary key, I just don't want to export primary key definitions.


    @mark.bb, your solution is working fine. Excellent, thanks.

    Additional to your test, I did another test. I took the same export statement as in your case, but I executed import without "modified by forcecreate", so:
    Code:
    import from myfile.ixf of ixf create into tgt_tab
    and it also works. It looks like export has taken care of not exporting primary key definitions. Just wondering what is "modified by forcecreate" suppose to take care of?
    Last edited by grofaty; 03-16-17 at 03:11.

  6. #5
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    294
    Provided Answers: 45
    I get the following on my 10.5.8 if I don't use this modifier:

    SQL3311N The import operation failed because some aspect of the data in the
    IXF input file is not supported with the CREATE parameter or the
    CREATE_REPLACE parameter. Reason code: "7".

    SQL3110N The utility has completed processing. "0" rows were read from the input file.


    What's your db2 version?
    Regards,
    Mark.

  7. #6
    Join Date
    Jan 2003
    Posts
    1,636
    Provided Answers: 1
    @mark.bb, I see I have source database 10.5 where export is executed and target database v11.1 where import is executed.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •