Re: Informix file to ASCII file??

>From: Dave <totalcell@mountk.conknet.com> >Date: Wed, 15 Jan 1997 21:24:58 -0500 >X-Informix-List-Id: <news.32619> > >our software vendor went under leaving us with 8 years of records on an informix based >system. The informix software is a ver4.1(runtime)Cisam. We need to convert the >tables/files with *.dat & *.idx to some type of ASCII file so we can use it with new >vendor. There appears to be several command line utilities like load and unload. Will >any of these work?? Hi, I've seen answers from Daniel Wright and Matt Brickley (below), but I don't think they necessarily addressed your problem accurately. You say that the software uses C-ISAM, rather than SE. If your system was really using SE, then Daniel and Matt have given perfectly good answers and you can immediately ignore the rest of this message as it will only confuse things unnecessarily. You're still reading, so maybe your system really did use C-ISAM rather than SE. This is apt to lead to problems, because although all the .dat and .idx files in an SE database are also C-ISAM files, not all C-ISAM files can be treated as tables in an SE database. In fact, unless SE-compatability was an issue for the developer, the C-ISAM files are probably not compatible with SE. Therefore, approaches based on using SE won't work immediately. Nevertheless, I am going to assume that SE is available to you, as it will make life easier. The object of the exercise is to make the C-ISAM data accessible to SE, so that you can then use the solutions mentioned by Matt and Daniel, or other equivalent tools such as dbexport. If you haven't already done so, you should investigate whether there is any way of getting hold of the source code for your application from the defunct company, its officers, its creditors, or whatever. If they only just went bust, you might stand a chance; if they went bust several years ago, you're probably out of luck. And you might want to arrange for code escrow with your new vendor (so that they deposit the software in some neutral place so that it will be available to you if they subsequently go bust). It will probably make life a lot easier for you. OK, how do we deal with the problem? First of all, do you have a schema for the C-ISAM files? That is, did your vendor supply you with the detailed layouts of the files? If so, your life will be easier. If not, then we have real problems (see next paragraph). Assuming that you do have the schema, I think the approach I would use is: * Create a set of SE tables to match the C-ISAM schemas * Check that the descriptions in the SE index files match the C-ISAM index files. Use bcheck (or secheck), and you are primarily concerned that the row sizes are correct, and that the columns are in the correct locations. Expect to see differences in the sequences of indexes, and I think you can expect one more index entry in the SE version (my memory is rusty). * When you've got the two schemas matching, copy the C-ISAM .dat files over the equivalent SE .dat files. * Run bcheck to fix the discrepancies in the data (number of rows in the table, etc). * With a modicum of luck, you can now read the data files using SE tools. What could go wrong? Your schema might not be correct (it changed after they gave the description to you). You may not be able to get the right types. Etc. You'll have to deal with these as if you didn't have the correct schema (because you don't have the correct schema). The other problem that you might run into is that C-ISAM doesn't enforce type integrity on the data not in the keys. This means that unrepentant Cobol programmers might have decided to use variant data layouts for the non-key information in a C-ISAM file, and C-ISAM will be quite happy, and their C-ISAM program will be quite happy, but SE will not. This would manifest itself as garbage in some but not all rows of the table, and is going to be a real pig to resolve. Also, they might just have made use of the variable length records that became available in C-ISAM 4.0 (in compliance with X/Open standard), and the variable portion of the data for those records is buried in the index file. Ugh! So, you don't have the correct schema. Well, you're going to have to work out what the correct schema is. 'Tain't easy, but can usually be done. You need to know a bit about the structure of C-ISAM files, and life will be much easier if you get yourself the C-ISAM manual because lots of information is in there which you will need (I would need). Each record is a fixed length. Each record is delimited by either a '\\n' (newline) character or a '\\0' (nul) character. A newline marks a current record; a nul marks a deleted record which is available for re-use. You can use bcheck to tell you what the record size is, and you can verify this by inspecting the data files and checking that the record boundaries are marked correctly. You should also make notes on the index structures, using the information from the manual to work out what the index structures mean. You now have to guess which columns are where in the data, and what the types of those columns are. It helps if you have some idea about which data is in which tables. Although the C-ISAM manuals encourage programmers to use a machine-independent data ordering, programmers could have been lazy and not used the correct techniques (though it is pretty unlikely). Character fields are fairly easy to identify. Integer fields can be 2 or 4 bytes long, and float fields can be 4 or 8 bytes (C float or double). Most probably they did not use the DECIMAL type, but that cannot be ruled out either. Dates might be stored as character strings (probably with a year 2000 problem), or as integers. Once you've worked out what the schema is by gazing intently at dumps of the C-ISAM data files, you can use the techniques of the previous paragraph to see whether you are correct. If you still haven't got things sorted out, then you're likely to have either variant records or variable length records. To handle these, you will probably have to write some C-ISAM code to read the records, and print the data correctly using the discriminating value to decide how to print out the correct variant, or simply to retrieve the variable portion of the data from the index file. Non-trivial, but probably not impossible. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> >From: Daniel Wright <danwright@bigfoot.com> >Date: Wed, 15 Jan 1997 23:00:43 -0600 >X-Informix-List-Id: <news.32626> > >Dave wrote: >>[...] > >Sounds like a Standard Engine database since there are actual files that >you can see in the file system. >It doesn't really matter though, you need to go into "isql", if you have >this - it was not supplied under all versions of SE (std. engine) and >execute UNLOAD TO "filename" SELECT * FROM <table-name>. > >This will leave you with an ASCII file delimited with pipes. (one line >per record, each field separated by pipes.) > >I am unaware of any command line utilities that do this (that doesn't >mean they don't exist, though). > >If "isql" is not available to you,