Professional Web Applications Themes

db2move and partitioned db on v8.1 - IBM DB2

I have exported the data from one DB using db2 move and now I want the data to be loaded on a newly created 2 node partitoned db. The source db was not partitoned. Can I load the data using "db2move load". How would I achieve this? What is the best way of loading all the tables from non-partitoned db to the partitioned db?. I have about 200 tables. When I try loading one of the table I get the following error. (I wanted to try loading just one before trying all) Is there anyway I can load the data ...

  1. #1

    Default db2move and partitioned db on v8.1

    I have exported the data from one DB using db2 move and now I want
    the data to be loaded on a newly created 2 node partitoned db. The
    source db was not partitoned. Can I load the data using "db2move
    load". How would I achieve this?

    What is the best way of loading all the tables from non-partitoned db
    to the partitioned db?. I have about 200 tables.

    When I try loading one of the table I get the following error. (I
    wanted to try loading just one before trying all)

    Is there anyway I can load the data using db2move?

    % db2move db2c load -lo replace -l /u02/tmp_pk/LOB

    ***** DB2MOVE *****

    Action: LOAD

    Start time: Fri Oct 10 19:08:58 2003


    Connecting to database DB2C ... successful! Server: DB2 Common Server
    V8.1.0


    *** Table "TEST"."TABLE2": ERROR -6100. Check message file
    tab46.msg!
    *** SQLCODE: -6100 - SQLSTATE:
    *** SQL6100N The partitioning map in the data file and the
    partitioning map for the database are not the same.

    Disconnecting from database ... successful!
    Prince Guest

  2. #2

    Default Re: db2move and partitioned db on v8.1

    Prince Kumar wrote: 

    You can't. db2move exports data in IXF format, but the DB2 splitter
    (built into the load utility in V8) will not accept IXF formatted
    data. You can use db2move import, though.
     

    Write a script to export the tables in delimited format, and another
    script to load the tables.



    Good luck,




    -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
    http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
    -----== Over 100,000 Newsgroups - 19 Different Servers! =-----
    Ian Guest

  3. #3

    Default Re: db2move and partitioned db on v8.1

    Dear Friends,

    Anyone has any ideas? Please advice, how it can be accomplished
    efficiently.

    I have the original tables on one DB without any partitioning key. Now
    I have anothet DB created with two partition groups and the tables
    have partitioning key on the first column of the primary key.

    How do I load the data from the tables which don't have the
    partitioning key to the ones with partitioning key? I would like to
    extract data from all the tables on the original db and load them on
    to the new db.


    Thanks,
    Prince.

    com (Prince Kumar) wrote in message news:<google.com>... 
    Prince Guest

  4. #4

    Default Re: db2move and partitioned db on v8.1

    Thanks Ian for your response.

    I will try the import option first and if it doesn't work I will try
    the 2nd alternative.

    Prince.

    Ian <com> wrote in message news:<3f8ac34a$newsgroups.com>... 
    >
    > You can't. db2move exports data in IXF format, but the DB2 splitter
    > (built into the load utility in V8) will not accept IXF formatted
    > data. You can use db2move import, though.

    >
    > Write a script to export the tables in delimited format, and another
    > script to load the tables.
    >
    >
    >
    > Good luck,
    >
    >
    >
    >
    > -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
    > http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
    > -----== Over 100,000 Newsgroups - 19 Different Servers! =-----[/ref]
    Prince Guest

  5. #5

    Default Re: db2move and partitioned db on v8.1

    Hi,

    I tried the import, but it compalined about dependent tables. Then I
    set the integrity of all the tables to off. Now I am getting the
    following error.

    % db2move db2c import -io replace -l /u02/tmp_pk/LOB


    SQL3109N The utility is beginning to load data from file "tab2.ixf".

    SQL3188N An error occurred while erasing the contents of the table.

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

    I am wondering, whether data can be imported when the table is left in
    check pending status? Noe, I am replacing the table data here.

    Thanks,
    Prince.

    Ian <com> wrote in message news:<3f8ac34a$newsgroups.com>... 
    >
    > You can't. db2move exports data in IXF format, but the DB2 splitter
    > (built into the load utility in V8) will not accept IXF formatted
    > data. You can use db2move import, though.

    >
    > Write a script to export the tables in delimited format, and another
    > script to load the tables.
    >
    >
    >
    > Good luck,
    >
    >
    >
    >
    > -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
    > http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
    > -----== Over 100,000 Newsgroups - 19 Different Servers! =-----[/ref]
    Prince Guest

  6. #6

    Default Re: db2move and partitioned db on v8.1

    Hi Prince,

    LOAD does not necessarily distinguish between input for partitioned and
    non-partitioned tables. Of course, you can load a single partition by
    saying so on the LOAD statement; however, you are free to load all of the
    partitions at once. Then the load statement looks the same as for the
    non-partitioned tables.

    Regards Rolf
    Rolf Guest

  7. #7

    Default Re: db2move and partitioned db on v8.1

    Prince Kumar wrote:
     

    using SET INTEGRITY OFF will not allow you to import your tables
    in the wrong order -- it allows only very limited access to the
    data.

    And, as you found, you can't use import ... replace when you have
    RI defined.


    You could drop all of your foreign keys and then re-apply them after
    importing the data, or, as I suggested earlier, simply write scripts
    to export / load your data in delimited format.


    Good luck,





    -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
    http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
    -----== Over 100,000 Newsgroups - 19 Different Servers! =-----
    Ian Guest

  8. #8

    Default Re: db2move and partitioned db on v8.1

    I have dealt with the same issues. If this is a one-time load
    (migration), try this:

    1. create your partitioned database with everything on a single
    partition
    2. create an export script to unload your original database in ixf
    format, you might be able to use db2move export to get this database
    unloaded.
    3. create a script to set integrity off on all your tables you will
    load
    4. create a script to run load commands for each table in the new
    database
    5. set integrity on for all your tables. You may have to make
    multiple runs of the script if you don't run them in the right parent
    child order. You can do this without harm
    6. now that the partitioned table is loaded, alter your partition
    groups to add the additional partitions
    7. redistribute the data

    Like I said, this works for a one time deal, it won't work for
    on-going loads.

    Jack

    com (Prince Kumar) wrote in message news:<google.com>... 
    Jack Guest

Similar Threads

  1. Replies: 5
    Last Post: October 6th, 11:43 PM
  2. Partitioned views
    By Amrit in forum IBM DB2
    Replies: 2
    Last Post: August 25th, 07:06 PM
  3. db2move and identity columns
    By Poul Mller Hansen in forum IBM DB2
    Replies: 1
    Last Post: July 13th, 01:56 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139