RE: Database Storage Difference between IDS 7.31 and IDS 9.40

If the indices in 7.x are not detached then you will need quite a bit More space for 9.x as the indices will detach. You need to allow for the index extents, the size is something like (keysize + 9 /rowsize) * table_extent_size Paul Watson VIS iTV Ltd Tel: 01382 341074 Fax: 01382 341045 Mob: 07818 003457 -----Original Message----- From: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org] On Behalf Of Christopher Sent: 28 September 2004 23:05 To: informix-list@iiug.org Subject: Database Storage Difference between IDS 7.31 and IDS 9.40 I would love an answer, but I am mainly sharing information. I have a database of about 3.5GB which I exported (without the -ss, as this is test) from IDS 7 and imported into IDS 9.4. This is something we have to do for a lot of our customers who are forced to change boxes in order to have a system which will run 9.4. I allocated 120% of the space, assuming this was a big enough buffer. When it failed, I allocated another chunk and completed the import. The space usage for the same database on two systems was rather frightening: Space required on IDS 7.31: 1769542 pages or 3,539,084kb Space required on IDS 9.40: 3282799 pages or 6,565,598kb That is 1.86% of the storage space, some of which constitutes extensions to the dbspace overhead. With oncheck -pe, I found most of the increase is in the system catalogs related to SPL procedures, of which we have 100, some of which are quite lengthy. There also seems to be some increase in index usage. I did a similar test with the stores7 database, and found the following data: Total Database Size on IDS 7.31.UD7: 7,680 kb Total Database Size on IDS 9.40.UC3: 27,632 kb After reviewing the always fascinating IBM Informix Migration Guide, I found this enlightening passage: <In some cases, even if the database server conversion is successful, internal conversion of some databases might fail because of insufficient space for system catalog tables. For more information, see your release notes, as "Additional Documentation" on page 20 of the Introduction indicates.> The release notes are no help. The Migration Guide has more information, plus calculations and SQL statements for determining available space, along with this caveat: "The dbspace estimates could be higher if you have an unusually large number of SPL routines or indexes in the database." We have 540 indexes and 100 SPL procedures. I can accept that we are going to need additional space, but we have to have some means of estimating the difference. We have a couple hundred customers to convert from 7.31 to 9.4, either by dbexport/dbimport or by direct conversion, and we have to have some idea how much space we are going to need. I have opened case 413775 with support, though I do not know if they will have an answer. I thought this would be worth sharing, though, since I started by searching this list to see if anyone else had experienced such discrepancies. Sincerely, Christopher Coleman Database Analyst Pharmacy Division Mediware Information Systems, Inc. tel: 913/307-1073 sending to informix-list