Each Netezza table has a minimum size. When you first instantiate the table, it will take up this minimum amount of space, which is significantly larger than the several records you are placing into it. Even when doing a reclaim, you probably won't see any movement in the table size if it has not breached the minimum.

Try the same scenario with several millions of records where the data size hits into the tens of gb's. Then set aside the reclaim as a viable option for tables this small.

The reclaim is an option of last resort, reserved for tables that are too large to rebuild with a simple CTAS (that is, there's not enough space on the machine for a CTAS, not that it has "too many records"). For a table this small, you should be using the CTAS.

Given this information, the cause is almost certainly that you have an open transaction on the server.  If someone started a transaction several days ago and never committed it or aborted the session, you cannot reclaim any records in any table that would be visible to that transaction.  This is because Netezza is trying to conserve the data as it was at the time that old transaction started.   Netezza prevents you from reclaiming records which would be visible to a transaction if that transaction were to access the table (even if the transaction has not yet accessed the table).  Once you abort this transaction you should be able to reclaim records as normal.

A transaction does not have to access the table to prevent you from reclaiming records.  A transaction only has to be active on the system.  If there is any transacation which started prior to you deleting rows, NPS will not allow you to reclaim any records which were deleted after that transaction started.  I thought I was clear about that on my prior post, hopefully this one clears up it up completely.

For example, if I connect to NPS, start a transaction, and never commit/rollback or disconnect, no records which are deleted after my transaction began can be reclaimed until my transaction is completed.  I've seen sitations where a transaction has been left open for weeks and it can cause all sorts of problems in this way.  To repeat again, it does not matter if my session accesses a table.  No records from *any* table can be reclaimed until my transaction completes regardless of if my transaction has accessed the tables.

It almost sounds like there is a safety-net necessity to jump into single-user mode to accomplish the reclaim, such that no transactions can accidentally sneak-inside the machine before I can launch the reclaim.

Either way, these are all good reasons to keep the reclaim in a back pocket for a rainy day, or when the box runs out of space to support a CTAS. However, if the box is this close to runnng out of space, there is another problem looming - capacity. So in the end, the need to use a reclaim at all could be a symptom of a larger capacity issue.

Hi,

can you do a show locks and verify that there are no locks? or nzession to see who was what locks?

If you can, you can stop and start the database open the database in exclusive lock mode. this will ensure that there are no zombie transactions out there

then run nz_reclaim from the contrib and check the estimates

and then run nzreclaim on the table