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. You've not said what the target environment is, AFAICS. Is that AIX and PowerPC too, or something else? > Unfortunately 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. A large number of questions spring to mind, but the chances are that your answers will be favourable. First - do you have the schemas of the databases, separate from the system catalogs? Are you sure? If so, we may be able to cut out quite a lot of work... If not, life is going to be harder. Secondly - do you have the space to make backups of the tables, at least one at a time if not all together, so you can consider running bcheck on the files? Does bcheck agree that there are corruption problems? Can it fix them? Thirdly - from the schemas, do you have any FLOAT or SMALLFLOAT columns in your data? These are nnot stored in a portable format; all other types are stored in a portable format. If you are moving within the AIX systems, or if you don't have any FLOAT or SMALLFLOAT columns, then you should validate the data files (possibly by unloading them - but I'll come back to that in a moment), and you can then simply copy them to the new system. Well, nearly simply copy them to the new system. You would actually create an empty database using the schema that have from your answer to Q1. You would then copy corresponding data files from the old system over the new, empty data files. Straight, binary copy. Then you run bcheck to rebuild the indexes on those tables. This is likely to be the fastest way to do the transfer. The only downsides are (1) the data won't be reorganized and so (2) you may be wasting a bit of disk space for the deleted rows. Validating the data files. You can run 'bcheck -n' to validate the data and index files. You're really only concerned with the data files here. If they are OK, you can probably simply transfer them. If bcheck identifies corruption in the data files, you cannot just transfer them, but you are then into heavy duty forensic work. The IIUG software archive has a program isextract which can extract data from C-ISAM files as long as you know the schema of the files, or can determine the schema of the files. You could adapt it, with a good deal of care, to transcribe the data in reverse - to take in variable width data and create the binary record layout, and create the .dat file. This table could then be given indexes by the bcheck (secheck in SE 7.x) process outlined for transferring the data. This would get you over FLOAT and SMALLFLOAT hurdles; the intermediate load format would be transportable. Note that with care you could sort the data so that it is in index order for the most important index. You should also review all the indexes you have to see whether they are still beneficial. > 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 size of the problem depends on the speed of the new system, the speed of the old system (slow if it is circa 1990), and the speed of the network connecting them. I'm curious that you have a major problem with 2GB limits. The older SE 4.x had no clues about that; 7.25 does. The older tools have no clues about that limit either. But you must be sailing close to danger if the unload files are close to the 2GB limit. If your data can be transferred using the binary copy technique plus bcheck/secheck which I outlined, then you may be in with a chance, though a few hundred GB of data, necessarily in tables of less than 2 GB each, means that you've a few hundred large tables to transfer. Are you counting indexes in your few hundred GB? How fast is your new machine? How fast are the disk arrays? Are they using RAID 10 or RAID 5 or something else. If you've got a choice, RAID 10 is the choice to use -- see http://www.baarf.com/ or ask Art Kagel for a suitable rant on why to use RAID 10. > 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 -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

> You've not said what the target environment is, AFAICS. Is that AIX > and PowerPC too, or something else? The test environment is an IBM RS6000 F80 running AIX 4.x, the production environment is an RS6000 6F1 running the same AIX version. > First - do you have the schemas of the databases, separate from the > system catalogs? Are you sure? Yes, I can create a text file (using DBSCHEMA output) containing valid schemas for each table in every database I've tested (18DBs - 100s of tables). I even made a little C program can parse the schema text file and write the 4GL code to unload (or load) each table. Kind of a poor man's DBEXPORT I think-lol. > Secondly - do you have the space to make backups of the tables, at > least one at a time if not all together, so you can consider running > bcheck on the files? We are lucky enough to have a decent test environment using an older RS6000 backup server (specs. above). There's plenty of space to work/make back-ups. > Does bcheck agree that there are corruption > problems? Can it fix them? The previous administrator had bcheck -iY running as a cron job each night. We now run it only as needed when a table/index behaves weirdly (bad idea?). But no, it doesn't report any sys* table corruption. I'm still learning but I initially thought BCHECK dealt only with indexes (not data/system tables). I will read up more on BCHECK and do some more testing to look for any strange reports. > Thirdly - from the schemas, do you have any FLOAT or SMALLFLOAT > columns in your data? These are nnot stored in a portable format; all > other types are stored in a portable format. Nope, seems like using DECIMAL(x,x) is the preferred way to store real numbers there. I grepped the schema file and found no FLOAT or SMALLFLOATs > ...then you should validate the data files > (possibly by unloading them - but I'll come back to that in a moment), Validate by unloading them? I guess you mean just to make sure it doesn't bomb out and has the correct number of rows when finished? > > The IIUG software archive has a program isextract which can extract > data from C-ISAM files as long as you know the schema of the files, or > can determine the schema of the files. You could adapt it, with a > good deal of care, to transcribe the data in reverse - to take in > variable width data and create the binary record layout, and create > the .dat file. This table could then be given indexes by the bcheck > (secheck in SE 7.x) process outlined for transferring the data. This > would get you over FLOAT and SMALLFLOAT hurdles; the intermediate load > format would be transportable. Note that with care you could sort the > data so that it is in index order for the most important index. You > should also review all the indexes you have to see whether they are > still beneficial. I may end up needing to explore this low-level solution, thanks. The *.dat files are essentially just big old ASCII files, maybe that's why this problem seems extra frustrating! > I'm curious that you have a major > problem with 2GB limits. I noticed that UNLOAD would complete on a large table (say four million records),and the unload file would contain only about 150,000 records. I finally realized it wasn't a record limitation, it was a size thing and each of the files was just a little over 2GB. I need to investigate whether it's an UNLOAD limitation or an AIX limitation. Either way (although an extra pain-in-the-neck) as you mentioned above, this can be circumvented by copying the table in smaller sections. > If your data can be transferred using the binary > copy technique plus bcheck/secheck which I outlined, then you may be > in with a chance, though a few hundred GB of data, necessarily in > tables of less than 2 GB each, means that you've a few hundred large > tables to transfer. Are you counting indexes in your few hundred GB? Yes, the 200ish GB included the *.idx files and the *.dat files. And that's a rough approximiation, probably a little on the high end. I'll get an extra size measurement. > How fast is your new machine? How fast are the disk arrays? Are they > using RAID 10 or RAID 5 or something else. If you've got a choice, > RAID 10 is the choice to use -- see http://www.baarf.com/ or ask Art > Kagel for a suitable rant on why to use RAID 10. I'm not a IBM mid-range man, but every consultant who's been through the door has said it's a nice machine; RS6000 6F1 with a couple GB of RAM and lots of disk space. Thanks for the comments man, I appreciate them.