Question Deleted 90% of huge table, but Storage Area is still full, why?

Vito

New Member
Recently one huge table grew up and over-filled entire storage area (called AppData).

I spoke with developers and App-Support, all decided that we can safely purge 90+% of this table, so I did it.

But when I run "prostrct statistics" or query <select * from PUB."_AreaStatus">, it still shows that AppData area is full.
Database Block Usage for Area: AppData
Active blocks: 2619327
Data blocks: 2619272
Free blocks: 55
Empty blocks: 65
Total blocks: 2619392
Extent blocks: 3
Records/Block: 128
Cluster size: 64

Do you know how to really free this space without dumping and loading the table?
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
A storage area will grow, logically or physically, but it will not shrink. Purging data does not free up disk space.

In a Type II storage area, which this is, a table is a chain of clusters of RM (record) blocks. In this case, each cluster is 64 blocks. Purging data will remove records from blocks but it will not change their type. Even if a block has all of its records deleted, it will still be a record block, not an empty block.

If you want to free up disk space that is currently used by the extent(s) of this storage area, you will need to remove it. Before you can do that, you will need to relocate all storage objects that are currently assigned to it. If it is just this one table, your options are to dump and load it into a new storage area or relocate it with proutil dbname -C tablemove. The latter is only feasible if the table is relatively small. Use it with caution as it has the potential to grow the BI file.
 

TomBascom

Curmudgeon
Be very, very, very careful with tablemove. When Rob says "potential to grow the BI file" he is not kidding. I have seen tablemove result in the bi file growing 20x larger than the table being moved.

In the case of tablemove "relatively small" should be taken to mean "a handful of megabytes". Or utterly empty.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
As with all things related to maintenance activities, test in a non-production (but production-like) environment before attempting to do anything to the data that you care about.
 

Vito

New Member
Thank you guys, thanks a lot!
You clarified this to me.

And yes, very interesting thing about Before Image files.

When I was deleting the records from that huge table, my BI grew up a lot.
I got two fixed size BI files with default size 512M, and one dynamic file, and he grew up till 20G.
I would like to shrink it back. How can I do it, guys, by shutting down DB and executing proutil DBANME -C truncate bi ?
 

TomBascom

Curmudgeon
Yes, "truncate bi" will reclaim the growth.

That kind of growth strongly suggests that your purge process was executed as a single gigantic transaction. Or another user had a "long transaction" active that started before your purge and did not end until after you were done.

Either situation is bad.

If you have chronic "long transactions" bi growth is probably a routine thing in your environment. You may have plenty of disk space but "plenty" has a way of becoming "barely enough".

If you executed the purge as a single gigantic transaction that is a bad thing because 1) it generates a huge bi file 2) if it fails for some reason midstream then you have to back out that huge transaction (which requires even *more* bi space...) 3) you would have been using a very large number of record locks (and thus exposing yourself to failures due to -L exhaustion)

While it is superficially somewhat attractive there is actually no good reason to execute a purge as a single database transaction (all or nothing). Purge routines are completely restartable and can easily pick up where they left off. Mixing up the semantics of a "business transaction" with a "database transaction" leads to all sorts of bad behaviors. So, in reality, there is zero good reason to execute a purge as one big transaction and lots of reasons not to do that. (And don't form the habit on smaller transactions -- practice good coding even for the small stuff!)
 

Vito

New Member
Thank Thank you Tom, I just executed "proutil -C truncate bi" and it worked.
I did it on test DB.
Real database participates in replication.

Can we run "proutil -C truncate bi" on Replicated database?
 

TomBascom

Curmudgeon
Nope. You will have to re-seed replication to truncate the bi file.
 

Vito

New Member
Thank you again, Tom and Rob!
Last night truncated BI, later will work with dumping the table, Storage Area truncate, and loading table back
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
Last night truncated BI, later will work with dumping the table, Storage Area truncate, and loading table back
It's a good idea to prove to yourself before the area truncate that this large table is the only storage object in the AppData area. Proutil will warn you about objects in the area before it truncates but that's not the time you want to be finding out.

Note that this activity (area truncate followed by load/idxbuild) will require disabling AI and truncating the BI and thus will again require reseeding your replication target.
 

Vito

New Member
It's a good idea to prove to yourself before the area truncate that this large table is the only storage object in the AppData area. Proutil will warn you about objects in the area before it truncates but that's not the time you want to be finding out.
Yes, you are right, PROUTIL did it already :)

There are some tiny one-record tables, which I will also easily dump--drop-truncate_the_area-load
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
There are some tiny one-record tables, which I will also easily dump--drop-truncate_the_area-load
For tiny tables, don't bother with dump and load. This is an appropriate use case for proutil tablemove.

Also, given the cause of your original problem, I suggest you put this large table in its own area and put the tiny ones in a multi-use area with other small to medium-sized tables.
 

Cringer

ProgressTalk.com Moderator
Staff member
And while you're at it, make sure the indexes are in index only areas if they're not already.
 

Vito

New Member
Yes, guys, indexes are in separate area. I was going to use "tablemove" to move all tiny tables to another area and keep only that large table inside.
 

Patrice Perrot

New Member
Hi
The "truncate area" will not decrease its size ,
If you want to decrease it ,
i Think You could use Something like that

proutil [db] -C dump

proutil [db] -C truncate area [table_area]
proutil [db] -C truncate area [idx_area]
proutil [db] -C idxbuild table
… # You cannot move the inactive index (inactive by Truncate area)
proutil [db] -C tablemove
"Schema area" "Schema area" # There is no data in this table at this point
prostrct remove d [table_area]
prostrct remove d [idx-area]
prostrct add [Table_area + idx_area] # area which are removed in the 2 steps above
proutil [db] -C tablemove
"[table_area]" "[idx_table]"
proutil [db] -C load
.bd
proutil [db] -C idxbuild table



Regards
Patrice
 
Top