Re: alter table wipes out systables.dirpath - SE5.0

<<I tried ALTER TABLE on the table whose database files are located in the directory other than $DBPATH/$DBASE_NAME. After the table had been altered, I could not access that table. After some investigation, I found that dirpath column in systables for that table has been initialized. Has anyone stumbled on this one?>> Yes. This problem has existed in the Standard Engine since the very first version (the ".03" version). It did this in 4.0 and 4.1 also. It is documented somewhere, either in the manual or in one of the release files that comes with SE - at least it was at some point. I remember reading it. I think it should keep the original directory. The reason why it changes it is because when you ALTER TABLE, it actually builds a new ISAM file for the table and it automatically puts this in the $DBPATH. So the old path isn't there anymore because the underlying ISAM file is deleted. Ideally, it should put the new files (.dat and .idx) back into the original directory specified. But it doesn't. I haven't checked in the new 7.1 version, but in .03 and 4.0, when you ALTER TABLE and the original table is in the $DBPATH directory, after the ALTER TABLE it puts the full path name of the ISAM file in dirpath. When you create a table, it does not put this full path name. This is a problem because it then doesn't allow you to move the entire database to another directory because there are full pathnames in dirpath. <<I have called Informix support. Let's see what they say. I am preparing for workaround.>> I wouldn't prepare for one. This problem has existed since I started using Informix SQL (around 1987 I think). The only workaround is to find out the filename of the ISAM files (.dat and .idx) and move them to the directory where you want them and update dirpath in systables manually.