f



DB2MOVE #4

I want to copy the data of all tables from database A  to database B,
which are already defined and are
sitting on different aix servers.  Basically, this is a "refresh" of a
test db from its corresponding prod db.

Many tables have PK/FK  contraints defined on them.

If I use db2move/export  followed by  db2move/IMPORT,   will the
tables be imported in the correct sequence
so as to avoid RI errors ?   If not,  what's the workaround ?
Would it work differently if I use db2move/LOAD
instead of IMPORT  ?   Am I better off using a redirected restore from
a prod backup ?

Thanks
Orlando

0
Puer_Apuliae
7/17/2009 7:39:50 PM
comp.databases.ibm-db2 12448 articles. 3 followers. arunrocks (9) is leader. Post Follow

12 Replies
33119 Views

Similar Articles

[PageSpeed] 47

Puer_Apuliae wrote:
> I want to copy the data of all tables from database A  to database B,
> which are already defined and are
> sitting on different aix servers.  Basically, this is a "refresh" of a
> test db from its corresponding prod db.
> 
> Many tables have PK/FK  contraints defined on them.
> 
> If I use db2move/export  followed by  db2move/IMPORT,   will the
> tables be imported in the correct sequence
> so as to avoid RI errors ?   If not,  what's the workaround ?
> Would it work differently if I use db2move/LOAD
> instead of IMPORT  ?   Am I better off using a redirected restore from
> a prod backup ?
> 
> Thanks
> Orlando
> 
Another possible solution: Optim Test Data management:

http://www-01.ibm.com/software/data/data-management/optim/test-data-management-solution/

Larry E.
0
Larry
7/17/2009 10:12:40 PM
"Puer_Apuliae" <puer_apuliae@live.com> wrote in message 
news:f6de6eb3-88a6-4333-8155-91f868fd90b0@y10g2000prf.googlegroups.com...
>
> I want to copy the data of all tables from database A  to database B,
> which are already defined and are
> sitting on different aix servers.  Basically, this is a "refresh" of a
> test db from its corresponding prod db.
>
> Many tables have PK/FK  contraints defined on them.
>
> If I use db2move/export  followed by  db2move/IMPORT,   will the
> tables be imported in the correct sequence
> so as to avoid RI errors ?   If not,  what's the workaround ?
> Would it work differently if I use db2move/LOAD
> instead of IMPORT  ?   Am I better off using a redirected restore from
> a prod backup ?
>
> Thanks
> Orlando

A redirected restore is preferable if possible.

For db2move, I first create the schema with db2look for everything but the 
foreign keys (which are segregated toward the end of the db2look). Then I do 
the db2move, then create the foreign keys after that.

db2move import is OK if there is not too much data and you use the 
COMMITCOUNT option (or else the transaction logs may fill up), but I usually 
use the load option, which works well if there are no foreign keys defined.

You can also work up a script to do set integrity on all tables (you must do 
each table separately), but you may have to run it multiple times if the 
tables are not listed in the correct order. It is usually easier to run it 
multiple times instead of trying to figure out the correct order. Just keep 
running it until you get an error for each table saying it is not in set 
integrity pending state. 


0
Mark
7/18/2009 12:09:37 AM
On 18 Jul., 00:12, Larry <la...@nospam.net> wrote:
> Puer_Apuliae wrote:
> > I want to copy the data of all tables from database A =A0to database B,
> > which are already defined and are
> > sitting on different aix servers. =A0Basically, this is a "refresh" of =
a
> > test db from its corresponding prod db.
>
> > Many tables have PK/FK =A0contraints defined on them.
>
> > If I use db2move/export =A0followed by =A0db2move/IMPORT, =A0 will the
> > tables be imported in the correct sequence
> > so as to avoid RI errors ? =A0 If not, =A0what's the workaround ?
> > Would it work differently if I use db2move/LOAD
> > instead of IMPORT =A0? =A0 Am I better off using a redirected restore f=
rom
> > a prod backup ?
>
> > Thanks
> > Orlando
>
> Another possible solution: Optim Test Data management:
>
> http://www-01.ibm.com/software/data/data-management/optim/test-data-m...

another tool i find useful for moving data is http://jailer.sourceforge.net=
/
0
Stevie
7/18/2009 7:59:57 AM
db2move option can be used when the database is of different version
and OS are different.
so that it does export and import.
If not a redirected restore will do.

On Jul 18, 12:59=A0pm, Stevie <stevemaie...@web.de> wrote:
> On 18 Jul., 00:12, Larry <la...@nospam.net> wrote:
>
>
>
> > Puer_Apuliae wrote:
> > > I want to copy the data of all tables from database A =A0to database =
B,
> > > which are already defined and are
> > > sitting on different aix servers. =A0Basically, this is a "refresh" o=
f a
> > > test db from its corresponding prod db.
>
> > > Many tables have PK/FK =A0contraints defined on them.
>
> > > If I use db2move/export =A0followed by =A0db2move/IMPORT, =A0 will th=
e
> > > tables be imported in the correct sequence
> > > so as to avoid RI errors ? =A0 If not, =A0what's the workaround ?
> > > Would it work differently if I use db2move/LOAD
> > > instead of IMPORT =A0? =A0 Am I better off using a redirected restore=
 from
> > > a prod backup ?
>
> > > Thanks
> > > Orlando
>
> > Another possible solution: Optim Test Data management:
>
> >http://www-01.ibm.com/software/data/data-management/optim/test-data-m...
>
> another tool i find useful for moving data ishttp://jailer.sourceforge.ne=
t/

0
Sivaswami
7/18/2009 8:59:55 AM
On Jul 18, 2:09=A0am, "Mark A" <no...@nowhere.com> wrote:
> "Puer_Apuliae" <puer_apul...@live.com> wrote in message
>
> news:f6de6eb3-88a6-4333-8155-91f868fd90b0@y10g2000prf.googlegroups.com...
>
>
>
>
>
> > I want to copy the data of all tables from database A =A0to database B,
> > which are already defined and are
> > sitting on different aix servers. =A0Basically, this is a "refresh" of =
a
> > test db from its corresponding prod db.
>
> > Many tables have PK/FK =A0contraints defined on them.
>
> > If I use db2move/export =A0followed by =A0db2move/IMPORT, =A0 will the
> > tables be imported in the correct sequence
> > so as to avoid RI errors ? =A0 If not, =A0what's the workaround ?
> > Would it work differently if I use db2move/LOAD
> > instead of IMPORT =A0? =A0 Am I better off using a redirected restore f=
rom
> > a prod backup ?
>
> > Thanks
> > Orlando
>
> A redirected restore is preferable if possible.
>
> For db2move, I first create the schema with db2look for everything but th=
e
> foreign keys (which are segregated toward the end of the db2look). Then I=
 do
> the db2move, then create the foreign keys after that.
>
> db2move import is OK if there is not too much data and you use the
> COMMITCOUNT option (or else the transaction logs may fill up), but I usua=
lly
> use the load option, which works well if there are no foreign keys define=
d.
>
> You can also work up a script to do set integrity on all tables (you must=
 do
> each table separately), but you may have to run it multiple times if the
> tables are not listed in the correct order. It is usually easier to run i=
t
> multiple times instead of trying to figure out the correct order. Just ke=
ep
> running it until you get an error for each table saying it is not in set
> integrity pending state.

Something like

SELECT 'SET INTEGRITY FOR ' || rtrim(tabschema) || '.' || tabname || '
IMMEDIATE CHECKED;'
FROM syscat.tables
WHERE tabschema NOT LIKE 'SYS%';

can generate you series of commands to execute to set integrity on non-
catalog tables.

BTW, one might need to set code page before importing (for example,
when importing to DB2/Windows from DB2/AIX export, at least for some
European code pages/territories):

db2set DB2CODEPAGE=3D1250

Darko Krstic
0
darko
7/23/2009 5:50:29 AM
On Jul 23, 7:50=A0am, darko <darko.krs...@gmail.com> wrote:
> On Jul 18, 2:09=A0am, "Mark A" <no...@nowhere.com> wrote:
>
>
>
>
>
> > "Puer_Apuliae" <puer_apul...@live.com> wrote in message
>
> >news:f6de6eb3-88a6-4333-8155-91f868fd90b0@y10g2000prf.googlegroups.com..=
..
>
> > > I want to copy the data of all tables from database A =A0to database =
B,
> > > which are already defined and are
> > > sitting on different aix servers. =A0Basically, this is a "refresh" o=
f a
> > > test db from its corresponding prod db.
>
> > > Many tables have PK/FK =A0contraints defined on them.
>
> > > If I use db2move/export =A0followed by =A0db2move/IMPORT, =A0 will th=
e
> > > tables be imported in the correct sequence
> > > so as to avoid RI errors ? =A0 If not, =A0what's the workaround ?
> > > Would it work differently if I use db2move/LOAD
> > > instead of IMPORT =A0? =A0 Am I better off using a redirected restore=
 from
> > > a prod backup ?
>
> > > Thanks
> > > Orlando
>
> > A redirected restore is preferable if possible.
>
> > For db2move, I first create the schema with db2look for everything but =
the
> > foreign keys (which are segregated toward the end of the db2look). Then=
 I do
> > the db2move, then create the foreign keys after that.
>
> > db2move import is OK if there is not too much data and you use the
> > COMMITCOUNT option (or else the transaction logs may fill up), but I us=
ually
> > use the load option, which works well if there are no foreign keys defi=
ned.
>
> > You can also work up a script to do set integrity on all tables (you mu=
st do
> > each table separately), but you may have to run it multiple times if th=
e
> > tables are not listed in the correct order. It is usually easier to run=
 it
> > multiple times instead of trying to figure out the correct order. Just =
keep
> > running it until you get an error for each table saying it is not in se=
t
> > integrity pending state.
>
> Something like
>
> SELECT 'SET INTEGRITY FOR ' || rtrim(tabschema) || '.' || tabname || '
> IMMEDIATE CHECKED;'
> FROM syscat.tables
> WHERE tabschema NOT LIKE 'SYS%';
>
> can generate you series of commands to execute to set integrity on non-
> catalog tables.
>
> BTW, one might need to set code page before importing (for example,
> when importing to DB2/Windows from DB2/AIX export, at least for some
> European code pages/territories):
>
> db2set DB2CODEPAGE=3D1250
>
> Darko Krstic

You can include multiple tables in one SET INTEGRITY command. This
way, you don't need to worry about the order in which to execute them.

Somewhere in this group, there is a SQL statement using XMLAGG that
will generate this command for you.

--
Frederik
0
Frederik
7/23/2009 1:15:45 PM
"Frederik" <engelenfrederik@gmail.com> wrote in message 
news:785f46b6-004f-422f-92fd-34f8baedcf23@c2g2000yqi.googlegroups.com...
> On Jul 23, 7:50 am, darko <darko.krs...@gmail.com> wrote:
> You can include multiple tables in one SET INTEGRITY command. This
> way, you don't need to worry about the order in which to execute them.
>
> Somewhere in this group, there is a SQL statement using XMLAGG that
> will generate this command for you.
>
> --
> Frederik

Unless something has changed recently (9.7), this will not work. If there 
are any tables in the list that are not in some kind of pending state, then 
the entire SET INTEGRITY statement will fail.

This is one thing that I would really like to see IBM fix. 


0
Mark
7/23/2009 9:45:26 PM
On Jul 23, 2:45=A0pm, "Mark A" <no...@nowhere.com> wrote:
> If there
> are any tables in the list that are not in some kind of pending state, th=
en
> the entire SET INTEGRITY statement will fail.

Well, if you modify the query to only look at those tables in set
integrity pending state, then this shouldn't be a problem.  Something
like:

SELECT 'SET INTEGRITY FOR ' || left(replace(replace(xmlserialize(xmlagg
(xmlelement(NAME "X", rtrim(tabschema) || '.' || rtrim(tabname))) as
varchar(4000)), '<X>', ''), '</X>', ', '), length(replace(replace
(xmlserialize(xmlagg(xmlelement(NAME "X", rtrim(tabschema) || '.' ||
rtrim(tabname))) as varchar(4000)), '<X>', ''), '</X>', ', ')) - 2) ||
' IMMEDIATE CHECKED;'
FROM syscat.tables
WHERE tabschema NOT LIKE 'SYS%'
  AND status =3D 'C'

should work.  Unless there are so many tables that their names won't
fit into 4000 characters, that is.

-Chris
0
Chris
7/24/2009 3:54:48 PM
On Jul 23, 3:15 pm, Frederik <engelenfrede...@gmail.com> wrote:
> On Jul 23, 7:50 am, darko <darko.krs...@gmail.com> wrote:
>
>
>
> > On Jul 18, 2:09 am, "Mark A" <no...@nowhere.com> wrote:
>
> > > "Puer_Apuliae" <puer_apul...@live.com> wrote in message
>
> > >news:f6de6eb3-88a6-4333-8155-91f868fd90b0@y10g2000prf.googlegroups.com...
>
> > > > I want to copy the data of all tables from database A  to database B,
> > > > which are already defined and are
> > > > sitting on different aix servers.  Basically, this is a "refresh" of a
> > > > test db from its corresponding prod db.
>
> > > > Many tables have PK/FK  contraints defined on them.
>
> > > > If I use db2move/export  followed by  db2move/IMPORT,   will the
> > > > tables be imported in the correct sequence
> > > > so as to avoid RI errors ?   If not,  what's the workaround ?
> > > > Would it work differently if I use db2move/LOAD
> > > > instead of IMPORT  ?   Am I better off using a redirected restore from
> > > > a prod backup ?
>
> > > > Thanks
> > > > Orlando
>
> > > A redirected restore is preferable if possible.
>
> > > For db2move, I first create the schema with db2look for everything but the
> > > foreign keys (which are segregated toward the end of the db2look). Then I do
> > > the db2move, then create the foreign keys after that.
>
> > > db2move import is OK if there is not too much data and you use the
> > > COMMITCOUNT option (or else the transaction logs may fill up), but I usually
> > > use the load option, which works well if there are no foreign keys defined.
>
> > > You can also work up a script to do set integrity on all tables (you must do
> > > each table separately), but you may have to run it multiple times if the
> > > tables are not listed in the correct order. It is usually easier to run it
> > > multiple times instead of trying to figure out the correct order. Just keep
> > > running it until you get an error for each table saying it is not in set
> > > integrity pending state.
>
> > Something like
>
> > SELECT 'SET INTEGRITY FOR ' || rtrim(tabschema) || '.' || tabname || '
> > IMMEDIATE CHECKED;'
> > FROM syscat.tables
> > WHERE tabschema NOT LIKE 'SYS%';
>
> > can generate you series of commands to execute to set integrity on non-
> > catalog tables.
>
> > BTW, one might need to set code page before importing (for example,
> > when importing to DB2/Windows from DB2/AIX export, at least for some
> > European code pages/territories):
>
> > db2set DB2CODEPAGE=1250
>
> > Darko Krstic
>
> You can include multiple tables in one SET INTEGRITY command. This
> way, you don't need to worry about the order in which to execute them.
>
> Somewhere in this group, there is a SQL statement using XMLAGG that
> will generate this command for you.
>
> --
> Frederik

What is wrong to generate a series of SET INTEGRITY commands, one for
each table? You can just use the output of the SELECT I've mentioned
as a SQL script to execute those SET INTEGRITY commands. It is
primitive, but worked fairly in practice. That way there is no risk to
generate a single SET INTEGRITY command that is to large to be
executed or contains tables not in pending state (the problems that
Mark and Chris highlighted).

Darko Krstic
0
darko
7/29/2009 6:19:28 PM
On Jul 23, 3:15 pm, Frederik <engelenfrede...@gmail.com> wrote:
> On Jul 23, 7:50 am, darko <darko.krs...@gmail.com> wrote:
>
>
>
> > On Jul 18, 2:09 am, "Mark A" <no...@nowhere.com> wrote:
>
> > > "Puer_Apuliae" <puer_apul...@live.com> wrote in message
>
> > >news:f6de6eb3-88a6-4333-8155-91f868fd90b0@y10g2000prf.googlegroups.com...
>
> > > > I want to copy the data of all tables from database A  to database B,
> > > > which are already defined and are
> > > > sitting on different aix servers.  Basically, this is a "refresh" of a
> > > > test db from its corresponding prod db.
>
> > > > Many tables have PK/FK  contraints defined on them.
>
> > > > If I use db2move/export  followed by  db2move/IMPORT,   will the
> > > > tables be imported in the correct sequence
> > > > so as to avoid RI errors ?   If not,  what's the workaround ?
> > > > Would it work differently if I use db2move/LOAD
> > > > instead of IMPORT  ?   Am I better off using a redirected restore from
> > > > a prod backup ?
>
> > > > Thanks
> > > > Orlando
>
> > > A redirected restore is preferable if possible.
>
> > > For db2move, I first create the schema with db2look for everything but the
> > > foreign keys (which are segregated toward the end of the db2look). Then I do
> > > the db2move, then create the foreign keys after that.
>
> > > db2move import is OK if there is not too much data and you use the
> > > COMMITCOUNT option (or else the transaction logs may fill up), but I usually
> > > use the load option, which works well if there are no foreign keys defined.
>
> > > You can also work up a script to do set integrity on all tables (you must do
> > > each table separately), but you may have to run it multiple times if the
> > > tables are not listed in the correct order. It is usually easier to run it
> > > multiple times instead of trying to figure out the correct order. Just keep
> > > running it until you get an error for each table saying it is not in set
> > > integrity pending state.
>
> > Something like
>
> > SELECT 'SET INTEGRITY FOR ' || rtrim(tabschema) || '.' || tabname || '
> > IMMEDIATE CHECKED;'
> > FROM syscat.tables
> > WHERE tabschema NOT LIKE 'SYS%';
>
> > can generate you series of commands to execute to set integrity on non-
> > catalog tables.
>
> > BTW, one might need to set code page before importing (for example,
> > when importing to DB2/Windows from DB2/AIX export, at least for some
> > European code pages/territories):
>
> > db2set DB2CODEPAGE=1250
>
> > Darko Krstic
>
> You can include multiple tables in one SET INTEGRITY command. This
> way, you don't need to worry about the order in which to execute them.
>
> Somewhere in this group, there is a SQL statement using XMLAGG that
> will generate this command for you.
>
> --
> Frederik

What is wrong to generate a series of SET INTEGRITY commands, one for
each table? You can just use the output of the SELECT I've mentioned
as a SQL script to execute those SET INTEGRITY commands. It is
primitive, but worked fairly in practice. That way there is no risk to
generate a single SET INTEGRITY command that is to large to be
executed or contains tables not in pending state (the problems that
Mark and Chris highlighted).

Darko Krstic
0
darko
7/29/2009 6:20:35 PM
On Jul 29, 11:19=A0am, darko <darko.krs...@gmail.com> wrote:
> What is wrong to generate a series of SET INTEGRITY commands, one for
> each table?

There isn't really anything wrong with that.  You will just need to
look at a potentially long list of output to determine if all of them
worked right the first time.  If you have many tables with RI, it is
very likely that that won't happen, which means you will need to run
the script several times and each time look to see if every command
failed saying the table is not in set integrity pending state.  Just a
bit more manual work, but it does work.

0
Chris
7/29/2009 11:15:03 PM
On Jul 30, 1:15=A0am, Chris <cunningham...@gmail.com> wrote:
> On Jul 29, 11:19=A0am, darko <darko.krs...@gmail.com> wrote:
>
> > What is wrong to generate a series of SET INTEGRITY commands, one for
> > each table?
>
> There isn't really anything wrong with that. =A0You will just need to
> look at a potentially long list of output to determine if all of them
> worked right the first time. =A0If you have many tables with RI, it is
> very likely that that won't happen, which means you will need to run
> the script several times and each time look to see if every command
> failed saying the table is not in set integrity pending state. =A0Just a
> bit more manual work, but it does work.

If you have circular dependencies, you need to check several tables at
a time, but that is rarely the case. I usually do the "where
status=3D'C'" trick.

Note: Better do
export DB2CODEPAGE=3Dnnn
db2 terminate

instead of db2set DB2CODEPAGE=3Dnnn because that would set the codepage
permanently instead of only the current session.
0
Willem
7/30/2009 9:58:44 PM
Reply: