IDS Forum

RE: Loading blobs

Posted By: Andrew Grantham
Date: Thursday, 21 February 2013, at 9:53 a.m.

In Response To: Re: Loading blobs (Art Kagel)

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 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.
> > >
> >
> >
> >
> >
> *******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --e89a8f22beb9b824c804d63d242a
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>

Messages In This Thread

• Loading blobs
  Andrew Grantham -- Thursday, 21 February 2013, at 5:06 a.m.
  • Re: Loading blobs
    Art Kagel -- Thursday, 21 February 2013, at 8:38 a.m.
    • RE: Loading blobs
      Andrew Grantham -- Thursday, 21 February 2013, at 8:48 a.m.
      • Re: Loading blobs
        Art Kagel -- Thursday, 21 February 2013, at 9:05 a.m.
        • RE: Loading blobs
          Andrew Grantham -- Thursday, 21 February 2013, at 9:36 a.m.
          • Re: Loading blobs
            Art Kagel -- Thursday, 21 February 2013, at 9:46 a.m.
            • RE: Loading blobs
              Andrew Grantham -- Thursday, 21 February 2013, at 9:53 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.