Space Recalamation
A beginner asked why deleting all rows from a table doesn't return space to the dbspace (onstat -d shows no change). Answers: IDS keeps the table's extents allocated for reuse; use oncheck -pt to inspect partition/extent info. Options given were TRUNCATE TABLE (9.40+), or ALTER FRAGMENT ON TABLE ... INIT IN <dbspace>, which frees all but the initial extent. Caveat: if earlier extents merged into a large initial extent, you must drop and recreate the table with a smaller extent size. ALTER FRAGMENT can consume lots of logical log, though not if the table is already empty; some argued simply dropping/recreating is cheaper.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Here
is one beginer's question:
For example when I delete all the rows from a table residing in a dbspace,
space "freed" with the deletion of those rows is not reclaimed to dbspace but
continue to be table's "ownership". Consecuently, there is no change in onstat
-d output for free space in this particular dbspace.Free space in this dbspace
is just the same as it was before.
What statement should I use in order to give free space in table's segment to
dbspace?
Thanks.
Dejan,
delete will delete all rows but the extent for that table will still be
there and that is why you see the same onstat -d output.
oncheck -pt to see partition info ( table info )
if you wan to reclaim the space you have to drop the table.
Additionally, you will see that is cheaper and faster to drop the table
with all the rows than 1) delete the rows and then drop the table.
regards
esteban.-
DEJAN STOJC.... wrote:
> Here is one beginer's question:
> For example when I delete all the rows from a table residing in a dbspace,
space "freed" with the deletion of those rows is not reclaimed to dbspace but
continue to be table's "ownership". Consecuently, there is no change in onstat
-d output for free space in this particular dbspace.Free space in this dbspace
is just the same as it was before.
> What statement should I use in order to give free space in table's segment
to dbspace?
> Thanks.
>
>
___________________________________________________________
1GB gratis, Antivirus y Antispam
Correo Yahoo!, el mejor correo web del mundo
http://correo.yahoo.com.ar
That's what I hate about getting older and losing my eyesight. I missed the
phrase 'ALL THE ROWS'. Esteban is quite correct.
- sigh -
j.
-----Original Message-----
From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]On
Behalf Of Esteban Cas....
Sent: Sunday, November 20, 2005 1:02 PM
To: ids@iiug.org
Subject: Re: Space Recalamation [6014]
Dejan,
delete will delete all rows but the extent for that table will still be
there and that is why you see the same onstat -d output.
oncheck -pt to see partition info ( table info )
if you wan to reclaim the space you have to drop the table.
Additionally, you will see that is cheaper and faster to drop the table
with all the rows than 1) delete the rows and then drop the table.
regards
esteban.-
DEJAN STOJC.... wrote:
> Here is one beginer's question:
> For example when I delete all the rows from a table residing in a dbspace,
space "freed" with the deletion of those rows is not reclaimed to dbspace
but continue to be table's "ownership". Consecuently, there is no change in
onstat -d output for free space in this particular dbspace.Free space inthis dbspace is just the same as it was before.
> What statement should I use in order to give free space in table's segment
to dbspace?
> Thanks.
>
>
___________________________________________________________
1GB gratis, Antivirus y Antispam
Correo Yahoo!, el mejor correo web del mundo
http://correo.yahoo.com.ar
From IDS9.40 use the SQL statement "TRUNCATE table".
Other idea is to cluster the table :
"alter fragment on table <tabname> init on <dbspace>"
mvg,
yves
-----Original Message-----
From: nobody@ace.iiug.org [mailto:nobody@ace.iiug.org]
Sent: 20 November 2005 19:41
To: ids@iiug.org
Subject: RE: Space Recalamation [6015]
That's what I hate about getting older and losing my eyesight. I missed the
phrase 'ALL THE ROWS'. Esteban is quite correct.
- sigh -
j.
-----Original Message-----
From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]On
Behalf Of Esteban Cas....
Sent: Sunday, November 20, 2005 1:02 PM
To: ids@iiug.org
Subject: Re: Space Recalamation [6014]
Dejan,
delete will delete all rows but the extent for that table will still be
there and that is why you see the same onstat -d output.
oncheck -pt to see partition info ( table info )
if you wan to reclaim the space you have to drop the table.
Additionally, you will see that is cheaper and faster to drop the table
with all the rows than 1) delete the rows and then drop the table.
regards
esteban.-
DEJAN STOJC.... wrote:
> Here is one beginer's question:
> For example when I delete all the rows from a table residing in a dbspace,
space "freed" with the deletion of those rows is not reclaimed to dbspace
but continue to be table's "ownership". Consecuently, there is no change in
onstat -d output for free space in this particular dbspace.Free space inthis dbspace is just the same as it was before.
> What statement should I use in order to give free space in table's segment
to dbspace?
> Thanks.
>
>
___________________________________________________________
1GB gratis, Antivirus y Antispam
Correo Yahoo!, el mejor correo web del mundo
http://correo.yahoo.com.ar
*****DISCLAIMER*****
Dit bericht en alle bijhorende zijn uitsluitend bestemd voor de geadresseerde
en
vertrouwelijk. Indien dit bericht niet voor U bestemd is, gelieve dit dan te
vernietigen en
de verzender te verwittigen. Openbaring, vermenigvuldiging, verspreiding en
verstrekking aan
derden is niet toegestaan, tenzij anders vermeld. Aangezien internet de
integriteit van dit
bericht niet kan verzekeren, kan de Dienst Vreemdelingenzaken niet
verantwoordelijk gesteld
worden indien dit bericht gewijzigd is.
Bezoek onze website: http://www.dofi.fgov.be
----------------------------------------
*****DISCLAIMER*****
Ce message et toutes les pieces jointes sont etablis a l'intention exclusive
de ses
destinataires et sont confidentiels. Si vous recevez ce message par erreur,
merci de le
detruire et d'en avertir l'expediteur. Toute utilisation de ce message non
conforme a sa
destination, toute diffusion ou toute publication, totale ou partielle, est
interdite, sauf
autorisation expresse. L'internet ne permettant pas d'assurer l'integrite de
ce message,
l'Office des Etrangers decline toute responsabilite au titre de ce message,
dans l'hypothese
ou il aurait ete modifie.
Visitez notre site web: http://www.dofi.fgov.be
IDS
reserves the space allocated to a table for future reuse even if all of the
rows have been deleted. If you want to release the space used by the table
without dropping it, you can release all but the initial extent using:
ALTER FRAGMENT ON tablename INIT IN dbspacename; -- dbspace can be the same onethe table already resides in or another one.
HOWEVER, if the initial extent is rather large which can happen if extents
added
after the table was first created happened to be contiguous with that initial
extent. Then the engine would have concatenated them all into one big extent
for efficiency. If that happened the only option is to drop the table and
recreate it with a smaller initial extent.
Art S. Kagel
----- Original Message -----
From: Dejan Stojc....
At: 11/20 12:34
Here is one beginer's question:
For example when I delete all the rows from a table residing in a dbspace,
space
"freed" with the deletion of those rows is not reclaimed to dbspace but
continue
to be table's "ownership". Consecuently, there is no change in onstat -d output
for free space in this particular dbspace.Free space in this dbspace is just
the
same as it was before.
What statement should I use in order to give free space in table's segment to
dbspace?
Thanks.
"alter fragment" is a llog eater... Be sure you have enough log space
allocated.
-----Original Message-----
From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On
Behalf Of Support Inf....
Sent: Monday, November 21, 2005 3:02 AM
To: ids@iiug.org
Subject: RE: Space Recalamation [6017]
From IDS9.40 use the SQL statement "TRUNCATE table".
Other idea is to cluster the table :
"alter fragment on table <tabname> init on <dbspace>"
mvg,
yves
-----Original Message-----
From: nobody@ace.iiug.org [mailto:nobody@ace.iiug.org]
Sent: 20 November 2005 19:41
To: ids@iiug.org
Subject: RE: Space Recalamation [6015]
That's what I hate about getting older and losing my eyesight. I missed
the phrase 'ALL THE ROWS'. Esteban is quite correct.
- sigh -
j.
-----Original Message-----
From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]On
Behalf Of Esteban Cas....
Sent: Sunday, November 20, 2005 1:02 PM
To: ids@iiug.org
Subject: Re: Space Recalamation [6014]
Dejan,
delete will delete all rows but the extent for that table will still be
there and that is why you see the same onstat -d output.
oncheck -pt to see partition info ( table info )
if you wan to reclaim the space you have to drop the table.
Additionally, you will see that is cheaper and faster to drop the table
with all the rows than 1) delete the rows and then drop the table.
regards
esteban.-
DEJAN STOJC.... wrote:
> Here is one beginer's question:
> For example when I delete all the rows from a table residing in a
> dbspace,
space "freed" with the deletion of those rows is not reclaimed to
dbspace but continue to be table's "ownership". Consecuently, there is
no change in onstat -d output for free space in this particular
dbspace.Free space in this dbspace is just the same as it was before.
> What statement should I use in order to give free space in table's
> segment
to dbspace?
> Thanks.
>
>
___________________________________________________________
1GB gratis, Antivirus y Antispam
Correo Yahoo!, el mejor correo web del mundo http://correo.yahoo.com.ar
*****DISCLAIMER*****
Dit bericht en alle bijhorende zijn uitsluitend bestemd voor de
geadresseerde en vertrouwelijk. Indien dit bericht niet voor U bestemd
is, gelieve dit dan te vernietigen en de verzender te verwittigen.
Openbaring, vermenigvuldiging, verspreiding en verstrekking aan derden
is niet toegestaan, tenzij anders vermeld. Aangezien internet de
integriteit van dit bericht niet kan verzekeren, kan de Dienst
Vreemdelingenzaken niet verantwoordelijk gesteld worden indien dit
bericht gewijzigd is.
Bezoek onze website: http://www.dofi.fgov.be
----------------------------------------
*****DISCLAIMER*****
Ce message et toutes les pieces jointes sont etablis a l'intention
exclusive de ses destinataires et sont confidentiels. Si vous recevez ce
message par erreur, merci de le detruire et d'en avertir l'expediteur.
Toute utilisation de ce message non conforme a sa destination, toute
diffusion ou toute publication, totale ou partielle, est interdite, sauf
autorisation expresse. L'internet ne permettant pas d'assurer
l'integrite de ce message, l'Office des Etrangers decline toute
responsabilite au titre de ce message, dans l'hypothese ou il aurait ete
modifie.
Visitez notre site web: http://www.dofi.fgov.be
Not if the table's already been emptied by DELETE or TRUNCATE.
Art S. Kagel
----- Original Message -----
From: Jerry Hamilton <hamiltoj@fleishman.com>
At: 11/21 11:07
"alter fragment" is a llog eater... Be sure you have enough log space
allocated.
-----Original Message-----
From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On
Behalf Of Support Inf....
Sent: Monday, November 21, 2005 3:02 AM
To: ids@iiug.org
Subject: RE: Space Recalamation [6017]
From IDS9.40 use the SQL statement "TRUNCATE table".
Other idea is to cluster the table :
"alter fragment on table <tabname> init on <dbspace>"
mvg,
yves
-----Original Message-----
From: nobody@ace.iiug.org [mailto:nobody@ace.iiug.org]
Sent: 20 November 2005 19:41
To: ids@iiug.org
Subject: RE: Space Recalamation [6015]
That's what I hate about getting older and losing my eyesight. I missed
the phrase 'ALL THE ROWS'. Esteban is quite correct.
- sigh -
j.
-----Original Message-----
From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]On
Behalf Of Esteban Cas....
Sent: Sunday, November 20, 2005 1:02 PM
To: ids@iiug.org
Subject: Re: Space Recalamation [6014]
Dejan,
delete will delete all rows but the extent for that table will still be
there and that is why you see the same onstat -d output.
oncheck -pt to see partition info ( table info )
if you wan to reclaim the space you have to drop the table.
Additionally, you will see that is cheaper and faster to drop the table
with all the rows than 1) delete the rows and then drop the table.
regards
esteban.-
DEJAN STOJC.... wrote:
> Here is one beginer's question:
> For example when I delete all the rows from a table residing in a
> dbspace,
space "freed" with the deletion of those rows is not reclaimed to
dbspace but continue to be table's "ownership". Consecuently, there is
no change in onstat -d output for free space in this particular
dbspace.Free space in this dbspace is just the same as it was before.
> What statement should I use in order to give free space in table's
> segment
to dbspace?
> Thanks.
>
>
___________________________________________________________
1GB gratis, Antivirus y Antispam
Correo Yahoo!, el mejor correo web del mundo http://correo.yahoo.com.ar
*****DISCLAIMER*****
Dit bericht en alle bijhorende zijn uitsluitend bestemd voor de
geadresseerde en vertrouwelijk. Indien dit bericht niet voor U bestemd
is, gelieve dit dan te vernietigen en de verzender te verwittigen.
Openbaring, vermenigvuldiging, verspreiding en verstrekking aan derden
is niet toegestaan, tenzij anders vermeld. Aangezien internet de
integriteit van dit bericht niet kan verzekeren, kan de Dienst
Vreemdelingenzaken niet verantwoordelijk gesteld worden indien dit
bericht gewijzigd is.
Bezoek onze website: http://www.dofi.fgov.be
----------------------------------------
*****DISCLAIMER*****
Ce message et toutes les pieces jointes sont etablis a l'intention
exclusive de ses destinataires et sont confidentiels. Si vous recevez ce
message par erreur, merci de le detruire et d'en avertir l'expediteur.
Toute utilisation de ce message non conforme a sa destination, toute
diffusion ou toute publication, totale ou partielle, est interdite, sauf
autorisation expresse. L'internet ne permettant pas d'assurer
l'integrite de ce message, l'Office des Etrangers decline toute
responsabilite au titre de ce message, dans l'hypothese ou il aurait ete
modifie.
Visitez notre site web: http://www.dofi.fgov.be
May be
I am missing something about this thread but
if the table has been emptied by a delete or truncate
why are you going to use alter fragment ?, when the
normal way and easy way to do it is to drop table and
recreate it as you want ? and when it is even cheaper
to delete all the rows using drop table rather than
delete or truncate.
If the table is going to be deleted I would use drop
table instead of delete and alter frag or truncate and
alter .
my 2 cents
esteban.-
--- "ART KAGEL, ...." <KAGEL@bloomberg.net> escribis:
> Not if the table's already been emptied by DELETE or
> TRUNCATE.
>
> Art S. Kagel
>
> ----- Original Message -----
> From: Jerry Hamilton <hamiltoj@fleishman.com>
> At: 11/21 11:07
>
> "alter fragment" is a llog eater... Be sure you
> have enough log space
> allocated.
>
>
> -----Original Message-----
> From: forum.subscriber@iiug.org
> [mailto:forum.subscriber@iiug.org] On
> Behalf Of Support Inf....
> Sent: Monday, November 21, 2005 3:02 AM
> To: ids@iiug.org
> Subject: RE: Space Recalamation [6017]
>
> From IDS9.40 use the SQL statement "TRUNCATE table".
> Other idea is to cluster the table :
> "alter fragment on table <tabname> init on
> <dbspace>"
>
> mvg,
> yves
>
> -----Original Message-----
> From: nobody@ace.iiug.org
> [mailto:nobody@ace.iiug.org]
> Sent: 20 November 2005 19:41
> To: ids@iiug.org
> Subject: RE: Space Recalamation [6015]
>
> That's what I hate about getting older and losing my
> eyesight. I missed
> the phrase 'ALL THE ROWS'. Esteban is quite
> correct.
>
> - sigh -
> j.
>
> -----Original Message-----
> From: forum.subscriber@iiug.org
> [mailto:forum.subscriber@iiug.org]On
> Behalf Of Esteban Cas....
> Sent: Sunday, November 20, 2005 1:02 PM
> To: ids@iiug.org
> Subject: Re: Space Recalamation [6014]
>
>
> Dejan,
>
> delete will delete all rows but the extent for that
> table will still be
> there and that is why you see the same onstat -d
> output.
>
> oncheck -pt to see partition info ( table info )>
> if you wan to reclaim the space you have to drop the
> table.
>
> Additionally, you will see that is cheaper and
> faster to drop the table
> with all the rows than 1) delete the rows and then
> drop the table.
>
> regards
> esteban.-
>
> DEJAN STOJC.... wrote:
> > Here is one beginer's question:
> > For example when I delete all the rows from a
> table residing in a
> > dbspace,
> space "freed" with the deletion of those rows is not
> reclaimed to
> dbspace but continue to be table's "ownership".
> Consecuently, there is
> no change in onstat -d output for free space in this
> particular
> dbspace.Free space in this dbspace is just the same
> as it was before.
> > What statement should I use in order to give free
> space in table's
> > segment
> to dbspace?
> > Thanks.
> >
> >
>
>
>
>
>
>
___________________________________________________________
> 1GB gratis, Antivirus y Antispam
> Correo Yahoo!, el mejor correo web del mundo
> http://correo.yahoo.com.ar
> *****DISCLAIMER*****
> Dit bericht en alle bijhorende zijn uitsluitend
> bestemd voor de
> geadresseerde en vertrouwelijk. Indien dit bericht
> niet voor U bestemd
> is, gelieve dit dan te vernietigen en de verzender
> te verwittigen.
> Openbaring, vermenigvuldiging, verspreiding en
> verstrekking aan derden
> is niet toegestaan, tenzij anders vermeld. Aangezien
> internet de
> integriteit van dit bericht niet kan verzekeren, kan
> de Dienst
> Vreemdelingenzaken niet verantwoordelijk gesteld
> worden indien dit
> bericht gewijzigd is.
> Bezoek onze website: http://www.dofi.fgov.be
> ----------------------------------------
> *****DISCLAIMER*****
> Ce message et toutes les pieces jointes sont etablis
> a l'intention
> exclusive de ses destinataires et sont
> confidentiels. Si vous recevez ce
> message par erreur, merci de le detruire et d'en
> avertir l'expediteur.
> Toute utilisation de ce message non conforme a sa
> destination, toute
> diffusion ou toute publication, totale ou partielle,
> est interdite, sauf
> autorisation expresse. L'internet ne permettant pas
> d'assurer
> l'integrite de ce message, l'Office des Etrangers
> decline toute
> responsabilite au titre de ce message, dans
> l'hypothese ou il aurait ete
> modifie.
> Visitez notre site web: http://www.dofi.fgov.be
>
>
___________________________________________________________
1GB gratis, Antivirus y Antispam
Correo Yahoo!, el mejor correo web del mundo
http://correo.yahoo.com.ar
Related threads
- IDS 10 table-level restore
- Informix Development Webinar December 11, 2007
- ontape -p/r with changed ROOTPATH
- Migrate from HP PA-RISC to HP ITANIUM by ontape