Dbimport problems with IDS 10 from IDS731
Migrating from IDS 7.31 (AIX 5.2) to IDS 10 (AIX 5.3), the poster found that dbexport/dbimport inflated dbspace usage by about 33%, while an in-place upgrade (copying files and running oninit -v) did not. Respondents explained this as expected rather than a bug: outstanding in-place ALTER TABLEs mean old rows are rewritten in full at import (checkable with oncheck -pT, and the same growth would occur on a 7.31-to-7.31 export/import), and detached indexes from IDS 9 onward put each index in its own extents, adding further space. No fix beyond understanding/accepting the growth is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Dear all,
I had to migrate my system according to the following scenario:
Source: Machine 1 with AIX 5.2 and IDS7.31
Destination: Machine 2 with AIX 5.3 and IDS10.0
Workaround:
1) I could do some tests with dbexport and dbimport like that:
Dbexport from the Machine 1 and
Dbimport to the Machine 2, BUT I could see that my dbspaces were increasing
until about 33% more than the original size.
2) In another way, following the Migration documentation from IBM:
Copy of the datafiles (I don't have a raw device) from Source to Destination.
After that I just started the new IDS with "oninit -v", and them it
automatically converted the old database to the new one without increase my
dbspaces, BUT after all this conversion I have decided to dbexport from this
new scenario, drop the database and dbimport, AND the result was similar like
the first scenario, it means that my dbspaces increased about 33% more than
the original size.
Question:
What happen? I just can't have plus 33% due to conversion issues!
Does anybody could have the same situation?
MICHEL TAGAMI said: > > I just can't have plus 33% due to conversion issues! You'd think, eh? -- Bye now, Obnoxio "... no bill is required as no value was provided." -- Christine Normile
Hi,
one possible cause of unexpected table growth:
outstanding in place alter tables.
For example: you have a table with lots of entries and change a column from
char(10) to char(1000) - no additional space required for old (unchanged)
rows. But after export/import all rows (including the old ones) will occuppy
much more space.
Another possible cause: switch from attached to detached indices - but I don't
think that would lead to 33% plus.
Bye
Andreas Kutsche
>
-------------------------------------------
SPAR Oesterreichische Warenhandels-AG
Hauptzentrale
Europastrasse 3
A - 5015 Salzburg
Tel: +43 662 4470 24223
Mobile: +43 664 6259575
E-Mail: Andreas.KUTSCHE@spar.at
Internet: http://www.spar.at
-------------------------------------------
-----Ursprüngliche Nachricht-----
> Von: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]Im Auftrag von
> MICHEL TAGAMI
> Gesendet: Dienstag, 20. Juni 2006 13:51
> An: ids@iiug.org
> Betreff: Dbimport problems with IDS 10 from IDS731 [6990]
>
>
>
> Dear all,
>
> I had to migrate my system according to the following scenario:
>
> Source: Machine 1 with AIX 5.2 and IDS7.31
> Destination: Machine 2 with AIX 5.3 and IDS10.0
>
> Workaround:
> 1) I could do some tests with dbexport and dbimport like that:
> Dbexport from the Machine 1 and
> Dbimport to the Machine 2, BUT I could see that my dbspaces
> were increasing
> until about 33% more than the original size.
>
> 2) In another way, following the Migration documentation from IBM:
> Copy of the datafiles (I don't have a raw device) from Source
> to Destination.
> After that I just started the new IDS with "oninit -v", and them it
> automatically converted the old database to the new one
> without increase my
> dbspaces, BUT after all this conversion I have decided to
> dbexport from this
> new scenario, drop the database and dbimport, AND the result
> was similar like
> the first scenario, it means that my dbspaces increased about
> 33% more than> the original size.
>
> Question:
> What happen? I just can't have plus 33% due to conversion issues!
> Does anybody could have the same situation?
>
>
> **************************************************************
> *****************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
Hi,
I didn't change my dbschema after the dbexport from IDS731 to dbimport it in
IDS10.
What are the differences in the indices structure between IDS731 and IDS10? I
could not understand the switching from attached to detached indices.
Best regards,
Michel
Hi,
the in-place-alter-table issue has nothing to do with changing the
dbschema between export und import.
If some (perhaps very long) time ago somebody changed your tables
with an alter table command (altering column definitions or adding new
columns etc.) that change will affect old rows NOW (at dbimport)!
Attached/Detached index: IDS 7.x put table data and index into the
same partition (fragment). Starting with IDS 9.?? the default changed
and table data and indices all have their own partitions (fragments).
Bye
Andreas Kutsche
>
-------------------------------------------
SPAR Oesterreichische Warenhandels-AG
Hauptzentrale
Europastrasse 3
A - 5015 Salzburg
Tel: +43 662 4470 24223
Mobile: +43 664 6259575
E-Mail: Andreas.KUTSCHE@spar.at
Internet: http://www.spar.at
-------------------------------------------
-----Ursprüngliche Nachricht-----
> Von: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]Im Auftrag von
> MICHEL TAGAMI
> Gesendet: Dienstag, 20. Juni 2006 14:26
> An: ids@iiug.org
> Betreff: Re: AW: Dbimport problems with IDS 10 from IDS731 [6993]
>
>
>
> Hi,
>
> I didn't change my dbschema after the dbexport from IDS731 to
> dbimport it in
> IDS10.
>
> What are the differences in the indices structure between
> IDS731 and IDS10? I
> could not understand the switching from attached to detached indices.
>
> Best regards,
> Michel
>
>
> **************************************************************
> *****************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
Hi Obnoxio, It was an unexpected situation because I really could not find any reference at IBM Thanks anyway. Michel
Hi, Due to the "alter tables", it means that if I make an export/drop/import on my own IDS731 I will have 33% more occupation in my dbspaces! Is is correct? Even without migrating from IDS731 to IDS10? Best regards, Michel
I could believe a 33% increase.
Consider the following: a table with a row size of 210 bytes and an index
key of 35 bytes. For a 16KB extent size, IDS 7.31 would use just 16KB per
extent. In IDS v10, the table will use the same 16 KB for the table
extents, but there will be additional 3KB extents for the index. For a
few rows, that's about a 20% increase. If you have multiple indexes, the
effect is compounded.
That's the real impact of the detached indexes. You have no control over
the extents for indexes. Those sizes are computed as the fraction of the
table extents equal to the fraction of the row size that makes up the
index key. And each index has separate extents. This is true whether the
indexes are in the same dbspace as the table or not.
Cheers,
Dick Snoke
IBM Software Group - ChannelWorks
dsnoke@us.ibm.com
(404) 487-1595
"MICHEL TAGAMI" <mtagami@lvmh.com.br>
Sent by: ids-bounces@iiug.org
06/20/2006 07:50 AM
Please respond to
ids@iiug.org
To
ids@iiug.org
cc
Subject
Dbimport problems with IDS 10 from IDS731 [6990]
Dear all,
I had to migrate my system according to the following scenario:
Source: Machine 1 with AIX 5.2 and IDS7.31
Destination: Machine 2 with AIX 5.3 and IDS10.0
Workaround:
1) I could do some tests with dbexport and dbimport like that:
Dbexport from the Machine 1 and
Dbimport to the Machine 2, BUT I could see that my dbspaces were
increasing
until about 33% more than the original size.
2) In another way, following the Migration documentation from IBM:
Copy of the datafiles (I don't have a raw device) from Source to
Destination.
After that I just started the new IDS with "oninit -v", and them it
automatically converted the old database to the new one without increase
my
dbspaces, BUT after all this conversion I have decided to dbexport from
this
new scenario, drop the database and dbimport, AND the result was similar
like
the first scenario, it means that my dbspaces increased about 33% more
than
the original size.
Question:
What happen? I just can't have plus 33% due to conversion issues!
Does anybody could have the same situation?
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Hi,
if you have some in-place-alter-tables like mentioned before (you can check
with oncheck -pT <db>:<table> , attention: share lock required): YES , an
export/import from IDS7 to IDS7 will produce the same increase.
Bye
Andreas
>
-------------------------------------------
SPAR Oesterreichische Warenhandels-AG
Hauptzentrale
Europastrasse 3
A - 5015 Salzburg
Tel: +43 662 4470 24223
Mobile: +43 664 6259575
E-Mail: Andreas.KUTSCHE@spar.at
Internet: http://www.spar.at
-------------------------------------------
-----Ursprüngliche Nachricht-----
> Von: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]Im Auftrag von
> MICHEL TAGAMI
> Gesendet: Dienstag, 20. Juni 2006 14:53
> An: ids@iiug.org
> Betreff: Re: AW: AW: Dbimport problems with IDS 10 from.... [6996]
>
>
>
> Hi,
>
> Due to the "alter tables", it means that if I make an
> export/drop/import on my
> own IDS731 I will have 33% more occupation in my dbspaces! Is
> is correct?
> Even without migrating from IDS731 to IDS10?
>
> Best regards,
> Michel
>
>
> **************************************************************
> *****************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>