RE: Lvarchar and dbimport
Hi, malcolm,
I think I know what the actual problem is:
I so it in the past
When 'dbimport' calculates the FIRST?NEXT extent size for the
table before import, it bases it's calculations on the
maximum length's of VARCHAR and LVARCHAR fields rather then
on the average record length's. As a result, first/next
extent sizes become MUCH bigger then necessary.
The only workaround I know (and used successfully in the past)
is to manually edit the SQL file created by 'dbexport'
and modify the 'row size' value to something realistic
for large table containing 'varchar' and 'lvarchar'
------------------------------------------
Alexey Sonkin
> -----Original Message-----
> From: malcolm.weallans@btopenworld.com
> [mailto:malcolm.weallans@btopenworld.com]
> Sent: Thursday, March 25, 2004 7:23 AM
> To: informix-list@iiug.org
> Subject: Lvarchar and dbimport
>
> A customer has migrated from IDS 9.2 to IDS 9.4, changing machines
> en-route. They used dbexport from 9.2 and dbimport in 9.4. They
> migrated 1.5Gbyte database in IDS 9.2 and it became 8.5Gbyte in IDS
> 9.4. Further investigation has revealed that particular tables have
> increased in size dramatically. All of these tables have lvarchar
> columns, and the worst offender has 2 of them.
>
> Further investigation has revealed that the dbexport was not done with
> -ss option, so I had expected to find default extent sizes for the
> tables. However it appears that dbimport is now setting appropriate
> extent sizes in version 9.4 and that it gets the calculation wrong
> when lvarchar is used.
>
> These are the details of two of the tables found from sysmaster
> tables.
>
> table 1 table 2
> ti_nptotal 1,513,374 1,174,570
> ti_npused 74,875 367,754
> ti_nrows 739,182 2,795,364
> ti_rowsize 4,166 2,349
>
> table 1 uses 2 lvarchars of length 2048 and table 2 uses 1 lvarchar.
>
> I am trying to report this problem to IBM/Informix tech support but
> hitting problems due to uncertainties about the revenue getting from
> customer to VAR to distributor to IBM.
>
> Looks like this could be one of the causes of size increase when
> migrating to 9.4
>
> regards
>
> Malcolm
sending to informix-list