Re: Moving Data from Informix SE 4 to Informix SE 7
Smitty wrote: > 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 Try loading data into tables without indexes and create indexes after the load has been completed. If the unload is over 2 GB, try to split it let's say by date or region (if applicable). You might be able to use this technique when performing unload/load for "go live", for example move data from year before 2004 earlier and only 2004 on the weekend. Likely IDS would be a better choice than SE for that volume of data and Workgroup Edition is not that much more expensive either. HTH Michael
> Try loading data into tables without indexes and create indexes after > the load has been completed. > If the unload is over 2 GB, try to split it let's say by date or > region (if applicable). > You might be able to use this technique when performing unload/load > for "go live", for example move data from year before 2004 earlier > and only 2004 on the weekend. > Likely IDS would be a better choice than SE for that volume of data and > Workgroup Edition is not that much more expensive either. > I agree, unloading the data using year/office/something to divide it into managable chunks will probably fix that part of the problem. A small hassle, but no big deal, good idea. Thanks!
> Try loading data into tables without indexes and create indexes after > the load has been completed. Another good tip (wish I thought of this - duh!). When it was going to take 10 hours to load a table, should've been a clue. > Likely IDS would be a better choice than SE for that volume of data and > Workgroup Edition is not that much more expensive either. That seems to be the concensus. We are just grateful for the baby-steps of leaving SE4 behind (and getting ODBC drivers!) at this point. But if it could be done over, that would likely be the better choice.