Hi all,

We have SPL we run every night.
We run IDS7.31.UD2 on AIX4.3
Some SPL's do "delete from table" before loading new data into table.
I know it should be better to "drop table; create table".
It seems that "delete from table" drops all data but it certainly not release all allocated pages and worse when loading new data in the table it does not reuse already allocated pages.
1 table has now npused=137 500 and npdata=1362.
After unload and reload the table and create index npused=3152.
The table has 2 columns and 1 primary index. nothing complex.

Why should IDS not reuse all pages still allocated to a table after a "delete from table" ?
Can someone help us ?
Should we do cluster index in the procedure or replace all "delete" by "drop, create" ?

De lete statement will not delete records immediately. It just marked records for delete. So, the space will not immediately available after delete. Eventually, btree cleaner delete records and reclaim the space.

You have couple of choice:

1)Delete records, followed by update statistics medium on that table
2)Drop and recreate the table
3)Create cluster index after load data. Basically, it will recreate the table with different partition number.

Cheers!
Sanjit Chakraborty

Thanks !