>Date: Wed, 15 Jan 1997 21:24:58 -0500
>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??
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
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
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
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.
>Date: Wed, 15 Jan 1997 23:00:43 -0600
>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, I have a wonderful utility called
>"olsql" (unfortunately it's on 1/4" tape, so I can't get to it easily,
>but I think Informix was the supplier of the original C-code (of which I
>do have the source, on the same tape...it has compiled under both
>NCR-unix and HP-UX, and since portability was a major concern, I would
>be very interested in why it might not compile under any other
>C-compiler (even a DOS one))).
>With this, you could do something similar and parse the resulting file
>with a customized C program (or if you're really good at sed, I suppose
>that's another possibility).
>Date: Thu, 16 Jan 1997 08:11:41 -0800
>Yep, at least in SE 5.0. I assume they are the same in your version.