Import Information quickly
Gustavo asked whether dbload can load data produced by dbexport, since dbimport of his ~900-table database takes about 20 hours. Answers: yes, dbload works but needs a command file per table (FILE ... DELIMITER "|" <numcols>; INSERT INTO <table>;), though dbimport is the natural inverse of dbexport. For speed, posters suggested HPL, running several loads in parallel, creating indexes after loading, Art Kagel's myexport/myimport and utils2_ak/utils4_ak scripts (external tables unavailable on his IDS 7.31 Windows server), and setting FET_BUF_SIZE=32000. Gustavo settled on plain dbimport.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Good morning gentlemen of the forum!
I wanted to know if you can use the utility "dbload" to import the records
generated by a "dbexport." And if the answer is yes, what is the syntax of the
command?.
Thank you very much in advance, and for dedicating your valuable time reading
this message.
Gustavo Echenique
Gustavo,
Yes you can. You will need to create a command file for each table to be
loaded. The syntax for each table to be loaded is:
FILE <data_filename> DELIMITER "|" <numcols> ;
INSERT INTO <tablename> ;
Where: <data_filename> is the name of the unload file produced by dbexport.
<numcols> is the number of columns in the table.
<tablename> is the name of the table to load the data into.
Then use dbload to load them. The command will be something like:
dbload -d <dbname> -c <command_filename> -l <logfilename>
Where: <dbname> is the name of the database to load the data.
<command_filename> is the name of the command file containing all the FILE
statements above.
<logfilename> is the path to file to hold and log file information.
If the tables are large consider using the High Performance Loader.
Good luck.
> To: ids@iiug.org
> From: gustavo.echenique@cemdo.com.ar
> Subject: Import Information quickly [25329]
> Date: Thu, 3 Nov 2011 09:32:10 -0400
>
> Good morning gentlemen of the forum!
>
> I wanted to know if you can use the utility "dbload" to import the records
> generated by a "dbexport." And if the answer is yes, what is the syntax of
the
> command?.
>
> Thank you very much in advance, and for dedicating your valuable time reading
> this message.
>
> Gustavo Echenique
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Hi Andrew, first of all thank you very much for your quick response.
What I need is to import all the information generated by the "dbexport" and
are more than 900 tables. Can you do that?
A big hello and a new appreciation for you.
Gustavo Echenique
Gustavo,
Daft question but why not use dbimport?
The short answer to your question there is no "quick" way of doing this. With
some shell scripting you can produce the command file very easily though. All
you need is the table name, the name of the data file and the number of
columns in the table.
> To: ids@iiug.org
> From: gustavo.echenique@cemdo.com.ar
> Subject: Re: RE: Import Information quickly [25332]
> Date: Thu, 3 Nov 2011 09:45:47 -0400
>
> Hi Andrew, first of all thank you very much for your quick response.
>
> What I need is to import all the information generated by the "dbexport" and
> are more than 900 tables. Can you do that?
>
> A big hello and a new appreciation for you.
>
> Gustavo Echenique
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Have you considered Art Kagel's myimport (part of utils2_ak download)? It can
use external tables to speed up the import and is designed to use a dbexport
as the base.
--EEM
>-----Original Message-----
>From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
>GUSTAVO ECHENIQUE
>Sent: Thursday, November 03, 2011 8:46 AM
>To: ids@iiug.org
>Subject: Re: RE: Import Information quickly [25332]
>
>Hi Andrew, first of all thank you very much for your quick response.
>
>What I need is to import all the information generated by the "dbexport"
>and are more than 900 tables. Can you do that?
>
>A big hello and a new appreciation for you.
>
>Gustavo Echenique
>
>
>************************************************************************
>*******
> Forum Note: Use "Reply" to post a response in the discussion forum.
To import the entire database export created with dbexport you would use
the dbimport utility. However, if you want to import the records from one
of the .unl files that dbexport creates, yes you can use dbload. Dbload is
documented in the Migration Guide manual. The commandline is simple, but
the import instructions describing the file and the table into which to
place the records are described in a command file that dbload reads. See
the format described in the manual and if you have any remaining questions,
feel free to post them here.
Art
Art S. Kagel
Advanced DataTools (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 Thu, Nov 3, 2011 at 9:32 AM, GUSTAVO ECHENIQUE <
gustavo.echenique@cemdo.com.ar> wrote:
> Good morning gentlemen of the forum!
>
> I wanted to know if you can use the utility "dbload" to import the records
> generated by a "dbexport." And if the answer is yes, what is the syntax of
> the
> command?.
>
> Thank you very much in advance, and for dedicating your valuable time
> reading
> this message.
>
> Gustavo Echenique
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--14dae9341053ea7f7a04b0d4fae6
Hi Andrew, is not a silly question yours.
Because I do not use dbimport takes about 20 hours to import the entire base,
and do not have the time.
I have to work with a database exported on 31/07/2011 and I was wondering if
there was a much more rapid than the dbimport to import.
A hug.
Hi Everett! I did not know this utility, now tell me, look it and prove it. Tell you how I've done. Thank you very much for your information. A hug. Gustavo Echenique
Gustavo,
Well yes there are quicker ways but it requires more administration and
configuration work. I gather from another IIUG member that Art Kagel has a
script ( myimport ) that could be used for this purpose. I must admit to
having no knowledge of this utility so look on the IIUG website for more
details or perhaps another contributor will be kind enough to explain it. I
tend to mix dbload and HPL to do my large database lots. Its more work but
much faster. Don´t forget to split the creation of the tables and indexes and
other components out into different command files and to create the indexes
etc AFTER the data has been loaded. What version of Informix are you loading
data into?
> To: ids@iiug.org
> From: gustavo.echenique@cemdo.com.ar
> Subject: Re: RE: Import Information quickly [25338]
> Date: Thu, 3 Nov 2011 09:59:56 -0400
>
> Hi Andrew, is not a silly question yours.
>
> Because I do not use dbimport takes about 20 hours to import the entire base,
> and do not have the time.
>
> I have to work with a database exported on 31/07/2011 and I was wondering if
> there was a much more rapid than the dbimport to import.
>
> A hug.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
This is what I feared. Art thank you very much, Andrew and Everett for their responses. They have been very kind and very much appreciate your help. a hug. Gustavo Echenique
Gustavo, There are ways to speed up the import of data. For example run more than one load at a time. I tend to do 4 running at the same time. use multiple file systems as well. If you need help writing the DBLOAD or HPL commands then let me know. I have scripts to do it. Regards Andy G > To: ids@iiug.org > From: gustavo.echenique@cemdo.com.ar > Subject: Re: Import Information quickly [25341] > Date: Thu, 3 Nov 2011 10:15:02 -0400 > > This is what I feared. > > Art thank you very much, Andrew and Everett for their responses. > > They have been very kind and very much appreciate your help. > > a hug. > > Gustavo Echenique > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
Use the high performance loader. It runs about 10x faster than dbload.
j.
On Nov 3, 2011, at 10:06 AM, Andrew Grantham wrote:
> Gustavo,=20
>=20
> Well yes there are quicker ways but it requires more administration =
and=20
> configuration work. I gather from another IIUG member that Art Kagel =
has a=20
> script ( myimport ) that could be used for this purpose. I must admit =
to=20
> having no knowledge of this utility so look on the IIUG website for =
more=20
> details or perhaps another contributor will be kind enough to explain =
it. I=20
> tend to mix dbload and HPL to do my large database lots. Its more work =
but=20
> much faster. Don=B4t forget to split the creation of the tables and =
indexes and=20
> other components out into different command files and to create the =
indexes=20
> etc AFTER the data has been loaded. What version of Informix are you =
loading=20
> data into?=20
>=20
>> To: ids@iiug.org=20
>> From: gustavo.echenique@cemdo.com.ar=20
>> Subject: Re: RE: Import Information quickly [25338]=20
>> Date: Thu, 3 Nov 2011 09:59:56 -0400=20
>>=20
>> Hi Andrew, is not a silly question yours.=20
>>=20
>> Because I do not use dbimport takes about 20 hours to import the =
entire=20
> base,=20
>> and do not have the time.=20
>>=20
>> I have to work with a database exported on 31/07/2011 and I was =
wondering if=20
>> there was a much more rapid than the dbimport to import.=20
>>=20
>> A hug.=20
>>=20
>>=20
>>=20
> =
**************************************************************************=
*****=20
>> Forum Note: Use "Reply" to post a response in the discussion forum.=20=
>>=20
>=20
>=20
> =
**************************************************************************=
*****=20
> Forum Note: Use "Reply" to post a response in the discussion forum.=20=
>=20
Use dbimport! That is the inverse utility to dbexport.
Art
Art S. Kagel
Advanced DataTools (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 Thu, Nov 3, 2011 at 9:45 AM, GUSTAVO ECHENIQUE <
gustavo.echenique@cemdo.com.ar> wrote:
> Hi Andrew, first of all thank you very much for your quick response.
>
> What I need is to import all the information generated by the "dbexport"
> and
> are more than 900 tables. Can you do that?
>
> A big hello and a new appreciation for you.
>
> Gustavo Echenique
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--14dae9340e1743ba4a04b0d55d55
Andrew, you're right in assuming that I know of Art Kagel tool. With respect to the engine, is a TD6 IDS 7.31 for Windows. Greetings! Gustavo Echenique
You can get utils2_ak from the downloads section of the members area of the iiug.org website. You will have to compile it using esqlc and an ansi C compiler (Gnu-C or similar). I'm surprised Art didn't suggest it himself. --EEM >-----Original Message----- >From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of >GUSTAVO ECHENIQUE >Sent: Thursday, November 03, 2011 9:03 AM >To: ids@iiug.org >Subject: Re: RE: RE: Import Information quickly [25339] > >Hi Everett! > >I did not know this utility, now tell me, look it and prove it. > >Tell you how I've done. > >Thank you very much for your information. > >A hug. > >Gustavo Echenique > > >************************************************************************ >******* > Forum Note: Use "Reply" to post a response in the discussion forum.
Gustavo, Well good luck with your testing. Remember to think about using the HPL if possible in that version and parallel loading. It will speed things up significantly. Art Kagels utility may be a good alternative as well. May save you a lot of work. If you need help, ask. Regards Andy G. > To: ids@iiug.org > From: gustavo.echenique@cemdo.com.ar > Subject: Re: RE: Import Information quickly [25347] > Date: Thu, 3 Nov 2011 10:30:46 -0400 > > Andrew, you're right in assuming that I know of Art Kagel tool. > > With respect to the engine, is a TD6 IDS 7.31 for Windows. > > Greetings! > > Gustavo Echenique > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
Ok, forget myimport as being much faster. IDS 7.31 doesn't support external tables. --EEM >-----Original Message----- >From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of >Everett Mills >Sent: Thursday, November 03, 2011 9:33 AM >To: ids@iiug.org >Subject: RE: Import Information quickly [25348] > >You can get utils2_ak from the downloads section of the members area of >the iiug.org website. You will have to compile it using esqlc and an >ansi C compiler (Gnu-C or similar). >I'm surprised Art didn't suggest it himself. > >--EEM > >>-----Original Message----- >>From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of >>GUSTAVO ECHENIQUE >>Sent: Thursday, November 03, 2011 9:03 AM >>To: ids@iiug.org >>Subject: Re: RE: RE: Import Information quickly [25339] >> >>Hi Everett! >> >>I did not know this utility, now tell me, look it and prove it. >> >>Tell you how I've done. >> >>Thank you very much for your information. >> >>A hug. >> >>Gustavo Echenique >> >> >>*********************************************************************** >>* >>******* >> Forum Note: Use "Reply" to post a response in the discussion forum. > > >************************************************************************ >******* > Forum Note: Use "Reply" to post a response in the discussion forum.
As mentioned, get my myexport package which also contains the myimport
script. It is a drop in replacement for dbimport that runs much faster
optionally using external tables for importing the data which is the
fastest way to bring in the data. Myimport can also import multiple tables
in parallel which will make better use of the server's available resources
to pull in data as fast as possible. Myexport/myimport will also require
my package utils2_ak and optionally Jonathan Leffler's sqlcmd package (this
contains the default load utility that myimport uses, sqlreload, which
operates at about the same speed as dbimport) and Ravi Krishna's myonpload
package (needed if you want to have myimport use the hploader to import the
data).
Doing a parallel load from an export created with dbexport rather then
myexport will take a bit of work. Using the -E (external table) option
will also require a manual step (running myschema with the
--myexport-scripts or --myexport-express options to generate the import
scripts that would normally have been generated if you had used myexport
-E). This will only work, of course, if you still have access to the
original database so that myschema can read the database catalog tables.
If not, then you can either use the -HD or -HE options to myimport to have
it use the hploader (with myonpload) to get a faster load. If you have to
use the default load method (using sqlreload) then the speed of the load
will be about the same as using dbimport.
If you want to try using external table or even a parallel load and have
trouble email me directly (see my sig) and I'll try to help. Using these
options from a dbexport data set is not documented particularly well
<sorry>.
Art
Art S. Kagel
Advanced DataTools (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 Thu, Nov 3, 2011 at 9:59 AM, GUSTAVO ECHENIQUE <
gustavo.echenique@cemdo.com.ar> wrote:
> Hi Andrew, is not a silly question yours.
>
> Because I do not use dbimport takes about 20 hours to import the entire
> base,
> and do not have the time.
>
> I have to work with a database exported on 31/07/2011 and I was wondering
> if
> there was a much more rapid than the dbimport to import.
>
> A hug.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--14dae9340577d23c3d04b0d59027
OUCH, so no external tables. Check out my package utils4_ak contains a
bunch of AWK scripts for post-processing dbschema and myschema output to
create scripts that can affect all tables in a database. For example, one,
mkreload.awk, generates an SQL script to use the dbaccess LOAD command to
load a .unl file into a table for all tables listed in the schema file.
You could modify this to instead generate a shell script of sqlreload
command lines (from Jonathan Leffler's sqlcmd package) to load all tables.
Then you could edit the script so that <N> tables are loaded in parallel.
There is even a C utility in my myexport package, ak_launcher.c, which you
can use to control how many commands to run in parallel (run ak_launcher -?
to see usage). The ak_launcher.c utility reads shell commands from its
stdin and runs <N> copies at a time (configurable on the commandline), so
you could just use the awk script to output sqlreload commands and pipe the
output to "ak_launcher -m 5 -r 3" and it will launch 5 commands at a time
and add more when there are 3 left running. That is how myimport does it.
Art
Art S. Kagel
Advanced DataTools (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 Thu, Nov 3, 2011 at 10:30 AM, GUSTAVO ECHENIQUE <
gustavo.echenique@cemdo.com.ar> wrote:
> Andrew, you're right in assuming that I know of Art Kagel tool.
>
> With respect to the engine, is a TD6 IDS 7.31 for Windows.
>
> Greetings!
>
> Gustavo Echenique
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--14dae9340e1790b47904b0d5c023
I try not to push my stuff on folk, much as I like it. Also using myimport
with a dbexport data set only simple if you don't want to (or cannot) take
advantage of the advanced features like parallel loads and external table
loading. Then there's some prep work you have to do which is not well
documented.
Art
Art S. Kagel
Advanced DataTools (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 Thu, Nov 3, 2011 at 10:32 AM, Everett Mills <
Everett.Mills@nationalbeef.com> wrote:
> You can get utils2_ak from the downloads section of the members area of the
> iiug.org website. You will have to compile it using esqlc and an ansi C
> compiler (Gnu-C or similar).
> I'm surprised Art didn't suggest it himself.
>
> --EEM
>
> >-----Original Message-----
> >From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> >GUSTAVO ECHENIQUE
> >Sent: Thursday, November 03, 2011 9:03 AM
> >To: ids@iiug.org
> >Subject: Re: RE: RE: Import Information quickly [25339]
> >
> >Hi Everett!
> >
> >I did not know this utility, now tell me, look it and prove it.
> >
> >Tell you how I've done.
> >
> >Thank you very much for your information.
> >
> >A hug.
> >
> >Gustavo Echenique
> >
> >
> >************************************************************************
> >*******
> > Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--14dae93409554be12904b0d5ef76
But it can use the hploader and it is MUCH simpler than coding the loader yourself (something I avoid whenever possible)! Art Art S. Kagel Advanced DataTools (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 Thu, Nov 3, 2011 at 10:36 AM, Everett Mills < Everett.Mills@nationalbeef.com> wrote: > Ok, forget myimport as being much faster. IDS 7.31 doesn't support external > tables. > > --EEM > > >-----Original Message----- > >From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > >Everett Mills > >Sent: Thursday, November 03, 2011 9:33 AM > >To: ids@iiug.org > >Subject: RE: Import Information quickly [25348] > > > >You can get utils2_ak from the downloads section of the members area of > >the iiug.org website. You will have to compile it using esqlc and an > >ansi C compiler (Gnu-C or similar). > >I'm surprised Art didn't suggest it himself. > > > >--EEM > > > >>-----Original Message----- > >>From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > >>GUSTAVO ECHENIQUE > >>Sent: Thursday, November 03, 2011 9:03 AM > >>To: ids@iiug.org > >>Subject: Re: RE: RE: Import Information quickly [25339] > >> > >>Hi Everett! > >> > >>I did not know this utility, now tell me, look it and prove it. > >> > >>Tell you how I've done. > >> > >>Thank you very much for your information. > >> > >>A hug. > >> > >>Gustavo Echenique > >> > >> > >>*********************************************************************** > >>* > >>******* > >> 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. > > --14dae9340577f1638904b0d5f31e
Many, many thanks to all who have helped me.
I decided to use the "dbimport" to import the entire base.
Art, I promise you more time definitely prove your tool because I do not have
the same learning and take greater risks, however your excellent willingness
to help.
Again, thank you very much everyone!
Based on one of these emails, I think you said your problem is the
import speed. I didn't see any mention of setting the FET_BUF_SIZE
environment variable to 32000.
FET_BUF_SIZE=32000; export FET_BUF_SIZE
Worked wonders for me.
On 11/3/2011 12:21 PM, GUSTAVO ECHENIQUE wrote:
> Many, many thanks to all who have helped me.
>
> I decided to use the "dbimport" to import the entire base.
>
> Art, I promise you more time definitely prove your tool because I do not have
> the same learning and take greater risks, however your excellent willingness
> to help.
>
> Again, thank you very much everyone!
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>