DBIMPORT 2KB page to 4KB page
Migrating a 7.31 32-bit database with 2KB pages (Linux) to IDS 10 64-bit on AIX with 4KB pages, dbimport aborted because some tables had initial/next extents of 8 pages, below the new 16-page minimum; dbimport -c didn't help and the export went to tape, so the schema couldn't easily be edited. Advice: use dbexport -f to write the schema file to disk (data can still go to tape) and hand-edit extent sizes/placements before dbimport, or use Art Kagel's myexport/myimport (sqlcmd-based, no 2GB file limit, auto-calculates extent sizes). The poster thanked both. A follow-up question about a -lgen link error building utils2_ak on Fedora got no answer in the thread.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Before I re-invent the wheel... ;-)
7.31 (32 bit) 2 KB page size on redhat going to a nice new IDS 10 (64 bit) on
aix.
- db2export to tape... (2GB limits and all that mutter mutter mutter)
- dbexport -ss to ensure the same dbspace layout
- dbimport is aborting with minimum extent size 16... (I have some 8's ;-)
Tried dbimport -c but still aborts - Could edit the 'tape' file but then the
fixed length would be wrong...
Is there a flag to tell dbimport to simply ignore or overwrite the inital /
next extents 8 (Or does Alter table in 7.31 allow me to alter the initial as
well (as it does the next extent?)
Otherwise the options I see are:
1) Not use -ss and move the indexes after the dbimport
2) Use unload / load or sql to move the data after using dbschema to create
the desire table/index layout...
Ideas?
2008/10/10 ROBERT CLOW <robert@clow.biz>:
> Before I re-invent the wheel... ;-)
>
> 7.31 (32 bit) 2 KB page size on redhat going to a nice new IDS 10 (64 bit) on
> aix.
>
> - db2export to tape... (2GB limits and all that mutter mutter mutter)
> - dbexport -ss to ensure the same dbspace layout
> - dbimport is aborting with minimum extent size 16... (I have some 8's ;-)
>
> Tried dbimport -c but still aborts - Could edit the 'tape' file but then the
> fixed length would be wrong...
>
> Is there a flag to tell dbimport to simply ignore or overwrite the inital /
> next extents 8 (Or does Alter table in 7.31 allow me to alter the initial as
> well (as it does the next extent?)
>
> Otherwise the options I see are:
> 1) Not use -ss and move the indexes after the dbimport
> 2) Use unload / load or sql to move the data after using dbschema to create
> the desire table/index layout...
>
> Ideas?
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
Robert
Not sure what you mean by "Could edit the 'tape' file but then the
fixed length would be wrong..."?
dbexport has a -f option to place the schema file in the file system
with the data files being sent to tape. This schema file can be
editted (within limits) to change initial and next sizes, table and
index placements etc before being transferred to your new server and
being used as the input to dbimport. Informix Migration Guide
(G251-2293-00) is your friend, particuarly Chapter 8.
Keith
Robert:
3) Use my dbexport/dbimport replacement utility myexport. It uses sqlcmd's
sqlunload and sqlreload to handle the data (by default) which does not have
the 2GB file size limit so you can output the export to disk instead of tape
allowing you to edit the schema to fiix the extents before reloading it into
the new server. The resulting export is completely compatible with dbimport
so you can a) use dbimport to build the new database, b) use myimport to
perform the import faster using the parallel load option, c) use myimport to
perform the import REALLY fast by having it use the HPLoader during the
load.
The myexport package is available for download from the Oninit web site (
www.oninit.com/utils) or the IIUG Software Repository (www.iiug.org/software).
It also required my utils2_ak package, Jonathan Leffler's sqlcmd package,
and Ravi Krishna's myonpload package (if you want to use the HPLoader
options). Note that since myexport uses my dbschema replacement utility,
myschema, to generate the dbimport compatible schema file, it automatically
exports as if -ss were included and automatically calculates optimal extent
sizes in the schema file. By default these are entered as recommendations
in comments, but you can easily edit the myexport script to include the
myschema options (-a, -m, -n, & -e) that control that calculation and
whether to use the calculated values in the CREATE TABLE statement or just
comment on the recommended values. I'd also recommend using the myexport -u
option since the most recent dbexport versions include the UPDATE STATISTICS
statements that this option generates (if you use dbimport to create the
database you'll have to run the update stats script manually afterwards or
append it to the main schema before the reload run - dbimport takes care of
that for you).
Art
On Fri, Oct 10, 2008 at 6:19 AM, ROBERT CLOW <robert@clow.biz> wrote:
> Before I re-invent the wheel... ;-)
>
> 7.31 (32 bit) 2 KB page size on redhat going to a nice new IDS 10 (64 bit)
> on
> aix.
>
> - db2export to tape... (2GB limits and all that mutter mutter mutter)
> - dbexport -ss to ensure the same dbspace layout
> - dbimport is aborting with minimum extent size 16... (I have some 8's ;-)
>
> Tried dbimport -c but still aborts - Could edit the 'tape' file but then
> the
> fixed length would be wrong...
>
> Is there a flag to tell dbimport to simply ignore or overwrite the inital /
> next extents 8 (Or does Alter table in 7.31 allow me to alter the initial
> as
> well (as it does the next extent?)
>
> Otherwise the options I see are:
> 1) Not use -ss and move the indexes after the dbimport
> 2) Use unload / load or sql to move the data after using dbschema to create
> the desire table/index layout...
>
> Ideas?
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
I found myexport, but I can't find myimport. Also, do the infos have
step-by-step instructions for installation/compile?> To: ids@iiug.org> From:
art.kagel@gmail.com> Subject: Re: DBIMPORT 2KB page to 4KB page [13671]> Date:
Fri, 10 Oct 2008 08:40:24 -0400> > Robert: > > 3) Use my dbexport/dbimport
replacement utility myexport. It uses sqlcmd's > sqlunload and sqlreload to
handle the data (by default) which does not have > the 2GB file size limit so
you can output the export to disk instead of tape > allowing you to edit the
schema to fiix the extents before reloading it into > the new server. The
resulting export is completely compatible with dbimport > so you can a) use
dbimport to build the new database, b) use myimport to > perform the import
faster using the parallel load option, c) use myimport to > perform the import
REALLY fast by having it use the HPLoader during the > load. > > The myexport
package is available for download from the Oninit web site ( >
www.oninit.com/utils) or the IIUG Software Repository (www.iiug.org/software).
> It also required my utils2_ak package, Jonathan Leffler's sqlcmd package, >
and Ravi Krishna's myonpload package (if you want to use the HPLoader >
options). Note that since myexport uses my dbschema replacement utility, >
myschema, to generate the dbimport compatible schema file, it automatically >
exports as if -ss were included and automatically calculates optimal extent >
sizes in the schema file. By default these are entered as recommendations > in
comments, but you can easily edit the myexport script to include the >
myschema options (-a, -m, -n, & -e) that control that calculation and >
whether to use the calculated values in the CREATE TABLE statement or just >
comment on the recommended values. I'd also recommend using the myexport -u >
option since the most recent dbexport versions include the UPDATE STATISTICS >
statements that this option generates (if you use dbimport to create the >
database you'll have to run the update stats script manually afterwards or >
append it to the main schema before the reload run - dbimport takes care of >
that for you). > > Art > > On Fri, Oct 10, 2008 at 6:19 AM, ROBERT CLOW
<robert@clow.biz> wrote: > > > Before I re-invent the wheel... ;-) > > > >
7.31 (32 bit) 2 KB page size on redhat going to a nice new IDS 10 (64 bit) > >
on > > aix. > > > > - db2export to tape... (2GB limits and all that mutter
mutter mutter) > > - dbexport -ss to ensure the same dbspace layout > > -
dbimport is aborting with minimum extent size 16... (I have some 8's ;-) > > >
> Tried dbimport -c but still aborts - Could edit the 'tape' file but then > >
the > > fixed length would be wrong... > > > > Is there a flag to tell
dbimport to simply ignore or overwrite the inital / > > next extents 8 (Or
does Alter table in 7.31 allow me to alter the initial > > as > > well (as it
does the next extent?) > > > > Otherwise the options I see are: > > 1) Not use
-ss and move the indexes after the dbimport > > 2) Use unload / load or sql to
move the data after using dbschema to create > > the desire table/index
layout... > > > > Ideas? > > > > > > > > >
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum. > > >
> > > -- > Art S. Kagel > Oninit (www.oninit.com) > IIUG Board of Directors
(art@iiug.org) > > Disclaimer: Please keep in mind that my own opinions are my
own opinions and > do not reflect on my employer, Oninit, the IIUG, nor any
other organization > with which I am associated either explicitly or
implicitly. Neither do > those opinions reflect those of other individuals
affiliated with any entity > with which I am affiliated nor those of the
entities themselves. > > >
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum. >
myimport is just another script in the same package if you went to the IIUG
site. It does not have to be compiled, it is just two Korn Shell scripts
that will run OK with Bash as well (though you'll have to change the first
line in each to point to Bash) if you don't want to install ksh. Install
the two scripts where ever you put executables in your PATH. To build
myschema just unpack (execute) the shell archive for utils2_ak and run
'make' on most platforms. The README.1st and BUILD files (which will list
out during the unpacking of the shell archive) explain any changes you need
for specific platforms (on RedHat you may get some warnings but otherwise is
should compile out of the box). Sqlcmd comes with instructions as does
myonpload.
Art
On Fri, Oct 10, 2008 at 11:33 AM, LARRY SORENSEN <lsorensen25@msn.com>wrote:
> I found myexport, but I can't find myimport. Also, do the infos have
> step-by-step instructions for installation/compile?> To: ids@iiug.org>
> From:
> art.kagel@gmail.com> Subject: Re: DBIMPORT 2KB page to 4KB page [13671]>
> Date:
> Fri, 10 Oct 2008 08:40:24 -0400> > Robert: > > 3) Use my dbexport/dbimport
> replacement utility myexport. It uses sqlcmd's > sqlunload and sqlreload to
> handle the data (by default) which does not have > the 2GB file size limit
> so
> you can output the export to disk instead of tape > allowing you to edit
> the
> schema to fiix the extents before reloading it into > the new server. The
> resulting export is completely compatible with dbimport > so you can a) use
> dbimport to build the new database, b) use myimport to > perform the import
> faster using the parallel load option, c) use myimport to > perform the
> import
> REALLY fast by having it use the HPLoader during the > load. > > The
> myexport
> package is available for download from the Oninit web site ( >
> www.oninit.com/utils) or the IIUG Software Repository (
> www.iiug.org/software).
> > It also required my utils2_ak package, Jonathan Leffler's sqlcmd package,
> >
> and Ravi Krishna's myonpload package (if you want to use the HPLoader >
> options). Note that since myexport uses my dbschema replacement utility, >
> myschema, to generate the dbimport compatible schema file, it automatically
> >
> exports as if -ss were included and automatically calculates optimal extent
> >
> sizes in the schema file. By default these are entered as recommendations >
> in
> comments, but you can easily edit the myexport script to include the >
> myschema options (-a, -m, -n, & -e) that control that calculation and >
> whether to use the calculated values in the CREATE TABLE statement or just
> >
> comment on the recommended values. I'd also recommend using the myexport -u
> >
> option since the most recent dbexport versions include the UPDATE
> STATISTICS >
> statements that this option generates (if you use dbimport to create the >
> database you'll have to run the update stats script manually afterwards or
> >
> append it to the main schema before the reload run - dbimport takes care of
> >
> that for you). > > Art > > On Fri, Oct 10, 2008 at 6:19 AM, ROBERT CLOW
> <robert@clow.biz> wrote: > > > Before I re-invent the wheel... ;-) > > > >
> 7.31 (32 bit) 2 KB page size on redhat going to a nice new IDS 10 (64 bit)
> > >
> on > > aix. > > > > - db2export to tape... (2GB limits and all that mutter
> mutter mutter) > > - dbexport -ss to ensure the same dbspace layout > > -
> dbimport is aborting with minimum extent size 16... (I have some 8's ;-) >
> > >
> > Tried dbimport -c but still aborts - Could edit the 'tape' file but then
> > >
> the > > fixed length would be wrong... > > > > Is there a flag to tell
> dbimport to simply ignore or overwrite the inital / > > next extents 8 (Or
> does Alter table in 7.31 allow me to alter the initial > > as > > well (as
> it
> does the next extent?) > > > > Otherwise the options I see are: > > 1) Not
> use
> -ss and move the indexes after the dbimport > > 2) Use unload / load or sql
> to
> move the data after using dbschema to create > > the desire table/index
> layout... > > > > Ideas? > > > > > > > > >
>
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum. > >
> >
> > > > -- > Art S. Kagel > Oninit (www.oninit.com) > IIUG Board of
> Directors
> (art@iiug.org) > > Disclaimer: Please keep in mind that my own opinions
> are my
> own opinions and > do not reflect on my employer, Oninit, the IIUG, nor any
> other organization > with which I am associated either explicitly or
> implicitly. Neither do > those opinions reflect those of other individuals
> affiliated with any entity > with which I am affiliated nor those of the
> entities themselves. > > >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum. >
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
Thanks Art, Keith Robert
I am trying to make utils2_ak on a fedora 2.6.25-14.fc9.i686 box. When running
`make`, I receive the following errors. Any suggestions for help:?
INFORMIXC-gcc esql -DHAVE_GNU_GETOPT=1 -o ul ul.ec -L/usr/lib -lgen
/usr/bin/ld: cannot find -lgen
collect2: ld returned 1 exit status
make:*** [ul] Error 1
Thanks> To: ids@iiug.org> From: art.kagel@gmail.com> Subject: Re: DBIMPORT 2KB
page to 4KB page [13681]> Date: Fri, 10 Oct 2008 17:00:08 -0400> > myimport is
just another script in the same package if you went to the IIUG > site. It
does not have to be compiled, it is just two Korn Shell scripts > that will
run OK with Bash as well (though you'll have to change the first > line in
each to point to Bash) if you don't want to install ksh. Install > the two
scripts where ever you put executables in your PATH. To build > myschema just
unpack (execute) the shell archive for utils2_ak and run > 'make' on most
platforms. The README.1st and BUILD files (which will list > out during the
unpacking of the shell archive) explain any changes you need > for specific
platforms (on RedHat you may get some warnings but otherwise is > should
compile out of the box). Sqlcmd comes with instructions as does > myonpload. >
> Art > > On Fri, Oct 10, 2008 at 11:33 AM, LARRY SORENSEN
<lsorensen25@msn.com>wrote: > > > I found myexport, but I can't find myimport.
Also, do the infos have > > step-by-step instructions for
installation/compile?> To: ids@iiug.org> > > From: > > art.kagel@gmail.com>
Subject: Re: DBIMPORT 2KB page to 4KB page [13671]> > > Date: > > Fri, 10 Oct
2008 08:40:24 -0400> > Robert: > > 3) Use my dbexport/dbimport > > replacement
utility myexport. It uses sqlcmd's > sqlunload and sqlreload to > > handle the
data (by default) which does not have > the 2GB file size limit > > so > > you
can output the export to disk instead of tape > allowing you to edit > > the >
> schema to fiix the extents before reloading it into > the new server. The >
> resulting export is completely compatible with dbimport > so you can a) use
> > dbimport to build the new database, b) use myimport to > perform the
import > > faster using the parallel load option, c) use myimport to > perform
the > > import > > REALLY fast by having it use the HPLoader during the >
load. > > The > > myexport > > package is available for download from the
Oninit web site ( > > > www.oninit.com/utils) or the IIUG Software Repository
( > > www.iiug.org/software). > > > It also required my utils2_ak package,
Jonathan Leffler's sqlcmd package, > > > > > and Ravi Krishna's myonpload
package (if you want to use the HPLoader > > > options). Note that since
myexport uses my dbschema replacement utility, > > > myschema, to generate the
dbimport compatible schema file, it automatically > > > > > exports as if -ss
were included and automatically calculates optimal extent > > > > > sizes in
the schema file. By default these are entered as recommendations > > > in > >
comments, but you can easily edit the myexport script to include the > > >
myschema options (-a, -m, -n, & -e) that control that calculation and > > >
whether to use the calculated values in the CREATE TABLE statement or just > >
> > > comment on the recommended values. I'd also recommend using the myexport
-u > > > > > option since the most recent dbexport versions include the UPDATE
> > STATISTICS > > > statements that this option generates (if you use
dbimport to create the > > > database you'll have to run the update stats
script manually afterwards or > > > > > append it to the main schema before
the reload run - dbimport takes care of > > > > > that for you). > > Art > >
On Fri, Oct 10, 2008 at 6:19 AM, ROBERT CLOW > > <robert@clow.biz> wrote: > >
> Before I re-invent the wheel... ;-) > > > > > > 7.31 (32 bit) 2 KB page size
on redhat going to a nice new IDS 10 (64 bit) > > > > > > on > > aix. > > > >
- db2export to tape... (2GB limits and all that mutter > > mutter mutter) > >
- dbexport -ss to ensure the same dbspace layout > > - > > dbimport is
aborting with minimum extent size 16... (I have some 8's ;-) > > > > > > > >
Tried dbimport -c but still aborts - Could edit the 'tape' file but then > > >
> > > the > > fixed length would be wrong... > > > > Is there a flag to tell >
> dbimport to simply ignore or overwrite the inital / > > next extents 8 (Or >
> does Alter table in 7.31 allow me to alter the initial > > as > > well (as >
> it > > does the next extent?) > > > > Otherwise the options I see are: > >
1) Not > > use > > -ss and move the indexes after the dbimport > > 2) Use
unload / load or sql > > to > > move the data after using dbschema to create >
> the desire table/index > > layout... > > > > Ideas? > > > > > > > > > > > >
> >
*******************************************************************************
> > > > Forum Note: Use "Reply" to post a response in the discussion forum. >
> > > > > > > > > -- > Art S. Kagel > Oninit (www.oninit.com) > IIUG Board of
> > Directors > > (art@iiug.org) > > Disclaimer: Please keep in mind that my
own opinions > > are my > > own opinions and > do not reflect on my employer,
Oninit, the IIUG, nor any > > other organization > with which I am associated
either explicitly or > > implicitly. Neither do > those opinions reflect those
of other individuals > > affiliated with any entity > with which I am
affiliated nor those of the > > entities themselves. > > > > > > > >
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum. > >
> > > > > > > >
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum. > > >
> > > -- > Art S. Kagel > Oninit (www.oninit.com) > IIUG Board of Directors
(art@iiug.org) > > Disclaimer: Please keep in mind that my own opinions are my
own opinions and > do not reflect on my employer, Oninit, the IIUG, nor any
other organization > with which I am associated either explicitly or
implicitly. Neither do > those opinions reflect those of other individuals
affiliated with any entity > with which I am affiliated nor those of the
entities themselves. > > >
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum. >