How can I resize a chunk?
The poster wanted to shrink an existing 50GB chunk that only needs 1GB. Consensus: chunks are fixed-size and cannot be resized in place (you can only add further chunks, e.g. using an offset on the same device). The recommended workaround is to create a new, smaller dbspace and move everything with ALTER FRAGMENT ON TABLE <table> INIT IN <newdbspace> for each table, then drop the old dbspace/chunk - possible only if it holds no database catalogs and isn't rootdbs. Caveats raised: needs exclusive access/downtime, watch logical log usage and long transactions (consider turning off logging or using HPL via pipes for big tables). One posted example wrongly named the source dbspace as the target and was corrected.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello, is it possible to resize a chunk? I know i can do it the long way by exporting the data, drop tables, drop the chunks, create new chunks of smaller size, recreate dropped schema, import data. However this is a very long, convoluted and error prone process. So does anyone know how to resize an existing chunk?
You can't. What you can do is to add another chunk from the same disk structure using an offset to start the new chunk at the end of the original one. Art On Thu, Oct 23, 2008 at 9:14 AM, ANDREW LEMIN <a_lemin@hotmail.com> wrote: > Hello, is it possible to resize a chunk? > I know i can do it the long way by exporting the data, drop tables, drop > the > chunks, create new chunks of smaller size, recreate dropped schema, import > data. However this is a very long, convoluted and error prone process. > > So does anyone know how to resize an existing chunk? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.
Sorry i wasn't very clear. I actually want to shrink a chunk. We have defined a chunk of 50GB, but it only needs 1GB !!! Hence i would like to shrink it. Can i move data from one chunk into another allowing the original to be dropped? Thanks in advance.
You certainly can do it,But it will require exclusive access. Create a new dbspace with the new chunk, and try the alter fragment for (table/index) in {new dbspace} ... for each partition All in just one transaction. Not messing with permission, drops nor creates. Walter Milan DBA -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of ANDREW LEMIN Sent: Thursday, October 23, 2008 10:08 AM To: ids@iiug.org Subject: Re: How can I resize a chunk? [13772] Sorry i wasn't very clear. I actually want to shrink a chunk. We have defined a chunk of 50GB, but it only needs 1GB !!! Hence i would like to shrink it. Can i move data from one chunk into another allowing the original to be dropped? Thanks in advance. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.
Rereading my previous post, there are a couple of assumptions I made, if you want later to drop de chunk and there is just one chunk on that dbspace - It must not be part of root dbspace, - No Database was created on that dbspace Walter Milan DBA -----Original Message----- From: Walter Milan Sent: Thursday, October 23, 2008 10:29 AM To: 'ids@iiug.org' Subject: RE: How can I resize a chunk? [13772] You certainly can do it,But it will require exclusive access. Create a new dbspace with the new chunk, and try the alter fragment for (table/index) in {new dbspace} ... for each partition All in just one transaction. Not messing with permission, drops nor creates. Walter Milan DBA -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of ANDREW LEMIN Sent: Thursday, October 23, 2008 10:08 AM To: ids@iiug.org Subject: Re: How can I resize a chunk? [13772] Sorry i wasn't very clear. I actually want to shrink a chunk. We have defined a chunk of 50GB, but it only needs 1GB !!! Hence i would like to shrink it. Can i move data from one chunk into another allowing the original to be dropped? Thanks in advance. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.
ALTER FRAGMENT ON TABLE <tablename> INIT IN <newdbspace>;
If you do that for all of the tables in the dbspace containing that chunk
(or for all tables that reside in the oversized chunk as long as it is not
the first chunk in the dbspace) you want to drop, and there are no system
catalog tables there (ie no databases defined in the dbspace) then you
should be able to drop the chunk or the entire dbspace.
Art
On Thu, Oct 23, 2008 at 11:07 AM, ANDREW LEMIN <a_lemin@hotmail.com> wrote:
> Sorry i wasn't very clear. I actually want to shrink a chunk.
> We have defined a chunk of 50GB, but it only needs 1GB !!!
> Hence i would like to shrink it.
>
> Can i move data from one chunk into another allowing the original to be
> dropped?
>
> Thanks in advance.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
Hi Andrew, as everyone else already pointed, you will need downtime for this. If using ALTER FRAGMENT, you have to consider your table size too. For large tables (whatever that might mean in your environment) it uses a lot of logical logs (since it has to effectively copy the table). Plus, if you do it all in one transaction as suggested, you are risking running into long tx. This however depends on your logical log space configured and table sizes. So first check these before you continue. If you do have such situation, I would recommend express HPL load through pipes using no conversion job. This cuts down downtime to the minimum. There are limitations when you can use it, please refer the manuals. Regards Davorin
Andrew,
These are steps you could use which should do what you want
1. create a new dbspace, newdbs_1Gb
2. identify all tables in the 50G_dbspace
3. move tables from 50Gb_dbspace to newdbs_1Gb as followings
(turn off database logging)
set pdqpriority 20;
alter fragment on table table1 init in 50Bb_dbspace;
alter fragment on table table2 init in 50Bb_dbspace;
alter fragment on table table3 init in 50Bb_dbspace;
...
4. turn back on database logging
5. drop the 50Gb_dbspace
If your data is only 1Gb or less and depending on how powerful your system is,
the actual moving data from the old dbspace to a new dbspace shouldn't take
you more 30, 45 minutes.
Good luck
________________________________
From: ANDREW LEMIN <a_lemin@hotmail.com>
To: ids@iiug.org
Sent: Thursday, October 23, 2008 11:07:30 AM
Subject: Re: How can I resize a chunk? [13772]
Sorry i wasn't very clear. I actually want to shrink a chunk.
We have defined a chunk of 50GB, but it only needs 1GB !!!
Hence i would like to shrink it.
Can i move data from one chunk into another allowing the original to be
dropped?
Thanks in advance.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Sorry KERN, but the statement to move table are wrong!
Andrew:
run these statements , replacing 50Bb_dbspace by newdbs_1Gb.
If you'll run the statements that Kern wrote you'll receive an error. You
cannot move a table to the same dbspace.
Best regards. Roberto FERRONATO
> To: ids@iiug.org
> From: kern_doe@yahoo.com
> Subject: Re: How can I resize a chunk? [13777]
> Date: Thu, 23 Oct 2008 12:20:01 -0400
>
> Andrew,
> These are steps you could use which should do what you want
> 1. create a new dbspace, newdbs_1Gb
> 2. identify all tables in the 50G_dbspace
> 3. move tables from 50Gb_dbspace to newdbs_1Gb as followings
>
> (turn off database logging)
>
> set pdqpriority 20;>
> alter fragment on table table1 init in 50Bb_dbspace;>
> alter fragment on table table2 init in 50Bb_dbspace;>
> alter fragment on table table3 init in 50Bb_dbspace;>
> ....
> 4. turn back on database logging
> 5. drop the 50Gb_dbspace
>
> If your data is only 1Gb or less and depending on how powerful your system
is,
> the actual moving data from the old dbspace to a new dbspace shouldn't take
> you more 30, 45 minutes.
> Good luck
>
> ________________________________
> From: ANDREW LEMIN <a_lemin@hotmail.com>
> To: ids@iiug.org
> Sent: Thursday, October 23, 2008 11:07:30 AM
> Subject: Re: How can I resize a chunk? [13772]
>
> Sorry i wasn't very clear. I actually want to shrink a chunk.
> We have defined a chunk of 50GB, but it only needs 1GB !!!
> Hence i would like to shrink it.
>
> Can i move data from one chunk into another allowing the original to be
> dropped?
>
> Thanks in advance.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
_________________________________________________________________
Discover the new Windows Vista
http://search.msn.com/results.aspx?q=windows+vista&mkt=en-US&form=QBRE
Good catch Roberto, it should be:
alter fragment on table table3 init in newdbs_1Gb;...
Thanks.
________________________________
From: R Fo <roeferr@hotmail.com>
To: ids@iiug.org
Sent: Thursday, October 23, 2008 12:24:49 PM
Subject: RE: How can I resize a chunk? [13778]
Sorry KERN, but the statement to move table are wrong!
Andrew:
run these statements , replacing 50Bb_dbspace by newdbs_1Gb.
If you'll run the statements that Kern wrote you'll receive an error. You
cannot move a table to the same dbspace.
Best regards. Roberto FERRONATO
> To: ids@iiug.org
> From: kern_doe@yahoo.com
> Subject: Re: How can I resize a chunk? [13777]
> Date: Thu, 23 Oct 2008 12:20:01 -0400
>
> Andrew,
> These are steps you could use which should do what you want
> 1. create a new dbspace, newdbs_1Gb
> 2. identify all tables in the 50G_dbspace
> 3. move tables from 50Gb_dbspace to newdbs_1Gb as followings
>
> (turn off database logging)
>
> set pdqpriority 20;>
> alter fragment on table table1 init in 50Bb_dbspace;>
> alter fragment on table table2 init in 50Bb_dbspace;>
> alter fragment on table table3 init in 50Bb_dbspace;>
> ....
> 4. turn back on database logging
> 5. drop the 50Gb_dbspace
>
> If your data is only 1Gb or less and depending on how powerful your system
is,
> the actual moving data from the old dbspace to a new dbspace shouldn't take
> you more 30, 45 minutes.
> Good luck
>
> ________________________________
> From: ANDREW LEMIN <a_lemin@hotmail.com>
> To: ids@iiug.org
> Sent: Thursday, October 23, 2008 11:07:30 AM
> Subject: Re: How can I resize a chunk? [13772]
>
> Sorry i wasn't very clear. I actually want to shrink a chunk.
> We have defined a chunk of 50GB, but it only needs 1GB !!!
> Hence i would like to shrink it.
>
> Can i move data from one chunk into another allowing the original to be
> dropped?
>
> Thanks in advance.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
_________________________________________________________________
Discover the new Windows Vista
http://search.msn.com/results.aspx?q=windows+vista&mkt=en-US&form=QBRE
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Hi,
You cannot resize an existing chunk.
You can however add a new chunk to the dbspace containing the chunk you
wanted to resize.
This could be done either onmonitor or through the onspaces command.
Khaled
----- Original Message -----
From: "ANDREW LEMIN" <a_lemin@hotmail.com>
To: <ids@iiug.org>
Sent: Thursday, October 23, 2008 3:14 PM
Subject: How can I resize a chunk? [13768]
> Hello, is it possible to resize a chunk?
> I know i can do it the long way by exporting the data, drop tables, drop
> the
> chunks, create new chunks of smaller size, recreate dropped schema, import
> data. However this is a very long, convoluted and error prone process.
>
> So does anyone know how to resize an existing chunk?
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
HI Andrew
In this case, you have to copy all of the contents (tables, indexes, logs)
of the current chunk into the new one; for tables and indexes, you can
create them with a different name and rename later on.
You have to find out first what you have into that chunk (oncheck -pe will
give you a more precise look at the contents of that chunk).
Then you should drop its contents before you can drop the chunk.
Then you can rename all of the objects that you have transferred.
Since the chunk that you are looking for is relatively small (1 Gb), may be
you can just unload the data, drop the chunk, create the new chunk and
reload the data.
There are different ways of going about it.
Remember that chunks are fixed size physical devices and are not like
logical volumes.
Khaled
----- Original Message -----
From: "ANDREW LEMIN" <a_lemin@hotmail.com>
To: <ids@iiug.org>
Sent: Thursday, October 23, 2008 5:07 PM
Subject: Re: How can I resize a chunk? [13772]
> Sorry i wasn't very clear. I actually want to shrink a chunk.
> We have defined a chunk of 50GB, but it only needs 1GB !!!
> Hence i would like to shrink it.
>
> Can i move data from one chunk into another allowing the original to be
> dropped?
>
> Thanks in advance.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>