Data migration Brainstorm.

Posted in 2010

Summary

A DBA asked for the fastest way to migrate 1GB–300GB databases from IDS 7.3x/9.x to 11.50, given that backup/restore across versions and replication (some databases unlogged) weren't options. Suggestions: dbexport/dbimport for smaller databases; for larger ones, parallel 'INSERT INTO new SELECT * FROM old' across servers, loading into RAW tables then converting to STANDARD and building indexes with PDQ; Art Kagel's dbcopy (with the mk_dbcopy.awk script in utils4_ak) against unlogged targets; and IBM's John Miller recommending external tables (CREATE EXTERNAL TABLE ... then INSERT ... SELECT) as faster and simpler than HPL. One reader hit a syntax error on REJECTFILE; Miller noted that option requires 11.50.xC6 or later. No single choice was reported as finally adopted.

Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.

Topics: Migration, Import/Export & Data Conversion, Versions, Editions & End-of-Life

Knox, Ernest — Wed, 27 Jan 2010 16:46:12 — source: IIUG Forums & Mailing Lists

We are upgrading our Servers, OS and version of Informix from 7.30, 7.31, 9.21, 9.30, and 9.40 to version 11.50. My brainstorming question(s) to the group is: What is the quickest way to migrate between 1 gig to 300 gig of data? Since I can't use a backup and restore from different versions (I don't believe) and one or two databases do not have logging turned on, so replication is basically out. Would HPL, manual scripts (dbexport and dbimport), or a third party product do the trick. Please, your thoughts and suggestions are very helpful. Thanks, ******************************************************************* Ernie Knox IT Database Administrator Specialist Sears Holdings 3333 Beverly Rd., B4-266A Hoffman Estates, IL. 60179 Office: (847) 286-5735 Email: Ernest.Knox@searshc.com Blackberry: 2244650553@messaging.sprintpcs.com <mailto:2244650553@messaging.sprintpcs.com> Page via Skytel: 2244650553@sprint.skytel.com <mailto:2244650553@sprint.skytel.com> Informix or MySQL Primary: 9110210@skytel.com <mailto:9110210@skytel.com> Informix or MySQL Secondary: 7276872@skytel.com <mailto:7276872@skytel.com> " Yes we can make a Change! " " It's always a great day to watch Sports - GO LIONS, TIGERS, and BEARS! " " Lets not forget - GO Pistons and Red Wings! " GSU *******************************************************************

↪ replying to Knox, Ernest

Obnoxio The Clown — Wed, 27 Jan 2010 16:57:28 — source: IIUG Forums & Mailing Lists

Knox, Ernest wrote: > We are upgrading our Servers, OS and version of Informix from 7.30, > 7.31, 9.21, 9.30, and 9.40 to version 11.50. > > My brainstorming question(s) to the group is: > > What is the quickest way to migrate between 1 gig to 300 gig of data? > > Since I can't use a backup and restore from different versions (I don't > believe) and one or two databases do not have logging turned on, so > replication is basically out. > > Would HPL, manual scripts (dbexport and dbimport), or a third party > product do the trick. > > Please, your thoughts and suggestions are very helpful. For a small database (anything less than 50 GB), I reckon dbexport would probably be adequate. It will probably take you longer to do all the faffing around that HPL requires. If you have to do all the messing around to get tables in the right order for referential integrity, then you might as well use INSERT INTO new SELECT * FROM old, rather than HPLing. 300GB isn't all that much any more. :o) -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

↪ replying to Obnoxio The Clown

Andreas.KUTSCHE@spar.at — Wed, 27 Jan 2010 17:05:43 — source: IIUG Forums & Mailing Lists

Hi, yes, for small databases dbexport/dbimport will be the best thing. For larger ones the suggestion INSERT INTO new SELECT * FROM oldis very good, because you can copy tables in parallel (at least the large ones). You can use raw-tables on the target database and change them to standard afterwards - really fast! For index creation afterwards use PDQ. Regards, Andreas > ------------------------------------------- SPAR Österreichische Warenhandels-AG Hauptzentrale A - 5015 Salzburg, Europastrasse 3 FN 34170 a Tel: +43 662 4470 84923 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 Obnoxio The Clown > Gesendet: Mittwoch, 27. Januar 2010 17:57 > An: ids@iiug.org > Betreff: Re: Data migration Brainstorm. [18800] > > Knox, Ernest wrote: > > We are upgrading our Servers, OS and version of Informix from 7.30, > > 7.31, 9.21, 9.30, and 9.40 to version 11.50. > > > > My brainstorming question(s) to the group is: > > > > What is the quickest way to migrate between 1 gig to 300 > gig of data? > > > > Since I can't use a backup and restore from different versions (I > > don't > > believe) and one or two databases do not have logging turned on, so > > replication is basically out. > > > > Would HPL, manual scripts (dbexport and dbimport), or a third party > > product do the trick. > > > > Please, your thoughts and suggestions are very helpful. > > For a small database (anything less than 50 GB), I reckon > dbexport would probably be adequate. It will probably take > you longer to do all the faffing around that HPL requires. If > you have to do all the messing around to get tables in the > right order for referential integrity, then you might as well > use INSERT INTO new SELECT * FROM old, rather than HPLing. > 300GB isn't all that much any more. :o) > > -- > Cheers, > Obnoxio The Clown > > http://obotheclown.blogspot.com > I will now proceed to pleasure myself with this fish. > > -- > This message has been scanned for viruses and dangerous > content by OpenProtect(http://www.openprotect.com), and is > believed to be clean. > > > ************************************************************** > ***************** > Forum Note: Use "Reply" to post a response in the discussion forum. > >

↪ replying to Knox, Ernest

Knox, Ernest — Wed, 27 Jan 2010 17:16:57 — source: IIUG Forums & Mailing Lists

I'm getting some good suggestions. I appreciate and will review them all, so keep them coming. Thanks, ******************************************************************* Ernie Knox IT Database Administrator Specialist Sears Holdings 3333 Beverly Rd., B4-266A Hoffman Estates, IL. 60179 Office: (847) 286-5735 Email: Ernest.Knox@searshc.com Blackberry: 2244650553@messaging.sprintpcs.com Page via Skytel: 2244650553@sprint.skytel.com Informix or MySQL Primary: 9110210@skytel.com Informix or MySQL Secondary: 7276872@skytel.com " Yes we can make a Change! " " It's always a great day to watch Sports - GO LIONS, TIGERS, and BEARS! " " Lets not forget - GO Pistons and Red Wings! " GSU ******************************************************************* -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Knox, Ernest Sent: Wednesday, January 27, 2010 10:46 AM To: ids@iiug.org Subject: Data migration Brainstorm. [18798] We are upgrading our Servers, OS and version of Informix from 7.30, 7.31, 9.21, 9.30, and 9.40 to version 11.50. My brainstorming question(s) to the group is: What is the quickest way to migrate between 1 gig to 300 gig of data? Since I can't use a backup and restore from different versions (I don't believe) and one or two databases do not have logging turned on, so replication is basically out. Would HPL, manual scripts (dbexport and dbimport), or a third party product do the trick. Please, your thoughts and suggestions are very helpful. Thanks, ******************************************************************* Ernie Knox IT Database Administrator Specialist Sears Holdings 3333 Beverly Rd., B4-266A Hoffman Estates, IL. 60179 Office: (847) 286-5735 Email: Ernest.Knox@searshc.com Blackberry: 2244650553@messaging.sprintpcs.com <mailto:2244650553@messaging.sprintpcs.com> Page via Skytel: 2244650553@sprint.skytel.com <mailto:2244650553@sprint.skytel.com> Informix or MySQL Primary: 9110210@skytel.com <mailto:9110210@skytel.com> Informix or MySQL Secondary: 7276872@skytel.com <mailto:7276872@skytel.com> " Yes we can make a Change! " " It's always a great day to watch Sports - GO LIONS, TIGERS, and BEARS! " " Lets not forget - GO Pistons and Red Wings! " GSU ******************************************************************* ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Knox, Ernest

Pankaj Patel — Wed, 27 Jan 2010 17:42:01 — source: IIUG Forums & Mailing Lists

Art's dbcopy utility Write down the thoughts of the moment. Those that come unsought for are commonly the most valuable. - Francis Bacon, 1561 - 1626 -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Knox, Ernest Sent: Wednesday, January 27, 2010 9:17 AM To: ids@iiug.org Subject: RE: Data migration Brainstorm. [18803] I'm getting some good suggestions. I appreciate and will review them all, so keep them coming. Thanks, ******************************************************************* Ernie Knox IT Database Administrator Specialist Sears Holdings 3333 Beverly Rd., B4-266A Hoffman Estates, IL. 60179 Office: (847) 286-5735 Email: Ernest.Knox@searshc.com Blackberry: 2244650553@messaging.sprintpcs.com Page via Skytel: 2244650553@sprint.skytel.com Informix or MySQL Primary: 9110210@skytel.com Informix or MySQL Secondary: 7276872@skytel.com " Yes we can make a Change! " " It's always a great day to watch Sports - GO LIONS, TIGERS, and BEARS! " " Lets not forget - GO Pistons and Red Wings! " GSU ******************************************************************* -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Knox, Ernest Sent: Wednesday, January 27, 2010 10:46 AM To: ids@iiug.org Subject: Data migration Brainstorm. [18798] We are upgrading our Servers, OS and version of Informix from 7.30, 7.31, 9.21, 9.30, and 9.40 to version 11.50. My brainstorming question(s) to the group is: What is the quickest way to migrate between 1 gig to 300 gig of data? Since I can't use a backup and restore from different versions (I don't believe) and one or two databases do not have logging turned on, so replication is basically out. Would HPL, manual scripts (dbexport and dbimport), or a third party product do the trick. Please, your thoughts and suggestions are very helpful. Thanks, ******************************************************************* Ernie Knox IT Database Administrator Specialist Sears Holdings 3333 Beverly Rd., B4-266A Hoffman Estates, IL. 60179 Office: (847) 286-5735 Email: Ernest.Knox@searshc.com Blackberry: 2244650553@messaging.sprintpcs.com <mailto:2244650553@messaging.sprintpcs.com> Page via Skytel: 2244650553@sprint.skytel.com <mailto:2244650553@sprint.skytel.com> Informix or MySQL Primary: 9110210@skytel.com <mailto:9110210@skytel.com> Informix or MySQL Secondary: 7276872@skytel.com <mailto:7276872@skytel.com> " Yes we can make a Change! " " It's always a great day to watch Sports - GO LIONS, TIGERS, and BEARS! " " Lets not forget - GO Pistons and Red Wings! " GSU ******************************************************************* ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Knox, Ernest

John Miller iii — Wed, 27 Jan 2010 18:39:43 — source: IIUG Forums & Mailing Lists

If you are looking at upgrading to a version of 11.50 I would look at using external tables. My tests show that they are much faster than HPL and I think simpler. Here are the three steps for a simple load from two unload files of a customer table. I have included several of the load options CREATE EXTERNAL TABLE 'informix'.EXTcustomer SAMEAS informix.customer USING ( DATAFILES('DISK:/tmp/customer.unl','DISK:/tmp/customer2.unl'), FORMAT 'DELIMITED', DELIMITER '|', RECORDEND '', Deluxe, NUMROWS 50, MAXERRORS 50, REJECTFILE '' ); INSERT INTO informix.customer SELECT * FROM EXTcustomer; DROP TABLE EXTcustomer; John F. Miller III STSM, Support Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 01/27/2010 09:16:57 AM: > [image removed] > > RE: Data migration Brainstorm. [18803]: > > Knox, Ernest > > to: > > ids > > 01/27/2010 09:17 AM > > Sent by: > > ids-bounces@iiug.org > > Please respond to ids > > I'm getting some good suggestions. I appreciate and will review them > all, so keep them coming. > > Thanks, > ******************************************************************* > Ernie Knox > IT Database Administrator Specialist > Sears Holdings > 3333 Beverly Rd., B4-266A > Hoffman Estates, IL. 60179 > Office: (847) 286-5735 > Email: Ernest.Knox@searshc.com > Blackberry: 2244650553@messaging.sprintpcs.com > Page via Skytel: 2244650553@sprint.skytel.com > Informix or MySQL Primary: 9110210@skytel.com > Informix or MySQL Secondary: 7276872@skytel.com > > " Yes we can make a Change! " > " It's always a great day to watch Sports - GO LIONS, TIGERS, and BEARS! > " > " Lets not forget - GO Pistons and Red Wings! " > GSU > ******************************************************************* > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Knox, Ernest > Sent: Wednesday, January 27, 2010 10:46 AM > To: ids@iiug.org > Subject: Data migration Brainstorm. [18798] > > We are upgrading our Servers, OS and version of Informix from 7.30, > 7.31, 9.21, 9.30, and 9.40 to version 11.50. > > My brainstorming question(s) to the group is: > > What is the quickest way to migrate between 1 gig to 300 gig of data? > > Since I can't use a backup and restore from different versions (I don't > believe) and one or two databases do not have logging turned on, so > replication is basically out. > > Would HPL, manual scripts (dbexport and dbimport), or a third party > product do the trick. > > Please, your thoughts and suggestions are very helpful. > > Thanks, > > ******************************************************************* > > Ernie Knox > > IT Database Administrator Specialist > > Sears Holdings > > 3333 Beverly Rd., B4-266A > > Hoffman Estates, IL. 60179 > > Office: (847) 286-5735 > > Email: Ernest.Knox@searshc.com > > Blackberry: 2244650553@messaging.sprintpcs.com > <mailto:2244650553@messaging.sprintpcs.com> > > Page via Skytel: 2244650553@sprint.skytel.com > <mailto:2244650553@sprint.skytel.com> > > Informix or MySQL Primary: 9110210@skytel.com > <mailto:9110210@skytel.com> > > Informix or MySQL Secondary: 7276872@skytel.com > <mailto:7276872@skytel.com> > > " Yes we can make a Change! " > > " It's always a great day to watch Sports - GO LIONS, TIGERS, and BEARS! > > " > > " Lets not forget - GO Pistons and Red Wings! " > > GSU > > ******************************************************************* > > ************************************************************************ > ******* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

↪ replying to John Miller iii

Plugge, Joe R. — Wed, 27 Jan 2010 18:50:33 — source: IIUG Forums & Mailing Lists

John, is the informix.customer table created RAW? -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of John Miller iii Sent: Wednesday, January 27, 2010 12:40 PM To: ids@iiug.org Subject: RE: Data migration Brainstorm. [18805] If you are looking at upgrading to a version of 11.50 I would look at using external tables. My tests show that they are much faster than HPL and I think simpler. Here are the three steps for a simple load from two unload files of a customer table. I have included several of the load options CREATE EXTERNAL TABLE 'informix'.EXTcustomer SAMEAS informix.customer USING ( DATAFILES('DISK:/tmp/customer.unl','DISK:/tmp/customer2.unl'), FORMAT 'DELIMITED', DELIMITER '|', RECORDEND '', Deluxe, NUMROWS 50, MAXERRORS 50, REJECTFILE '' ); INSERT INTO informix.customer SELECT * FROM EXTcustomer; DROP TABLE EXTcustomer; John F. Miller III STSM, Support Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 01/27/2010 09:16:57 AM: > [image removed] > > RE: Data migration Brainstorm. [18803]: > > Knox, Ernest > > to: > > ids > > 01/27/2010 09:17 AM > > Sent by: > > ids-bounces@iiug.org > > Please respond to ids > > I'm getting some good suggestions. I appreciate and will review them > all, so keep them coming. > > Thanks, > ******************************************************************* > Ernie Knox > IT Database Administrator Specialist > Sears Holdings > 3333 Beverly Rd., B4-266A > Hoffman Estates, IL. 60179 > Office: (847) 286-5735 > Email: Ernest.Knox@searshc.com > Blackberry: 2244650553@messaging.sprintpcs.com > Page via Skytel: 2244650553@sprint.skytel.com > Informix or MySQL Primary: 9110210@skytel.com > Informix or MySQL Secondary: 7276872@skytel.com > > " Yes we can make a Change! " > " It's always a great day to watch Sports - GO LIONS, TIGERS, and BEARS! > " > " Lets not forget - GO Pistons and Red Wings! " > GSU > ******************************************************************* > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Knox, Ernest > Sent: Wednesday, January 27, 2010 10:46 AM > To: ids@iiug.org > Subject: Data migration Brainstorm. [18798] > > We are upgrading our Servers, OS and version of Informix from 7.30, > 7.31, 9.21, 9.30, and 9.40 to version 11.50. > > My brainstorming question(s) to the group is: > > What is the quickest way to migrate between 1 gig to 300 gig of data? > > Since I can't use a backup and restore from different versions (I don't > believe) and one or two databases do not have logging turned on, so > replication is basically out. > > Would HPL, manual scripts (dbexport and dbimport), or a third party > product do the trick. > > Please, your thoughts and suggestions are very helpful. > > Thanks, > > ******************************************************************* > > Ernie Knox > > IT Database Administrator Specialist > > Sears Holdings > > 3333 Beverly Rd., B4-266A > > Hoffman Estates, IL. 60179 > > Office: (847) 286-5735 > > Email: Ernest.Knox@searshc.com > > Blackberry: 2244650553@messaging.sprintpcs.com > <mailto:2244650553@messaging.sprintpcs.com> > > Page via Skytel: 2244650553@sprint.skytel.com > <mailto:2244650553@sprint.skytel.com> > > Informix or MySQL Primary: 9110210@skytel.com > <mailto:9110210@skytel.com> > > Informix or MySQL Secondary: 7276872@skytel.com > <mailto:7276872@skytel.com> > > " Yes we can make a Change! " > > " It's always a great day to watch Sports - GO LIONS, TIGERS, and BEARS! > > " > > " Lets not forget - GO Pistons and Red Wings! " > > GSU > > ******************************************************************* > > ************************************************************************ > ******* > 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.

↪ replying to John Miller iii

Peter_Logan@spartanstores.com — Wed, 27 Jan 2010 19:16:45 — source: IIUG Forums & Mailing Lists

John ... I was trying this out .. and I get a syntax error on the REJECTFILE line ... position 14 ... I tried putting a file in there and that didn't work ... which manual is this in so I can verify my syntax. Thanks for the help ... Peter Logan Senior Database Administrator Phone: 616/878-8309 From: "John Miller iii" <miller3@us.ibm.com> To: ids@iiug.org Date: 01/27/2010 01:40 PM Subject: RE: Data migration Brainstorm. [18805] Sent by: ids-bounces@iiug.org If you are looking at upgrading to a version of 11.50 I would look at using external tables. My tests show that they are much faster than HPL and I think simpler. Here are the three steps for a simple load from two unload files of a customer table. I have included several of the load options CREATE EXTERNAL TABLE 'informix'.EXTcustomer SAMEAS informix.customer USING ( DATAFILES('DISK:/tmp/customer.unl','DISK:/tmp/customer2.unl'), FORMAT 'DELIMITED', DELIMITER '|', RECORDEND '', Deluxe, NUMROWS 50, MAXERRORS 50, REJECTFILE '' ); INSERT INTO informix.customer SELECT * FROM EXTcustomer; DROP TABLE EXTcustomer; John F. Miller III STSM, Support Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 01/27/2010 09:16:57 AM: > [image removed] > > RE: Data migration Brainstorm. [18803]: > > Knox, Ernest > > to: > > ids > > 01/27/2010 09:17 AM > > Sent by: > > ids-bounces@iiug.org > > Please respond to ids > > I'm getting some good suggestions. I appreciate and will review them > all, so keep them coming. > > Thanks, > ******************************************************************* > Ernie Knox > IT Database Administrator Specialist > Sears Holdings > 3333 Beverly Rd., B4-266A > Hoffman Estates, IL. 60179 > Office: (847) 286-5735 > Email: Ernest.Knox@searshc.com > Blackberry: 2244650553@messaging.sprintpcs.com > Page via Skytel: 2244650553@sprint.skytel.com > Informix or MySQL Primary: 9110210@skytel.com > Informix or MySQL Secondary: 7276872@skytel.com > > " Yes we can make a Change! " > " It's always a great day to watch Sports - GO LIONS, TIGERS, and BEARS! > " > " Lets not forget - GO Pistons and Red Wings! " > GSU > ******************************************************************* > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Knox, Ernest > Sent: Wednesday, January 27, 2010 10:46 AM > To: ids@iiug.org > Subject: Data migration Brainstorm. [18798] > > We are upgrading our Servers, OS and version of Informix from 7.30, > 7.31, 9.21, 9.30, and 9.40 to version 11.50. > > My brainstorming question(s) to the group is: > > What is the quickest way to migrate between 1 gig to 300 gig of data? > > Since I can't use a backup and restore from different versions (I don't > believe) and one or two databases do not have logging turned on, so > replication is basically out. > > Would HPL, manual scripts (dbexport and dbimport), or a third party > product do the trick. > > Please, your thoughts and suggestions are very helpful. > > Thanks, > > ******************************************************************* > > Ernie Knox > > IT Database Administrator Specialist > > Sears Holdings > > 3333 Beverly Rd., B4-266A > > Hoffman Estates, IL. 60179 > > Office: (847) 286-5735 > > Email: Ernest.Knox@searshc.com > > Blackberry: 2244650553@messaging.sprintpcs.com > <mailto:2244650553@messaging.sprintpcs.com> > > Page via Skytel: 2244650553@sprint.skytel.com > <mailto:2244650553@sprint.skytel.com> > > Informix or MySQL Primary: 9110210@skytel.com > <mailto:9110210@skytel.com> > > Informix or MySQL Secondary: 7276872@skytel.com > <mailto:7276872@skytel.com> > > " Yes we can make a Change! " > > " It's always a great day to watch Sports - GO LIONS, TIGERS, and BEARS! > > " > > " Lets not forget - GO Pistons and Red Wings! " > > GSU > > ******************************************************************* > > ************************************************************************ > ******* > 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.

↪ replying to Peter_Logan@spartanstores.com

John Miller iii — Wed, 27 Jan 2010 22:35:10 — source: IIUG Forums & Mailing Lists

What version of IDS are you using. This feature is available in 11.50.xC6 and above. John F. Miller III STSM, Support Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 01/27/2010 11:16:46 AM: > [image removed] > > RE: Data migration Brainstorm. [18807]: > > Peter_Logan@spartanstores.com > > to: > > ids > > 01/27/2010 11:17 AM > > Sent by: > > ids-bounces@iiug.org > > Please respond to ids > > John ... I was trying this out .. and I get a syntax error on the > REJECTFILE line ... position 14 ... I tried putting a file in there and > that didn't work ... which manual is this in so I can verify my syntax. > Thanks for the help ... > > Peter Logan > Senior Database Administrator > Phone: 616/878-8309 > > From: > "John Miller iii" <miller3@us.ibm.com> > To: > ids@iiug.org > Date: > 01/27/2010 01:40 PM > Subject: > RE: Data migration Brainstorm. [18805] > Sent by: > ids-bounces@iiug.org > > If you are looking at upgrading to a version of 11.50 I would look > at using external tables. My tests show that they are much faster > than HPL and I think simpler. > > Here are the three steps for a simple load from two unload files of a > customer table. I > have included several of the load options > > CREATE EXTERNAL TABLE 'informix'.EXTcustomer SAMEAS informix.customer > USING > ( > DATAFILES('DISK:/tmp/customer.unl','DISK:/tmp/customer2.unl'), > FORMAT 'DELIMITED', > DELIMITER '|', > RECORDEND '', > Deluxe, > NUMROWS 50, > MAXERRORS 50, > REJECTFILE '' > ); > > INSERT INTO informix.customer SELECT * FROM EXTcustomer; > DROP TABLE EXTcustomer;> > John F. Miller III > STSM, Support Architect > miller3@us.ibm.com > 503-578-5645 > IBM Informix Dynamic Server (IDS) > > ids-bounces@iiug.org wrote on 01/27/2010 09:16:57 AM: > > > [image removed] > > > > RE: Data migration Brainstorm. [18803]: > > > > Knox, Ernest > > > > to: > > > > ids > > > > 01/27/2010 09:17 AM > > > > Sent by: > > > > ids-bounces@iiug.org > > > > Please respond to ids > > > > I'm getting some good suggestions. I appreciate and will review them > > all, so keep them coming. > > > > Thanks, > > ******************************************************************* > > Ernie Knox > > IT Database Administrator Specialist > > Sears Holdings > > 3333 Beverly Rd., B4-266A > > Hoffman Estates, IL. 60179 > > Office: (847) 286-5735 > > Email: Ernest.Knox@searshc.com > > Blackberry: 2244650553@messaging.sprintpcs.com > > Page via Skytel: 2244650553@sprint.skytel.com > > Informix or MySQL Primary: 9110210@skytel.com > > Informix or MySQL Secondary: 7276872@skytel.com > > > > " Yes we can make a Change! " > > " It's always a great day to watch Sports - GO LIONS, TIGERS, and BEARS! > > > " > > " Lets not forget - GO Pistons and Red Wings! " > > GSU > > ******************************************************************* > > > > -----Original Message----- > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > > Knox, Ernest > > Sent: Wednesday, January 27, 2010 10:46 AM > > To: ids@iiug.org > > Subject: Data migration Brainstorm. [18798] > > > > We are upgrading our Servers, OS and version of Informix from 7.30, > > 7.31, 9.21, 9.30, and 9.40 to version 11.50. > > > > My brainstorming question(s) to the group is: > > > > What is the quickest way to migrate between 1 gig to 300 gig of data? > > > > Since I can't use a backup and restore from different versions (I don't > > believe) and one or two databases do not have logging turned on, so > > replication is basically out. > > > > Would HPL, manual scripts (dbexport and dbimport), or a third party > > product do the trick. > > > > Please, your thoughts and suggestions are very helpful. > > > > Thanks, > > > > ******************************************************************* > > > > Ernie Knox > > > > IT Database Administrator Specialist > > > > Sears Holdings > > > > 3333 Beverly Rd., B4-266A > > > > Hoffman Estates, IL. 60179 > > > > Office: (847) 286-5735 > > > > Email: Ernest.Knox@searshc.com > > > > Blackberry: 2244650553@messaging.sprintpcs.com > > <mailto:2244650553@messaging.sprintpcs.com> > > > > Page via Skytel: 2244650553@sprint.skytel.com > > <mailto:2244650553@sprint.skytel.com> > > > > Informix or MySQL Primary: 9110210@skytel.com > > <mailto:9110210@skytel.com> > > > > Informix or MySQL Secondary: 7276872@skytel.com > > <mailto:7276872@skytel.com> > > > > " Yes we can make a Change! " > > > > " It's always a great day to watch Sports - GO LIONS, TIGERS, and BEARS! > > > > > " > > > > " Lets not forget - GO Pistons and Red Wings! " > > > > GSU > > > > ******************************************************************* > > > > ************************************************************************ > > > ******* > > 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. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

↪ replying to Knox, Ernest

Art Kagel — Wed, 27 Jan 2010 22:39:20 — source: IIUG Forums & Mailing Lists

You can use dbcopy to move the data from the source servers to the new ones. It will be fastest if the target tables or their databases are unlogged and you add logging after copying the data. You should be able to run several copies of dbcopy per CPU VP either on different tables or on subsets of data. My package utils4_ak contains an awk script, mk_dbcopy.awk, which will read dbschema or myschema output and generate a shell script to run dbcopy against all user tables in the database. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Jan 27, 2010 at 11:46 AM, Knox, Ernest <Ernest.Knox@searshc.com>wrote: > We are upgrading our Servers, OS and version of Informix from 7.30, > 7.31, 9.21, 9.30, and 9.40 to version 11.50. > > My brainstorming question(s) to the group is: > > What is the quickest way to migrate between 1 gig to 300 gig of data? > > Since I can't use a backup and restore from different versions (I don't > believe) and one or two databases do not have logging turned on, so > replication is basically out. > > Would HPL, manual scripts (dbexport and dbimport), or a third party > product do the trick. > > Please, your thoughts and suggestions are very helpful. > > Thanks, > > ******************************************************************* > > Ernie Knox > > IT Database Administrator Specialist > > Sears Holdings > > 3333 Beverly Rd., B4-266A > > Hoffman Estates, IL. 60179 > > Office: (847) 286-5735 > > Email: Ernest.Knox@searshc.com > > Blackberry: 2244650553@messaging.sprintpcs.com > <mailto:2244650553@messaging.sprintpcs.com> > > Page via Skytel: 2244650553@sprint.skytel.com > <mailto:2244650553@sprint.skytel.com> > > Informix or MySQL Primary: 9110210@skytel.com > <mailto:9110210@skytel.com> > > Informix or MySQL Secondary: 7276872@skytel.com > <mailto:7276872@skytel.com> > > " Yes we can make a Change! " > > " It's always a great day to watch Sports - GO LIONS, TIGERS, and BEARS! > " > > " Lets not forget - GO Pistons and Red Wings! " > > GSU > > ******************************************************************* > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0023545bd0f0930464047e2d12c0

↪ replying to Plugge, Joe R.

John Miller iii — Wed, 27 Jan 2010 22:40:23 — source: IIUG Forums & Mailing Lists

No the customer table is not created as a raw table. John F. Miller III STSM, Support Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 01/27/2010 10:50:33 AM: > [image removed] > > RE: Data migration Brainstorm. [18806]: > > Plugge, Joe R. > > to: > > ids > > 01/27/2010 10:51 AM > > Sent by: > > ids-bounces@iiug.org > > Please respond to ids > > John, is the informix.customer table created RAW? > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of John > Miller iii > Sent: Wednesday, January 27, 2010 12:40 PM > To: ids@iiug.org > Subject: RE: Data migration Brainstorm. [18805] > > If you are looking at upgrading to a version of 11.50 I would look > at using external tables. My tests show that they are much faster > than HPL and I think simpler. > > Here are the three steps for a simple load from two unload files of a > customer table. I > have included several of the load options > > CREATE EXTERNAL TABLE 'informix'.EXTcustomer SAMEAS informix.customer > USING > ( > DATAFILES('DISK:/tmp/customer.unl','DISK:/tmp/customer2.unl'), > FORMAT 'DELIMITED', > DELIMITER '|', > RECORDEND '', > Deluxe, > NUMROWS 50, > MAXERRORS 50, > REJECTFILE '' > ); > > INSERT INTO informix.customer SELECT * FROM EXTcustomer; > DROP TABLE EXTcustomer;> > John F. Miller III > STSM, Support Architect > miller3@us.ibm.com > 503-578-5645 > IBM Informix Dynamic Server (IDS) > > ids-bounces@iiug.org wrote on 01/27/2010 09:16:57 AM: > > > [image removed] > > > > RE: Data migration Brainstorm. [18803]: > > > > Knox, Ernest > > > > to: > > > > ids > > > > 01/27/2010 09:17 AM > > > > Sent by: > > > > ids-bounces@iiug.org > > > > Please respond to ids > > > > I'm getting some good suggestions. I appreciate and will review them > > all, so keep them coming. > > > > Thanks, > > ******************************************************************* > > Ernie Knox > > IT Database Administrator Specialist > > Sears Holdings > > 3333 Beverly Rd., B4-266A > > Hoffman Estates, IL. 60179 > > Office: (847) 286-5735 > > Email: Ernest.Knox@searshc.com > > Blackberry: 2244650553@messaging.sprintpcs.com > > Page via Skytel: 2244650553@sprint.skytel.com > > Informix or MySQL Primary: 9110210@skytel.com > > Informix or MySQL Secondary: 7276872@skytel.com > > > > " Yes we can make a Change! " > > " It's always a great day to watch Sports - GO LIONS, TIGERS, and BEARS! > > " > > " Lets not forget - GO Pistons and Red Wings! " > > GSU > > ******************************************************************* > > > > -----Original Message----- > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > > Knox, Ernest > > Sent: Wednesday, January 27, 2010 10:46 AM > > To: ids@iiug.org > > Subject: Data migration Brainstorm. [18798] > > > > We are upgrading our Servers, OS and version of Informix from 7.30, > > 7.31, 9.21, 9.30, and 9.40 to version 11.50. > > > > My brainstorming question(s) to the group is: > > > > What is the quickest way to migrate between 1 gig to 300 gig of data? > > > > Since I can't use a backup and restore from different versions (I don't > > believe) and one or two databases do not have logging turned on, so > > replication is basically out. > > > > Would HPL, manual scripts (dbexport and dbimport), or a third party > > product do the trick. > > > > Please, your thoughts and suggestions are very helpful. > > > > Thanks, > > > > ******************************************************************* > > > > Ernie Knox > > > > IT Database Administrator Specialist > > > > Sears Holdings > > > > 3333 Beverly Rd., B4-266A > > > > Hoffman Estates, IL. 60179 > > > > Office: (847) 286-5735 > > > > Email: Ernest.Knox@searshc.com > > > > Blackberry: 2244650553@messaging.sprintpcs.com > > <mailto:2244650553@messaging.sprintpcs.com> > > > > Page via Skytel: 2244650553@sprint.skytel.com > > <mailto:2244650553@sprint.skytel.com> > > > > Informix or MySQL Primary: 9110210@skytel.com > > <mailto:9110210@skytel.com> > > > > Informix or MySQL Secondary: 7276872@skytel.com > > <mailto:7276872@skytel.com> > > > > " Yes we can make a Change! " > > > > " It's always a great day to watch Sports - GO LIONS, TIGERS, and BEARS! > > > > " > > > > " Lets not forget - GO Pistons and Red Wings! " > > > > GSU > > > > ******************************************************************* > > > > ************************************************************************ > > ******* > > 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. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

↪ replying to Peter_Logan@spartanstores.com

Art Kagel — Wed, 27 Jan 2010 22:55:59 — source: IIUG Forums & Mailing Lists

Should be: CREATE EXTERNAL TABLE 'informix'.EXTcustomer SAMEAS informix.customer USING ( DATAFILES('DISK:/tmp/customer.unl', 'DISK:/tmp/customer2.unl'), FORMAT 'DELIMITED', DELIMITER '|', RECORDEND '', Deluxe, NUMROWS 50, MAXERRORS 50, REJECTFILE ''/tmp/customer_reject.unl" ); Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Jan 27, 2010 at 2:16 PM, Peter_Logan@spartanstores.com < Peter_Logan@spartanstores.com> wrote: > John ... I was trying this out .. and I get a syntax error on the > REJECTFILE line ... position 14 ... I tried putting a file in there and > that didn't work ... which manual is this in so I can verify my syntax. > Thanks for the help ... > > Peter Logan > Senior Database Administrator > Phone: 616/878-8309 > > From: > "John Miller iii" <miller3@us.ibm.com> > To: > ids@iiug.org > Date: > 01/27/2010 01:40 PM > Subject: > RE: Data migration Brainstorm. [18805] > Sent by: > ids-bounces@iiug.org > > If you are looking at upgrading to a version of 11.50 I would look > at using external tables. My tests show that they are much faster > than HPL and I think simpler. > > Here are the three steps for a simple load from two unload files of a > customer table. I > have included several of the load options > > CREATE EXTERNAL TABLE 'informix'.EXTcustomer SAMEAS informix.customer > USING > ( > DATAFILES('DISK:/tmp/customer.unl','DISK:/tmp/customer2.unl'), > FORMAT 'DELIMITED', > DELIMITER '|', > RECORDEND '', > Deluxe, > NUMROWS 50, > MAXERRORS 50, > REJECTFILE '' > ); > > INSERT INTO informix.customer SELECT * FROM EXTcustomer; > DROP TABLE EXTcustomer;> > John F. Miller III > STSM, Support Architect > miller3@us.ibm.com > 503-578-5645 > IBM Informix Dynamic Server (IDS) > > ids-bounces@iiug.org wrote on 01/27/2010 09:16:57 AM: > > > [image removed] > > > > RE: Data migration Brainstorm. [18803]: > > > > Knox, Ernest > > > > to: > > > > ids > > > > 01/27/2010 09:17 AM > > > > Sent by: > > > > ids-bounces@iiug.org > > > > Please respond to ids > > > > I'm getting some good suggestions. I appreciate and will review them > > all, so keep them coming. > > > > Thanks, > > ******************************************************************* > > Ernie Knox > > IT Database Administrator Specialist > > Sears Holdings > > 3333 Beverly Rd., B4-266A > > Hoffman Estates, IL. 60179 > > Office: (847) 286-5735 > > Email: Ernest.Knox@searshc.com > > Blackberry: 2244650553@messaging.sprintpcs.com > > Page via Skytel: 2244650553@sprint.skytel.com > > Informix or MySQL Primary: 9110210@skytel.com > > Informix or MySQL Secondary: 7276872@skytel.com > > > > " Yes we can make a Change! " > > " It's always a great day to watch Sports - GO LIONS, TIGERS, and BEARS! > > > " > > " Lets not forget - GO Pistons and Red Wings! " > > GSU > > ******************************************************************* > > > > -----Original Message----- > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > > Knox, Ernest > > Sent: Wednesday, January 27, 2010 10:46 AM > > To: ids@iiug.org > > Subject: Data migration Brainstorm. [18798] > > > > We are upgrading our Servers, OS and version of Informix from 7.30, > > 7.31, 9.21, 9.30, and 9.40 to version 11.50. > > > > My brainstorming question(s) to the group is: > > > > What is the quickest way to migrate between 1 gig to 300 gig of data? > > > > Since I can't use a backup and restore from different versions (I don't > > believe) and one or two databases do not have logging turned on, so > > replication is basically out. > > > > Would HPL, manual scripts (dbexport and dbimport), or a third party > > product do the trick. > > > > Please, your thoughts and suggestions are very helpful. > > > > Thanks, > > > > ******************************************************************* > > > > Ernie Knox > > > > IT Database Administrator Specialist > > > > Sears Holdings > > > > 3333 Beverly Rd., B4-266A > > > > Hoffman Estates, IL. 60179 > > > > Office: (847) 286-5735 > > > > Email: Ernest.Knox@searshc.com > > > > Blackberry: 2244650553@messaging.sprintpcs.com > > <mailto:2244650553@messaging.sprintpcs.com> > > > > Page via Skytel: 2244650553@sprint.skytel.com > > <mailto:2244650553@sprint.skytel.com> > > > > Informix or MySQL Primary: 9110210@skytel.com > > <mailto:9110210@skytel.com> > > > > Informix or MySQL Secondary: 7276872@skytel.com > > <mailto:7276872@skytel.com> > > > > " Yes we can make a Change! " > > > > " It's always a great day to watch Sports - GO LIONS, TIGERS, and BEARS! > > > > > " > > > > " Lets not forget - GO Pistons and Red Wings! " > > > > GSU > > > > ******************************************************************* > > > > ************************************************************************ > > > ******* > > 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. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --00151747beaaf5f1d8047e2d4d71

↪ replying to Art Kagel

John Miller iii — Wed, 27 Jan 2010 23:06:14 — source: IIUG Forums & Mailing Lists

Art: Those were single quotes which were empty. Now that might not be the best but it showed the option. I copied and pasted the statement into 11.50.xC6 and it works fine for me. John F. Miller III STSM, Support Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 01/27/2010 02:56:00 PM: > [image removed] > > Re: Data migration Brainstorm. [18814]: > > Art Kagel > > to: > > ids > > 01/27/2010 02:56 PM > > Sent by: > > ids-bounces@iiug.org > > Please respond to ids > > Should be: > > CREATE EXTERNAL TABLE 'informix'.EXTcustomer SAMEAS informix.customer > USING > ( > DATAFILES('DISK:/tmp/customer.unl', 'DISK:/tmp/customer2.unl'), > FORMAT 'DELIMITED', > DELIMITER '|', > RECORDEND '', > Deluxe, > NUMROWS 50, > MAXERRORS 50, > REJECTFILE ''/tmp/customer_reject.unl" > ); > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > IIUG Board of Directors (art@iiug.org) > > See you at the 2010 IIUG Informix Conference > April 25-28, 2010 > Overland Park (Kansas City), KS > www.iiug.org/conf > > Disclaimer: Please keep in mind that my own opinions are my own opinions and > do not reflect on my employer, Advanced DataTools, the IIUG, nor any other > organization with which I am associated either explicitly, implicitly, or by > inference. Neither do those opinions reflect those of other individuals > affiliated with any entity with which I am affiliated nor those of the > entities themselves. > > On Wed, Jan 27, 2010 at 2:16 PM, Peter_Logan@spartanstores.com < > Peter_Logan@spartanstores.com> wrote: > > > John ... I was trying this out .. and I get a syntax error on the > > REJECTFILE line ... position 14 ... I tried putting a file in there and > > that didn't work ... which manual is this in so I can verify my syntax. > > Thanks for the help ... > > > > Peter Logan > > Senior Database Administrator > > Phone: 616/878-8309 > > > > From: > > "John Miller iii" <miller3@us.ibm.com> > > To: > > ids@iiug.org > > Date: > > 01/27/2010 01:40 PM > > Subject: > > RE: Data migration Brainstorm. [18805] > > Sent by: > > ids-bounces@iiug.org > > > > If you are looking at upgrading to a version of 11.50 I would look > > at using external tables. My tests show that they are much faster > > than HPL and I think simpler. > > > > Here are the three steps for a simple load from two unload files of a > > customer table. I > > have included several of the load options > > > > CREATE EXTERNAL TABLE 'informix'.EXTcustomer SAMEAS informix.customer > > USING > > ( > > DATAFILES('DISK:/tmp/customer.unl','DISK:/tmp/customer2.unl'), > > FORMAT 'DELIMITED', > > DELIMITER '|', > > RECORDEND '', > > Deluxe, > > NUMROWS 50, > > MAXERRORS 50, > > REJECTFILE '' > > ); > > > > INSERT INTO informix.customer SELECT * FROM EXTcustomer; > > DROP TABLE EXTcustomer;> > > > John F. Miller III > > STSM, Support Architect > > miller3@us.ibm.com > > 503-578-5645 > > IBM Informix Dynamic Server (IDS) > > > > ids-bounces@iiug.org wrote on 01/27/2010 09:16:57 AM: > > > > > [image removed] > > > > > > RE: Data migration Brainstorm. [18803]: > > > > > > Knox, Ernest > > > > > > to: > > > > > > ids > > > > > > 01/27/2010 09:17 AM > > > > > > Sent by: > > > > > > ids-bounces@iiug.org > > > > > > Please respond to ids > > > > > > I'm getting some good suggestions. I appreciate and will review them > > > all, so keep them coming. > > > > > > Thanks, > > > ******************************************************************* > > > Ernie Knox > > > IT Database Administrator Specialist > > > Sears Holdings > > > 3333 Beverly Rd., B4-266A > > > Hoffman Estates, IL. 60179 > > > Office: (847) 286-5735 > > > Email: Ernest.Knox@searshc.com > > > Blackberry: 2244650553@messaging.sprintpcs.com > > > Page via Skytel: 2244650553@sprint.skytel.com > > > Informix or MySQL Primary: 9110210@skytel.com > > > Informix or MySQL Secondary: 7276872@skytel.com > > > > > > " Yes we can make a Change! " > > > " It's always a great day to watch Sports - GO LIONS, TIGERS, and BEARS! > > > > > " > > > " Lets not forget - GO Pistons and Red Wings! " > > > GSU > > > ******************************************************************* > > > > > > -----Original Message----- > > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > > > Knox, Ernest > > > Sent: Wednesday, January 27, 2010 10:46 AM > > > To: ids@iiug.org > > > Subject: Data migration Brainstorm. [18798] > > > > > > We are upgrading our Servers, OS and version of Informix from 7.30, > > > 7.31, 9.21, 9.30, and 9.40 to version 11.50. > > > > > > My brainstorming question(s) to the group is: > > > > > > What is the quickest way to migrate between 1 gig to 300 gig of data? > > > > > > Since I can't use a backup and restore from different versions (I don't > > > believe) and one or two databases do not have logging turned on, so > > > replication is basically out. > > > > > > Would HPL, manual scripts (dbexport and dbimport), or a third party > > > product do the trick. > > > > > > Please, your thoughts and suggestions are very helpful. > > > > > > Thanks, > > > > > > ******************************************************************* > > > > > > Ernie Knox > > > > > > IT Database Administrator Specialist > > > > > > Sears Holdings > > > > > > 3333 Beverly Rd., B4-266A > > > > > > Hoffman Estates, IL. 60179 > > > > > > Office: (847) 286-5735 > > > > > > Email: Ernest.Knox@searshc.com > > > > > > Blackberry: 2244650553@messaging.sprintpcs.com > > > <mailto:2244650553@messaging.sprintpcs.com> > > > > > > Page via Skytel: 2244650553@sprint.skytel.com > > > <mailto:2244650553@sprint.skytel.com> > > > > > > Informix or MySQL Primary: 9110210@skytel.com > > > <mailto:9110210@skytel.com> > > > > > > Informix or MySQL Secondary: 7276872@skytel.com > > > <mailto:7276872@skytel.com> > > > > > > " Yes we can make a Change! " > > > > > > " It's always a great day to watch Sports - GO LIONS, TIGERS, and BEARS! > > > > > > > > " > > > > > > " Lets not forget - GO Pistons and Red Wings! " > > > > > > GSU > > > > > > ******************************************************************* > > > > > > ************************************************************************ > > > > > ******* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > >@@NL@

↪ replying to John Miller iii

Cesar Inacio Martins — Wed, 27 Jan 2010 23:19:23 — source: IIUG Forums & Mailing Lists

Hi John, Can you explain here details about some configuration (ONCONFIG) what maybe you do for execute this? I already executed a few tests with EXTERNALTABLE too (in my limited NetBook) and get high usage of physical log and just a bit faster then HPL , but my table have only 400 MB. I don't know, maybe I need test over a bigger table to feel the gain of performance... Any tips of the configuration to use the EXTERNAL TABLE? Regards Cesar --- Em qua, 27/1/10, John Miller iii <miller3@us.ibm.com> escreveu: De: John Miller iii <miller3@us.ibm.com> Assunto: RE: Data migration Brainstorm. [18805] Para: ids@iiug.org Data: Quarta-feira, 27 de Janeiro de 2010, 16:39 If you are looking at upgrading to a version of 11.50 I would look at using external tables. My tests show that they are much faster than HPL and I think simpler. Here are the three steps for a simple load from two unload files of a customer table. I have included several of the load options CREATE EXTERNAL TABLE 'informix'.EXTcustomer SAMEAS informix.customer USING ( DATAFILES('DISK:/tmp/customer.unl','DISK:/tmp/customer2.unl'), FORMAT 'DELIMITED', DELIMITER '|', RECORDEND '', Deluxe, NUMROWS 50, MAXERRORS 50, REJECTFILE '' ); INSERT INTO informix.customer SELECT * FROM EXTcustomer; DROP TABLE EXTcustomer; John F. Miller III STSM, Support Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 01/27/2010 09:16:57 AM: > [image removed] > > RE: Data migration Brainstorm. [18803]: > > Knox, Ernest > > to: > > ids > > 01/27/2010 09:17 AM > > Sent by: > > ids-bounces@iiug.org > > Please respond to ids > > I'm getting some good suggestions. I appreciate and will review them > all, so keep them coming. > > Thanks, > ******************************************************************* > Ernie Knox > IT Database Administrator Specialist > Sears Holdings > 3333 Beverly Rd., B4-266A > Hoffman Estates, IL. 60179 > Office: (847) 286-5735 > Email: Ernest.Knox@searshc.com > Blackberry: 2244650553@messaging.sprintpcs.com > Page via Skytel: 2244650553@sprint.skytel.com > Informix or MySQL Primary: 9110210@skytel.com > Informix or MySQL Secondary: 7276872@skytel.com > > " Yes we can make a Change! " > " It's always a great day to watch Sports - GO LIONS, TIGERS, and BEARS! > " > " Lets not forget - GO Pistons and Red Wings! " > GSU > ******************************************************************* > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Knox, Ernest > Sent: Wednesday, January 27, 2010 10:46 AM > To: ids@iiug.org > Subject: Data migration Brainstorm. [18798] > > We are upgrading our Servers, OS and version of Informix from 7.30, > 7.31, 9.21, 9.30, and 9.40 to version 11.50. > > My brainstorming question(s) to the group is: > > What is the quickest way to migrate between 1 gig to 300 gig of data? > > Since I can't use a backup and restore from different versions (I don't > believe) and one or two databases do not have logging turned on, so > replication is basically out. > > Would HPL, manual scripts (dbexport and dbimport), or a third party > product do the trick. > > Please, your thoughts and suggestions are very helpful. > > Thanks, > > ******************************************************************* > > Ernie Knox > > IT Database Administrator Specialist > > Sears Holdings > > 3333 Beverly Rd., B4-266A > > Hoffman Estates, IL. 60179 > > Office: (847) 286-5735 > > Email: Ernest.Knox@searshc.com > > Blackberry: 2244650553@messaging.sprintpcs.com > <mailto:2244650553@messaging.sprintpcs.com> > > Page via Skytel: 2244650553@sprint.skytel.com > <mailto:2244650553@sprint.skytel.com> > > Informix or MySQL Primary: 9110210@skytel.com > <mailto:9110210@skytel.com> > > Informix or MySQL Secondary: 7276872@skytel.com > <mailto:7276872@skytel.com> > > " Yes we can make a Change! " > > " It's always a great day to watch Sports - GO LIONS, TIGERS, and BEARS! > > " > > " Lets not forget - GO Pistons and Red Wings! " > > GSU > > ******************************************************************* > > ************************************************************************ > ******* > 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. ________________________________________________________________________________ ____ Veja quais são os assuntos do momento no Yahoo! +Buscados http://br.maisbuscados.yahoo.com

↪ replying to Cesar Inacio Martins

Art Kagel — Wed, 27 Jan 2010 23:22:32 — source: IIUG Forums & Mailing Lists

The problem on your NETBOOK may be that the database's chunks and the external table's file are all residing on your single disk drive. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Jan 27, 2010 at 6:19 PM, Cesar Inacio Martins < cesar_inacio_martins@yahoo.com.br> wrote: > Hi John, > > Can you explain here details about some configuration (ONCONFIG) what maybe > you do for execute this? > > I already executed a few tests with EXTERNALTABLE too (in my limited > NetBook) > and get high usage of physical log and just a bit faster then HPL , but my > table have only 400 MB. > I don't know, maybe I need test over a bigger table to feel the gain of > performance... > > Any tips of the configuration to use the EXTERNAL TABLE? > > Regards > Cesar > > --- Em qua, 27/1/10, John Miller iii <miller3@us.ibm.com> escreveu: > > De: John Miller iii <miller3@us.ibm.com> > Assunto: RE: Data migration Brainstorm. [18805] > Para: ids@iiug.org > Data: Quarta-feira, 27 de Janeiro de 2010, 16:39 > > If you are looking at upgrading to a version of 11.50 I would look > at using external tables. My tests show that they are much faster > than HPL and I think simpler. > > Here are the three steps for a simple load from two unload files of a > customer table. I > have included several of the load options > > CREATE EXTERNAL TABLE 'informix'.EXTcustomer SAMEAS informix.customer > USING > ( > DATAFILES('DISK:/tmp/customer.unl','DISK:/tmp/customer2.unl'), > FORMAT 'DELIMITED', > DELIMITER '|', > RECORDEND '', > Deluxe, > NUMROWS 50, > MAXERRORS 50, > REJECTFILE '' > ); > > INSERT INTO informix.customer SELECT * FROM EXTcustomer; > DROP TABLE EXTcustomer;> > John F. Miller III > STSM, Support Architect > miller3@us.ibm.com > 503-578-5645 > IBM Informix Dynamic Server (IDS) > > ids-bounces@iiug.org wrote on 01/27/2010 09:16:57 AM: > > > [image removed] > > > > RE: Data migration Brainstorm. [18803]: > > > > Knox, Ernest > > > > to: > > > > ids > > > > 01/27/2010 09:17 AM > > > > Sent by: > > > > ids-bounces@iiug.org > > > > Please respond to ids > > > > I'm getting some good suggestions. I appreciate and will review them > > all, so keep them coming. > > > > Thanks, > > ******************************************************************* > > Ernie Knox > > IT Database Administrator Specialist > > Sears Holdings > > 3333 Beverly Rd., B4-266A > > Hoffman Estates, IL. 60179 > > Office: (847) 286-5735 > > Email: Ernest.Knox@searshc.com > > Blackberry: 2244650553@messaging.sprintpcs.com > > Page via Skytel: 2244650553@sprint.skytel.com > > Informix or MySQL Primary: 9110210@skytel.com > > Informix or MySQL Secondary: 7276872@skytel.com > > > > " Yes we can make a Change! " > > " It's always a great day to watch Sports - GO LIONS, TIGERS, and BEARS! > > " > > " Lets not forget - GO Pistons and Red Wings! " > > GSU > > ******************************************************************* > > > > -----Original Message----- > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > > Knox, Ernest > > Sent: Wednesday, January 27, 2010 10:46 AM > > To: ids@iiug.org > > Subject: Data migration Brainstorm. [18798] > > > > We are upgrading our Servers, OS and version of Informix from 7.30, > > 7.31, 9.21, 9.30, and 9.40 to version 11.50. > > > > My brainstorming question(s) to the group is: > > > > What is the quickest way to migrate between 1 gig to 300 gig of data? > > > > Since I can't use a backup and restore from different versions (I don't > > believe) and one or two databases do not have logging turned on, so > > replication is basically out. > > > > Would HPL, manual scripts (dbexport and dbimport), or a third party > > product do the trick. > > > > Please, your thoughts and suggestions are very helpful. > > > > Thanks, > > > > ******************************************************************* > > > > Ernie Knox > > > > IT Database Administrator Specialist > > > > Sears Holdings > > > > 3333 Beverly Rd., B4-266A > > > > Hoffman Estates, IL. 60179 > > > > Office: (847) 286-5735 > > > > Email: Ernest.Knox@searshc.com > > > > Blackberry: 2244650553@messaging.sprintpcs.com > > <mailto:2244650553@messaging.sprintpcs.com> > > > > Page via Skytel: 2244650553@sprint.skytel.com > > <mailto:2244650553@sprint.skytel.com> > > > > Informix or MySQL Primary: 9110210@skytel.com > > <mailto:9110210@skytel.com> > > > > Informix or MySQL Secondary: 7276872@skytel.com > > <mailto:7276872@skytel.com> > > > > " Yes we can make a Change! " > > > > " It's always a great day to watch Sports - GO LIONS, TIGERS, and BEARS! > > > > " > > > > " Lets not forget - GO Pistons and Red Wings! " > > > > GSU > > > > ******************************************************************* > > > > ************************************************************************ > > ******* > > 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. > > > > ________________________________________________________________________________ ____ > Veja quais são os assuntos do momento no Yahoo! +Buscados > http://br.maisbuscados.yahoo.com > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001517478636b72094047e2dacbe

↪ replying to Knox, Ernest

Andreas.KUTSCHE@spar.at — Thu, 28 Jan 2010 08:32:35 — source: IIUG Forums & Mailing Lists

Hi, focussing on the term UPGRADING (all at once - Servers, OS, Informix). If the hardware is compatible (CPU and OS-Type the same, or 100% compatible for data like PA-Risc and IA64 under HPUX) and you are happy with your database layout (chunk names, dbspace sizes, etc. ) you can migrate the following way: - shutdown old IDS version on old server - copy chunks (raw disks with dd, files as you like) - maybe parallel - from one server to the other - startup new IDS version on new server (version upgrade will start automatically) - change any network configuration parameters / sqlhosts as you need (maybe switch names/ip-adresses of machines etc.) We did that a few times from HPUX PA-Risc to HPUX IA64, sometimes with IDS 7.31 to IDS 10 and sometimes with IDS 9.4 to IDS 10. Regards, Andreas > ------------------------------------------- SPAR Österreichische Warenhandels-AG Hauptzentrale A - 5015 Salzburg, Europastrasse 3 FN 34170 a Tel: +43 662 4470 84923 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 > Knox, Ernest > Gesendet: Mittwoch, 27. Januar 2010 17:46 > An: ids@iiug.org > Betreff: Data migration Brainstorm. [18798] > > We are upgrading our Servers, OS and version of Informix from 7.30, > 7.31, 9.21, 9.30, and 9.40 to version 11.50. > > My brainstorming question(s) to the group is: > > What is the quickest way to migrate between 1 gig to 300 gig of data? > > Since I can't use a backup and restore from different versions (I don't > believe) and one or two databases do not have logging turned on, so > replication is basically out. > > Would HPL, manual scripts (dbexport and dbimport), or a third party > product do the trick. > > Please, your thoughts and suggestions are very helpful. > > Thanks, > > ******************************************************************* > > Ernie Knox > > IT Database Administrator Specialist > > Sears Holdings > > 3333 Beverly Rd., B4-266A > > Hoffman Estates, IL. 60179 > > Office: (847) 286-5735 > > Email: Ernest.Knox@searshc.com > > Blackberry: 2244650553@messaging.sprintpcs.com > <mailto:2244650553@messaging.sprintpcs.com> > > Page via Skytel: 2244650553@sprint.skytel.com > <mailto:2244650553@sprint.skytel.com> > > Informix or MySQL Primary: 9110210@skytel.com > <mailto:9110210@skytel.com> > > Informix or MySQL Secondary: 7276872@skytel.com > <mailto:7276872@skytel.com> > > " Yes we can make a Change! " > > " It's always a great day to watch Sports - GO LIONS, TIGERS, and BEARS! > " > > " Lets not forget - GO Pistons and Red Wings! " > > GSU > > ******************************************************************* > > > ************************************************************************** > ***** > Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Knox, Ernest

Keith Simmons — Thu, 28 Jan 2010 08:53:26 — source: IIUG Forums & Mailing Lists

On 27 January 2010 16:46, Knox, Ernest <Ernest.Knox@searshc.com> wrote: > We are upgrading our Servers, OS and version of Informix from 7.30, > 7.31, 9.21, 9.30, and 9.40 to version 11.50. > > My brainstorming question(s) to the group is: > > What is the quickest way to migrate between 1 gig to 300 gig of data? > > Since I can't use a backup and restore from different versions (I don't > believe) and one or two databases do not have logging turned on, so > replication is basically out. > > Would HPL, manual scripts (dbexport and dbimport), or a third party > product do the trick. > > Please, your thoughts and suggestions are very helpful. > > Thanks, > > ******************************************************************* > > Ernie Knox > > IT Database Administrator Specialist > > Sears Holdings > > 3333 Beverly Rd., B4-266A > > Hoffman Estates, IL. 60179 > > Office: (847) 286-5735 > > Email: Ernest.Knox@searshc.com > > Blackberry: 2244650553@messaging.sprintpcs.com > <mailto:2244650553@messaging.sprintpcs.com> > > Page via Skytel: 2244650553@sprint.skytel.com > <mailto:2244650553@sprint.skytel.com> > > Informix or MySQL Primary: 9110210@skytel.com > <mailto:9110210@skytel.com> > > Informix or MySQL Secondary: 7276872@skytel.com > <mailto:7276872@skytel.com> > > " Yes we can make a Change! " > > " It's always a great day to watch Sports - GO LIONS, TIGERS, and BEARS! > " > > " Lets not forget - GO Pistons and Red Wings! " > > GSU > > ******************************************************************* > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > Ernest If you're happy with current dbspace and data layout you could install your old IDS version on the new machine, archive old and restore to new and then in-place upgrade. Alternatively, to reorg data and get a cleaner start, I used named pipes on a new server. In dbaccess on new machine, unload to pipe select * from old table, at command line have a dbload running, load from pipe insert into new table. With 4 streams running simultaneouslyfor larger tables and a single 'insert into new table select * from old table' for all tables less than 500,000 rows I shifted 500 G in around 8 hours. Keith

↪ replying to Keith Simmons

Lello, Nick — Thu, 28 Jan 2010 09:53:16 — source: IIUG Forums & Mailing Lists

In a similar vein, I have found a combination of sqlunload & sqlreload (from the IIUG sqlcmd package) works really well:- On New machine:- Ssh -C <old machine> "sqlunload -d DB -t TBL" | sqlreload -d DB -t TBL (The -C to ssh makes the network connection transparently compress/decompress data). -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Keith Simmons Sent: 28 January 2010 08:53 To: ids@iiug.org Subject: Re: Data migration Brainstorm. [18821] On 27 January 2010 16:46, Knox, Ernest <Ernest.Knox@searshc.com> wrote: > We are upgrading our Servers, OS and version of Informix from 7.30, > 7.31, 9.21, 9.30, and 9.40 to version 11.50. > > My brainstorming question(s) to the group is: > > What is the quickest way to migrate between 1 gig to 300 gig of data? > > Since I can't use a backup and restore from different versions (I don't > believe) and one or two databases do not have logging turned on, so > replication is basically out. > > Would HPL, manual scripts (dbexport and dbimport), or a third party > product do the trick. > > Please, your thoughts and suggestions are very helpful. > > Thanks, > > ******************************************************************* > > Ernie Knox > > IT Database Administrator Specialist > > Sears Holdings > > 3333 Beverly Rd., B4-266A > > Hoffman Estates, IL. 60179 > > Office: (847) 286-5735 > > Email: Ernest.Knox@searshc.com > > Blackberry: 2244650553@messaging.sprintpcs.com > <mailto:2244650553@messaging.sprintpcs.com> > > Page via Skytel: 2244650553@sprint.skytel.com > <mailto:2244650553@sprint.skytel.com> > > Informix or MySQL Primary: 9110210@skytel.com > <mailto:9110210@skytel.com> > > Informix or MySQL Secondary: 7276872@skytel.com > <mailto:7276872@skytel.com> > > " Yes we can make a Change! " > > " It's always a great day to watch Sports - GO LIONS, TIGERS, and BEARS! > " > > " Lets not forget - GO Pistons and Red Wings! " > > GSU > > ******************************************************************* > > > ************************************************************************ ******* > Forum Note: Use "Reply" to post a response in the discussion forum. > > Ernest If you're happy with current dbspace and data layout you could install your old IDS version on the new machine, archive old and restore to new and then in-place upgrade. Alternatively, to reorg data and get a cleaner start, I used named pipes on a new server. In dbaccess on new machine, unload to pipe select * from old table, at command line have a dbload running, load from pipe insert into new table. With 4 streams running simultaneouslyfor larger tables and a single 'insert into new table select * from old table' for all tables less than 500,000 rows I shifted 500 G in around 8 hours. Keith ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Art Kagel

Cesar Inacio Martins — Thu, 28 Jan 2010 11:18:51 — source: IIUG Forums & Mailing Lists

Hi Art, No, I' m very careful with this kind of issues , I create a tmpfs (memory fs) and put my unl there... this way I don' t get of I/O and the read is very fast. Except by Physical Log X dbspace concurrency transfer... what so far I know work with big I/O... I stranger a lot this physical log usage... or I'm confusing something... Regards Cesar --- Em qua, 27/1/10, Art Kagel <art.kagel@gmail.com> escreveu: De: Art Kagel <art.kagel@gmail.com> Assunto: Re: Data migration Brainstorm. [18818] Para: ids@iiug.org Data: Quarta-feira, 27 de Janeiro de 2010, 21:22 The problem on your NETBOOK may be that the database's chunks and the external table's file are all residing on your single disk drive. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Jan 27, 2010 at 6:19 PM, Cesar Inacio Martins < cesar_inacio_martins@yahoo.com.br> wrote: > Hi John, > > Can you explain here details about some configuration (ONCONFIG) what maybe > you do for execute this? > > I already executed a few tests with EXTERNALTABLE too (in my limited > NetBook) > and get high usage of physical log and just a bit faster then HPL , but my > table have only 400 MB. > I don't know, maybe I need test over a bigger table to feel the gain of > performance... > > Any tips of the configuration to use the EXTERNAL TABLE? > > Regards > Cesar > > --- Em qua, 27/1/10, John Miller iii <miller3@us.ibm.com> escreveu: > > De: John Miller iii <miller3@us.ibm.com> > Assunto: RE: Data migration Brainstorm. [18805] > Para: ids@iiug.org > Data: Quarta-feira, 27 de Janeiro de 2010, 16:39 > > If you are looking at upgrading to a version of 11.50 I would look > at using external tables. My tests show that they are much faster > than HPL and I think simpler. > > Here are the three steps for a simple load from two unload files of a > customer table. I > have included several of the load options > > CREATE EXTERNAL TABLE 'informix'.EXTcustomer SAMEAS informix.customer > USING > ( > DATAFILES('DISK:/tmp/customer.unl','DISK:/tmp/customer2.unl'), > FORMAT 'DELIMITED', > DELIMITER '|', > RECORDEND '', > Deluxe, > NUMROWS 50, > MAXERRORS 50, > REJECTFILE '' > ); > > INSERT INTO informix.customer SELECT * FROM EXTcustomer; > DROP TABLE EXTcustomer;> > John F. Miller III > STSM, Support Architect > miller3@us.ibm.com > 503-578-5645 > IBM Informix Dynamic Server (IDS) > > ids-bounces@iiug.org wrote on 01/27/2010 09:16:57 AM: > > > [image removed] > > > > RE: Data migration Brainstorm. [18803]: > > > > Knox, Ernest > > > > to: > > > > ids > > > > 01/27/2010 09:17 AM > > > > Sent by: > > > > ids-bounces@iiug.org > > > > Please respond to ids > > > > I'm getting some good suggestions. I appreciate and will review them > > all, so keep them coming. > > > > Thanks, > > ******************************************************************* > > Ernie Knox > > IT Database Administrator Specialist > > Sears Holdings > > 3333 Beverly Rd., B4-266A > > Hoffman Estates, IL. 60179 > > Office: (847) 286-5735 > > Email: Ernest.Knox@searshc.com > > Blackberry: 2244650553@messaging.sprintpcs.com > > Page via Skytel: 2244650553@sprint.skytel.com > > Informix or MySQL Primary: 9110210@skytel.com > > Informix or MySQL Secondary: 7276872@skytel.com > > > > " Yes we can make a Change! " > > " It's always a great day to watch Sports - GO LIONS, TIGERS, and BEARS! > > " > > " Lets not forget - GO Pistons and Red Wings! " > > GSU > > ******************************************************************* > > > > -----Original Message----- > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > > Knox, Ernest > > Sent: Wednesday, January 27, 2010 10:46 AM > > To: ids@iiug.org > > Subject: Data migration Brainstorm. [18798] > > > > We are upgrading our Servers, OS and version of Informix from 7.30, > > 7.31, 9.21, 9.30, and 9.40 to version 11.50. > > > > My brainstorming question(s) to the group is: > > > > What is the quickest way to migrate between 1 gig to 300 gig of data? > > > > Since I can't use a backup and restore from different versions (I don't > > believe) and one or two databases do not have logging turned on, so > > replication is basically out. > > > > Would HPL, manual scripts (dbexport and dbimport), or a third party > > product do the trick. > > > > Please, your thoughts and suggestions are very helpful. > > > > Thanks, > > > > ******************************************************************* > > > > Ernie Knox > > > > IT Database Administrator Specialist > > > > Sears Holdings > > > > 3333 Beverly Rd., B4-266A > > > > Hoffman Estates, IL. 60179 > > > > Office: (847) 286-5735 > > > > Email: Ernest.Knox@searshc.com > > > > Blackberry: 2244650553@messaging.sprintpcs.com > > <mailto:2244650553@messaging.sprintpcs.com> > > > > Page via Skytel: 2244650553@sprint.skytel.com > > <mailto:2244650553@sprint.skytel.com> > > > > Informix or MySQL Primary: 9110210@skytel.com > > <mailto:9110210@skytel.com> > > > > Informix or MySQL Secondary: 7276872@skytel.com > > <mailto:7276872@skytel.com> > > > > " Yes we can make a Change! " > > > > " It's always a great day to watch Sports - GO LIONS, TIGERS, and BEARS! > > > > " > > > > " Lets not forget - GO Pistons and Red Wings! " > > > > GSU > > > > ******************************************************************* > > > > ************************************************************************ > > ******* > > 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. > > > > ________________________________________________________________________________ ____ > Veja quais são os assuntos do momento no Yahoo! +Buscados > http://br.maisbuscados.yahoo.com > > > > *******************************************************************

↪ replying to Lello, Nick

Art Kagel — Thu, 28 Jan 2010 12:37:12 — source: IIUG Forums & Mailing Lists

To make that easier, you can also use my myexport/myimport package which uses sqlunload & sql reload or optionally uses HPLoader (via Ravi Krishna's myonpload utility) to emulate dbexport/dbimport but faster. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Jan 28, 2010 at 4:53 AM, Lello, Nick <Nick.Lello@nielsen.com> wrote: > In a similar vein, I have found a combination of sqlunload & sqlreload > (from the IIUG sqlcmd package) works really well:- > > On New machine:- > > Ssh -C <old machine> "sqlunload -d DB -t TBL" | sqlreload -d DB -t > TBL > > (The -C to ssh makes the network connection transparently > compress/decompress data). > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Keith Simmons > Sent: 28 January 2010 08:53 > To: ids@iiug.org > Subject: Re: Data migration Brainstorm. [18821] > > On 27 January 2010 16:46, Knox, Ernest <Ernest.Knox@searshc.com> wrote: > > We are upgrading our Servers, OS and version of Informix from 7.30, > > 7.31, 9.21, 9.30, and 9.40 to version 11.50. > > > > My brainstorming question(s) to the group is: > > > > What is the quickest way to migrate between 1 gig to 300 gig of data? > > > > Since I can't use a backup and restore from different versions (I > don't > > believe) and one or two databases do not have logging turned on, so > > replication is basically out. > > > > Would HPL, manual scripts (dbexport and dbimport), or a third party > > product do the trick. > > > > Please, your thoughts and suggestions are very helpful. > > > > Thanks, > > > > ******************************************************************* > > > > Ernie Knox > > > > IT Database Administrator Specialist > > > > Sears Holdings > > > > 3333 Beverly Rd., B4-266A > > > > Hoffman Estates, IL. 60179 > > > > Office: (847) 286-5735 > > > > Email: Ernest.Knox@searshc.com > > > > Blackberry: 2244650553@messaging.sprintpcs.com > > <mailto:2244650553@messaging.sprintpcs.com> > > > > Page via Skytel: 2244650553@sprint.skytel.com > > <mailto:2244650553@sprint.skytel.com> > > > > Informix or MySQL Primary: 9110210@skytel.com > > <mailto:9110210@skytel.com> > > > > Informix or MySQL Secondary: 7276872@skytel.com > > <mailto:7276872@skytel.com> > > > > " Yes we can make a Change! " > > > > " It's always a great day to watch Sports - GO LIONS, TIGERS, and > BEARS! > > " > > > > " Lets not forget - GO Pistons and Red Wings! " > > > > GSU > > > > ******************************************************************* > > > > > > > ************************************************************************ > ******* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > Ernest > > If you're happy with current dbspace and data layout you could install > your old IDS version on the new machine, archive old and restore to > new and then in-place upgrade. > Alternatively, to reorg data and get a cleaner start, I used named > pipes on a new server. In dbaccess on new machine, unload to pipe > select * from old table, at command line have a dbload running, load > from pipe insert into new table. With 4 streams running simultaneously> for larger tables and a single 'insert into new table select * from > old table' for all tables less than 500,000 rows I shifted 500 G in > around 8 hours. > > Keith > > ************************************************************************ > ******* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0023545bd0f0b9f9e2047e38c66e

↪ replying to John Miller iii

Peter_Logan@spartanstores.com — Thu, 28 Jan 2010 13:08:47 — source: IIUG Forums & Mailing Lists

ok ... 11.50.fc5 ... that explains it .. thanks .... Peter Logan Senior Database Administrator Phone: 616/878-8309 From: "John Miller iii" <miller3@us.ibm.com> To: ids@iiug.org Date: 01/27/2010 05:35 PM Subject: RE: Data migration Brainstorm. [18810] Sent by: ids-bounces@iiug.org What version of IDS are you using. This feature is available in 11.50.xC6 and above. John F. Miller III STSM, Support Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 01/27/2010 11:16:46 AM: > [image removed] > > RE: Data migration Brainstorm. [18807]: > > Peter_Logan@spartanstores.com > > to: > > ids > > 01/27/2010 11:17 AM > > Sent by: > > ids-bounces@iiug.org > > Please respond to ids > > John ... I was trying this out .. and I get a syntax error on the > REJECTFILE line ... position 14 ... I tried putting a file in there and > that didn't work ... which manual is this in so I can verify my syntax. > Thanks for the help ... > > Peter Logan > Senior Database Administrator > Phone: 616/878-8309 > > From: > "John Miller iii" <miller3@us.ibm.com> > To: > ids@iiug.org > Date: > 01/27/2010 01:40 PM > Subject: > RE: Data migration Brainstorm. [18805] > Sent by: > ids-bounces@iiug.org > > If you are looking at upgrading to a version of 11.50 I would look > at using external tables. My tests show that they are much faster > than HPL and I think simpler. > > Here are the three steps for a simple load from two unload files of a > customer table. I > have included several of the load options > > CREATE EXTERNAL TABLE 'informix'.EXTcustomer SAMEAS informix.customer > USING > ( > DATAFILES('DISK:/tmp/customer.unl','DISK:/tmp/customer2.unl'), > FORMAT 'DELIMITED', > DELIMITER '|', > RECORDEND '', > Deluxe, > NUMROWS 50, > MAXERRORS 50, > REJECTFILE '' > ); > > INSERT INTO informix.customer SELECT * FROM EXTcustomer; > DROP TABLE EXTcustomer;> > John F. Miller III > STSM, Support Architect > miller3@us.ibm.com > 503-578-5645 > IBM Informix Dynamic Server (IDS) > > ids-bounces@iiug.org wrote on 01/27/2010 09:16:57 AM: > > > [image removed] > > > > RE: Data migration Brainstorm. [18803]: > > > > Knox, Ernest > > > > to: > > > > ids > > > > 01/27/2010 09:17 AM > > > > Sent by: > > > > ids-bounces@iiug.org > > > > Please respond to ids > > > > I'm getting some good suggestions. I appreciate and will review them > > all, so keep them coming. > > > > Thanks, > > ******************************************************************* > > Ernie Knox > > IT Database Administrator Specialist > > Sears Holdings > > 3333 Beverly Rd., B4-266A > > Hoffman Estates, IL. 60179 > > Office: (847) 286-5735 > > Email: Ernest.Knox@searshc.com > > Blackberry: 2244650553@messaging.sprintpcs.com > > Page via Skytel: 2244650553@sprint.skytel.com > > Informix or MySQL Primary: 9110210@skytel.com > > Informix or MySQL Secondary: 7276872@skytel.com > > > > " Yes we can make a Change! " > > " It's always a great day to watch Sports - GO LIONS, TIGERS, and BEARS! > > > " > > " Lets not forget - GO Pistons and Red Wings! " > > GSU > > ******************************************************************* > > > > -----Original Message----- > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > > Knox, Ernest > > Sent: Wednesday, January 27, 2010 10:46 AM > > To: ids@iiug.org > > Subject: Data migration Brainstorm. [18798] > > > > We are upgrading our Servers, OS and version of Informix from 7.30, > > 7.31, 9.21, 9.30, and 9.40 to version 11.50. > > > > My brainstorming question(s) to the group is: > > > > What is the quickest way to migrate between 1 gig to 300 gig of data? > > > > Since I can't use a backup and restore from different versions (I don't > > believe) and one or two databases do not have logging turned on, so > > replication is basically out. > > > > Would HPL, manual scripts (dbexport and dbimport), or a third party > > product do the trick. > > > > Please, your thoughts and suggestions are very helpful. > > > > Thanks, > > > > ******************************************************************* > > > > Ernie Knox > > > > IT Database Administrator Specialist > > > > Sears Holdings > > > > 3333 Beverly Rd., B4-266A > > > > Hoffman Estates, IL. 60179 > > > > Office: (847) 286-5735 > > > > Email: Ernest.Knox@searshc.com > > > > Blackberry: 2244650553@messaging.sprintpcs.com > > <mailto:2244650553@messaging.sprintpcs.com> > > > > Page via Skytel: 2244650553@sprint.skytel.com > > <mailto:2244650553@sprint.skytel.com> > > > > Informix or MySQL Primary: 9110210@skytel.com > > <mailto:9110210@skytel.com> > > > > Informix or MySQL Secondary: 7276872@skytel.com > > <mailto:7276872@skytel.com> > > > > " Yes we can make a Change! " > > > > " It's always a great day to watch Sports - GO LIONS, TIGERS, and BEARS! > > > > > " > > > > " Lets not forget - GO Pistons and Red Wings! " > > > > GSU > > > > ******************************************************************* > > > > ************************************************************************ > > > ******* > > 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. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Cesar Inacio Martins

John Miller iii — Thu, 28 Jan 2010 17:51:32 — source: IIUG Forums & Mailing Lists

There is a few things to try. 1. Which mode where you running the load/unload in. Dexule or Express= . Express is much faster, but does have a few limitations, HPL has both of these modes also. 2. Please make sure you do a checkpoint before doing you loads. If you have done a drop table in the dbspace to where you are load data, this will cause a lot more physical logging. 3. Please check the size of your physical log buffer, onstat -l will show it average usage. John F. Miller III STSM, Support Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 01/28/2010 03:18:51 AM: > Hi Art, > > No, I' m very careful with this kind of issues , I create a tmpfs (me= mory fs) > and put my unl there... this way I don' t get of I/O and the read is = very > fast. > Except by Physical Log X dbspace concurrency transfer... what so far = I know > work with big I/O... > I stranger a lot this physical log usage... or I'm confusing somethin= g... > > Regards > Cesar > > --- Em qua, 27/1/10, Art Kagel <art.kagel@gmail.com> escreveu: > > De: Art Kagel <art.kagel@gmail.com> > Assunto: Re: Data migration Brainstorm. [18818] > Para: ids@iiug.org > Data: Quarta-feira, 27 de Janeiro de 2010, 21:22 > > The problem on your NETBOOK may be that the database's chunks and the= > external table's file are all residing on your single disk drive. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > IIUG Board of Directors (art@iiug.org) > > See you at the 2010 IIUG Informix Conference > April 25-28, 2010 > Overland Park (Kansas City), KS > www.iiug.org/conf > > Disclaimer: Please keep in mind that my own opinions are my own opini= ons and > do not reflect on my employer, Advanced DataTools, the IIUG, nor any other > organization with which I am associated either explicitly, implicitly= , or by > inference. Neither do those opinions reflect those of other individua= ls > affiliated with any entity with which I am affiliated nor those of th= e > entities themselves. > > On Wed, Jan 27, 2010 at 6:19 PM, Cesar Inacio Martins < > cesar_inacio_martins@yahoo.com.br> wrote: > > > Hi John, > > > > Can you explain here details about some configuration (ONCONFIG) wh= at maybe > > you do for execute this? > > > > I already executed a few tests with EXTERNALTABLE too (in my limite= d > > NetBook) > > and get high usage of physical log and just a bit faster then HPL ,= but my > > table have only 400 MB. > > I don't know, maybe I need test over a bigger table to feel the gai= n of > > performance... > > > > Any tips of the configuration to use the EXTERNAL TABLE? > > > > Regards > > Cesar > > > > --- Em qua, 27/1/10, John Miller iii <miller3@us.ibm.com> escreveu:= > > > > De: John Miller iii <miller3@us.ibm.com> > > Assunto: RE: Data migration Brainstorm. [18805] > > Para: ids@iiug.org > > Data: Quarta-feira, 27 de Janeiro de 2010, 16:39 > > > > If you are looking at upgrading to a version of 11.50 I would look > > at using external tables. My tests show that they are much faster > > than HPL and I think simpler. > > > > Here are the three steps for a simple load from two unload files of= a > > customer table. I > > have included several of the load options > > > > CREATE EXTERNAL TABLE 'informix'.EXTcustomer SAMEAS informix.custom= er > > USING > > ( > > DATAFILES('DISK:/tmp/customer.unl','DISK:/tmp/customer2.unl'), > > FORMAT 'DELIMITED', > > DELIMITER '|', > > RECORDEND '', > > Deluxe, > > NUMROWS 50, > > MAXERRORS 50, > > REJECTFILE '' > > ); > > > > INSERT INTO informix.customer SELECT * FROM EXTcustomer; > > DROP TABLE EXTcustomer;> > > > John F. Miller III > > STSM, Support Architect > > miller3@us.ibm.com > > 503-578-5645 > > IBM Informix Dynamic Server (IDS) > > > > ids-bounces@iiug.org wrote on 01/27/2010 09:16:57 AM: > > > > > [image removed] > > > > > > RE: Data migration Brainstorm. [18803]: > > > > > > Knox, Ernest > > > > > > to: > > > > > > ids > > > > > > 01/27/2010 09:17 AM > > > > > > Sent by: > > > > > > ids-bounces@iiug.org > > > > > > Please respond to ids > > > > > > I'm getting some good suggestions. I appreciate and will review t= hem > > > all, so keep them coming. > > > > > > Thanks, > > > *****************************************************************= ** > > > Ernie Knox > > > IT Database Administrator Specialist > > > Sears Holdings > > > 3333 Beverly Rd., B4-266A > > > Hoffman Estates, IL. 60179 > > > Office: (847) 286-5735 > > > Email: Ernest.Knox@searshc.com > > > Blackberry: 2244650553@messaging.sprintpcs.com > > > Page via Skytel: 2244650553@sprint.skytel.com > > > Informix or MySQL Primary: 9110210@skytel.com > > > Informix or MySQL Secondary: 7276872@skytel.com > > > > > > " Yes we can make a Change! " > > > " It's always a great day to watch Sports - GO LIONS, TIGERS, and= BEARS! > > > " > > > " Lets not forget - GO Pistons and Red Wings! " > > > GSU > > > *****************************************************************= ** > > > > > > -----Original Message----- > > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behal= f Of > > > Knox, Ernest > > > Sent: Wednesday, January 27, 2010 10:46 AM > > > To: ids@iiug.org > > > Subject: Data migration Brainstorm. [18798] > > > > > > We are upgrading our Servers, OS and version of Informix from 7.3= 0, > > > 7.31, 9.21, 9.30, and 9.40 to version 11.50. > > > > > > My brainstorming question(s) to the group is: > > > > > > What is the quickest way to migrate between 1 gig to 300 gig of d= ata? > > > > > > Since I can't use a backup and restore from different versions (I= don't > > > believe) and one or two databases do not have logging turned on, = so > > > replication is basically out. > > > > > > Would HPL, manual scripts (dbexport and dbimport), or a third par= ty > > > product do the trick. > > > > > > Please, your thoughts and suggestions are very helpful. > > > > > > Thanks, > > > > > > *****************************************************************= ** > > > > > > Ernie Knox > > > > > > IT Database Administrator Specialist > > > > > > Sears Holdings > > > > > > 3333 Beverly Rd., B4-266A > > > > > > Hoffman Estates, IL. 60179 > > > > > > Office: (847) 286-5735 > > > > > > Email: Ernest.Knox@searshc.com > > > > > > Blackberry: 2244650553@messaging.sprintpcs.com > > > <mailto:2244650553@messaging.sprintpcs.com> > > > > > > Page via Skytel: 2244650553@sprint.skytel.com > > > <mailto:2244650553@sprint.skytel.com> > > > > > > Informix or MySQL Primary: 9110210@skytel.com > > > <mailto:9110210@skytel.com> > > > >

↪ replying to John Miller iii

Peter_Logan@spartanstores.com — Thu, 28 Jan 2010 21:32:33 — source: IIUG Forums & Mailing Lists

Hi John, I installed an FC6 engine today and did some testing with the external tables. So far so good. I do have a question that hopefully you can answer. It appears that when the table is dropped, the data files remain .. Is that expected behavior.... Peter Logan Senior Database Administrator Phone: 616/878-8309 From: "John Miller iii" <miller3@us.ibm.com> To: ids@iiug.org Date: 01/27/2010 01:40 PM Subject: RE: Data migration Brainstorm. [18805] Sent by: ids-bounces@iiug.org If you are looking at upgrading to a version of 11.50 I would look at using external tables. My tests show that they are much faster than HPL and I think simpler. Here are the three steps for a simple load from two unload files of a customer table. I have included several of the load options CREATE EXTERNAL TABLE 'informix'.EXTcustomer SAMEAS informix.customer USING ( DATAFILES('DISK:/tmp/customer.unl','DISK:/tmp/customer2.unl'), FORMAT 'DELIMITED', DELIMITER '|', RECORDEND '', Deluxe, NUMROWS 50, MAXERRORS 50, REJECTFILE '' ); INSERT INTO informix.customer SELECT * FROM EXTcustomer; DROP TABLE EXTcustomer; John F. Miller III STSM, Support Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 01/27/2010 09:16:57 AM: > [image removed] > > RE: Data migration Brainstorm. [18803]: > > Knox, Ernest > > to: > > ids > > 01/27/2010 09:17 AM > > Sent by: > > ids-bounces@iiug.org > > Please respond to ids > > I'm getting some good suggestions. I appreciate and will review them > all, so keep them coming. > > Thanks, > ******************************************************************* > Ernie Knox > IT Database Administrator Specialist > Sears Holdings > 3333 Beverly Rd., B4-266A > Hoffman Estates, IL. 60179 > Office: (847) 286-5735 > Email: Ernest.Knox@searshc.com > Blackberry: 2244650553@messaging.sprintpcs.com > Page via Skytel: 2244650553@sprint.skytel.com > Informix or MySQL Primary: 9110210@skytel.com > Informix or MySQL Secondary: 7276872@skytel.com > > " Yes we can make a Change! " > " It's always a great day to watch Sports - GO LIONS, TIGERS, and BEARS! > " > " Lets not forget - GO Pistons and Red Wings! " > GSU > ******************************************************************* > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Knox, Ernest > Sent: Wednesday, January 27, 2010 10:46 AM > To: ids@iiug.org > Subject: Data migration Brainstorm. [18798] > > We are upgrading our Servers, OS and version of Informix from 7.30, > 7.31, 9.21, 9.30, and 9.40 to version 11.50. > > My brainstorming question(s) to the group is: > > What is the quickest way to migrate between 1 gig to 300 gig of data? > > Since I can't use a backup and restore from different versions (I don't > believe) and one or two databases do not have logging turned on, so > replication is basically out. > > Would HPL, manual scripts (dbexport and dbimport), or a third party > product do the trick. > > Please, your thoughts and suggestions are very helpful. > > Thanks, > > ******************************************************************* > > Ernie Knox > > IT Database Administrator Specialist > > Sears Holdings > > 3333 Beverly Rd., B4-266A > > Hoffman Estates, IL. 60179 > > Office: (847) 286-5735 > > Email: Ernest.Knox@searshc.com > > Blackberry: 2244650553@messaging.sprintpcs.com > <mailto:2244650553@messaging.sprintpcs.com> > > Page via Skytel: 2244650553@sprint.skytel.com > <mailto:2244650553@sprint.skytel.com> > > Informix or MySQL Primary: 9110210@skytel.com > <mailto:9110210@skytel.com> > > Informix or MySQL Secondary: 7276872@skytel.com > <mailto:7276872@skytel.com> > > " Yes we can make a Change! " > > " It's always a great day to watch Sports - GO LIONS, TIGERS, and BEARS! > > " > > " Lets not forget - GO Pistons and Red Wings! " > > GSU > > ******************************************************************* > > ************************************************************************ > ******* > 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.

↪ replying to Peter_Logan@spartanstores.com

John Miller iii — Fri, 29 Jan 2010 00:18:08 — source: IIUG Forums & Mailing Lists

This is the correct behavior, we do not remove the underlying file when dropping the external table that maps to the file. John F. Miller III STSM, Support Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 01/28/2010 01:32:34 PM: > [image removed] > > RE: Data migration Brainstorm. [18829]: > > Peter_Logan@spartanstores.com > > to: > > ids > > 01/28/2010 01:33 PM > > Sent by: > > ids-bounces@iiug.org > > Please respond to ids > > Hi John, > > I installed an FC6 engine today and did some testing with the external > tables. So far so good. I do have a question that hopefully you can > answer. It appears that when the table is dropped, the data files remain > ... Is that expected behavior.... > > Peter Logan > Senior Database Administrator > Phone: 616/878-8309 > > From: > "John Miller iii" <miller3@us.ibm.com> > To: > ids@iiug.org > Date: > 01/27/2010 01:40 PM > Subject: > RE: Data migration Brainstorm. [18805] > Sent by: > ids-bounces@iiug.org > > If you are looking at upgrading to a version of 11.50 I would look > at using external tables. My tests show that they are much faster > than HPL and I think simpler. > > Here are the three steps for a simple load from two unload files of a > customer table. I > have included several of the load options > > CREATE EXTERNAL TABLE 'informix'.EXTcustomer SAMEAS informix.customer > USING > ( > DATAFILES('DISK:/tmp/customer.unl','DISK:/tmp/customer2.unl'), > FORMAT 'DELIMITED', > DELIMITER '|', > RECORDEND '', > Deluxe, > NUMROWS 50, > MAXERRORS 50, > REJECTFILE '' > ); > > INSERT INTO informix.customer SELECT * FROM EXTcustomer; > DROP TABLE EXTcustomer;> > John F. Miller III > STSM, Support Architect > miller3@us.ibm.com > 503-578-5645 > IBM Informix Dynamic Server (IDS) > > ids-bounces@iiug.org wrote on 01/27/2010 09:16:57 AM: > > > [image removed] > > > > RE: Data migration Brainstorm. [18803]: > > > > Knox, Ernest > > > > to: > > > > ids > > > > 01/27/2010 09:17 AM > > > > Sent by: > > > > ids-bounces@iiug.org > > > > Please respond to ids > > > > I'm getting some good suggestions. I appreciate and will review them > > all, so keep them coming. > > > > Thanks, > > ******************************************************************* > > Ernie Knox > > IT Database Administrator Specialist > > Sears Holdings > > 3333 Beverly Rd., B4-266A > > Hoffman Estates, IL. 60179 > > Office: (847) 286-5735 > > Email: Ernest.Knox@searshc.com > > Blackberry: 2244650553@messaging.sprintpcs.com > > Page via Skytel: 2244650553@sprint.skytel.com > > Informix or MySQL Primary: 9110210@skytel.com > > Informix or MySQL Secondary: 7276872@skytel.com > > > > " Yes we can make a Change! " > > " It's always a great day to watch Sports - GO LIONS, TIGERS, and BEARS! > > > " > > " Lets not forget - GO Pistons and Red Wings! " > > GSU > > ******************************************************************* > > > > -----Original Message----- > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > > Knox, Ernest > > Sent: Wednesday, January 27, 2010 10:46 AM > > To: ids@iiug.org > > Subject: Data migration Brainstorm. [18798] > > > > We are upgrading our Servers, OS and version of Informix from 7.30, > > 7.31, 9.21, 9.30, and 9.40 to version 11.50. > > > > My brainstorming question(s) to the group is: > > > > What is the quickest way to migrate between 1 gig to 300 gig of data? > > > > Since I can't use a backup and restore from different versions (I don't > > believe) and one or two databases do not have logging turned on, so > > replication is basically out. > > > > Would HPL, manual scripts (dbexport and dbimport), or a third party > > product do the trick. > > > > Please, your thoughts and suggestions are very helpful. > > > > Thanks, > > > > ******************************************************************* > > > > Ernie Knox > > > > IT Database Administrator Specialist > > > > Sears Holdings > > > > 3333 Beverly Rd., B4-266A > > > > Hoffman Estates, IL. 60179 > > > > Office: (847) 286-5735 > > > > Email: Ernest.Knox@searshc.com > > > > Blackberry: 2244650553@messaging.sprintpcs.com > > <mailto:2244650553@messaging.sprintpcs.com> > > > > Page via Skytel: 2244650553@sprint.skytel.com > > <mailto:2244650553@sprint.skytel.com> > > > > Informix or MySQL Primary: 9110210@skytel.com > > <mailto:9110210@skytel.com> > > > > Informix or MySQL Secondary: 7276872@skytel.com > > <mailto:7276872@skytel.com> > > > > " Yes we can make a Change! " > > > > " It's always a great day to watch Sports - GO LIONS, TIGERS, and BEARS! > > > > > " > > > > " Lets not forget - GO Pistons and Red Wings! " > > > > GSU > > > > ******************************************************************* > > > > ************************************************************************ > > > ******* > > 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. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

↪ replying to John Miller iii

Cesar Inacio Martins — Fri, 29 Jan 2010 01:28:42 — source: IIUG Forums & Mailing Lists

Hi John, The problem is the missing checkpoint... I can't figure out why this , for me this appear be a bug. But you said this is an expected behave, can you explain? please... BTW, with checkpoint, HPL run in 33sec , EXTERNAL TABLE in 22 seconds , very good... not 3x time faster like the White paper on IIUG site, but I'm very happy with this results! :) HPL and EXTERNAL have similar configuration (express, load 3 files in parallel) Here is the answers for your questions ... ---------------- 1. I run in express mode 2. I forgot to force the checkpoint and this is the reason to use of the physical log, what for me isn't a expected behave. 3. Default value, 128KB ---------------- If you want to reproduce, check the steps bellow. Steps what I do: (running on Linux) ---------------- A. Create a file to load my table: |$ find / -printf "%h|%f|%p|%l|%m|%M|%F|%y|%Y|%u|%g|%U|" \\\\ | -printf "%G|%s|%i|%AY-%Am-%Ad %AT|%CY-%Cm-%Cd %CT|" \\\\ | -printf "%TY-%Tm-%Td %TT|%D|\\ " 2>/dev/null | \\\\ | sed -e "s,\\\\.[0-9]\\\\{10\\\\},\\\\.0,g" > /tmp/dados.unl | my /tmp is a tmpfs split this file in 3 files (no reason, just for fun): |$ split -l 117000 dados.unl ---------------- B. Create a database with no log ---------------- C. Create the scripts (2 files: fs_full.sql , ext.sql) | $ cat fs_full.sql | drop table fs_full; | CREATE RAW TABLE fs_full | ( | diretorio NCHAR(300), | nome_arquivo NCHAR(100) not null , | path_nome_arquivo NCHAR(400), | link_destino NCHAR(400), | permissao_octal SMALLINT, | permissao_str NCHAR(10), | filesystem_armazenado NCHAR(10), | tipo1 NCHAR(1), | tipo2 NCHAR(1), | owner_user NCHAR(15) not null , | owner_group NCHAR(15) not null , | owner_uid INTEGER not null , | owner_gid INTEGER not null , | tamanho_bytes BIGINT, | inode BIGINT, | ultimo_acesso DATETIME YEAR TO FRACTION(3), | ultima_mod_status DATETIME YEAR TO FRACTION(3), | ultima_mod_dados DATETIME YEAR TO FRACTION(3), | device_number INTEGER | ) |extent size 160000 next size 10240 LOCK MODE ROW ; |$ cat ext.sql |drop table ex_fs_full; | |create external table ex_fs_full SAMEAS fs_full |USING ( | DATAFILES ( 'DISK:/tmp/xaa', | 'DISK:/tmp/xab', | 'DISK:/tmp/xac'), | FORMAT 'DELIMITED', | REJECTFILE '/tmp/dados.rej', | MAXERRORS 10, | EXPRESS |) | ---------------- D. Execute the scripts: $ cat fs_full.sql ext.sql | dbaccess myfs_db ---------------- E. Run the load, drop tables, create tables, run the load again: | $ time { echo "set explain on; insert into fs_full select * from ex_fs_full" | dbaccess myfs_db; } | .... | $ cat fs_full.sql ext.sql | dbaccess myfs_db | .... | $ time { echo "set explain on; insert into fs_full select * from ex_fs_full" | dbaccess myfs_db; } | Database selected. | Explain set. | 335799 row(s) inserted. | Database closed. | real 0m37.669s <<<<<<<<<<<<<<<<<<<<<<<<< | user 0m0.015s | sys 0m0.018s Sorry, I'm confuse my self with the times... this way the EXTERNAL TABLE is slower than HPL (37s x 33s) Monitoring my physical log , I got this picture : | IBM Informix Dynamic Server Version 11.50.UC6DE -- On-Line -- Up 00:26:23 -- 353916 Kbytes | Physical Logging | Buffer bufused bufsize numpages numwrits pages/io | P-1 0 64 114176 3576 31.93 | phybegin physize phypos phyused %used | 3:53 70000 27891 35295 50.42 | ---------------- F. If run with a checkpoint after the drop/create table, works fine: | $ cat fs_full.sql ext.sql | dbaccess myfs_db | .... | $ onmode -c | $ time { echo "set explain on; insert into fs_full select * from ex_fs_full" | dbaccess myfs_db; } | Database selected. | Explain set. | 335799 row(s) inserted. | Database closed. | real 0m22.533s <<<<<<<<<<<<<<<<<<<<<<<<<<<<< | user 0m0.019s | sys 0m0.030s | $ onmode -c I don't detect any Physical log usage . ---------------- G. Look the last 4 checkpoint stats (total pages): | $ onstat -g ckp | IBM Informix Dynamic Server Version 11.50.UC6DE -- On-Line -- Up 00:31:59 -- 353916 Kbytes | AUTO_CKPTS=Off RTO_SERVER_RESTART=Off | Critical Sections Physical Log Logical Log | Clock Total Flush Block # Ckpt Wait Long # Dirty Dskflu Total Avg Total Avg | Interval Time Trigger LSN Time Time Time Waits Time Time Time Buffers /Sec Pages /Sec Pages /Sec | 43720 22:27:56 *User 652:0x5cc018 0.0 0.0 0.0 1 0.0 0.0 0.0 11 11 138 1 53 0 | 43721 22:29:55 *Pload 652:0x5da018 0.1 0.0 0.0 1 0.0 0.1 0.1 8 8 76 0 14 0 | 43722 22:30:21 *Pload 652:0x5dd4d4 0.0 0.0 0.0 0 0.0 0.0 0.0 1 1 63 2 3 0 | 43723 22:30:37 *Pload 652:0x5f1018 0.1 0.0 0.0 1 0.0 0.1 0.1 8 8 76 4 20 1 | 43724 22:32:12 *Pload 652:0x61f018 0.2 0.0 0.0 1 0.0 0.2 0.2 12 12 135 1 46 0 | 43725 22:32:39 *Pload 652:0x6224d4 0.0 0.0 0.0 0 0.0 0.0 0.0 1 1 63 2 3 0 | 43726 22:37:00 *User 652:0x642018 0.0 0.0 0.0 1 0.0 0.0 0.0 9 9 120 0 32 0 | 43727 22:38:25 Plog 652:0x659018 0.2 0.1 0.0 1 0.0 0.1 0.1 51 51 52500 617 23 0 | 43728 22:39:15 Plog 652:0x672018 0.4 0.3 0.0 1 0.0 0.0 0.0 70 70 52500 1050 25 0 | 43729 22:41:04 Plog 652:0x696018 0.5 0.5 0.0 1 0.0 0.0 0.0 96 96 52500 481 36 0 | 43730 22:46:19 CKPTINTVL 652:0x69a018 0.5 0.2 0.0 0 0.0 0.0 0.0 32 32 648 2 4 0 |*43731 22:50:58 Plog 652:0x6b1018 0.3 0.1 0.0 1 0.0 0.1 0.1 51 51 *52500 188 23 0 |*43732 22:52:01 Plog 652:0x6ca018 0.4 0.3 0.0 1 0.0 0.0 0.0 70 70 *52500 833 25 0 |*43733 22:53:43 *User 652:0x6e3018 0.3 0.0 0.0 1 0.0 0.3 0.3 8 8 *533 5 25 0 |*43734 22:59:09 CKPTINTVL 652:0x6e7018 0.5 0.1 0.0 0 0.0 0.0 0.0 32 32 *64 0 4 0 | | Max Plog Max Llog Max Dskflush Avg Dskflush Avg Dirty Blocked | pages/sec pages/sec Time pages/sec pages/sec Time | 4008 200 0 27 0 0 | | Based on the current workload, the physical log might be too small | to accommodate the time it takes to flush the buffer pool during | checkpoint processing. The server might block transactions during checkpoints. | If the server blocks transactions, increase the physical log size to | at least 280560 KB. Just to know, The AUTO_CKPTS is active, this output is wrong. | $ onstat -c |grep ^AUTO_CKPT | AUTO_CKPTS 1 Regards César --- Em qui, 28/1/10, John Miller iii <miller3@us.ibm.c

↪ replying to Cesar Inacio Martins

John Miller iii — Fri, 29 Jan 2010 04:10:14 — source: IIUG Forums & Mailing Lists

It is actually a data recovery issue.If there has been no dropped tabl= es since the last checkpoint, then IDS does not need to protect the before image= s (i.e. no physical logging). If there has been a dropped table since the last checkpoint then the be= fore image MUST be saved. If you crash then IDS must put back the original images and create a consistent look at the checkpoint. Without these before images then there is no wa= y to put back the dropped table. I guess you can call it an optimistic drop table. Doing the checkpoint before doing a large load is not a new optimizatio= n in IDS, but it was first introduced in version 6.0. I would like to point out that if you are moving data from one IDS inst= ance to another IDS instance there is actually a format called "informix" that will keep the data in= native informix format reducing the conversion to and from acsii. This should actually improve performance when moving data between IDS systems. John F. Miller III STSM, Support Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 01/28/2010 05:28:43 PM: > Hi John, > > The problem is the missing checkpoint... I can't figure out why this,= for me > this appear be a bug. > > But you said this is an expected behave, can you explain? please... > > BTW, with checkpoint, HPL run in 33sec , EXTERNAL TABLE in 22 seconds= , very > good... not 3x time faster like the White paper on IIUG site, but I'm= very > happy with this results! :) > > HPL and EXTERNAL have similar configuration (express, load 3 files in= > parallel) > > Here is the answers for your questions ... > > ---------------- > > 1. I run in express mode > > 2. I forgot to force the checkpoint and this is the reason to use of = the > physical log, what for me isn't a expected behave. > > 3. Default value, 128KB > > ---------------- > > If you want to reproduce, check the steps bellow. > > Steps what I do: > > (running on Linux) > > ---------------- > > A. Create a file to load my table: > > |$ find / -printf "%h|%f|%p|%l|%m|%M|%F|%y|%Y|%u|%g|%U|" \\\\ > > | -printf "%G|%s|%i|%AY-%Am-%Ad %AT|%CY-%Cm-%Cd %CT|" \\\\ > > | -printf "%TY-%Tm-%Td %TT|%D|\\ " 2>/dev/null | \\\\ > > | sed -e "s,\\\\.[0-9]\\\\{10\\\\},\\\\.0,g" > /tmp/dados.unl > > | > > my /tmp is a tmpfs > > split this file in 3 files (no reason, just for fun): > > |$ split -l 117000 dados.unl > > ---------------- > > B. Create a database with no log > > ---------------- > > C. Create the scripts (2 files: fs_full.sql , ext.sql) > > | $ cat fs_full.sql > > | drop table fs_full; > > | CREATE RAW TABLE fs_full > > | ( > > | diretorio NCHAR(300), > > | nome_arquivo NCHAR(100) not null , > > | path_nome_arquivo NCHAR(400), > > | link_destino NCHAR(400), > > | permissao_octal SMALLINT, > > | permissao_str NCHAR(10), > > | filesystem_armazenado NCHAR(10), > > | tipo1 NCHAR(1), > > | tipo2 NCHAR(1), > > | owner_user NCHAR(15) not null , > > | owner_group NCHAR(15) not null , > > | owner_uid INTEGER not null , > > | owner_gid INTEGER not null , > > | tamanho_bytes BIGINT, > > | inode BIGINT, > > | ultimo_acesso DATETIME YEAR TO FRACTION(3), > > | ultima_mod_status DATETIME YEAR TO FRACTION(3), > > | ultima_mod_dados DATETIME YEAR TO FRACTION(3), > > | device_number INTEGER > > | ) > > |extent size 160000 next size 10240 LOCK MODE ROW ; > > |$ cat ext.sql > > |drop table ex_fs_full; > > | > > |create external table ex_fs_full SAMEAS fs_full > > |USING ( > > | DATAFILES ( 'DISK:/tmp/xaa', > > | 'DISK:/tmp/xab', > > | 'DISK:/tmp/xac'), > > | FORMAT 'DELIMITED', > > | REJECTFILE '/tmp/dados.rej', > > | MAXERRORS 10, > > | EXPRESS > > |) > > | > > ---------------- > > D. Execute the scripts: > > $ cat fs_full.sql ext.sql | dbaccess myfs_db> > ---------------- > > E. Run the load, drop tables, create tables, run the load again: > > | $ time { echo "set explain on; insert into fs_full select * from > ex_fs_full" > | dbaccess myfs_db; } > > | .... > > | $ cat fs_full.sql ext.sql | dbaccess myfs_db > > | .... > > | $ time { echo "set explain on; insert into fs_full select * from > ex_fs_full" > | dbaccess myfs_db; } > > | Database selected. > > | Explain set. > > | 335799 row(s) inserted. > > | Database closed. > > | real 0m37.669s <<<<<<<<<<<<<<<<<<<<<<<<< > > | user 0m0.015s > > | sys 0m0.018s > > Sorry, I'm confuse my self with the times... this way the EXTERNAL TA= BLE is > slower than HPL (37s x 33s) > > Monitoring my physical log , I got this picture : > > | IBM Informix Dynamic Server Version 11.50.UC6DE -- On-Line -- Up > 00:26:23 -- > 353916 Kbytes > > | Physical Logging > > | Buffer bufused bufsize numpages numwrits pages/io > > | P-1 0 64 114176 3576 31.93 > > | phybegin physize phypos phyused %used > > | 3:53 70000 27891 35295 50.42 > > | > > ---------------- > > F. If run with a checkpoint after the drop/create table, works fine: > > | $ cat fs_full.sql ext.sql | dbaccess myfs_db > > | .... > > | $ onmode -c > > | $ time { echo "set explain on; insert into fs_full select * from > ex_fs_full" > | dbaccess myfs_db; } > > | Database selected. > > | Explain set. > > | 335799 row(s) inserted. > > | Database closed. > > | > real 0m22.533s > <<<<<<<<<<<<<<<<<<<<<<<<<<<<< > > | user 0m0.019s > > | sys 0m0.030s > > | $ onmode -c > > I don't detect any Physical log usage . > > ---------------- > > G. Look the last 4 checkpoint stats (total pages): > > | $ onstat -g ckp > > | IBM Informix Dynamic Server Version 11.50.UC6DE -- On-Line -- Up > 00:31:59 -- > 353916 Kbytes > > | AUTO_CKPTS=3DOff RTO_SERVER_RESTART=3DOff > > | > Critical Sections Physical Log Logical Log > > | > Clock Total Flush Block # Ckpt > Wait Long # Dirty Dskflu Total Avg Total Avg > > | > Interval Time Trigger LSN Time Time Time > Waits Time Time Time Buffers /Sec Pages /Sec Pages /Sec > > | > 43720 22:27:56 *User 652:0x5cc018 0.0 0.0 0.0 > 1 0.0 0.0 0.0 11 11 138 1 53 0 > > | > 43721 22:29:55 *Pload 652:0x5da018 0.1 0.0 0.0 > 1 0.0 0.1 0.1 8 8 76 0 14 0 > > | > 43722 22:30:21 *Pload 652:0x5dd4d4 0.0 0.0 0.0 > 0 0.0 0.0 0.0 1 1 63 2 3 0 > > | > 43723 22:30:37 *Pload 652:0x5f1018 0.1 0.0 0.0 > 1 0.0 0.1 0.1 8 8 76 4 20 1 > > | > 43724 22:32:12 *Pload 652:0x61f018 0.2

↪ replying to John Miller iii

Cesar Inacio Martins — Fri, 29 Jan 2010 11:32:17 — source: IIUG Forums & Mailing Lists

Hum... yeah, a little obvious, but very tricky Thanks, this explain everything. Cesar --- Em sex, 29/1/10, John Miller iii <miller3@us.ibm.com> escreveu: De: John Miller iii <miller3@us.ibm.com> Assunto: Re: Data migration Brainstorm. [18832] Para: ids@iiug.org Data: Sexta-feira, 29 de Janeiro de 2010, 2:10 It is actually a data recovery issue.If there has been no dropped tabl= es since the last checkpoint, then IDS does not need to protect the before image= s (i.e. no physical logging). If there has been a dropped table since the last checkpoint then the be= fore image MUST be saved. If you crash then IDS must put back the original images and create a consistent look at the checkpoint. Without these before images then there is no wa= y to put back the dropped table. I guess you can call it an optimistic drop table. Doing the checkpoint before doing a large load is not a new optimizatio= n in IDS, but it was first introduced in version 6.0. I would like to point out that if you are moving data from one IDS inst= ance to another IDS instance there is actually a format called "informix" that will keep the data in= native informix format reducing the conversion to and from acsii. This should actually improve performance when moving data between IDS systems. John F. Miller III STSM, Support Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 01/28/2010 05:28:43 PM: > Hi John, > > The problem is the missing checkpoint... I can't figure out why this,= for me > this appear be a bug. > > But you said this is an expected behave, can you explain? please... > > BTW, with checkpoint, HPL run in 33sec , EXTERNAL TABLE in 22 seconds= , very > good... not 3x time faster like the White paper on IIUG site, but I'm= very > happy with this results! :) > > HPL and EXTERNAL have similar configuration (express, load 3 files in= > parallel) > > Here is the answers for your questions ... > > ---------------- > > 1. I run in express mode > > 2. I forgot to force the checkpoint and this is the reason to use of = the > physical log, what for me isn't a expected behave. > > 3. Default value, 128KB > > ---------------- > > If you want to reproduce, check the steps bellow. > > Steps what I do: > > (running on Linux) > > ---------------- > > A. Create a file to load my table: > > |$ find / -printf "%h|%f|%p|%l|%m|%M|%F|%y|%Y|%u|%g|%U|" \\\\ > > | -printf "%G|%s|%i|%AY-%Am-%Ad %AT|%CY-%Cm-%Cd %CT|" \\\\ > > | -printf "%TY-%Tm-%Td %TT|%D|\\ " 2>/dev/null | \\\\ > > | sed -e "s,\\\\.[0-9]\\\\{10\\\\},\\\\.0,g" > /tmp/dados.unl > > | > > my /tmp is a tmpfs > > split this file in 3 files (no reason, just for fun): > > |$ split -l 117000 dados.unl > > ---------------- > > B. Create a database with no log > > ---------------- > > C. Create the scripts (2 files: fs_full.sql , ext.sql) > > | $ cat fs_full.sql > > | drop table fs_full; > > | CREATE RAW TABLE fs_full > > | ( > > | diretorio NCHAR(300), > > | nome_arquivo NCHAR(100) not null , > > | path_nome_arquivo NCHAR(400), > > | link_destino NCHAR(400), > > | permissao_octal SMALLINT, > > | permissao_str NCHAR(10), > > | filesystem_armazenado NCHAR(10), > > | tipo1 NCHAR(1), > > | tipo2 NCHAR(1), > > | owner_user NCHAR(15) not null , > > | owner_group NCHAR(15) not null , > > | owner_uid INTEGER not null , > > | owner_gid INTEGER not null , > > | tamanho_bytes BIGINT, > > | inode BIGINT, > > | ultimo_acesso DATETIME YEAR TO FRACTION(3), > > | ultima_mod_status DATETIME YEAR TO FRACTION(3), > > | ultima_mod_dados DATETIME YEAR TO FRACTION(3), > > | device_number INTEGER > > | ) > > |extent size 160000 next size 10240 LOCK MODE ROW ; > > |$ cat ext.sql > > |drop table ex_fs_full; > > | > > |create external table ex_fs_full SAMEAS fs_full > > |USING ( > > | DATAFILES ( 'DISK:/tmp/xaa', > > | 'DISK:/tmp/xab', > > | 'DISK:/tmp/xac'), > > | FORMAT 'DELIMITED', > > | REJECTFILE '/tmp/dados.rej', > > | MAXERRORS 10, > > | EXPRESS > > |) > > | > > ---------------- > > D. Execute the scripts: > > $ cat fs_full.sql ext.sql | dbaccess myfs_db> > ---------------- > > E. Run the load, drop tables, create tables, run the load again: > > | $ time { echo "set explain on; insert into fs_full select * from > ex_fs_full" > | dbaccess myfs_db; } > > | .... > > | $ cat fs_full.sql ext.sql | dbaccess myfs_db > > | .... > > | $ time { echo "set explain on; insert into fs_full select * from > ex_fs_full" > | dbaccess myfs_db; } > > | Database selected. > > | Explain set. > > | 335799 row(s) inserted. > > | Database closed. > > | real 0m37.669s <<<<<<<<<<<<<<<<<<<<<<<<< > > | user 0m0.015s > > | sys 0m0.018s > > Sorry, I'm confuse my self with the times... this way the EXTERNAL TA= BLE is > slower than HPL (37s x 33s) > > Monitoring my physical log , I got this picture : > > | IBM Informix Dynamic Server Version 11.50.UC6DE -- On-Line -- Up > 00:26:23 -- > 353916 Kbytes > > | Physical Logging > > | Buffer bufused bufsize numpages numwrits pages/io > > | P-1 0 64 114176 3576 31.93 > > | phybegin physize phypos phyused %used > > | 3:53 70000 27891 35295 50.42 > > | > > ---------------- > > F. If run with a checkpoint after the drop/create table, works fine: > > | $ cat fs_full.sql ext.sql | dbaccess myfs_db > > | .... > > | $ onmode -c > > | $ time { echo "set explain on; insert into fs_full select * from > ex_fs_full" > | dbaccess myfs_db; } > > | Database selected. > > | Explain set. > > | 335799 row(s) inserted. > > | Database closed. > > | > real 0m22.533s > <<<<<<<<<<<<<<<<<<<<<<<<<<<<< > > | user 0m0.019s > > | sys 0m0.030s > > | $ onmode -c > > I don't detect any Physical log usage . > > ---------------- > > G. Look the last 4 checkpoint stats (total pages): > > | $ onstat -g ckp > > | IBM Informix Dynamic Server Version 11.50.UC6DE -- On-Line -- Up > 00:31:59 -- > 353916 Kbytes > > | AUTO_CKPTS=3DOff RTO_SERVER_RESTART=3DOff > > | > Critical Sections Physical Log Logical Log > > | > Clock Total Flush Block # Ckpt > Wait Long # Dirty Dskflu Total Avg Total Avg > > | > Interval Time Trigger LSN Time Time Time > Waits Time Time Time Buffers /Sec Pages /Sec Pages /Sec > > | > 43720 22:27:56 *User 652:0x5cc018 0.0 0.0 0.0 > 1 0.0 0.0 0.0 11 11 138 1 5

Related threads

Glossary

dbexport / dbimport