IDS Forum

RE: dbload and load

Posted By: John Miller iii
Date: Monday, 15 March 2010, at 7:06 p.m.

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

It is doing one large insert statement as a single
transaction. You can use row, page or table level locking, depending
on your needs and requirements.

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/10/2010 11:18:34 AM:

> [image removed]
>
> RE: dbload and load [19302]
>
> Plugge, Joe R.
>
> to:
>
> ids
>
> 03/10/2010 11:24 AM
>
> Sent by:
>
> ids-bounces@iiug.org
>
> Please respond to ids
>
> 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.
>

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.