RE: dbimport feature/bug on 9.40HC3

Neil, I reported this bug about a year ago. Dbimport, if no extent sizes are provided, is attempting to set the correct extent sizes and if varchars are used gets it very wrong. In my case the ratio was 8:1 as they had large var chars with nothing in them. Regards Malcolm -----Original Message----- From: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org] On Behalf Of Neil Truby Sent: 27 April 2005 23:00 To: informix-list@iiug.org Subject: 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 sending to informix-list

Hi all, I am quite confuse here. Neil, you have executed a dbexport and using the same files you are doing a dbimport in both the version. The result is different, in 9.40.HC3 the final result show that the First extent size is 788896 and Next extent size is 78889, while in 9.40.HC4 First extent size is 215908 and Next extent size is 21590. More over I suppose that you exported the database with the option -ss (dbexport -ss) otherwise you will not have the information about the extents and Informix will use the default values. If all this is correct, can you answer the following questions: 1) Can you specify the informix version used to export the database? 2) Which command did you use to export the database? 3) Which is the value that you have in the dbexport file regarding the "extent size" and "next size" of this table? Go in the directory created by the dbexport and check the file with extention .sql 4) which is the value of the following query: select tabname, fextsize, nextsize from systables wheretabname="table_name" execute it from the the original database the one that you used to execute the dbexport? 5) is this problem happening with all the tables or only with someone? 6) Can you create the stores_demo database, execute the dbexport and dbimport in both the versions and check if the problem appear ? 7) have you executed the oncheck -ce -cr -cc -cDI before the dbexport?

Hi all, I am quite confuse here. Neil, you have executed a dbexport and using the same files you are doing a dbimport in both the version. The result is different, in 9.40.HC3 the final result show that the First extent size is 788896 and Next extent size is 78889, while in 9.40.HC4 First extent size is 215908 and Next extent size is 21590. More over I suppose that you exported the database with the option -ss (dbexport -ss) otherwise you will not have the information about the extents and Informix will use the default values. If all this is correct, can you answer the following questions: 1) Can you specify the informix version used to export the database? 2) Which command did you use to export the database? 3) Which is the value that you have in the dbexport file regarding the "extent size" and "next size" of this table? Go in the directory created by the dbexport and check the file with extention .sql 4) which is the value of the following query: select tabname, fextsize, nextsize from systables wheretabname="table_name" execute it from the the original database the one that you used to execute the dbexport? 5) is this problem happening with all the tables or only with someone? 6) Can you create the stores_demo database, execute the dbexport and dbimport in both the versions and check if the problem appear ? 7) have you executed the oncheck -ce -cr -cc -cDI before the dbexport?

> Neil, you have executed a dbexport and using the same files you are > doing a dbimport in both the version. The result is different, in > 9.40.HC3 the final result show that the First extent size is 788896 > and Next extent size is 78889, while in 9.40.HC4 First extent size is > 215908 and Next extent size is 21590. Correct. > More over I suppose that you exported the database with the option > -ss (dbexport -ss) otherwise you will not have the information > about the extents and Informix will use the default values. No, not with -ss. I think - in fact I'm sure - that dbimport calculates the extent size from the info the dbexport sql file contains about the average row size and number of rows. > If all this is correct, can you answer the following questions: > 1) Can you specify the informix version used to export the database? 9.40FC3W2 > 2) Which command did you use to export the database? dbexport cats > 3) Which is the value that you have in the dbexport file regarding the > "extent size" and "next size" of this table? Go in the > directory created by the dbexport and check the file with extention > .sql There is none specified. > 4) which is the value of the following query: > select tabname, fextsize, nextsize from systables where> tabname="table_name" > execute it from the the original database the one that you used to > execute the dbexport? tabname productsales fextsize 16 nextsize 16 > 5) is this problem happening with all the tables or only with someone? Not sure. > 7) have you executed the oncheck -ce -cr -cc -cDI before the > dbexport? No.

The dbimport will not calculate anything. What happen is that dbimport will create an extent that will have the default size then he will realise that he need another one and he will use a new exted that has two time the size used before then he need another extend and he will increase the size again. this mechanism is know as "Doubling". At the end all the extent will be contiguous so he will merge them in a big one this is known as "Concatenation". If the table is very big in the worst scenario you'll loose a lot of space. In fact what is suggested for big table is calculate the first extent, then define the next extend with a small size to avoid to loose space. So im my opinion is not a bug. Check the manual Administrator References.