Compacting a dbspace through onunload and onload
Larry wanted to consolidate a test instance (IDS 11.50 on Solaris 10) whose data was thinly spread across many dbspace chunks, so he could free chunks, and asked whether onunload/onload would do it. Suggestions: use ALTER FRAGMENT ... INIT IN <dbspace> to physically rewrite/relocate tables (including back into the same dbspace); or rebuild via onunload/onload into a new instance with freshly named dbspaces so each database lands in one dbspace. Mark advised dropping indexes before the ALTER and rebuilding them with PDQ for speed. Larry said he'd try ALTER FRAGMENT; no outcome reported.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Solaris 10
IDS 11.50.FC5
I have a test instance that is spread out across all of my dbspace chunks in
my test instance, some chunks just havin27 pages used, some having 31 used,
etc. I have recently deleted a couple of databases, and now I would like to
consolidate my test instance into as few chunks as possible to reclaim some
space/chunks for something else. Will performing an onunload and onload
accomplish this, or is there an easier way to accomplish this?
Thank you.
How about ALTER FRAGMENT .... INIT IN ...; ?
Would also physically copy your tables ...=20
Andreas
From: "LARRY SORENSEN" <LSORENSEN25@msn.com>
To: ids@iiug.org
Date: 25.10.2016 14:10
Subject: Compacting a dbspace through onunload and onload [38034]
Sent by: ids-bounces@iiug.org
Solaris 10=20
IDS 11.50.FC5=20
I have a test instance that is spread out across all of my dbspace chunks=20
in=20
my test instance, some chunks just havin27 pages used, some having 31=20
used,=20
etc. I have recently deleted a couple of databases, and now I would like=20
to=20
consolidate my test instance into as few chunks as possible to reclaim=20
some=20
space/chunks for something else. Will performing an onunload and onload=20
accomplish this, or is there an easier way to accomplish this?=20
Thank you.=20
***************************************************************************=
****=20
Forum Note: Use "Reply" to post a response in the discussion forum.=20
Hi Larry,
You and I had a similar discusion about onunload/onload before (August 2012)
so look it up from iiug website.
Basically you just want each of your database goes to just one dbspace in the
new instance that you've created dbspaces with fewer chunks, yes this can be
done. You just make sure to give new names to dbspaces then when you onload
your database to a particular dbspace, there will be no chance for onload to
spread tables of your database else where.
Kern ---
Sent from my iPhone
> On Oct 25, 2016, at 8:10 AM, LARRY SORENSEN <LSORENSEN25@msn.com> wrote:
>
> Solaris 10
>
> IDS 11.50.FC5
>
> I have a test instance that is spread out across all of my dbspace chunks in
> my test instance, some chunks just havin27 pages used, some having 31 used,
> etc. I have recently deleted a couple of databases, and now I would like to
> consolidate my test instance into as few chunks as possible to reclaim some
> space/chunks for something else. Will performing an onunload and onload
> accomplish this, or is there an easier way to accomplish this?
>
> Thank you.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
--Apple-Mail-1C9AF2ED-7244-4181-95EF-F1177AB5CDA4
Ok. Thank you for the ideas. I will take a look at ALTER FRAGMENT.
Larry
________________________________
From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of Andreas Legner
<andreas.legner@de.ibm.com>
Sent: Tuesday, October 25, 2016 7:05 AM
To: ids@iiug.org
Subject: Re: Compacting a dbspace through onunload and .... [38035]
How about ALTER FRAGMENT .... INIT IN ...; ?
Would also physically copy your tables ...=20
Andreas
From: "LARRY SORENSEN" <LSORENSEN25@msn.com>
To: ids@iiug.org
Date: 25.10.2016 14:10
Subject: Compacting a dbspace through onunload and onload [38034]
Sent by: ids-bounces@iiug.org
Solaris 10=20
IDS 11.50.FC5=20
I have a test instance that is spread out across all of my dbspace chunks=20
in=20
my test instance, some chunks just havin27 pages used, some having 31=20
used,=20
etc. I have recently deleted a couple of databases, and now I would like=20
to=20
consolidate my test instance into as few chunks as possible to reclaim=20
some=20
space/chunks for something else. Will performing an onunload and onload=20
accomplish this, or is there an easier way to accomplish this?=20
Thank you.=20
***************************************************************************=
****=20
Forum Note: Use "Reply" to post a response in the discussion forum.=20
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
So, if I want to keep the table in the same dbspace I can use this?
ALTER FRAGMENT ONLINE ON TABLE table_name INIT IN same_dbspace_name
Is that correct?
Larry
________________________________
From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of Andreas Legner
<andreas.legner@de.ibm.com>
Sent: Tuesday, October 25, 2016 7:05 AM
To: ids@iiug.org
Subject: Re: Compacting a dbspace through onunload and .... [38035]
How about ALTER FRAGMENT .... INIT IN ...; ?
Would also physically copy your tables ...=20
Andreas
From: "LARRY SORENSEN" <LSORENSEN25@msn.com>
To: ids@iiug.org
Date: 25.10.2016 14:10
Subject: Compacting a dbspace through onunload and onload [38034]
Sent by: ids-bounces@iiug.org
Solaris 10=20
IDS 11.50.FC5=20
I have a test instance that is spread out across all of my dbspace chunks=20
in=20
my test instance, some chunks just havin27 pages used, some having 31=20
used,=20
etc. I have recently deleted a couple of databases, and now I would like=20
to=20
consolidate my test instance into as few chunks as possible to reclaim=20
some=20
space/chunks for something else. Will performing an onunload and onload=20
accomplish this, or is there an easier way to accomplish this?=20
Thank you.=20
***************************************************************************=
****=20
Forum Note: Use "Reply" to post a response in the discussion forum.=20
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
For ALTER FRAGMENT ... it will be much faster if you drop any indexes, do the ALTER, then rebuild the indexes w/ PDQ (and appropriate settings). Otherwise we have to maintain the rowids in the index(es) as the data rows move to new pages. Mark Scranton The Mark Scranton Group mark@markscranton.com
Thank you.
________________________________
From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of MARK SCRANTON
<mark@markscranton.com>
Sent: Tuesday, November 1, 2016 1:57 PM
To: ids@iiug.org
Subject: Re: Compacting a dbspace through onunload and .... [38062]
For ALTER FRAGMENT ... it will be much faster if you drop any indexes, do the
ALTER, then rebuild the indexes w/ PDQ (and appropriate settings). Otherwise
we have to maintain the rowids in the index(es) as the data rows move to new
pages.
Mark Scranton
The Mark Scranton Group
mark@markscranton.com
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.