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

On 28 Sep 2004 15:05:15 -0700, christopher.coleman@mediware.com (Christopher) wrote: >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 > . . . clipped for brevity . . . Were your indices created at detached? If so, then that would explain a bit of what you've experienced. If your first extent sizing includes index pages in 7.31 and your indices are created as detached in 9.40, then your first extent is now mostly empty and your indices are stored separately. JWC

Responding to John Carlson, Marco Greco, and others who have responded via e-mail, some of the space is taken up by now-detached indexes. Frankly, it is not as much as I would have guessed. Paul Watson sent this formula for calculating index extents: (keysize + 9 /rowsize) * table_extent_size That is helpful, but I've been going over various onchecks on the stores7 databases, and indexes are not the answer. The biggest change is in sysprocedures, which matches what I saw in the larger test database. The original has one extent, the exported/imported database has 8 extents. I went from one data page to forty-four, from one index page to twelve, not including the newly detached indexes. Looking at the first non-system-catalog table, customer, I found the expected results with the lack of index pages, the indexes now being in separate extents; the biggest difference, though, comes from significantly larger system catalog tables, especially those related to procedures. Sincerely, Christopher Coleman Acting President Kansas City Informix Users Group www.iiug.org/kciug Database Analyst Pharmacy Division Mediware Information Systems, Inc. tel: 913/307-1073