IDS Forum

Re: dbload and load

Posted By: Art Kagel
Date: Wednesday, 10 March 2010, at 5:35 p.m.

In Response To: RE: dbload and load (Plugge, Joe R.)

It would be interesting to see how fast my dbcopy utility can transfer the
data from the external table to the real one. Like dbload it uses partial
transactions and big buffers so it is lightening fast on normal tables and
uses as few locks as you configure it to (10,000 by default).

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)

See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf

Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.

On Wed, Mar 10, 2010 at 2:18 PM, Plugge, Joe R. <JRPlugge@west.com> wrote:

> John, your method as described below chews up a ton of row locks, I had to
> alter my base table type(raw) and then issue the insert statement. Am I
> missing something?
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of John
> Miller iii
> Sent: Tuesday, March 09, 2010 1:44 PM
> To: ids@iiug.org
> Subject: Re: dbload and load [19290]
>
> Frank:
>
> I am not sure if you are aware but in version 11.50.xC6
> there was a new loading mechanism that I find very easy
> to use, which I believe is faster than both dbload and load.
> It is called external tables and to do the load you simple
> do the following.
>
> In addition, you can combine the external table feature and the newly
> add MERGE statement to insert new rows and update existing
> rows (or various combinations).
>
> CREATE EXTERNAL TABLE t1_ext SAMEAS t1
> USING
> (
> DATAFILES('DISK:/tmp/t1.unl'),
> FORMAT 'DELIMITED',
> DELIMITER '|',
> RECORDEND '',
> Deluxe,
> NUMROWS 50,
> MAXERRORS 50,
> REJECTFILE ''
> );
>
> INSERT INTO t1 SELECT * FROM t1_ext;
> DROP TABLE t1_ext;
>
> John F. Miller III
> STSM, Support Architect
> miller3@us.ibm.com
> 503-578-5645
> IBM Informix Dynamic Server (IDS)
>
> ids-bounces@iiug.org wrote on 03/09/2010 10:40:31 AM:
>
> > [image removed]
> >
> > Re: dbload and load [19289]
> >
> > FRANK
> >
> > to:
> >
> > ids
> >
> > 03/09/2010 10:42 AM
> >
> > Sent by:
> >
> > ids-bounces@iiug.org
> >
> > Please respond to ids
> >
> > Another interesting result observation I like to share.
> >
> > For a regular table and default configuration, We just change the number
> > of rows to be committed( a parameter of dbload) You can see the total
> > loading time changing. It is a curve. You may find the Point there.
> >
> > Rows Time
> > =================================
> > 5 rows 46 minutes 31 seconds
> > 20 rows 26 minutes 7 seconds
> > 40 row 23 minutes 15 seconds
> > 100 rows 24 minutes 11 seconds
> > 300 rows 22 minutes 53 seconds
> > 650 rows 22 minutes 44 seconds
> > 1250 rows 22 minutes 33 seconds
> > 2500 rows 22 minutes 46 seconds
> > 5000 rows 21 minutes 16 seconds
> > 10000 rows 20 minutes 15 seconds
> > 25000 rows 19 minutes 28 seconds
> > 50000 rows 20 minutes 16 seconds
> > 75000 rows 20 minutes 33 seconds
> > 100000 rows 20 minutes 18 seconds
> > 200000 rows 22 minutes 14 seconds
> > 400000 rows 28 minutes 34 seconds
> > 800000 rows 29 minutes 3 seconds
> > 1600000 rows 39 minutes 35 seconds
> >
> > Want to know what Fast Informix DBAs like to do?
> >
> > Frank
> >
> > On Tue, Mar 9, 2010 at 7:27 AM, Obnoxio The Clown
> > <obnoxio@serendipita.com>wrote:
> >
> > > CHUAN LU wrote:
> > > > Hello,
> > > >
> > > > when a parnter loading a whole file into the table, they find the
> > > performance
> > > > of dbload is better than SQL load, who know the internal reason? Any
> > > reply
> > > > will be appreciated.
> > >
> > > If you are committing dbload every couple of thousand rows, the
> > > transaction logging is much more efficient. SQL LOAD doesn't offer the
> > > option. You probably won't see it on small tables.
> > >
> > > --
> > > Cheers,
> > > Obnoxio The Clown
> > >
> > > http://obotheclown.blogspot.com
> > > I will now proceed to pleasure myself with this fish.
> > >
> > > --
> > > This message has been scanned for viruses and
> > > dangerous content by OpenProtect(http://www.openprotect.com), and is
> > > believed to be clean.
> > >
> > >
> > >
> > >
> >
>
>
>
> *******************************************************************************
>
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --0016e6407c9ee4b01a048162838c
> >
> >
> >
>
>
>
> *******************************************************************************
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--0015174027a627a64d048179ea6d

Messages In This Thread

• dbload and load
  CHUAN LU -- Tuesday, 9 March 2010, at 3:16 a.m.
  • Re: dbload and load
    Obnoxio The Clown -- Tuesday, 9 March 2010, at 7:27 a.m.
    • Re: dbload and load
      FRANK -- Tuesday, 9 March 2010, at 1:40 p.m.
      • Re: dbload and load
        John Miller iii -- Tuesday, 9 March 2010, at 2:43 p.m.
        • Re: dbload and load
          FRANK -- Tuesday, 9 March 2010, at 6:17 p.m.
        • RE: dbload and load
          Plugge, Joe R. -- Wednesday, 10 March 2010, at 2:18 p.m.
          • Re: dbload and load
            Art Kagel -- Wednesday, 10 March 2010, at 5:35 p.m.
          • RE: dbload and load
            John Miller iii -- Monday, 15 March 2010, at 7:06 p.m.
  • Re: dbload and load
    John Miller iii -- Tuesday, 9 March 2010, at 11:13 a.m.
    • Re: dbload and load
      Fernando Nunes -- Tuesday, 9 March 2010, at 12:44 p.m.
  • Re: dbload and load
    Art Kagel -- Tuesday, 9 March 2010, at 3:48 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.