I have some non-cluster based tables in which about 10% of the records get updated in daily batch. To reclaim the logically deleted records grooming is done at the end of batch. Despite that the table size becomes double the size of newly CTASed table in just a week. The size of newly CTASed table is comparable with original table for those tables in which there is very less update. So, clearly groom command is not working as expected. I'm in Netezza 6. What should I do?
Edit: Backup of database is made but in the cloud. So, groom has to work without the backup being taken in Netezza.
Groom will only clean up the space if a backup exists. (More info here) It is not a best practice to do a daily reclaim as that would make your incremental backup require a full table backup every day. I would recommend grooming your table on a weekly basis.