How to migrate date from another machine?

Hi Gurus, We have a system test databast(systestdb) on machine2. Our production database(productdb) sits on machine1. We want to migrate one table from production database to our test machine for test purpose. This table contains more than 8 millions records. Is there a fast way to handle it? Thanks, Denny ******************************************* The information contained in this e-mail message may contain privileged and confidential information. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you have received this message in error, please notify the sender by return e-mail, delete this message and destroy any copies. Internet e- mail is not guaranteed to be secure or error-free. Messages could be intercepted, corrupted, lost, arrive late or contain viruses. The sender will not be liable for these risks. ******************************************* Ce message electronique pourrait contenir des informations privilegiees et confidentielles. Si vous n'en etes pas le recipiendaire prevu, nous vous signalons qu'il est strictement interdit d'examiner, de diffuser, de distribuer et de reproduire le present message. Si vous l'avez recu par erreur, veuillez prevenir l'expediteur par courriel, puis effacer ce message et en detruire toute copie. Le courrier electronique n'est pas garanti securitaire ni exempt d'erreurs. Les messages pourraient etre interceptes, corrompus, egares, retardes ou contamines par des virus. L'exp'editeur n'est pas responsable de ces risques .

> -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On > Behalf Of Guo, Denny > Sent: Monday, February 27, 2006 12:05 PM > To: ids@iiug.org > Subject: How to migrate date from another machine? [6451] > > > Hi Gurus, > > We have a system test databast(systestdb) on machine2. > Our production database(productdb) sits on machine1. > We want to migrate one table from production database to our test > machine for test purpose. This table contains more than 8 millions > records. > Is there a fast way to handle it? > Try Art Kagel's "dbcopy" utility from the IIUG Software Repository. It is part of the utils2_ak package. This nifty little program can copy data directly from one table/database/instance/machine over to another identical table/database/instance/machine, without having to use an intermediate flat file. If that won't work for you, though, then you should try using HPL to unload from the production table, compress/zip the file, send it over to the test box, uncompress/unzip, and then use HPL to load into the test db. I would try dbcopy first, tho. I have used it many times with great success. HTH, Paul Mosser

Thanks for help. Any configuration needed? Because the two database sits on different machine. I never try it before. So before I have clear understand the impact, I do not want to do it because it involves our product database. Denny -----Original Message----- From: Bernhard Gramberg [mailto:mail@gramberg.de] Sent: February 27, 2006 2:39 PM To: Guo, Denny Subject: AW: How to migrate date from another machine? [6451] What about select * from database:table1 Insert into database2:table2 (I am not sure of the Syntax) -----Ursprüngliche Nachricht----- Von: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Im Auftrag von Guo, Denny Gesendet: Montag, 27. Februar 2006 20:05 An: ids@iiug.org Betreff: How to migrate date from another machine? [6451] Hi Gurus, We have a system test databast(systestdb) on machine2. Our production database(productdb) sits on machine1. We want to migrate one table from production database to our test machine for test purpose. This table contains more than 8 millions records. Is there a fast way to handle it? Thanks, Denny ******************************************* The information contained in this e-mail message may contain privileged and confidential information. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you have received this message in error, please notify the sender by return e-mail, delete this message and destroy any copies. Internet e- mail is not guaranteed to be secure or error-free. Messages could be intercepted, corrupted, lost, arrive late or contain viruses. The sender will not be liable for these risks. ******************************************* Ce message electronique pourrait contenir des informations privilegiees et confidentielles. Si vous n'en etes pas le recipiendaire prevu, nous vous signalons qu'il est strictement interdit d'examiner, de diffuser, de distribuer et de reproduire le present message. Si vous l'avez recu par erreur, veuillez prevenir l'expediteur par courriel, puis effacer ce message et en detruire toute copie. Le courrier electronique n'est pas garanti securitaire ni exempt d'erreurs. Les messages pourraient etre interceptes, corrompus, egares, retardes ou contamines par des virus. L'exp'editeur n'est pas responsable de ces risques . ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************* The information contained in this e-mail message may contain privileged and confidential information. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you have received this message in error, please notify the sender by return e-mail, delete this message and destroy any copies. Internet e- mail is not guaranteed to be secure or error-free. Messages could be intercepted, corrupted, lost, arrive late or contain viruses. The sender will not be liable for these risks. ******************************************* Ce message electronique pourrait contenir des informations privilegiees et confidentielles. Si vous n'en etes pas le recipiendaire prevu, nous vous signalons qu'il est strictement interdit d'examiner, de diffuser, de distribuer et de reproduire le present message. Si vous l'avez recu par erreur, veuillez prevenir l'expediteur par courriel, puis effacer ce message et en detruire toute copie. Le courrier electronique n'est pas garanti securitaire ni exempt d'erreurs. Les messages pourraient etre interceptes, corrompus, egares, retardes ou contamines par des virus. L'expéditeur n'est pas responsable de ces risques .

Guo, Denny schrieb: > Hi Gurus, > > We have a system test databast(systestdb) on machine2. > Our production database(productdb) sits on machine1. > We want to migrate one table from production database to our test > machine for test purpose. This table contains more than 8 millions > records. > Is there a fast way to handle it? > > Thanks, > Denny [ ... snipping a lot of unrelevat text ... ] 1) ALWAYS please specify your OS and IDS version In your case it'd be necessary to know both IDS versions 2) You can do this using the HPL. I understand that there may be a network connecting both machines. If not, you'll need 2 files instead. You will need 2 HPL jobs: UNLOAD on the test machine LOAD on the production machine Because target is a production machine I recommend to create a new 'test' database, which will contain extaxtly this (or parts of) this one table, if it works corretly. Later you can switch to your prod scenario. Both HPL jobs will use pipe devices as in- or output device. This is fully explained in the HPL documentation, which you can find here: http://www-306.ibm.com/software/data/informix/pubs/library/ids_9.html (this is a link pointing to the V9 docs) Your HPL devices actually can be UNIX commands like gzip -fc | scp ........ # syntax may be OS dependent on the source side (test machine) It depends on cpu vs network speed, if it pays off to compress or not A metadescription would be: HPL | gzip | scp | gzip | HPL That is, how I did this very often. Efficient, easy, fast. The side where you actually run the scp, depens on load. Maybe the test machine is the better place to run it. On a gigabit network & modern machines expect to be able to copy 25k 100-byte-rows/second. If you don't have scp, you can use rcp also, but it is less configurable and often slower, as it cannot use large blocksizes. Have fun. dic_k -- Richard Kofler SOLID STATE EDV Dienstleistungen GmbH Vienna/Austria/Europe

Hi, how large is the table really (rowsize * #rows) - or check with oncheck -pt <database>:<table> (#data pages * 2048 Bytes) If you have a few hundred MB or less you can go the simple way: production: set isolation dirty read ; unload to 'table.unl' select * from <table> ; FTP/rcp table.unl between machines test - use dbload utility: dbload -d <database> -c load.sql -n <#commit_rows> -l errfile with load.sql like: file "table.unl" delimiter "|" <#columns> ; insert into <table> values (f01, f02, ... f#columns); Remarks: #commit_rows depends on number of available LOCKS and LOGFILES, a few thousand should always work. Drop indices on the target table and recreate them after data loading, maybe with PSORT_NPROCS/PDQPRIORITY set. Bye Andreas > ------------------------------------------- SPAR Oesterreichische Warenhandels-AG Hauptzentrale Europastrasse 3 A - 5015 Salzburg Tel: +43 662 4470 24223 Mobile: +43 664 6259575 E-Mail: Andreas.KUTSCHE@spar.at Internet: http://www.spar.at ------------------------------------------- -----Ursprüngliche Nachricht----- > Von: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]Im Auftrag von > Guo, Denny > Gesendet: Montag, 27. Februar 2006 20:05 > An: ids@iiug.org > Betreff: How to migrate date from another machine? [6451] > > > > Hi Gurus, > > We have a system test databast(systestdb) on machine2. > Our production database(productdb) sits on machine1. > We want to migrate one table from production database to our test > machine for test purpose. This table contains more than 8 millions > records. > Is there a fast way to handle it? > > Thanks, > Denny > > ******************************************* > The information contained in this e-mail message may > contain privileged and confidential information. > If you are not the intended recipient, you are > hereby notified that any review, dissemination, > distribution or duplication of this communication > is strictly prohibited. If you have received this > message in error, please notify the sender by return > e-mail, delete this message and destroy any copies. > Internet e- mail is not guaranteed to be secure or > error-free. Messages could be intercepted, corrupted, > lost, arrive late or contain viruses. > The sender will not be liable for > these risks. > > ******************************************* > Ce message electronique pourrait contenir des > informations privilegiees et confidentielles. Si vous > n'en etes pas le recipiendaire prevu, nous vous > signalons qu'il est strictement interdit d'examiner, > de diffuser, de distribuer et de reproduire le > present message. Si vous l'avez recu par erreur, > veuillez prevenir l'expediteur par courriel, puis > effacer ce message et en detruire toute copie. > Le courrier electronique n'est pas garanti > securitaire ni exempt d'erreurs. Les messages > pourraient etre interceptes, corrompus, egares, > retardes ou contamines par des virus. > L'exp'editeur n'est pas > responsable de ces risques . > > > ************************************************************** > ***************** > Forum Note: Use "Reply" to post a response in the discussion forum. > >

Thanks Andreas, Total size around 6G fro this table, but we do not have enough space on production machine. I am trying to do it chunk by chunk now. Thanks, Denny -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Andreas.KUT.... Sent: February 28, 2006 1:01 PM To: ids@iiug.org Subject: AW: How to migrate date from another machine? [6461] Hi, how large is the table really (rowsize * #rows) - or check with oncheck -pt <database>:<table> (#data pages * 2048 Bytes) If you have a few hundred MB or less you can go the simple way: production: set isolation dirty read ; unload to 'table.unl' select * from <table> ; FTP/rcp table.unl between machines test - use dbload utility: dbload -d <database> -c load.sql -n <#commit_rows> -l errfile with load.sql like: file "table.unl" delimiter "|" <#columns> ; insert into <table> values (f01, f02, ... f#columns); Remarks: #commit_rows depends on number of available LOCKS and LOGFILES, a few thousand should always work. Drop indices on the target table and recreate them after data loading, maybe with PSORT_NPROCS/PDQPRIORITY set. Bye Andreas > ------------------------------------------- SPAR Oesterreichische Warenhandels-AG Hauptzentrale Europastrasse 3 A - 5015 Salzburg Tel: +43 662 4470 24223 Mobile: +43 664 6259575 E-Mail: Andreas.KUTSCHE@spar.at Internet: http://www.spar.at ------------------------------------------- -----Ursprüngliche Nachricht----- > Von: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]Im Auftrag von > Guo, Denny > Gesendet: Montag, 27. Februar 2006 20:05 > An: ids@iiug.org > Betreff: How to migrate date from another machine? [6451] > > > > Hi Gurus, > > We have a system test databast(systestdb) on machine2. > Our production database(productdb) sits on machine1. > We want to migrate one table from production database to our test > machine for test purpose. This table contains more than 8 millions > records. > Is there a fast way to handle it? > > Thanks, > Denny > > ******************************************* > The information contained in this e-mail message may contain > privileged and confidential information. > If you are not the intended recipient, you are hereby notified that > any review, dissemination, distribution or duplication of this > communication is strictly prohibited. If you have received this > message in error, please notify the sender by return e-mail, delete > this message and destroy any copies. > Internet e- mail is not guaranteed to be secure or error-free. > Messages could be intercepted, corrupted, lost, arrive late or contain > viruses. > The sender will not be liable for > these risks. > > ******************************************* > Ce message electronique pourrait contenir des informations > privilegiees et confidentielles. Si vous n'en etes pas le > recipiendaire prevu, nous vous signalons qu'il est strictement > interdit d'examiner, de diffuser, de distribuer et de reproduire le > present message. Si vous l'avez recu par erreur, veuillez prevenir > l'expediteur par courriel, puis effacer ce message et en detruire > toute copie. > Le courrier electronique n'est pas garanti securitaire ni exempt > d'erreurs. Les messages pourraient etre interceptes, corrompus, > egares, retardes ou contamines par des virus. > L'exp'editeur n'est pas > responsable de ces risques . > > > ************************************************************** > ***************** > Forum Note: Use "Reply" to post a response in the discussion forum. > > ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************* The information contained in this e-mail message may contain privileged and confidential information. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you have received this message in error, please notify the sender by return e-mail, delete this message and destroy any copies. Internet e- mail is not guaranteed to be secure or error-free. Messages could be intercepted, corrupted, lost, arrive late or contain viruses. The sender will not be liable for these risks. ******************************************* Ce message electronique pourrait contenir des informations privilegiees et confidentielles. Si vous n'en etes pas le recipiendaire prevu, nous vous signalons qu'il est strictement interdit d'examiner, de diffuser, de distribuer et de reproduire le present message. Si vous l'avez recu par erreur, veuillez prevenir l'expediteur par courriel, puis effacer ce message et en detruire toute copie. Le courrier electronique n'est pas garanti securitaire ni exempt d'erreurs. Les messages pourraient etre interceptes, corrompus, egares, retardes ou contamines par des virus. L'expéditeur n'est pas responsable de ces risques .