Reclaiming space on DB2/AIX V2

Michael Lim

Reclaiming space on DB2/AIX V2
Hello,

I have a table which has accumulated 3 years' worth of data. Now I would like to keep the last 6 months worth and delete the rest. How do I shrink the tablespace and reclaim the unused space?

In addition, the file system containing the tablespace has run out of space. I seem to have trouble with the 'delete' command failing with an out of space error. How do I calculate how much more space to give to the file system to enable me to perform the delete successfully?

thanks,
Mike



Viswanathan N

Re: Reclaiming space on DB2/AIX V2
(in response to Michael Lim)
hi Mike ,
You seem to have run into a typical situation where database
maintenance is required. Don't go for a delete. First you can unload the
data into a flat file. Drop the tablespace. Calculate using the formula for
space estimation where we have 4K data pages as the base. For this you will
have to have an approx. fig. of the number of rows the database will
contain after the delete. The row length will determine how much rows each
page will handle. And then you can arrive at the number of pages reuired.
Estimate the free space requirement and then add that to the space
required. After this create the table space with the revised space
parameters. Then load the table by placing conditions on the load
statements to filter out the data which is greater than 6 months old.

I think this will be best method. When you do a delete of the rows directly
. It will write a lot of information into the log records which is an added
cost.

regards
Vishy.





Michael Lim <[login to unmask email]> on 23-12-99 01:26:11 PM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>



To: [login to unmask email]

cc: (bcc: Viswanathan N/LTITL)



Subject: Reclaiming space on DB2/AIX V2









Note: Some recipients have been dropped due to syntax errors.
Please refer to the "$AdditionalHeaders" item for the complete headers.



Hello,

I have a table which has accumulated 3 years' worth of data. Now I would
like to keep the last 6 months worth and delete the rest. How do I shrink
the tablespace and reclaim the unused space?

In addition, the file system containing the tablespace has run out of
space. I seem to have trouble with the 'delete' command failing with an out
of space error. How do I calculate how much more space to give to the file
system to enable me to perform the delete successfully?

thanks,
Mike








Umair Hussain

Re: Reclaiming space on DB2/AIX V2
(in response to Viswanathan N)
To shrink tablespace you basically have to
export tables,
drop tablespace,
re-create tablespace,
import tables

For your delete you must be running out of space in your log filesystem.
Have to delete it programatically where a commit is issued after every n
deletes.


Umair Hussain********************************
DBA Consultant *
DB2 UDB, Oracle, Sybase, DataJoiner *
IBM Certified Solution Expert - DB2 UDB DBA *
IBM Certified AIX System Administrator *
********|All Disclaimers Apply|**************






>From: Michael Lim <[login to unmask email]>
>Reply-To: DB2 Data Base Discussion List <[login to unmask email]>
>To: [login to unmask email]
>Subject: Reclaiming space on DB2/AIX V2
>Date: Thu, 23 Dec 1999 15:56:11 +0800
>
>Hello,
>
>I have a table which has accumulated 3 years' worth of data. Now I would
>like to keep the last 6 months worth and delete the rest. How do I shrink
>the tablespace and reclaim the unused space?
>
>In addition, the file system containing the tablespace has run out of
>space. I seem to have trouble with the 'delete' command failing with an out
>of space error. How do I calculate how much more space to give to the file
>system to enable me to perform the delete successfully?
>
>thanks,
>Mike
>
>
>
>http://www.ryci.com/db2-l. The owners of the list can be reached at
>[login to unmask email]


______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com



Michael Lim

Re: Reclaiming space on DB2/AIX V2
(in response to Umair Hussain)
Thanks for the tip. I resolved my problem by exporting the data required to a flat file and then running a 'load replace'. This shrank the file system size.

regards,
Mike

>>> Umair Hussain <[login to unmask email]> 24/12/99 7:10 AM >>>
To shrink tablespace you basically have to
export tables,
drop tablespace,
re-create tablespace,
import tables

For your delete you must be running out of space in your log filesystem.
Have to delete it programatically where a commit is issued after every n
deletes.


Umair Hussain********************************
DBA Consultant *
DB2 UDB, Oracle, Sybase, DataJoiner *
IBM Certified Solution Expert - DB2 UDB DBA *
IBM Certified AIX System Administrator *
********|All Disclaimers Apply|**************






>From: Michael Lim <[login to unmask email]>
>Reply-To: DB2 Data Base Discussion List <[login to unmask email]>
>To: [login to unmask email]
>Subject: Reclaiming space on DB2/AIX V2
>Date: Thu, 23 Dec 1999 15:56:11 +0800
>
>Hello,
>
>I have a table which has accumulated 3 years' worth of data. Now I would
>like to keep the last 6 months worth and delete the rest. How do I shrink
>the tablespace and reclaim the unused space?
>
>In addition, the file system containing the tablespace has run out of
>space. I seem to have trouble with the 'delete' command failing with an out
>of space error. How do I calculate how much more space to give to the file
>system to enable me to perform the delete successfully?
>
>thanks,
>Mike
>
>
>
>http://www.ryci.com/db2-l. The owners of the list can be reached at
>[login to unmask email]


______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com