 |
IDS Forum
RE: Question on HPL
Posted By: Habichtsberg, Reinhard
Date: Friday, 19 October 2012, at 2:23 a.m.
In Response To: Re: Question on HPL (Kern Doe)
> (you probably meant cat > /tmp/target_table.lo < /tmp/target_table.un
&)
> (you meant target_table?)
You are right. I copied some lines from an existing shell scripts where
the tables are defined by parameters and forgot to change the values.
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Kern
> Doe
> Sent: Friday, October 19, 2012 2:58 AM
> To: ids@iiug.org
> Subject: Re: Question on HPL [28572]
>
> I've tried this unloading involved "raw table" -- the result has blown
my mind away.
> My first test result was 9 minutes to unload 63 mil rows while hpl
took about
> 1 hour.
> My 2nd test was 38 min for 250mil rows, amazing!! I will test the load
soon.
>
> Reinhard, thank you for the script, I haven't tried it, there are some
parts I believe
> you meant differently, please see my comments.
> Thank you Reinhard.
>
> ________________________________
> From: "Habichtsberg, Reinhard" <RHabichtsberg@arz-emmendingen.de>
> To: ids@iiug.org
> Sent: Thursday, October 18, 2012 2:47 AM
> Subject: RE: Question on HPL [28562]
>
> If the tables resp. the unload files will grow too big you may even
unload and load
> using external tables and pipes with no need of storage
> space:
>
> Solaris 10, Informix 11.70.FC5
> Example shell script:
> #!/usr/bin/bash
> # Target table was created in target database and has same schema as
source
> table # Two Informix Server: source_server and target_server, may also
work if
> source_server and target_server and source_database and
target_database are
> the same # FORMAT 'informix' may not work for tables with byte or text
columns
>
> # Target table in target database set to type raw due to express mode:
> dbaccess target_database@target_server - << EOF alter table
target_table type
> (raw); EOF
>
> # Create pipe for loading:
> mkfifo /tmp/target_table.lo
>
> # Create pipe for unloading:
> mkfifo /tmp/target_table.un
>
> # Create external table in target database:
> dbaccess target_database@target_server - << EOF CREATE EXTERNAL TABLE
> ext_target_table sameas target_table USING (DATAFILES
> ('PIPE:/tmp/target_table.lo'), FORMAT 'informix', REJECTFILE
> 'target_database_target_table.log',
> EXPRESS);
> EOF
>
> # Create external table in source database:
> dbaccess source_database@source_server - << EOF CREATE EXTERNAL
> TABLE ext_source_table SAMEAS source_table USING (DATAFILES
> ('PIPE:/tmp/source_table.un'), FORMAT 'informix'); EOF
>
> # Open pipe in background:
> cat > /tmp/$tabelle.lo < /tmp/$tabelle.un &
>
> (you probably meant cat > /tmp/target_table.lo < /tmp/target_table.un
&)
>
> # Insert Import Command in background:
> dbaccess target_database@target_Server - <<EOF & insert into
target_table
> select * from ext_target_table; EOF
>
> # Sleep 3 seconds
> sleep 3
>
> # Insert Export Command:
> dbaccess source_database@source_server - <<EOF insert into
ext_source_table
> select * from source_table; EOF
>
> echo EXPORT/IMPORT done!
> # Sleep 5 seconds
> sleep 5
>
> # Clean up
> dbaccess source_database@source_server - <<EOF drop table
ext_source_table;
> EOF dbaccess target_database@target_Server - <<EOF drop table
> ext_target_table; EOF
>
> echo "drop PIPE:"
> rm /tmp/ target_table.lo
> rm /tmp/source_table.un
>
> # Set Target Table to type standard
> dbaccess target_database@target_Server - <<EOF alter table $tabelle
type
> (standard); (you meant target_table?) EOF
>
> I scipped the error handling in due of clarity.
>
> HTH
> Reinhard
>
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf
Of
> Art
> > Kagel
> > Sent: Thursday, October 18, 2012 2:25 AM
> > To: ids@iiug.org
> > Subject: Re: Question on HPL [28556]
> >
> > You can load using external tables also. Say you have a delimited
> file, mydata.unl,
> > that you want to load into a table named mydata. So you define an
> external table
> > defined over the file mydata.unl and insert into the normal table
> select from the
> > external table. So:
> >
> > create external table mydata_unl sameas mydata using (datafiles(
> > "disk:/some/path/mydata.unl"), format "delimited", delimiter "|" );
> >
> > insert into mydata
> > select * from mydata_unl;
> >
> > Art
> >
> > Art S. Kagel
> > Advanced DataTools (http://www.advancedatatools.com/)
> > Blog: http://informix-myview.blogspot.com/
> >
> > 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, Oct 17, 2012 at 6:51 PM, Kern Doe <kern_doe@yahoo.com>
wrote:
> >
> > > This is great! How would you guys load?
> > > Would you use dbload or HPL, I know I don't want to use "load
from".
> > >
> > > Any way, thank you John/Art, we'll try this tomorrow.
> > > Kern --
> > >
> > > ________________________________
> > > From: John Miller iii <miller3@us.ibm.com>
> > > To: ids@iiug.org
> > > Sent: Wednesday, October 17, 2012 5:09 PM
> > > Subject: Re: Question on HPL [28552]
> > >
> > > Here is the syntax for unloading the customer table to 2 files.
> > >
> > > CREATE EXTERNAL TABLE 'informix'.EXTcustomer SAMEAS
> > > 'informix'.customer USING (
> > > DATAFILES('DISK:/tmp/customer_1.unl','DISK:/tmp/customer_2.unl'),
> > > FORMAT 'DELIMITED',
> > > DELIMITER '|',
> > > RECORDEND '',
> > > ESCAPE
> > > );
> > >
> > > INSERT INTO 'informix'.customer
> > > SELECT * FROM 'informix'.EXTcustomer;
> > >
> > > John F. Miller III
> > > STSM, Embedability Architect
> > > miller3@us.ibm.com
> > > 503-578-5645
> > > IBM Informix Dynamic Server (IDS)
> > >
> > > ids-bounces@iiug.org wrote on 10/17/2012 11:54:06 AM:
> > >
> > > > From: "Art Kagel" <art.kagel@gmail.com>
> > > > To: ids@iiug.org,
> > > > Date: 10/17/2012 11:55 AM
> > > > Subject: Re: Question on HPL [28551] Sent by:
ids-bounces@iiug.org
> > > >
> > > > Note, this is typed off the cuff, so I may have missed a quote
or
> > > > comma somewhere.
> > > >
> > > > create external table sometable_ext like sometable using
> > > > (datafiles("disk:/some/path/file","disk:/other/path/file"));
> > > >
> > > > select * from sometable insert into sometable_ext;
> > > >
> > > > VOILA!
> > > >
> > > > For import, when you create the external table you can add the
> > > > EXPRESS
> > > mode
> > > > attribute and it will load the data the same way that HPL in
> express
> > > > mode
> > >
> > > > does. You can change the data format from DELIMITED, to
INFORMIX,
> or
> > > > FIXED. You can change the delimiter for the default delimited
> format
> > > > - no
> > >
> > > > you can't get rid of the trailing delimiter 8-( - etc. You can
> write
> > > > to a
> > >
> > > > pipe so the data can be compressed. Check out the description of
> > > > CREATE EXTERNAL TABLE in the online or PDF manuals for full
> details.
> > > >
> > > > Art
> > > >
> > > > Art S. Kagel
> > > > Advanced DataTools (http://www.advancedatatools.com/)
> > > > Blog: http://informix-myview.blogspot.com/
> > > >
> > > > 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, Oct 17, 2012 at 2:44 PM, Kern Doe <kern_doe@yahoo.com>
> wrote:
> > > >
> > > > > This is getting more interesting. Do you have a simple sample
> > > > > codes involved unload/load with external tables? I tried HPL
> > > > > unload yesterday on 63
> > > mil
> > > > > rows
> > > > > tables, it took 1hr15min and already happy about it, anything
> else
> > > > > I
> > > can
> > > > > do to
> > > > > improve further the speed and the flexibility, I'm all for it.
> > > > > Thanks Art.
> > > > >
> > > > > ________________________________
> > > > > From: Art Kagel <art.kagel@gmail.com>
> > > > > To: ids@iiug.org
> > > > > Sent: Wednesday, October 17, 2012 1:19 PM
> > > > > Subject: Re: Question on HPL [28546]
> > > > >
> > > > > FYI, if you have 11.50 or later, you can use external tables
for
> > > > > the
> > > unload
> > > > > and it is at least as fast as HPL. Then the select to filter
the
> > > > > data being written into the external table would be trivial.
> > > > >
> > > > > Art
> > > > >
> > > > > Art S. Kagel
> > > > > Advanced DataTools (http://www.advancedatatools.com/)
> > > > > Blog: http://informix-myview.blogspot.com/
> > > > >
> > > > > 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, Oct 17, 2012 at 1:32 PM, Kern Doe <kern_doe@yahoo.com>
> > wrote:
> > > > >
> > > > > > Dear HPL experts,
> > > > > > I create a simple HPL unload, it works fast, and it
basically
> > > > > > dumps
> > > the
> > > > > > entire
> > > > > > table. Is there way I can customize HPL unload for selected
> > > > > > data, for
> > >
> > > > > > example,
> > > > > > "customer_id < 2000000" ?
> > > > > > Thanks in advance.
> > > > > > Kern --
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > >
> > >
> > >
> > >
> >
>
************************************************************************
> *******
> > >
> > > > > > Forum Note: Use "Reply" to post a response in the discussion
> forum.
> > > > > >
> > > > > >
> > > > >
> > > > > --e89a8f923ba0ddf2be04cc454df1
> > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > >
> > >
> > >
> > >
> >
>
************************************************************************
> *******
> > >
> > > > > Forum Note: Use "Reply" to post a response in the discussion
> forum.
> > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > >
> > >
> > >
> > >
> >
>
************************************************************************
> *******
> > >
> > > > > Forum Note: Use "Reply" to post a response in the discussion
> forum.
> > > > >
> > > > >
> > > >
> > > > --14dae93404b93508a004cc45cbb6
> > > >
> > > >
> > > >
> > >
> > >
> > >
> > >
> >
>
************************************************************************
> *******
> > >
> > > > 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.
> > >
> > >
> >
> > --90e6ba614c8042c2e504cc4a69e1
> >
> >
> >
>
************************************************************************
> *******
> > 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
- Question on HPL
Kern Doe -- Wednesday, 17 October 2012, at 1:32 p.m.
- Re: Question on HPL
Jack Parker -- Wednesday, 17 October 2012, at 1:36 p.m.
- Re: Question on HPL
Art Kagel -- Wednesday, 17 October 2012, at 2:19 p.m.
- Re: Question on HPL
Kern Doe -- Wednesday, 17 October 2012, at 2:44 p.m.
- Re: Question on HPL
Jack Parker -- Wednesday, 17 October 2012, at 2:50 p.m.
- Re: Question on HPL
Art Kagel -- Wednesday, 17 October 2012, at 2:54 p.m.
- Re: Question on HPL
John Miller iii -- Wednesday, 17 October 2012, at 6:09 p.m.
- Re: Question on HPL
Art Kagel -- Wednesday, 17 October 2012, at 6:42 p.m.
- Re: Question on HPL
Kern Doe -- Wednesday, 17 October 2012, at 6:51 p.m.
- Re: Question on HPL
Art Kagel -- Wednesday, 17 October 2012, at 8:24 p.m.
- RE: Question on HPL
Habichtsberg, Reinhard -- Thursday, 18 October 2012, at 3:47 a.m.
- Re: Question on HPL
Kern Doe -- Thursday, 18 October 2012, at 8:58 p.m.
- Re: Question on HPL
Art Kagel -- Thursday, 18 October 2012, at 9:19 p.m.
- RE: Question on HPL
Habichtsberg, Reinhard -- Friday, 19 October 2012, at 2:23 a.m.
- Re: Question on HPL
John Miller iii -- Thursday, 18 October 2012, at 1:01 p.m.
- Re: Question on HPL
Jack Parker -- Wednesday, 17 October 2012, at 9:43 p.m.
- Re: Question on HPL
John Miller iii -- Thursday, 18 October 2012, at 1:17 p.m.
- Re: Question on HPL
FRANK J. COMPUTER -- Wednesday, 17 October 2012, at 11:24 p.m.
IDS Forum is maintained by Administrator with WebBBS 5.12.
|
|
