Re: Moving Data from Informix SE 4 to Informix SE 7
"DBEXPORT bombs out on certain databases because of corrupted system > > tables and/or bad data." I have found that nearly all instances of DBEXPORT "bombing" are due to invalid data in date fields, especially when the data originated in 4.x or earlier versions of the database. The best way around the problem is by first finding the offending rows with a where clause that includes <datefieldname> not between "01011800" and "12312099" and then updating or deleting such rows with the same where clause. If you delete such rows, or at least update the offending date fields to some unique valid date that you can later identify, your export should at least complete. John Fahey ----- Original Message ----- From: "scottishpoet" <dryburghj@yahoo.com> To: <informix-list@iiug.org> Sent: Tuesday, August 31, 2004 8:13 PM Subject: [iiug] Re: Moving Data from Informix SE 4 to Informix SE 7 > nlaods?? no thts not a special utility sorry its another typo > > wolphie@hotmail.com (Smitty) wrote in message news:<4f39af7f.0408301540.5de8ce63@posting.google.com>... > > I'm a programmer/analyst at a medical services company. At the core > > of our system is an Informix SE 4.x database - (c)1990, with all > > business software written in-house using Informix 4GL and FourGen (a > > CASE tool also from the early 90s). Everything runs from an RS/6000 > > server (AIX 4.x), accessed by the users using TELNET software on their > > Windows PCs (everything in text-mode only). Approx. 200 users > > scattered throughout 24 offices in several states are logged in at any > > given time. > > > > In January they finally decided to upgrade the old Informix database. > > The primary reason: to get ODBC connectivity to Windows, thus allowing > > the creation of Windows front-ends for the screens and reports. > > Informix SE 4 is so old that it is seemingly impossible to find any > > Windows connectivity solution to work with it. So they purchased > > Informix SE 7.x and licenses and were ready to go. > > > > Unfortunatelly the company who sold us the new Informix product (and > > is helping us with the migration to SE7) tell us that some of our > > databases/tables are corrupted and the move is extremely difficult. > > DBEXPORT bombs out on certain databases because of corrupted system > > tables and/or bad data. It's going to be a lot more hours and money > > "if" they can help us. Although we realized the system hadn't been > > properly maintained (under the previous IT manager), we didn't foresee > > the severity of this problem. Everything (tables and database > > functions) seems to behave normally during daily use (postings, data > > entry, etc.) and has for years. > > > > So bottom line: 14 years (a few hundred GBs) of our data are stuck in > > Informix SE4 and moving everything to SE7 seems really difficult and > > expensive. I'm not a DBA, mostly an Windows application programmer, > > but in my naievity I thought I could use DBSCHEMA to create empty > > shells of our tables in SE7, then UNLOAD each SE4 table to a delimited > > file, then LOAD those files into SE7. This worked in theory, but I > > ran into several difficulties: a file-size limit (2GB?) when using > > UNLOAD, and the sheer time the process took (10-12 hours to LOAD large > > tables). Plus the company is constantly modifying data throughout the > > week, so after testing is complete, the final migration would have to > > all be completed over a weekend, with everyone ready to roll in SE7 > > first thing on a Monday morning. My UNLOAD/LOAD method seems too slow > > to unload/copy/load everything within 48 hours. > > > > The company we're working with seems to know what they're doing (we're > > still waiting word on whether they can help us and if so how much > > extra time/expense it will involve). It's just so frustrating to be > > so stuck on something that seems (maybe deceptively) simple. I can > > get the data out and load it, but it's just SO SLOW! If anyone has > > experience with such a problem, or perhaps sees something obvious I'm > > missing, please let me know. Although we are working with a seemingly > > competent partner, it would be negligent not to keep our eyes open to > > every option. Thanks... Joe sending to informix-list
--> > > first thing on a Monday morning. My UNLOAD/LOAD method seems too slow
--> > > to unload/copy/load everything within 48 hours.
i read you used dbschema.... please get rid of the indexes before you load it!!
and of course do not log when loading!!! and make sure auditing for tables
is not switched on!!!
The difference in performance can be a factor 8 or more!!!!!
Also when dbexport is used (finally.... when all is ok) then make damn sure
you put the schema on disk; this version may contain a bug which makes
dbimport create the table and all indexes first after that it loads the data;
check the sql file if it has
create table ....(
);
create index.....
*** load table ***
Then move the line with *** load table ***
to just after the create table statement...
Ahum make a copy first in case you muck up.
The difference in performance can be a factor 8 or more!!!!!
ONE More thing dbexport to tape can not have a bigger size then 2GB
so i guess you will need a whole bunch of tapes of 2 GB.....
if you have the luxury of putting it all on disk then this is the way to go...
you should not hit any 2GB limit since a table in SE4 can not be bigger then
2GB.
See you
Superboer.
"John Fahey" <jfahey@mandm.net> wrote in message news:<ch3cum$moc$1@news.xmission.com>...
> "DBEXPORT bombs out on certain databases because of corrupted system
> > > tables and/or bad data."
>
> I have found that nearly all instances of DBEXPORT "bombing" are due to
> invalid
> data in date fields, especially when the data originated in 4.x or earlier
> versions of the database.
> The best way around the problem is by
> first finding the offending rows with a where clause that includes
>
> <datefieldname> not between "01011800" and "12312099"
>
> and then updating or deleting such rows with the same where clause.
> If you delete such rows, or at least update the offending date fields to
> some unique
> valid date that you can later identify, your export should at least
> complete.
>
> John Fahey
> ----- Original Message -----
> From: "scottishpoet" <dryburghj@yahoo.com>
> To: <informix-list@iiug.org>
> Sent: Tuesday, August 31, 2004 8:13 PM
> Subject: [iiug] Re: Moving Data from Informix SE 4 to Informix SE 7
>
>
> > nlaods?? no thts not a special utility sorry its another typo
> >
> > wolphie@hotmail.com (Smitty) wrote in message
> news:<4f39af7f.0408301540.5de8ce63@posting.google.com>...
> > > I'm a programmer/analyst at a medical services company. At the core
> > > of our system is an Informix SE 4.x database - (c)1990, with all
> > > business software written in-house using Informix 4GL and FourGen (a
> > > CASE tool also from the early 90s). Everything runs from an RS/6000
> > > server (AIX 4.x), accessed by the users using TELNET software on their
> > > Windows PCs (everything in text-mode only). Approx. 200 users
> > > scattered throughout 24 offices in several states are logged in at any
> > > given time.
> > >
> > > In January they finally decided to upgrade the old Informix database.
> > > The primary reason: to get ODBC connectivity to Windows, thus allowing
> > > the creation of Windows front-ends for the screens and reports.
> > > Informix SE 4 is so old that it is seemingly impossible to find any
> > > Windows connectivity solution to work with it. So they purchased
> > > Informix SE 7.x and licenses and were ready to go.
> > >
> > > Unfortunatelly the company who sold us the new Informix product (and
> > > is helping us with the migration to SE7) tell us that some of our
> > > databases/tables are corrupted and the move is extremely difficult.
> > > DBEXPORT bombs out on certain databases because of corrupted system
> > > tables and/or bad data. It's going to be a lot more hours and money
> > > "if" they can help us. Although we realized the system hadn't been
> > > properly maintained (under the previous IT manager), we didn't foresee
> > > the severity of this problem. Everything (tables and database
> > > functions) seems to behave normally during daily use (postings, data
> > > entry, etc.) and has for years.
> > >
> > > So bottom line: 14 years (a few hundred GBs) of our data are stuck in
> > > Informix SE4 and moving everything to SE7 seems really difficult and
> > > expensive. I'm not a DBA, mostly an Windows application programmer,
> > > but in my naievity I thought I could use DBSCHEMA to create empty
> > > shells of our tables in SE7, then UNLOAD each SE4 table to a delimited
> > > file, then LOAD those files into SE7. This worked in theory, but I
> > > ran into several difficulties: a file-size limit (2GB?) when using
> > > UNLOAD, and the sheer time the process took (10-12 hours to LOAD large
> > > tables). Plus the company is constantly modifying data throughout the
> > > week, so after testing is complete, the final migration would have to
> > > all be completed over a weekend, with everyone ready to roll in SE7
> > > first thing on a Monday morning. My UNLOAD/LOAD method seems too slow
> > > to unload/copy/load everything within 48 hours.
> > >
> > > The company we're working with seems to know what they're doing (we're
> > > still waiting word on whether they can help us and if so how much
> > > extra time/expense it will involve). It's just so frustrating to be
> > > so stuck on something that seems (maybe deceptively) simple. I can
> > > get the data out and load it, but it's just SO SLOW! If anyone has
> > > experience with such a problem, or perhaps sees something obvious I'm
> > > missing, please let me know. Although we are working with a seemingly
> > > competent partner, it would be negligent not to keep our eyes open to
> > > every option. Thanks... Joe
>
>
> sending to informix-list