Re: Database Storage Difference between IDS 7.31 and IDS 9.40

Indexes are deteched in 9, while they are attached in 7 - which means that if your non fragmented table has n indexes, you end up with n+1 partition pages and n+1 initial extents, which leads to the sort of space consumption that you have seen (YMMV: depends on how many indexes per table, table size, key lengths, index page fullness, etc - there's no definite formula to calculate the space requirement increase). This has also implications on memory usage, specifically rsam pool. If you want to revert to a 7 like behaiviour, DEFAULT_ATTACH is your friend, or plan your extent sizes with care Also bear in mind that object a user name sizes have increased from 18/8 chars to 128/32, which again has memory and system table size implications. 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 > > 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 > -- Ciao, Marco ______________________________________________________________________________ Marco Greco /UK /IBM Standard disclaimers apply! Informix faq http://www.iiug.org/resources/faq/ifaq.htm 4glworks http://www.4glworks.com Informix on Linux http://www.4glworks.com/ifmxlinux.htm sending to informix-list