Re: C-ISAM - SE Composite index Problems

Kate, Your problems illustrate exactly the point I am trying to make! If you don't understand about the differences in the index structures, you are in no position to second-guess how to create the C-ISAM files so that they can be used by Informix-SE too. There are many perfectly valid C-ISAM file structures which cannot be used by Informix-SE because SE expects to create the C-ISAM files it uses, and it imposes a number of disciplines on the contents of the index files which are not just consequences of using C-ISAM. Believe me; if you do not create the C-ISAM file in the same way as SE thinks it created the file, then simple queries which do not use indexes (sequential scans) will work correctly, but complex queries which use indexes will fail, because the index information that SE thinks it put into the C-ISAM files is not the index information that is actually present. So, unless you know how to make the C-ISAM file identical to what SE would create, you must not create the C-ISAM files from C-ISAM; you must create them, and their indexes, using SE. Then you write the C-ISAM code to use the indexes etc provided by SE. And you don't go playing with the indexing unless your C-ISAM code adapts to differences in the indexing. Integrating SE and C-ISAM is not 100% trivial; there are rules you need to obey, and one of those rules, the critical one, is that you let SE create the C-ISAM files. Another rule is that you don't place indexes on partial columns. Another rule is that you don't use the fact that col04 is CHAR(10) at positions 21-30 and col05 is CHAR(10) at positions 31-40 to create a composite index which refers to a single key component on columns 21-40; SE would use two components, so you had better do so too. There are probably some others, but I haven't done this sort of thing for about 8 years, so I'm a trifle rusty on the details. You should only break these rules when you know enough to know the consequences, and how to avoid the problems. Yes, the rules can be broken and both the C-ISAM application and SE can work with the files, even though they violate the rules I just articulated, but you have to know a lot to avoid the problems you describe. You also need a powerful, over-riding reason to do it. And you wouldn't then be asking questions about why it doesn't work! Yours pointedly, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> >From: kcj@netcom.com (Kate Juliff) >Date: Fri, 27 Oct 1995 00:52:39 GMT >X-Informix-List-Id: <news.18299> > >In article <46m280$177@cssun.mathcs.emory.edu> johnl@informix.com (Jonathan Leffler) writes: >>You need to analyse the '.idx' files of an SE-built C-ISAM file because >>they are not the same as you would normally build with C-ISAM. I attach >>(old) C-ISAM code which analyses the indexes -- no guarantees that it works >>8 or so years after it was written. Anyway, index 0 is different in SE >>from that you'd expect if using pure C-ISAM. >> >>The best way to handle interworking C-ISAM and SE is to have SE create the >>C-ISAM files and to have the C-ISAM application access exactly the files >>provided by SE. It is the '.idx' files which differ; the '.dat' contents >>are the same. > >I am also trying to use C-Isam files and I can't really understand about >the index difference. I defined my db tables to mirror the C-Isam file >descriptions and then copied the C-Isam files on top of the db tables >(both .dat and idx). I can access the data in these tables, as long as >queries are simple. But as soon as I have a complex query the whole thing >gives up and I get a "network receive failed" message. What I did to >overcome this was >1: tried experimenting with the column definitions for numerics... trying >small integers, integers and messing around a fair bit, as I believed the >problem was one of incorrect data typing. But this didn't work so I >ended up using >2: unloading from the db I'd copied the C-Isam files into, to a "clean" >db, defined identially as the one i had originally set up. This works of >course but it's slow. It takes me two hours to do this copy, unload, load >operation. > >I'd like to hear from any one else who has had to use C-Isam files in an >SE db. > >But surely if the index files are different my method of copying would >not work???? > >--Kate