dbimport feature/bug on 9.40HC3

This problem has been driving me mad for days! I am doing a dbimport. dbimport works differently from 9.40HC3 to HC4. Basically, HC3 for some tables uses far more disk space to store the data. This can be seen from the onchecks below. The 9.40HC3 instance is making a wild over-estimate of the necessary first extent size. Although I truncated the dbimport after a few seconds in both cases for the purposes of retrieving the onchecks for this posting, you can see that the HC3 instance has allocated triple the size for the first extent than HC4. In fact, the HC4 estimate - 215k pages - is a slight under-estimate for the 1.52 million rows, which actually take 250k pages, so in this case HC3 wastes 1GByte in storing 0.5GByte of data! 9.40 HC3 TBLspace Report for cats_small:informix.productsales Physical Address 1:113708 Creation date 05/02/2005 21:56:35 TBLspace Flags 801 Page Locking TBLspace use 4 bit bit-maps Maximum row size 26 Number of special columns 0 Number of keys 0 Number of extents 1 Current serial value 1 First extent size 788896 Next extent size 78889 Number of pages allocated 788896 Number of pages used 60 Number of data pages 59 Number of rows 3925 Partition partnum 1048856 Partition lockid 1048856 9.40HC4 TBLspace Report for cats_small:informix.productsales Physical Address 1:113708 Creation date 05/02/2005 22:04:40 TBLspace Flags 801 Page Locking TBLspace use 4 bit bit-maps Maximum row size 26 Number of special columns 0 Number of keys 0 Number of extents 1 Current serial value 1 First extent size 215908 Next extent size 21590 Number of pages allocated 215908 Number of pages used 95 Number of data pages 94 Number of rows 6280 Partition partnum 1048856 Partition lockid 1048856

could well be a bug set the extent size in the .sql file before running the dbimport, does that help? Neil Truby wrote: > This problem has been driving me mad for days! > > I am doing a dbimport. dbimport works differently from 9.40HC3 to HC4. > Basically, HC3 for some tables uses far more disk space to store the data. > This can be seen from the onchecks below. > > The 9.40HC3 instance is making a wild over-estimate of the necessary first > extent size. Although I truncated the dbimport after a few seconds in both > cases for the purposes of retrieving the onchecks for this posting, you can > see that the HC3 instance has allocated triple the size for the first extent > than HC4. In fact, the HC4 estimate - 215k pages - is a slight > under-estimate for the 1.52 million rows, which actually take 250k pages, so > in this case HC3 wastes 1GByte in storing 0.5GByte of data! > > 9.40 HC3 > > TBLspace Report for cats_small:informix.productsales > > Physical Address 1:113708 > Creation date 05/02/2005 21:56:35 > TBLspace Flags 801 Page Locking > TBLspace use 4 bit bit-maps > Maximum row size 26 > Number of special columns 0 > Number of keys 0 > Number of extents 1 > Current serial value 1 > First extent size 788896 > Next extent size 78889 > Number of pages allocated 788896 > Number of pages used 60 > Number of data pages 59 > Number of rows 3925 > Partition partnum 1048856 > Partition lockid 1048856 > > 9.40HC4 > > TBLspace Report for cats_small:informix.productsales > > Physical Address 1:113708 > Creation date 05/02/2005 22:04:40 > TBLspace Flags 801 Page Locking > TBLspace use 4 bit bit-maps > Maximum row size 26 > Number of special columns 0 > Number of keys 0 > Number of extents 1 > Current serial value 1 > First extent size 215908 > Next extent size 21590 > Number of pages allocated 215908 > Number of pages used 95 > Number of data pages 94 > Number of rows 6280 > Partition partnum 1048856 > Partition lockid 1048856

Neil Truby wrote: > This problem has been driving me mad for days! So what's your excuse for the rest of the time ?-) Sounds like someone has decided to try to do a better job guestimating the import sizes, and got it worng. Apart from echoing SP's suggestion to set the extent sizes (I'd be using -ss on the export if you are happy to get ALLLL the specific details emitted), my next idea is to try hacking the export file in a very easy way. My theory is that the table space is calculated from the lines { TABLE "neil".girlfriends row size = 20 number of columns = 7 index size = 10 } { unload file name = "girlf00100.unl" number of rows = 1 } I expect it multiplies the row size and index sizes against the number of rows and some fudge factor. You can't fiddle the number of rows because the import will definitely complain, but it might work to change the rowsize. Here's a little Perl one-liner (everyone has it these days, even in the O/S unless you are on a toy O/S) perl -p -i.old -e '/^{ TABLE / && s:(\\d+):int($1/2)+1:e' dbname.exp/dbname.sql the -i.old saves a copy of the original file with a .old suffix added. I'm being lazy here only changing the rowsize. A more delicate attempt might also adjust the index size too, but see how you go with this one. I'm interested to hear your results. And once the smoke clears, you can garner a bit more evidence and lodge a bug report

"scottishpoet" <dryburghj@yahoo.com> wrote in message news:1114642719.520190.203310@l41g2000cwc.googlegroups.com... > could well be a bug > > set the extent size in the .sql file before running the dbimport, does > that help? Yes, it does, but it's quite fiddly for hundreds of tables. Malcolm Weallans wrote to me privately to point out that it IS a bug which he reported, and pointed out on here, some months ago.

"Andrew Hamm" <ahamm@mail.com> wrote in message news:3daouqF6n2ob8U1@individual.net... > Neil Truby wrote: >> This problem has been driving me mad for days! > > So what's your excuse for the rest of the time ?-) > > Sounds like someone has decided to try to do a better job guestimating the > import sizes, and got it worng. > > Apart from echoing SP's suggestion to set the extent sizes (I'd be > using -ss on the export if you are happy to get ALLLL the specific details > emitted), my next idea is to try hacking the export file in a very easy > way. > > My theory is that the table space is calculated from the lines > > { TABLE "neil".girlfriends row size = 20 number of columns = 7 index size > = 10 } > { unload file name = "girlf00100.unl" number of rows = 1 } This is true, as by, say, halving the number of lines I can reduce the original allocation. This is of academic interest only though of course, for the reasons you give.