Migrate Databases

We currently have an Informix IDS 7.31.UC4 instance running on Solaris 2.6. As part of an overall aim to end up running on the newest version of Informix we intend to migrate the instance to new hardware running Solaris 9 and initially the same version of Informix. Currently a regular level 0 complemented by level 1 archives via Networker to DLT tape provide a restore method for the existing IDS. We would be transferring approx 200gb of data to an empty instance configured similarly to the donor. Any opinions/tips/experience on the most efficient way to populate the new IDS once online and ready for data? i.e tape based, or direct inserts from old instance to new instance, hpl, or indeed would temporarily configuring ER be an efficient option? Thanks for any input, Euan.

If you can configure the same chunk links you can just use an ontape/onbar restore to the new instance installation. Art S. Kagel ----- Original Message ----- From: Fielding Euan <ids@iiug.org> To: ids@iiug.org At: 10/15 8:00:31 We currently have an Informix IDS 7.31.UC4 instance running on Solaris 2.6. As part of an overall aim to end up running on the newest version of Informix we intend to migrate the instance to new hardware running Solaris 9 and initially the same version of Informix. Currently a regular level 0 complemented by level 1 archives via Networker to DLT tape provide a restore method for the existing IDS. We would be transferring approx 200gb of data to an empty instance configured similarly to the donor. Any opinions/tips/experience on the most efficient way to populate the new IDS once online and ready for data? i.e tape based, or direct inserts from old instance to new instance, hpl, or indeed would temporarily configuring ER be an efficient option? Thanks for any input, Euan. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Thanks very much for your response, Unfortunately we currently have a mixture of raw and cooked chunks in different dbspaces and plan to have the new system entirely on raw as previous assurances that performance differences would be negligable on new hardware have not been realised. Therefore we are limited with our recover options, I'm thinking the HPL and straight forward unloads and applying the schemae seperately might be the way to go but need to find out how to circumvent the 2gb limit as some of our tables exceed this. I'm also investigating whether or not I could unload the tables direct to the new hardware by defining a device array as we are limited for filesystem capacity on the current machine - it's old!

I recently had the same issue performing a v5 to v10 upgrade where I had more than 100 GB of blobs to pass. No way was I going to be given that much file system space! I used named pipes to move the data since the old and new instances were both located on the same server. In a scripting directory, I (under AIX) created the pipe using the mkfifo command (for this example, pipe named "data"). I then had two SQLs to run under two separate windows at the same time. One window was pointing to the old instance and database, the other window pointing to the new instance and database. Script one executed on old instance: unload to data select * from table_xyz . seconds later, I started script two . Script two executed on new instance: load from data insert into table_xyz Both should give you the same number of rows (unloaded/loaded). Take care. Clifton M. Bean Informix DBA / AIX System Admin Currency Technics & Metrics Phone: (972) 812-1411 x244 -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of EUAN FIELDING Sent: Tuesday, October 16, 2007 7:38 AM To: ids@iiug.org Subject: Re:Migrate Databases [10146] Thanks very much for your response, Unfortunately we currently have a mixture of raw and cooked chunks in different dbspaces and plan to have the new system entirely on raw as previous assurances that performance differences would be negligable on new hardware have not been realised. Therefore we are limited with our recover options, I'm thinking the HPL and straight forward unloads and applying the schemae seperately might be the way to go but need to find out how to circumvent the 2gb limit as some of our tables exceed this. I'm also investigating whether or not I could unload the tables direct to the new hardware by defining a device array as we are limited for filesystem capacity on the current machine - it's old! **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

One can also use my dbcopy utility to copy the data directly from one server to the other. Dbcopy is part of the package utils2_ak in the IIUG Software Repositiory. Art S. Kagel ----- Original Message ----- From: Clifton Bean <ids@iiug.org> To: ids@iiug.org At: 10/16 10:00:51 I recently had the same issue performing a v5 to v10 upgrade where I had more than 100 GB of blobs to pass. No way was I going to be given that much file system space! I used named pipes to move the data since the old and new instances were both located on the same server. In a scripting directory, I (under AIX) created the pipe using the mkfifo command (for this example, pipe named "data"). I then had two SQLs to run under two separate windows at the same time. One window was pointing to the old instance and database, the other window pointing to the new instance and database. Script one executed on old instance: unload to data select * from table_xyz .. seconds later, I started script two . Script two executed on new instance: load from data insert into table_xyz Both should give you the same number of rows (unloaded/loaded). Take care. Clifton M. Bean Informix DBA / AIX System Admin Currency Technics & Metrics Phone: (972) 812-1411 x244 -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of EUAN FIELDING Sent: Tuesday, October 16, 2007 7:38 AM To: ids@iiug.org Subject: Re:Migrate Databases [10146] Thanks very much for your response, Unfortunately we currently have a mixture of raw and cooked chunks in different dbspaces and plan to have the new system entirely on raw as previous assurances that performance differences would be negligable on new hardware have not been realised. Therefore we are limited with our recover options, I'm thinking the HPL and straight forward unloads and applying the schemae seperately might be the way to go but need to find out how to circumvent the 2gb limit as some of our tables exceed this. I'm also investigating whether or not I could unload the tables direct to the new hardware by defining a device array as we are limited for filesystem capacity on the current machine - it's old! **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

On 16/10/2007, Clifton Bean <cbean@ctm.com> wrote: > I recently had the same issue performing a v5 to v10 upgrade where I had > more than 100 GB of blobs to pass. No way was I going to be given that much > file system space! > > I used named pipes to move the data since the old and new instances were > both located on the same server. > > In a scripting directory, I (under AIX) created the pipe using the mkfifo > command (for this example, pipe named "data"). I then had two SQLs to run > under two separate windows at the same time. One window was pointing to the > old instance and database, the other window pointing to the new instance and > database. > > Script one executed on old instance: unload to data select * from table_xyz > > .. seconds later, I started script two . > > Script two executed on new instance: load from data insert into table_xyz > > Both should give you the same number of rows (unloaded/loaded). > > Take care. > > Clifton M. Bean > > Informix DBA / AIX System Admin > > Currency Technics & Metrics > > Phone: (972) 812-1411 x244 > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of EUAN > FIELDING > Sent: Tuesday, October 16, 2007 7:38 AM > To: ids@iiug.org > Subject: Re:Migrate Databases [10146] > > Thanks very much for your response, > > Unfortunately we currently have a mixture of raw and cooked chunks in > > different dbspaces and plan to have the new system entirely on raw as > previous > > assurances that performance differences would be negligable on new hardware > > have not been realised. > > Therefore we are limited with our recover options, I'm thinking the HPL and > > straight forward unloads and applying the schemae seperately might be the > way > > to go but need to find out how to circumvent the 2gb limit as some of our > > tables exceed this. I'm also investigating whether or not I could unload the > > tables direct to the new hardware by defining a device array as we are > limited > > for filesystem capacity on the current machine - it's old! > > **************************************************************************** > *** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > Just because you currently have a mixture of cooked and raw doesn't prevent the use of ontape to move the data. Just create your new raw chunks the same size as your exusting raw/ cooked chunks, create soft links (ln -s) from these to files of the same name as your current cooked files/raw file links (you are currently using links for raw files aren't you !!) and then restore. It may not be pretty but it will work. Keith

Thanks for the feedback all, very speedy! We could well end up going down the line of ensuring chunk naming remains consistent in order to allow ontape restore as that may well be the quickest option, we have used links for our existing raw chunks :), but as you say Keith it would be messy. I don't think named pipes would be an option as I believe they can only be used locally and our migration is to new hardware. IDS - 7.31.UC4 O.S - Solaris 2.6 (Current), Solaris 9 (Proposed) I shall post up what we do end up doing for interest value. Cheers again everyone, Euan.

On 16/10/2007, EUAN FIELDING <euan.fielding@ubertas.co.uk> wrote: > Thanks for the feedback all, very speedy! > > We could well end up going down the line of ensuring chunk naming remains > consistent in order to allow ontape restore as that may well be the quickest > option, we have used links for our existing raw chunks :), but as you say > Keith it would be messy. > > I don't think named pipes would be an option as I believe they can only be > used locally and our migration is to new hardware. > > IDS - 7.31.UC4 > O.S - Solaris 2.6 (Current), Solaris 9 (Proposed) > > I shall post up what we do end up doing for interest value. > > Cheers again everyone, Euan. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > Euan You could set up a new instance on you new box, set up a trust relatonship between them (hosts.equiv, /etc/services and sqlhosts) and then use insert into 'local' select * from 'remote' or unload to 'named pipe' select * from 'remote' and dbload from 'named pipe' inserting into 'local'. I've used the latter to shift 300 Gb of data in 4 parallel streams in around 8 hours (sloooww source disks !!). This would mean you can use a 'peerfect' IDS 10 (or 11 :-> ) setup on you new box. Come back direct for details if needed. Keith

Hello, IDS doesn't make much differences between raw and cooked files. You can take a backup from raw devices and restore it to a instance With cooked devices and vice versa. Regards, Andreas > ------------------------------------------- SPAR Österreichische Warenhandels-AG Hauptzentrale A - 5015 Salzburg, Europastrasse 3 FN 34170 a Tel: +43 662 4470 24223 Mobile: +43 664 6259575 E-Mail: Andreas.KUTSCHE@spar.at Internet: http://www.spar.at Wichtiger Hinweis: Der Inhalt dieser E-Mail kann vertrauliche und rechtlich geschützte Informationen, insbesondere Betriebs- oder Geschäftsgeheimnisse, enthalten, zu deren Geheimhaltung der Empfänger verpflichtet ist. Die Informationen in dieser E-Mail sind ausschließlich für den Adressaten bestimmt. Sollten Sie die E-Mail irrtümlich erhalten haben so ersuchen wir Sie, die Nachricht von Ihrem System zu löschen und sich mit uns in Verbindung zu setzen. Über das Internet versandte E-Mails können leicht manipuliert oder unter fremdem Namen erstellt werden. Daher schließen wir die rechtliche Verbindlichkeit der in dieser Nachricht enthaltenen Informationen aus. Der Inhalt der E-Mail ist nur rechtsverbindlich, wenn er von uns schriftlich bestätigt und gezeichnet wird. Sollte trotz der von uns verwendeten Virus-Schutzprogramme durch die Zusendung von E-Mails ein Virus in Ihre Systeme gelangen, haften wir nicht für evtl. hieraus entstehende Schäden. Wir danken für Ihr Verständnis. Important notice: The contents of this e-mail may contain confidential and legally protected information that is in particular related to operational and trade secrets, which the recipient is obliged to treat as confidential. The information in this e-mail is made available exclusively for use by the addressee. In the event that the e-mail may have been sent to you in error, we would ask you to kindly delete this communication from your system and to contact us. E-mails sent via the Internet can be easily manipulated or sent out under someone else's name. We therefore do not accept legal liability for the information contained in this communication. The contents of the e-mail are only legally binding if they have been confirmed and signed by us in writing. If, in spite of our using Antivirus protection software, a virus may have penetrated your system through the sending of this e-mail, we do not accept liability for any damage that may possibly arise as a result of this. We trust that you appreciate our position. ------------------------------------------- -----Ursprüngliche Nachricht----- > Von: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Im > Auftrag von EUAN FIELDING > Gesendet: Dienstag, 16. Oktober 2007 14:38 > An: ids@iiug.org > Betreff: Re:Migrate Databases [10146] > > Thanks very much for your response, > > Unfortunately we currently have a mixture of raw and cooked > chunks in different dbspaces and plan to have the new system > entirely on raw as previous assurances that performance > differences would be negligable on new hardware have not been > realised. > > Therefore we are limited with our recover options, I'm > thinking the HPL and straight forward unloads and applying > the schemae seperately might be the way to go but need to > find out how to circumvent the 2gb limit as some of our > tables exceed this. I'm also investigating whether or not I > could unload the tables direct to the new hardware by > defining a device array as we are limited for filesystem > capacity on the current machine - it's old! > > > ************************************************************** > ***************** > Forum Note: Use "Reply" to post a response in the discussion forum. > >

> One can also use my dbcopy utility to copy the data directly > from one server > to the other. Dbcopy is part of the package utils2_ak in the > IIUG Software > Repositiory. > > Art S. Kagel > This is the tool that I have used for several migrations -- works well!! Don't have to worry about the 2gb limit, and the target box/instance can be set up any way you like. HTH, Paul M. > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On > Behalf Of EUAN > FIELDING > Sent: Tuesday, October 16, 2007 7:38 AM > To: ids@iiug.org > Subject: Re:Migrate Databases [10146] > > Thanks very much for your response, > > Unfortunately we currently have a mixture of raw and cooked chunks in > > different dbspaces and plan to have the new system entirely on raw as > previous > > assurances that performance differences would be negligable > on new hardware > > have not been realised. > > Therefore we are limited with our recover options, I'm > thinking the HPL and > > straight forward unloads and applying the schemae seperately > might be the > way > > to go but need to find out how to circumvent the 2gb limit as > some of our > > tables exceed this. I'm also investigating whether or not I > could unload the > > tables direct to the new hardware by defining a device array > as we are > limited > > for filesystem capacity on the current machine - it's old! > > ************************************************************** > ************** > *** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ************************************************************** > ***************** > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ************************************************************** > ***************** > Forum Note: Use "Reply" to post a response in the discussion forum. > >