Prev: heavy inserts and bufferpool
Next: TRANSLATE function
From: peter on 29 Jun 2008 06:09
On Jun 29, 7:40 pm, "Okonita via DBMonster.com" <u36825(a)uwe> wrote:
> What do you mean by "For example, is there enough space to do a inline re-org
> or should temporary area be used". ?
>
> If you have space to do a reorg, you have space to do a reorg in-line/off-
> line, does it matter?
> If you don't have enough space, you don't have enough space. Both reorg
> method need space to work with, right? Please correct me in this matter as I
> am still learning some of the finer points of this tool...
>
> Thanks
>
>
>
> peter wrote:
> >> Hello Peter,
> >> Thanks for the post. It confirms what seems to be happening here especially
> >[quoted text clipped - 39 lines]
> >> --
> >> Message posted viahttp://www.dbmonster.com
>
> >The script evolved into a java UDF which does a lot more than just
> >selecting a re-org list.  It works out an optimal schedule for a re-
> >org window based on past performance details, resources required,
> >running multiple streams and so on.  For example, is there enough
> >space to do a inline re-org or should temporary area be used.  Has
> >exclusion lists.  Some tables such data propagator control table or
> >using table for event monitors cause issues.   Don´t mind telling
> >people what it does and why but I want to maintain ownership of the
> >techniques I have used for obvious reasons.  Sorry.
>
> --
> Message posted viahttp://www.dbmonster.com

I should have said inplace. Offline reorg needs to create an new
image. If it can't fit into the tablespace (inplace) then you have to
build the image in another tablespace and once the image is built the
old image can be removed. The new image has to be brought back into
the orginal tablespace. Obviously takes more time but means you don't
have to have large tablespaces. When your tables are large you don't
what to have double allocation just to do re-orgs. With automatic
storage management this equation changes. Note there are the free
space bit map issues to understand. If the table space isn't
specified as large you may hit the the table space size limit.

The other issue is with index sorts. If it is a big index it will use
temporary area for the sorts. If not enough area is avaliable it will
fail. Running several at once will cause more demand on temporary
tablespace.
From: Okonita via DBMonster.com on 29 Jun 2008 16:30
Got it and thank you.

peter wrote:
>> What do you mean by "For example, is there enough space to do a inline re-org
>> or should temporary area be used". ?
>[quoted text clipped - 25 lines]
>> --
>> Message posted viahttp://www.dbmonster.com
>
>I should have said inplace. Offline reorg needs to create an new
>image. If it can't fit into the tablespace (inplace) then you have to
>build the image in another tablespace and once the image is built the
>old image can be removed. The new image has to be brought back into
>the orginal tablespace. Obviously takes more time but means you don't
>have to have large tablespaces. When your tables are large you don't
>what to have double allocation just to do re-orgs. With automatic
>storage management this equation changes. Note there are the free
>space bit map issues to understand. If the table space isn't
>specified as large you may hit the the table space size limit.
>
>The other issue is with index sorts. If it is a big index it will use
>temporary area for the sorts. If not enough area is avaliable it will
>fail. Running several at once will cause more demand on temporary
>tablespace.

--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums.aspx/ibm-db2/200806/1

First  |  Prev  | 
Pages: 1 2
Prev: heavy inserts and bufferpool
Next: TRANSLATE function