Professional Web Applications Themes

database migration from Windows DB2 to Linux DB2 - IBM DB2

We need to migrate a database (basically all the 300+ tables along with the data) from a Windows UDB instance to a Linux UDB instance. What is the best way of doing this ? Thanks...

  1. #1

    Default database migration from Windows DB2 to Linux DB2

    We need to migrate a database (basically all the 300+ tables along with the
    data) from a Windows UDB instance
    to a Linux UDB instance. What is the best way of doing this ?

    Thanks


    Murty Guest

  2. #2

    Default Re: database migration from Windows DB2 to Linux DB2

    "Murty" <adavicomcast.net> wrote in message
    news:c4w7b.406960$Ho3.62540sccrnsc03...
    > We need to migrate a database (basically all the 300+ tables along with
    the
    > data) from a Windows UDB instance
    > to a Linux UDB instance. What is the best way of doing this ?
    >
    > Thanks
    >
    Bad news is that you cannot use backup and restore of the database from
    Windows to Linux. You can create the ddl with db2look and then export and
    import/load the data.


    Mark A Guest

  3. #3

    Default Re: database migration from Windows DB2 to Linux DB2

    Hi,

    2 utilities

    db2look
    db2move

    with some underwater rocks.

    ddl after db2look need to be pd to :

    - create db
    - cfg db
    - create bp (if you have tbs with page > 4K - restart db)
    - create tablespaces
    - create tables without any constrains

    then

    - load data (if you have tables with identity you need handmade load +
    alter tables with autoincremental column for new start values)
    - create table constrains, indexes, fk
    - create summary tables

    After that you need write simple script for create ddl for triggers, view
    and fucntions which create its in right order(!!!).

    Last stage - create sql procedures.


    Andrew
    [url]www.it4profit.com[/url]

    P.S. mail me if you have troubles.

    andreyp#Antispam@mapsitnA#e-vision-group.com Guest

  4. #4

    Default Re: database migration from Windows DB2 to Linux DB2

    We have moved all our bases from win to linux.
    we simply used db2look and db2move and kept all our foreign keys, but had to
    manually inform db2 that the table data are ok, as db2 thinks every foreign
    key is bad or something.


    # Create a database to move the data into:
    # create_db <dbname> # Same name as the source platform to avoid editing the
    _ddl.sql file

    # Increase some parameters on the database to increase speed and avoid
    stops:
    # set_params_higher <dbname>


    # Getting the DB DDL:
    # On the source platform do:
    # db2look -d <dbname> -e -i <admin userid> -w <admin passwd> -o
    <dbname>_ddl.sql

    # On the destination platform do (as instance owner):
    # db2 -tf <dbname>_ddl.sql
    # one should remove the "user .....;" from the connect line


    # Getting the data:
    # On the source platform do (in a new directory):
    # db2move <dbname> export
    # zip down the directory and copy it to the destination platform.

    # On the destination platform do (as instance owner):
    # unzip the data
    # enter the directory with the data
    # db2move <dbname> load


    # Unfortunately this leaves some tables in pending mode.
    # It is caused by foreignkeys to tabledata that is not there yet.

    # The solution is to run a script:
    # set_integrity_all_tables <dbname>

    # Sets the integrity right.







    #!/bin/bash
    set_integrity_all_tables()
    # argument 1 is the name of the database
    {
    # 1. make a list of all the tables
    tables=$(get_tables $1)
    # 2. run through the tables (for loop)
    # 3. run integrity check on the table in the loop
    # 4. if the test went ok - remove the table from the tables list
    # 5. if there is tables left - start again

    dbname="$1"
    db2 connect to ${dbname}
    new_tables=""

    echo
    echo "Setting integrity for all tables"

    for t in $tables
    do
    result=$(db2 set integrity for ${t} materialized query, foreign key,
    generated column, staging, check im
    mediate unchecked)
    echo " $t"
    echo "result: $result"
    done

    db2 terminate
    }

    I've done some quick paste/copy here.
    Hope it solves things for you.
    Note!! Please make sure your .ddl file has proper cr/lf after moving it from
    windows to linux. We wasted quite some time here :)

    regards
    Thomas Sigdestad

    <andreyp#AntispammapsitnA#e-vision-group.com> wrote in message
    news:3f5eceb8$0$248$4d4ebb8enews.nl.uu.net...
    > Hi,
    >
    > 2 utilities
    >
    > db2look
    > db2move
    >
    > with some underwater rocks.
    >
    > ddl after db2look need to be pd to :
    >
    > - create db
    > - cfg db
    > - create bp (if you have tbs with page > 4K - restart db)
    > - create tablespaces
    > - create tables without any constrains
    >
    > then
    >
    > - load data (if you have tables with identity you need handmade load +
    > alter tables with autoincremental column for new start values)
    > - create table constrains, indexes, fk
    > - create summary tables
    >
    > After that you need write simple script for create ddl for triggers, view
    > and fucntions which create its in right order(!!!).
    >
    > Last stage - create sql procedures.
    >
    >
    > Andrew
    > [url]www.it4profit.com[/url]
    >
    > P.S. mail me if you have troubles.
    >

    Thomas Sigdestad Guest

  5. #5

    Default Re: database migration from Windows DB2 to Linux DB2

    What about triggers, store procedures and applications?

    db2look generates ddl
    db2move moves data

    How to extract view, functions, triggers and store procedure
    definition in sql script format with all grants? What
    should be the order?


    B



    "Thomas Sigdestad" <_delete_tsienonic.com> wrote in message news:<3f61ceefnews.wineasy.se>...
    > We have moved all our bases from win to linux.
    > we simply used db2look and db2move and kept all our foreign keys, but had to
    > manually inform db2 that the table data are ok, as db2 thinks every foreign
    > key is bad or something.
    >
    >
    > # Create a database to move the data into:
    > # create_db <dbname> # Same name as the source platform to avoid editing the
    > _ddl.sql file
    >
    > # Increase some parameters on the database to increase speed and avoid
    > stops:
    > # set_params_higher <dbname>
    >
    >
    > # Getting the DB DDL:
    > # On the source platform do:
    > # db2look -d <dbname> -e -i <admin userid> -w <admin passwd> -o
    > <dbname>_ddl.sql
    >
    > # On the destination platform do (as instance owner):
    > # db2 -tf <dbname>_ddl.sql
    > # one should remove the "user .....;" from the connect line
    >
    >
    > # Getting the data:
    > # On the source platform do (in a new directory):
    > # db2move <dbname> export
    > # zip down the directory and copy it to the destination platform.
    >
    > # On the destination platform do (as instance owner):
    > # unzip the data
    > # enter the directory with the data
    > # db2move <dbname> load
    >
    >
    > # Unfortunately this leaves some tables in pending mode.
    > # It is caused by foreignkeys to tabledata that is not there yet.
    >
    > # The solution is to run a script:
    > # set_integrity_all_tables <dbname>
    >
    > # Sets the integrity right.
    >
    >
    >
    >
    >
    >
    >
    > #!/bin/bash
    > set_integrity_all_tables()
    > # argument 1 is the name of the database
    > {
    > # 1. make a list of all the tables
    > tables=$(get_tables $1)
    > # 2. run through the tables (for loop)
    > # 3. run integrity check on the table in the loop
    > # 4. if the test went ok - remove the table from the tables list
    > # 5. if there is tables left - start again
    >
    > dbname="$1"
    > db2 connect to ${dbname}
    > new_tables=""
    >
    > echo
    > echo "Setting integrity for all tables"
    >
    > for t in $tables
    > do
    > result=$(db2 set integrity for ${t} materialized query, foreign key,
    > generated column, staging, check im
    > mediate unchecked)
    > echo " $t"
    > echo "result: $result"
    > done
    >
    > db2 terminate
    > }
    >
    > I've done some quick paste/copy here.
    > Hope it solves things for you.
    > Note!! Please make sure your .ddl file has proper cr/lf after moving it from
    > windows to linux. We wasted quite some time here :)
    >
    > regards
    > Thomas Sigdestad
    >
    > <andreyp#AntispammapsitnA#e-vision-group.com> wrote in message
    > news:3f5eceb8$0$248$4d4ebb8enews.nl.uu.net...
    > > Hi,
    > >
    > > 2 utilities
    > >
    > > db2look
    > > db2move
    > >
    > > with some underwater rocks.
    > >
    > > ddl after db2look need to be pd to :
    > >
    > > - create db
    > > - cfg db
    > > - create bp (if you have tbs with page > 4K - restart db)
    > > - create tablespaces
    > > - create tables without any constrains
    > >
    > > then
    > >
    > > - load data (if you have tables with identity you need handmade load +
    > > alter tables with autoincremental column for new start values)
    > > - create table constrains, indexes, fk
    > > - create summary tables
    > >
    > > After that you need write simple script for create ddl for triggers, view
    > > and fucntions which create its in right order(!!!).
    > >
    > > Last stage - create sql procedures.
    > >
    > >
    > > Andrew
    > > [url]www.it4profit.com[/url]
    > >
    > > P.S. mail me if you have troubles.
    > >
    bob Guest

Similar Threads

  1. database migration
    By ben in forum MySQL
    Replies: 2
    Last Post: January 12th, 07:55 AM
  2. Coldfusino MX migration from windows to Linux
    By xinfg2133 in forum Coldfusion Server Administration
    Replies: 1
    Last Post: August 3rd, 07:45 PM
  3. Replies: 3
    Last Post: September 18th, 04:29 PM
  4. Replies: 4
    Last Post: September 17th, 03:54 PM
  5. Replies: 1
    Last Post: July 28th, 02:18 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