dbimport
A user planning a dbexport/dbimport of a ~130GB IDS 9.4 database asked whether indexes should be commented out of the schema first, fearing slow loads. Responses clarified that this isn't necessary: dbimport loads data first and builds indexes (plus permissions and update statistics) afterwards. To speed things up, suggestions were to set a significant PDQPRIORITY, allocate enough MGM memory so index sorts stay in memory, and set PSORT_NPROCS to at least (ideally twice) the number of CPU VPs. Alternatives offered were the High Performance Loader (HPL) or Art Kagel's myexport/myimport utilities from the IIUG Software Repository with their HPL and parallel load options (-m, -p, -U).
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I'm planning to use dbexport on a IDS 9.4 database of approximately 130GB and
then later use dbimport to restore the data. I'm wondering if I should use an
alternate/manual method as the import will be slow for indexed tables with
large data volumes. Do you have to comment out the indexes in the schema
before running dbimport?
-TD
TONY DEMEIS wrote:
> I'm planning to use dbexport on a IDS 9.4 database of approximately 130GB and
> then later use dbimport to restore the data. I'm wondering if I should use an
> alternate/manual method as the import will be slow for indexed tables with
> large data volumes. Do you have to comment out the indexes in the schema
> before running dbimport?
> -TD
>
Dbimport creates the indexes for each table after loading the data into
the table. The only thing you can do to speed it up even more is to set
PDQPRIORITY to some significant value, make sure that the engine has
enough MGM memory configured to allow sorting for the index builds in
memory most of the time, and set PSORT_NPROCS to at least the number of
CPU VPs to enable parallel sorting (I like to set it at 2x CPU VPs).
Or get my dbexport/dbimport replacement utility, myexport, from the IIUG
Software Repository and use its HPLoader and parallel load features and
use myexport with -m and myimport with -m, -p, and -U
Art S. Kagel
Oninit
I use dbimport to reload our test database. The indexes are recreated at
the end. It also recreates the permissions and runs update statistics.
Corinne L. Welters
Systems Project Analyst
corinnew@co.clackamas.or.us
(503) 723-4991
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
TONY DEMEIS
Sent: Friday, April 11, 2008 8:52 AM
To: ids@iiug.org
Subject: dbimport [11840]
I'm planning to use dbexport on a IDS 9.4 database of approximately
130GB and
then later use dbimport to restore the data. I'm wondering if I should
use an
alternate/manual method as the import will be slow for indexed tables
with
large data volumes. Do you have to comment out the indexes in the schema
before running dbimport?
-TD
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
See you at the IIUG Informix 2008 Conference
The Power Conference for Informix Professionals
April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas
http://www.iiug.org/conf
Registration Now Open!!
--
You might also want to look at the HPL .. that might be handy
=
"TONY DEMEIS" =
<tony.demeis@onta =
rio.ca> =
To
Sent by: ids@iiug.org =
ids-bounces@iiug. =
cc
org =
Subj=
ect
dbimport [11840] =
04/11/2008 10:52 =
AM =
=
=
Please respond to =
ids@iiug.org =
=
=
I'm planning to use dbexport on a IDS 9.4 database of approximately 130=
GB
and
then later use dbimport to restore the data. I'm wondering if I should =
use
an
alternate/manual method as the import will be slow for indexed tables w=
ith
large data volumes. Do you have to comment out the indexes in the schem=
a
before running dbimport?
-TD
***********************************************************************=
********
Forum Note: Use "Reply" to post a response in the discussion forum.
See you at the IIUG Informix 2008 Conference
The Power Conference for Informix Professionals
April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas
http://www.iiug.org/conf
Registration Now Open!!
=
Yes, definitely look into HPL. It rocks.
I think one of my IBM contacts in Chicago area is presenting HPL at the
IIUG conference in Kansas... Don't know his timeslot but he told me he's
presenting....
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Manoj Mohan
Sent: Friday, April 11, 2008 11:19 AM
To: ids@iiug.org
Subject: Re: dbimport [11843]
You might also want to look at the HPL .. that might be handy
=
"TONY DEMEIS" =
<tony.demeis@onta =
rio.ca> =
To
Sent by: ids@iiug.org =
ids-bounces@iiug. =
cc
org =
Subj=
ect
dbimport [11840] =
04/11/2008 10:52 =
AM =
=
=
Please respond to =
ids@iiug.org =
=
=
I'm planning to use dbexport on a IDS 9.4 database of approximately 130=
GB
and
then later use dbimport to restore the data. I'm wondering if I should =
use
an
alternate/manual method as the import will be slow for indexed tables w=
ith
large data volumes. Do you have to comment out the indexes in the schem=
a
before running dbimport?
-TD
***********************************************************************=
********
Forum Note: Use "Reply" to post a response in the discussion forum.
See you at the IIUG Informix 2008 Conference
The Power Conference for Informix Professionals
April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas
http://www.iiug.org/conf
Registration Now Open!!
=
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
See you at the IIUG Informix 2008 Conference
The Power Conference for Informix Professionals
April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas
http://www.iiug.org/conf
Registration Now Open!!
============================================================
The information contained in this message may be privileged
and confidential and protected from disclosure. If the reader
of this message is not the intended recipient, or an employee
or agent responsible for delivering this message to the
intended recipient, you are hereby notified that any reproduction,
dissemination or distribution of this communication is strictly
prohibited. If you have received this communication in error,
please notify us immediately by replying to the message and
deleting it from your computer. Thank you. Tellabs
============================================================
Art,Have the requirements and procedure to implement your tools dbexport /
dbimport in Unix (Solaris, AIX, HP-UX) and Linux
Thanks.
The freedom of Linux ... The power of Informix
> To: ids@iiug.org
> From: art@oninit.com
> Subject: Re: dbimport [11841]
> Date: Fri, 11 Apr 2008 12:14:50 -0400
>
> TONY DEMEIS wrote:
> > I'm planning to use dbexport on a IDS 9.4 database of approximately 130GB
> and
> > then later use dbimport to restore the data. I'm wondering if I should use
> an
> > alternate/manual method as the import will be slow for indexed tables with
> > large data volumes. Do you have to comment out the indexes in the schema
> > before running dbimport?
> > -TD
> >
> Dbimport creates the indexes for each table after loading the data into
> the table. The only thing you can do to speed it up even more is to set
> PDQPRIORITY to some significant value, make sure that the engine has
> enough MGM memory configured to allow sorting for the index builds in
> memory most of the time, and set PSORT_NPROCS to at least the number of
> CPU VPs to enable parallel sorting (I like to set it at 2x CPU VPs).
>
> Or get my dbexport/dbimport replacement utility, myexport, from the IIUG
> Software Repository and use its HPLoader and parallel load features and
> use myexport with -m and myimport with -m, -p, and -U
>
> Art S. Kagel
> Oninit
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
> See you at the IIUG Informix 2008 Conference
> The Power Conference for Informix Professionals
> April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas
> http://www.iiug.org/conf
> Registration Now Open!!
_________________________________________________________________
Descargá ya gratis y viví la experiencia Windows Live.
http://www.descubrewindowslive.com/latam/index.html