SEO

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" <adavi@comcast.net> wrote in message
news:c4w7b.406960$Ho3.62540@sccrnsc03...
> 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.

Hi,

2 utilities

db2look
db2move

with some underwater rocks.

ddl after db2look need to be parsed 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
www.it4profit.com

P.S. mail me if you have troubles.

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#Antispam@mapsitnA#e-vision-group.com> wrote in message
news:3f5eceb8$0$248$4d4ebb8e@news.nl.uu.net...
> Hi,
>
> 2 utilities
>
> db2look
> db2move
>
> with some underwater rocks.
>
> ddl after db2look need to be parsed 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
> www.it4profit.com
>
> P.S. mail me if you have troubles.
>

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_tsi@enonic.com> wrote in message news:<3f61ceef@news.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#Antispam@mapsitnA#e-vision-group.com> wrote in message
> news:3f5eceb8$0$248$4d4ebb8e@news.nl.uu.net...
> > Hi,
> >
> > 2 utilities
> >
> > db2look
> > db2move
> >
> > with some underwater rocks.
> >
> > ddl after db2look need to be parsed 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
> > www.it4profit.com
> >
> > P.S. mail me if you have troubles.
> >