Re: dbimport feature/bug on 9.40HC3

WRONG. That release of dbimport sets extent sizes according to the number of rows and the row size. And if the row includes VARCHAR then the row-size used is incorrect. I know this only too well as I had a 1Gb database need 8Gb after migration from 9.2 to 9.4. I did many tests on Linux and AIX and came up with the same situation. I believe the reason for this was to avoid the situation where a table has a serial field. As indexes are now detached when you create a new table that has a serial field it always interleaves table and index resulting in tables with 100's of extents. The only solution is to do the dbimport with -ss (if extents sizes were ever set) or to edit the dbname.sql file and put in the correct values. I do agree that extent size doubling will have an effect but in that case you would see the extent size as powers of 2 and the numbers Neil is seeing are not. You would also see that there were multiple extents after the dbimport but these tables only have one extent. So in my humble opinion, and as somebody who has worked with this for 18 years this is in fact a BUG and a very serious one. And it is even acknowledged to be one by IBM after a case which I pursued. Unfortunately I don't have access to the BUG database but I can tell you that I pursued this poblem with Sucwinder Bassi who works for IBM tech support UK under case number 400311. It took me some time to persuade them that this was a bug but they did ackowledge it as such. regards Malcolm

I shared this problem with Informix Tech Support and could not get them to acknowledge the bug, so you've gotten farther than I. What I did was create the stores7 database (dbaccessdemo) in IDS 7.31, then exported it without the -ss, then imported into IDS 9.40.xC3. I used Solaris, AIX, and Windows. Oddly, Solaris took the most space. However, the database took about four times the space. The sysprocedures table took eight times the space, alone. Next, I tried creating the database in IDS 9.4 with the dbaccessdemo7 program. It took more space than on IDS 7, but not nearly as much as the dbimport. Tech Support told me this was expected behaviour. No, I do not believe that, and, no, I could not get past this response. As Neil has noted, the space wastage comes from widely mis-calculating extent sizes. This might be acceptable if I am importing a production database that is going to grow, anyway, but a lot of the imports, especially those without the dbexport -ss, are for test databases that are never going to grow. Now, some people have recommended estimating the extent size and putting those in the file. One suggestion was to estimate based on row size from the SQL file.What we discovered was that this number was consistently lower, by an appreciable margin. There always seems to be more overhead than we calculated. Part of the problem is the indexes, especially those for constraints not built upon named indexes. Sincerely, Christopher Coleman Database Analyst Medication Management Mediware Information Systems, Inc.

I shared this problem with Informix Tech Support and could not get them to acknowledge the bug, so you've gotten farther than I. What I did was create the stores7 database (dbaccessdemo) in IDS 7.31, then exported it without the -ss, then imported into IDS 9.40.xC3. I used Solaris, AIX, and Windows. Oddly, Solaris took the most space. However, the database took about four times the space. The sysprocedures table took eight times the space, alone. Next, I tried creating the database in IDS 9.4 with the dbaccessdemo7 program. It took more space than on IDS 7, but not nearly as much as the dbimport. Tech Support told me this was expected behaviour. No, I do not believe that, and, no, I could not get past this response. As Neil has noted, the space wastage comes from widely mis-calculating extent sizes. This might be acceptable if I am importing a production database that is going to grow, anyway, but a lot of the imports, especially those without the dbexport -ss, are for test databases that are never going to grow. Now, some people have recommended estimating the extent size and putting those in the file. One suggestion was to estimate based on row size from the SQL file.What we discovered was that this number was consistently lower, by an appreciable margin. There always seems to be more overhead than we calculated. Part of the problem is the indexes, especially those for constraints not built upon named indexes. Sincerely, Christopher Coleman Database Analyst Medication Management Mediware Information Systems, Inc.