Loading blobs
A user on Informix 11.70.FC2 (Solaris) wanted to unload and reload a 27GB table containing BLOBs quickly, in order to rebuild it with fragmentation; HPL's fast no-conversion format can't reload BLOBs. Art Kagel suggested either ALTER FRAGMENT ... INIT (fast, needs downtime) or copying rows directly with his dbcopy utility (from IIUG's utils2_ak), running several filtered copies in parallel to avoid flat files. He also confirmed external tables work if you specify a blobdir in the datafiles clause, using the same tool for export and import, and advised loading into a RAW table then converting to STANDARD and archiving. The poster thanked him; no test results were reported.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
All, Informix 11.70.FC2 on Sun Solaris. I would like to reload a table that contains BLOBs using something like the High Performance Loader or EXTERNAL tables but nothing I have managed to configure so far works with any amount of speed. I believe I cannot use no-conversion format because loading BLOBs has restrictions. Anyway I am seeking your experiences on loading BLOBs and how to make it as quick as possible. All thoughts and advice welcome.
Question: What's the full mission you need to accomplish? For example, are you reloading blobs because these were exported then deleted and now you need them back? Or is it that you exported the records to move them from one database or server to another database or server and the reload is too slow? How were they exported (using what tools)? If what you REALLY need to do is to move the blobs from one server to another, consider copying the data directly using my dbcopy utility. It is very fast (almost as fast as the HPLoader or external files in general) and you save the time and overhead of writing the data to disk as flat files and reading it back. You can also divide the data in the table using filters into multiple subsets and run many copies of dbcopy copying all of those subsets in parallel which will make the copy MUCH faster than either HPLoader or external tables. Dbcopy is included in the package utils2_ak which you can download from the IIUG Software Repository (www.iiug.org/software). 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, Feb 21, 2013 at 5:06 AM, Andrew Grantham <agrantha@hotmail.com>wrote: > All, > > Informix 11.70.FC2 on Sun Solaris. > > I would like to reload a table that contains BLOBs using something like the > High Performance Loader or EXTERNAL tables but nothing I have managed to > configure so far works with any amount of speed. I believe I cannot use > no-conversion format because loading BLOBs has restrictions. Anyway I am > seeking your experiences on loading BLOBs and how to make it as quick as > possible. All thoughts and advice welcome. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f22beb990a30b04d63c2e1f
Art, Thanks for your email. I have a table which has reached 27Gb in one dbspace and I would like to introduce fragmentation to it. At the moment I am simplying unloading the data using the HPL and then reloading it into a shadow copy of the original table using HPL or external tables. Once the work is done the objects are created and the tables switched around. Nice and simple. I am trying to determine the fastest way of doing the work. Using the no-conversion utility in HPL is very quick to unload but I cannot use it to load back up as the table contains blobs unless you can tell me differently? > To: ids@iiug.org > From: art.kagel@gmail.com > Subject: Re: Loading blobs [29550] > Date: Thu, 21 Feb 2013 08:38:00 -0500 > > Question: What's the full mission you need to accomplish? For example, are > you reloading blobs because these were exported then deleted and now you > need them back? Or is it that you exported the records to move them from > one database or server to another database or server and the reload is too > slow? How were they exported (using what tools)? > > If what you REALLY need to do is to move the blobs from one server to > another, consider copying the data directly using my dbcopy utility. It is > very fast (almost as fast as the HPLoader or external files in general) and > you save the time and overhead of writing the data to disk as flat files > and reading it back. You can also divide the data in the table using > filters into multiple subsets and run many copies of dbcopy copying all of > those subsets in parallel which will make the copy MUCH faster than either > HPLoader or external tables. > > Dbcopy is included in the package utils2_ak which you can download from the > IIUG Software Repository (www.iiug.org/software). > > 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, Feb 21, 2013 at 5:06 AM, Andrew Grantham <agrantha@hotmail.com>wrote: > > > All, > > > > Informix 11.70.FC2 on Sun Solaris. > > > > I would like to reload a table that contains BLOBs using something like the > > High Performance Loader or EXTERNAL tables but nothing I have managed to > > configure so far works with any amount of speed. I believe I cannot use > > no-conversion format because loading BLOBs has restrictions. Anyway I am > > seeking your experiences on loading BLOBs and how to make it as quick as > > possible. All thoughts and advice welcome. > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --e89a8f22beb990a30b04d63c2e1f > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
OK, two suggestions:
1. If you can take the downtime, use ALTER FRAGMENT .... INIT FRAGENT BY
... to move the table to a fragmented storage scheme. This is pretty fast.
2. If you cannot take the downtime for #1, then use multiple copies of
dbcopy. You can use different SELECT statements in each filtering on the
columns used in the fragmentation expression (assuming you are not
fragmenting BY ROUND ROBIN) so that each one is copying the data for one
fragment. Or if you have lots of CPU resources available, you can use an
additional indexed key to have multiple dbcopy's loading to each fragment.
The easiest way to determine what ranges to use for the secondary key is to
run dbschema ... -hd <table> and look at the distribution of that key. You
will avoid all of the time needed to write out the flat files and to read
them back in. Nothing will be faster than this! Did this at a client
recently and a table that took over 23 hours in testing using unload/load
was copied in under 4 hours with 20 copies of dbcopy running. This was
between two servers on different systems, so the readers weren't competing
with writers for resources. Your results in a single server won't be quite
so dramatic unless you have 32 cores or so ;-). Also, copying BLOBs dbcopy
ends up moving blob data one row at a time while for tables without blobs
it uses array fetching to read hundreds of rows in a single fetch and
INSERT CURSORS to insert blocks of hundreds of rows at once. So, not so
dramatic as my example, but as I said, faster than anything else out there.
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, Feb 21, 2013 at 8:48 AM, Andrew Grantham <agrantha@hotmail.com>wrote:
> Art,
>
> Thanks for your email. I have a table which has reached 27Gb in one dbspace
> and I would like to introduce fragmentation to it. At the moment I am
> simplying unloading the data using the HPL and then reloading it into a
> shadow
> copy of the original table using HPL or external tables. Once the work is
> done
> the objects are created and the tables switched around. Nice and simple. I
> am
> trying to determine the fastest way of doing the work. Using the
> no-conversion
> utility in HPL is very quick to unload but I cannot use it to load back up
> as
> the table contains blobs unless you can tell me differently?
>
> > To: ids@iiug.org
> > From: art.kagel@gmail.com
> > Subject: Re: Loading blobs [29550]
> > Date: Thu, 21 Feb 2013 08:38:00 -0500
> >
> > Question: What's the full mission you need to accomplish? For example,
> are
> > you reloading blobs because these were exported then deleted and now you
> > need them back? Or is it that you exported the records to move them from
> > one database or server to another database or server and the reload is
> too
> > slow? How were they exported (using what tools)?
> >
> > If what you REALLY need to do is to move the blobs from one server to
> > another, consider copying the data directly using my dbcopy utility. It
> is
> > very fast (almost as fast as the HPLoader or external files in general)
> and
> > you save the time and overhead of writing the data to disk as flat files
> > and reading it back. You can also divide the data in the table using
> > filters into multiple subsets and run many copies of dbcopy copying all
> of
> > those subsets in parallel which will make the copy MUCH faster than
> either
> > HPLoader or external tables.
> >
> > Dbcopy is included in the package utils2_ak which you can download from
> the
> > IIUG Software Repository (www.iiug.org/software).
> >
> > 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, Feb 21, 2013 at 5:06 AM, Andrew Grantham
> <agrantha@hotmail.com>wrote:
> >
> > > All,
> > >
> > > Informix 11.70.FC2 on Sun Solaris.
> > >
> > > I would like to reload a table that contains BLOBs using something like
> the
> > > High Performance Loader or EXTERNAL tables but nothing I have managed
> to
> > > configure so far works with any amount of speed. I believe I cannot use
> > > no-conversion format because loading BLOBs has restrictions. Anyway I
> am
> > > seeking your experiences on loading BLOBs and how to make it as quick
> as
> > > possible. All thoughts and advice welcome.
> > >
> > >
> > >
> > >
> >
>
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --e89a8f22beb990a30b04d63c2e1f
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--485b390f7b200a530204d63c91af
Art,
I appreciate the dbcopy command is good but I really would like to know if the
HPL or even external tables could be used to unload and load BLOBs in a timely
fashion. For example by using one of the Informix internal formats, altering
the plconfig file etc etc.
Thanks
> To: ids@iiug.org
> From: art.kagel@gmail.com
> Subject: Re: Loading blobs [29553]
> Date: Thu, 21 Feb 2013 09:05:31 -0500
>
> OK, two suggestions:
>
> 1. If you can take the downtime, use ALTER FRAGMENT .... INIT FRAGENT BY
>
> .... to move the table to a fragmented storage scheme. This is pretty fast.
>
> 2. If you cannot take the downtime for #1, then use multiple copies of
>
> dbcopy. You can use different SELECT statements in each filtering on the
>
> columns used in the fragmentation expression (assuming you are not
>
> fragmenting BY ROUND ROBIN) so that each one is copying the data for one
>
> fragment. Or if you have lots of CPU resources available, you can use an
>
> additional indexed key to have multiple dbcopy's loading to each fragment.
>
> The easiest way to determine what ranges to use for the secondary key is to
>
> run dbschema ... -hd <table> and look at the distribution of that key. You
>
> will avoid all of the time needed to write out the flat files and to read
>
> them back in. Nothing will be faster than this! Did this at a client
>
> recently and a table that took over 23 hours in testing using unload/load
>
> was copied in under 4 hours with 20 copies of dbcopy running. This was
>
> between two servers on different systems, so the readers weren't competing
>
> with writers for resources. Your results in a single server won't be quite
>
> so dramatic unless you have 32 cores or so ;-). Also, copying BLOBs dbcopy
>
> ends up moving blob data one row at a time while for tables without blobs
>
> it uses array fetching to read hundreds of rows in a single fetch and
>
> INSERT CURSORS to insert blocks of hundreds of rows at once. So, not so
>
> dramatic as my example, but as I said, faster than anything else out there.
>
> 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, Feb 21, 2013 at 8:48 AM, Andrew Grantham <agrantha@hotmail.com>wrote:
>
> > Art,
> >
> > Thanks for your email. I have a table which has reached 27Gb in one dbspace
> > and I would like to introduce fragmentation to it. At the moment I am
> > simplying unloading the data using the HPL and then reloading it into a
> > shadow
> > copy of the original table using HPL or external tables. Once the work is
> > done
> > the objects are created and the tables switched around. Nice and simple. I
> > am
> > trying to determine the fastest way of doing the work. Using the
> > no-conversion
> > utility in HPL is very quick to unload but I cannot use it to load back up
> > as
> > the table contains blobs unless you can tell me differently?
> >
> > > To: ids@iiug.org
> > > From: art.kagel@gmail.com
> > > Subject: Re: Loading blobs [29550]
> > > Date: Thu, 21 Feb 2013 08:38:00 -0500
> > >
> > > Question: What's the full mission you need to accomplish? For example,
> > are
> > > you reloading blobs because these were exported then deleted and now you
> > > need them back? Or is it that you exported the records to move them from
> > > one database or server to another database or server and the reload is
> > too
> > > slow? How were they exported (using what tools)?
> > >
> > > If what you REALLY need to do is to move the blobs from one server to
> > > another, consider copying the data directly using my dbcopy utility. It
> > is
> > > very fast (almost as fast as the HPLoader or external files in general)
> > and
> > > you save the time and overhead of writing the data to disk as flat files
> > > and reading it back. You can also divide the data in the table using
> > > filters into multiple subsets and run many copies of dbcopy copying all
> > of
> > > those subsets in parallel which will make the copy MUCH faster than
> > either
> > > HPLoader or external tables.
> > >
> > > Dbcopy is included in the package utils2_ak which you can download from
> > the
> > > IIUG Software Repository (www.iiug.org/software).
> > >
> > > 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, Feb 21, 2013 at 5:06 AM, Andrew Grantham
> > <agrantha@hotmail.com>wrote:
> > >
> > > > All,
> > > >
> > > > Informix 11.70.FC2 on Sun Solaris.
> > > >
> > > > I would like to reload a table that contains BLOBs using something like
> > the
> > > > High Performance Loader or EXTERNAL tables but nothing I have managed
> > to
> > > > configure so far works with any amount of speed. I believe I cannot use
> > > > no-conversion format because loading BLOBs has restrictions. Anyway I
> > am
> > > > seeking your experiences on loading BLOBs and how to make it as quick
> > as
> > > > possible. All thoughts and advice welcome.
> > > >
> > > >
> > > >
> > > >
> > >
> >
> >
>
*******************************************************************************
> > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >
> > > >
> > >
> > > --e89a8f22beb990a30b04d63c2e1f
> > >
> > >
> > >
> >
> >
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> >
> >
> >
> >
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --485b390f7b200a530204d63c91af
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Yes, the direct answer is yes, you can certainly use external tables for
the export and import (best to use the same tool on both ends because of
differences in how the blobs are handled). I've never used HPLoader for
blobs though, but it should be doable. Again, you may only be able to load
the data with blobs using HPL if they were exported using HPL.
For external tables, you have to give a BLOBDIR to hold the blobs
themselves which are exported separately from the non-blob columns:
create external table table_with_blobs_ext sameas table_with_blobs
using (datafiles("disk:/file/path/for/non_blob/data.unl,
blobdir:/path/to/directory/for/blobs"), format delimited, delimiter "|" );
Then you would:
insert into table_with_blobs_ext
select * from table_with_blobs;
insert into fragmented_table_with_blobs
select * from table_with_blobs_ext;
I would create the fragmented table in RAW mode, load it, then convert it
to STANDARD mode and take an archive. That will make a big difference in
load speed.
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, Feb 21, 2013 at 9:36 AM, Andrew Grantham <agrantha@hotmail.com>wrote:
> Art,
>
> I appreciate the dbcopy command is good but I really would like to know if
> the
> HPL or even external tables could be used to unload and load BLOBs in a
> timely
> fashion. For example by using one of the Informix internal formats,
> altering
> the plconfig file etc etc.
>
> Thanks
>
> > To: ids@iiug.org
> > From: art.kagel@gmail.com
> > Subject: Re: Loading blobs [29553]
> > Date: Thu, 21 Feb 2013 09:05:31 -0500
> >
> > OK, two suggestions:
> >
> > 1. If you can take the downtime, use ALTER FRAGMENT .... INIT FRAGENT BY
> >
> > .... to move the table to a fragmented storage scheme. This is pretty
> fast.
> >
> > 2. If you cannot take the downtime for #1, then use multiple copies of
> >
> > dbcopy. You can use different SELECT statements in each filtering on the
> >
> > columns used in the fragmentation expression (assuming you are not
> >
> > fragmenting BY ROUND ROBIN) so that each one is copying the data for one
> >
> > fragment. Or if you have lots of CPU resources available, you can use an
> >
> > additional indexed key to have multiple dbcopy's loading to each
> fragment.
> >
> > The easiest way to determine what ranges to use for the secondary key is
> to
> >
> > run dbschema ... -hd <table> and look at the distribution of that key.
> You
> >
> > will avoid all of the time needed to write out the flat files and to read
> >
> > them back in. Nothing will be faster than this! Did this at a client
> >
> > recently and a table that took over 23 hours in testing using unload/load
> >
> > was copied in under 4 hours with 20 copies of dbcopy running. This was
> >
> > between two servers on different systems, so the readers weren't
> competing
> >
> > with writers for resources. Your results in a single server won't be
> quite
> >
> > so dramatic unless you have 32 cores or so ;-). Also, copying BLOBs
> dbcopy
> >
> > ends up moving blob data one row at a time while for tables without blobs
> >
> > it uses array fetching to read hundreds of rows in a single fetch and
> >
> > INSERT CURSORS to insert blocks of hundreds of rows at once. So, not so
> >
> > dramatic as my example, but as I said, faster than anything else out
> there.
> >
> > 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, Feb 21, 2013 at 8:48 AM, Andrew Grantham
> <agrantha@hotmail.com>wrote:
> >
> > > Art,
> > >
> > > Thanks for your email. I have a table which has reached 27Gb in one
> dbspace
> > > and I would like to introduce fragmentation to it. At the moment I am
> > > simplying unloading the data using the HPL and then reloading it into a
> > > shadow
> > > copy of the original table using HPL or external tables. Once the work
> is
> > > done
> > > the objects are created and the tables switched around. Nice and
> simple. I
> > > am
> > > trying to determine the fastest way of doing the work. Using the
> > > no-conversion
> > > utility in HPL is very quick to unload but I cannot use it to load
> back up
> > > as
> > > the table contains blobs unless you can tell me differently?
> > >
> > > > To: ids@iiug.org
> > > > From: art.kagel@gmail.com
> > > > Subject: Re: Loading blobs [29550]
> > > > Date: Thu, 21 Feb 2013 08:38:00 -0500
> > > >
> > > > Question: What's the full mission you need to accomplish? For
> example,
> > > are
> > > > you reloading blobs because these were exported then deleted and now
> you
> > > > need them back? Or is it that you exported the records to move them
> from
> > > > one database or server to another database or server and the reload
> is
> > > too
> > > > slow? How were they exported (using what tools)?
> > > >
> > > > If what you REALLY need to do is to move the blobs from one server to
> > > > another, consider copying the data directly using my dbcopy utility.
> It
> > > is
> > > > very fast (almost as fast as the HPLoader or external files in
> general)
> > > and
> > > > you save the time and overhead of writing the data to disk as flat
> files
> > > > and reading it back. You can also divide the data in the table using
> > > > filters into multiple subsets and run many copies of dbcopy copying
> all
> > > of
> > > > those subsets in parallel which will make the copy MUCH faster than
> > > either
> > > > HPLoader or external tables.
> > > >
> > > > Dbcopy is included in the package utils2_ak which you can download
> from
> > > the
> > > > IIUG Software Repository (www.iiug.org/software).
> > > >
> > > > 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 d
Art,
Okay thanks for your help.
Regards
Andy G
> To: ids@iiug.org
> From: art.kagel@gmail.com
> Subject: Re: Loading blobs [29555]
> Date: Thu, 21 Feb 2013 09:46:52 -0500
>
> Yes, the direct answer is yes, you can certainly use external tables for
> the export and import (best to use the same tool on both ends because of
> differences in how the blobs are handled). I've never used HPLoader for
> blobs though, but it should be doable. Again, you may only be able to load
> the data with blobs using HPL if they were exported using HPL.
>
> For external tables, you have to give a BLOBDIR to hold the blobs
> themselves which are exported separately from the non-blob columns:
>
> create external table table_with_blobs_ext sameas table_with_blobs
> using (datafiles("disk:/file/path/for/non_blob/data.unl,
> blobdir:/path/to/directory/for/blobs"), format delimited, delimiter "|" );
>
> Then you would:
>
> insert into table_with_blobs_ext
> select * from table_with_blobs;>
> insert into fragmented_table_with_blobs
> select * from table_with_blobs_ext;>
> I would create the fragmented table in RAW mode, load it, then convert it
> to STANDARD mode and take an archive. That will make a big difference in
> load speed.
>
> 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, Feb 21, 2013 at 9:36 AM, Andrew Grantham <agrantha@hotmail.com>wrote:
>
> > Art,
> >
> > I appreciate the dbcopy command is good but I really would like to know if
> > the
> > HPL or even external tables could be used to unload and load BLOBs in a
> > timely
> > fashion. For example by using one of the Informix internal formats,
> > altering
> > the plconfig file etc etc.
> >
> > Thanks
> >
> > > To: ids@iiug.org
> > > From: art.kagel@gmail.com
> > > Subject: Re: Loading blobs [29553]
> > > Date: Thu, 21 Feb 2013 09:05:31 -0500
> > >
> > > OK, two suggestions:
> > >
> > > 1. If you can take the downtime, use ALTER FRAGMENT .... INIT FRAGENT BY
> > >
> > > .... to move the table to a fragmented storage scheme. This is pretty
> > fast.
> > >
> > > 2. If you cannot take the downtime for #1, then use multiple copies of
> > >
> > > dbcopy. You can use different SELECT statements in each filtering on the
> > >
> > > columns used in the fragmentation expression (assuming you are not
> > >
> > > fragmenting BY ROUND ROBIN) so that each one is copying the data for one
> > >
> > > fragment. Or if you have lots of CPU resources available, you can use an
> > >
> > > additional indexed key to have multiple dbcopy's loading to each
> > fragment.
> > >
> > > The easiest way to determine what ranges to use for the secondary key is
> > to
> > >
> > > run dbschema ... -hd <table> and look at the distribution of that key.
> > You
> > >
> > > will avoid all of the time needed to write out the flat files and to read
> > >
> > > them back in. Nothing will be faster than this! Did this at a client
> > >
> > > recently and a table that took over 23 hours in testing using unload/load
> > >
> > > was copied in under 4 hours with 20 copies of dbcopy running. This was
> > >
> > > between two servers on different systems, so the readers weren't
> > competing
> > >
> > > with writers for resources. Your results in a single server won't be
> > quite
> > >
> > > so dramatic unless you have 32 cores or so ;-). Also, copying BLOBs
> > dbcopy
> > >
> > > ends up moving blob data one row at a time while for tables without blobs
> > >
> > > it uses array fetching to read hundreds of rows in a single fetch and
> > >
> > > INSERT CURSORS to insert blocks of hundreds of rows at once. So, not so
> > >
> > > dramatic as my example, but as I said, faster than anything else out
> > there.
> > >
> > > 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, Feb 21, 2013 at 8:48 AM, Andrew Grantham
> > <agrantha@hotmail.com>wrote:
> > >
> > > > Art,
> > > >
> > > > Thanks for your email. I have a table which has reached 27Gb in one
> > dbspace
> > > > and I would like to introduce fragmentation to it. At the moment I am
> > > > simplying unloading the data using the HPL and then reloading it into a
> > > > shadow
> > > > copy of the original table using HPL or external tables. Once the work
> > is
> > > > done
> > > > the objects are created and the tables switched around. Nice and
> > simple. I
> > > > am
> > > > trying to determine the fastest way of doing the work. Using the
> > > > no-conversion
> > > > utility in HPL is very quick to unload but I cannot use it to load
> > back up
> > > > as
> > > > the table contains blobs unless you can tell me differently?
> > > >
> > > > > To: ids@iiug.org
> > > > > From: art.kagel@gmail.com
> > > > > Subject: Re: Loading blobs [29550]
> > > > > Date: Thu, 21 Feb 2013 08:38:00 -0500
> > > > >
> > > > > Question: What's the full mission you need to accomplish? For
> > example,
> > > > are
> > > > > you reloading blobs because these were exported then deleted and now
> > you
> > > > > need them back? Or is it that you exported the records to move them
> > from
> > > > > one database or server to another database or server and the reload
> > is
> > > > too
> > > > > slow? How were they exported (using what tools)?
> > > > >
> > > > > If what you REALLY need to do is to move the blobs from one server to
> > > > > another, consider copying the data directly using my dbcopy utility.
> > It
> > > > is
> > > > > very fast (almost as fast as the HPLoader or external files in
> > general)
> > > > and
> > > > > you save the time and overhead of writing the data to disk as flat
> > files
> > > > > and reading it back. You can also divide the data in the table using
> > > > > filters into multiple subsets and run many copies of dbcopy copying
> > all
> > > > of
> > > > > those subsets in parallel which will make the copy MUCH faster than
> > > > either
> > > > > HPLoader or external tables.
> > > > >
> > > > > Dbcopy is included in the package utils2_ak whic