could well be a bug

set the extent size in the .sql file before running the dbimport, does
that help?

Neil

There are different numbers of rows in each of these tables.  One could
assume that they are imported from different exports and the sql files have
different extent size and next size.

MW

sending to informix-list

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

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

Well, it helps sometimes to be obstinate, pig headed, passionate, and
persistent.  

I think we can all see from the subsequent posts on this subject that
IBM have acknowledged they got it wrong.  They have put it right (thye
think) in future releases by adjusting the calculation.  However the
calculation will always be wrong if they persist in adding the full
length of VARCHAR when calculating row size.  That is what causes the
problem with even the smallest databaes (e.g. my speciality - the stores
database).  People have commented on this effect being different for
different tables - and that is down to the VARCHAR effect.

IF anybody from IBM is listening a better procedure would be to assume
empty varchars when calculating row size.  OK this will not keep the
whole table in one extent after loading but will be more correct.  OR
how about factoring in the size of the unload file when VARCHARs or
BLOBs are involved.

Regards

Malcolm

-----Original Message-----
From: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org]
On Behalf Of Christopher
Sent: 28 April 2005 18:56
To: informix-list@iiug.org
Subject: Re: dbimport feature/bug on 9.40HC3

I shared this problem with Informix Tech Support and could not get them
to acknowledge the bug, so you've gotten farther than I.

What I did was create the stores7 database (dbaccessdemo) in IDS 7.31,
then exported it without the -ss, then imported into IDS 9.40.xC3.  I
used Solaris, AIX, and Windows.  Oddly, Solaris took the most space.
However, the database took about four times the space.  The
sysprocedures table took eight times the space, alone.

Next, I tried creating the database in IDS 9.4 with the dbaccessdemo7
program.  It took more space than on IDS 7, but not nearly as much as
the dbimport.

Tech Support told me this was expected behaviour.  No, I do not believe
that, and, no, I could not get past this response.

As Neil has noted, the space wastage comes from widely mis-calculating
extent sizes.  This might be acceptable if I am importing a production
database that is going to grow, anyway, but a lot of the imports,
especially those without the dbexport -ss, are for test databases that
are never going to grow.

Now, some people have recommended estimating the extent size and putting
those in the file.  One suggestion was to estimate based on row size
from the SQL file.What we discovered was that this number was
consistently lower, by an appreciable margin.  There always seems to be
more overhead than we calculated.  Part of the problem is the indexes,
especially those for constraints not built upon named indexes.
Sincerely,

Christopher Coleman

Database Analyst
Medication Management
Mediware Information Systems, Inc.

sending to informix-list

And then I could argue that when migrating from
standard engine to IDS the -ss option is a little bit
of a problem.

Come off it John, we all know that the original
purpose of dbexport/dbimport was to migrate databases
fom SE to IDS and we all know what a pig it is to
calculate extent sizes.  We also know that if a table
takes too many extents it will cause performance
problems, if not initially then eventually, and I'm
sure we all know that before the advent of detached
indexes this was not a problem.  It is now.  And if
IBM don't manage to do something about it they will
antagonise customers.

If IBM need my help with how to do it I'm sure my
company will sell my services.

regards

Malcolm

--- scottishpoet <dryburghj@yahoo.com> wrote:

sending to informix-list

John,
Dbimport has only recently started calculating the extent sizes.  It
certainly didn't attempt to do it back in 1990.  I know I worked for
Informix in those days and was one of the people who discovered the need
to set extent sizes after dbexport.  After all dbexport -ss is a fairly
recent innovation.

And yes, there is no varchar in SE.  But there is in Online 5, and IDS
7, and migration from them also needs to use dbexport/dbimport.  Many of
us in the real world are faced with customers who have been using
Informix products for many years on these old - but still very reliable
- platforms.  They are often being forced to migrate to IDS 9 and even
10.  The pressures on the migration are that the hardware is getting old
and when they replace it they discover they can no longer get the old
OS, and the old versions of Informix for the new OS.
 
So they take their tried and trusted application and come to people like
us looking for a solution.  And we should be trying to help them not
presenting them with "your 1 Gbyte database will now require 8Gbytes".
Fortunately most of these systems migrate to much bigger hardware for
much less price so the problem isn't always that grave.

And the original problem I had was that the customer was migrating from
9.2 to 9.4.  And yes they should have calculated the extent sizes
properly for 9.2.  And yes, they could have migrated in place.  But
there were many acceptable reasons why these things hadn't happened.
The extent sizes had never been set as their previous Informix support
people had never bothered to do it.  They couldn't update in place as
they wanted to migrate to a different operating system.  And in my case
the customer had looked at how much disk space the old system took and
bought similar amounts.  Fortunately we now have a resolution for that
customer and for future migrations I have implemented a Database sizing
phase in all conversion plans.

I have quoted the original case number in this thread.  I assume you
have the tools to find it.  And Sucwinder Bassi worked with me on it.  

Regards

Malcolm
-----Original Message-----
From: John Dryburgh [mailto:dryburghj@yahoo.com]
Sent: 30 April 2005 10:43
To: malcolm weallans
Subject: Re: dbimport feature/bug on 9.40HC3

there is no varchar in standard engine.

back in 1990, long before I worked for Informix, there
were problems with dbexport and dbimport
miscalculating the size of extents.

i used to distribute a demo along with online 5 which
included the complete works of sheakspeare and the
dbimport wouldn't load without some tinkering because
it miscalculated the extents. IBM make a best guess. I
doubt they'll ever get it perfect.

however if you did have a bug logged that you don't
think has been fully fixed, do you have the bug number
I'll have a look.

--- malcolm weallans <malcolm.iiug@btopenworld.com>
wrote:

Send instant messages to your online friends
http://uk.messenger.yahoo.com

sending to informix-list