Moving Data from Informix SE 4 to Informix SE 7
Posted in 2004
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