Moving tables to another IDS
A user had to retire a server taking part in Enterprise Replication and had already migrated most tables via unload/load, but was stuck on two large tables (38M rows, 24GB) that would need too much downtime. Suggestions included: take an initial copy with HPL and let ER sync the rest; run several parallel unload streams split with mod() and move data via named pipes/gzip plus dbload; create a raw table and do insert into ... select over a remote connection; and Madison Pruet's advice to simply use ER's cdr sync/check online. The poster then asked whether a load can run while the replicate is active; no answer or final outcome is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello, I have one server that I have to turn off in order to replace it. This seerver is in ER replication mode. I have done the migration to another server by using unload/load, and disabling constraints and index for medium table, small table were synced by ER, and now I'm stuck with two tables. One of the blocking tables is about 38000000 rows and 24Go, so using load and unload take a lot of time and I can't have a long downtime for my applications. So how would you proceed if you were in my place?
Hello, Mr. I think you could take an initial "image" of the table, using HPL to unload the data, and load it using the same method on the target database. Then you could start an ER on that table to adjust the remaining differences (sync them) and the time would be faster than stop/unload and load/start. That´s just an idea, of course.... Regards! Alexandre Marini Tecnologia da Informação - DBA SEFAZ-MS / SGI-UIMP / Sistemas IBM-Informix See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf <http://www.iiug.org>; JEAN-MICHEL RAMSEYER escreveu: > Hello, > > I have one server that I have to turn off in order to replace it. This seerver > is in ER replication mode. I have done the migration to another server by > using unload/load, and disabling constraints and index for medium table, small > table were synced by ER, and now I'm stuck with two tables. One of the > blocking tables is about 38000000 rows and 24Go, so using load and unload take > a lot of time and I can't have a long downtime for my applications. So how > would you proceed if you were in my place? > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > >
----- Original Message ---- From: JEAN-MICHEL RAMSEYER <jm..ramseyer@greenivory.com> To: ids@iiug.org Sent: Wed, January 27, 2010 7:10:39 AM Subject: Moving tables to another IDS [18788] Hello, I have one server that I have to turn off in order to replace it. This seerver is in ER replication mode. I have done the migration to another server by using unload/load, and disabling constraints and index for medium table, small table were synced by ER, and now I'm stuck with two tables. One of the blocking tables is about 38000000 rows and 24Go, so using load and unload take a lot of time and I can't have a long downtime for my applications. So how would you proceed if you were in my place? ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
JEAN-MICHEL,
When posting you should always include your version of IDS and OS.
1. You can still use unload if you are not too familiar with HPL like me :(
I did unload of 57 million rows in 8 hours before.
what you can do is to unload the entire table in multiple unload streams --
meaning to run them all at the same time. Each unload must involve unique set
of rows of course. Depending on the structure of your table, perhaps you can
use MOD function, for example:
unload to file1.uld
select * from account
where (mod(acctid, 10) = 1)
Look it up to make sure you understand what it does for you.
2. Depending on your OS and assuming that you will unload to a "shared" area
where you will have access to it from both old and new systems, you may run
into 2G file size limit. If this is the case, you can unload to "pipe", for
example:
*mknod file1.pipe p
*chmod 660 file1.pipe
*gzip < file1.pipe > file1_uld.gz &
*from dbaccess or in your script:
unload to file1.pipe select * from account ....
3. To load to another instance on another server, use "dbload" NOT load
statement.
*recreate the table
*mknod file1.pipe p
*chmod 660 file1.pipe
*gunzip -c file1_uld.gz > file1.pipe &
*dbload -d yourdbname -c load1.cmd -l load1.log
your load1.cmd:
file "file1.pipe" delimiter '|' <# columns>;
insert into <table_name>;
If you prepare all your scripts and test them well, I believe you can do it in
a day or less.
Hope this helps.
Kern --
----- Forwarded Message ----
From: JEAN-MICHEL RAMSEYER <jm.ramseyer@greenivory.com>
To: ids@iiug.org
Sent: Wed, January 27, 2010 7:10:39 AM
Subject: Moving tables to another IDS [18788]
Hello,
I have one server that I have to turn off in order to replace it. This seerver
is in ER replication mode. I have done the migration to another server by
using unload/load, and disabling constraints and index for medium table, small
table were synced by ER, and now I'm stuck with two tables. One of the
blocking tables is about 38000000 rows and 24Go, so using load and unload take
a lot of time and I can't have a long downtime for my applications. So how
would you proceed if you were in my place?
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Depending on the version ... and if you have connectivity between the
servers ... You could create a raw table on the source system .. then do
and insert into ... select * from ... then rebuild indexes and ad any
constraints ... HPL would definitely be faster .. but this is probably
easier ...
Peter Logan
Senior Database Administrator
Phone: 616/878-8309
From:
"Kern Doe" <kern_doe@yahoo.com>
To:
ids@iiug.org
Date:
01/27/2010 08:52 AM
Subject:
Fw: Moving tables to another IDS [18791]
Sent by:
ids-bounces@iiug.org
JEAN-MICHEL,
When posting you should always include your version of IDS and OS.
1. You can still use unload if you are not too familiar with HPL like me
:(
I did unload of 57 million rows in 8 hours before.
what you can do is to unload the entire table in multiple unload streams
--
meaning to run them all at the same time. Each unload must involve unique
set
of rows of course. Depending on the structure of your table, perhaps you
can
use MOD function, for example:
unload to file1.uld
select * from account
where (mod(acctid, 10) = 1)
Look it up to make sure you understand what it does for you.
2. Depending on your OS and assuming that you will unload to a "shared"
area
where you will have access to it from both old and new systems, you may
run
into 2G file size limit. If this is the case, you can unload to "pipe",
for
example:
*mknod file1.pipe p
*chmod 660 file1.pipe
*gzip < file1.pipe > file1_uld.gz &
*from dbaccess or in your script:
unload to file1.pipe select * from account ....
3. To load to another instance on another server, use "dbload" NOT load
statement.
*recreate the table
*mknod file1.pipe p
*chmod 660 file1.pipe
*gunzip -c file1_uld.gz > file1.pipe &
*dbload -d yourdbname -c load1.cmd -l load1.log
your load1.cmd:
file "file1.pipe" delimiter '|' <# columns>;
insert into <table_name>;
If you prepare all your scripts and test them well, I believe you can do
it in
a day or less.
Hope this helps.
Kern --
----- Forwarded Message ----
From: JEAN-MICHEL RAMSEYER <jm.ramseyer@greenivory.com>
To: ids@iiug.org
Sent: Wed, January 27, 2010 7:10:39 AM
Subject: Moving tables to another IDS [18788]
Hello,
I have one server that I have to turn off in order to replace it. This
seerver
is in ER replication mode. I have done the migration to another server by
using unload/load, and disabling constraints and index for medium table,
small
table were synced by ER, and now I'm stuck with two tables. One of the
blocking tables is about 38000000 rows and 24Go, so using load and unload
take
a lot of time and I can't have a long downtime for my applications. So how
would you proceed if you were in my place?
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
If the server is part of ER, then why not take advantage of ER to synchronize the server? cdr SYNC/CHECK will take some time to complete= , but it can be done while the system is active. = From: "JEAN-MICHEL RAMSEYER" <jm.ramseyer@greenivory.com> = = To: ids@iiug.org = = Date: 01/27/2010 06:11 AM = = Subject: Moving tables to another IDS [18788] = = Sent by: ids-bounces@iiug.org = = Hello, I have one server that I have to turn off in order to replace it. This seerver is in ER replication mode. I have done the migration to another server = by using unload/load, and disabling constraints and index for medium table= , small table were synced by ER, and now I'm stuck with two tables. One of the blocking tables is about 38000000 rows and 24Go, so using load and unlo= ad take a lot of time and I can't have a long downtime for my applications. So = how would you proceed if you were in my place? ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =
As I'm not to familar with HPL I'm wondering if I can launch a load task while the replicate is active