Dropping PPI partitions


Dropping PPI partitions

Anybody had issues dropping PPI partitions ?

We have a PPI table (3.2 Billion rows, 12 months data, partitioned by month). Lat night, a Prod job attempted to drop the oldest partition (approx 164 million rows), and the statement took nearly 7 hours to complete.
Sadly, the developer had omitted the 'WITH DELETE' clause from the drop.
I would have expected this to generate an error, as I didn't think you could drop populated partitions, but it didn't.
However, after 7 hours, the Drop finally finished and it had indeed dropped the partition (ie the table definition no longer shows the dropped month as part of its partition range), but the data which was previously housed in the dropped partition is STILL there on the table. D'oh.
I'd have assumed that the 7 hour run was due partly to Transient journalling being done on the 164 million rows - but they weren't actually deleted ! So what's going on ?
Oh also, I've checked, and the job wasn't being blocked, although towards the end it WAS blocking quite a few other jobs, apparently because of a Row Hash Write lock on DBC.DBCAssociation.
Junior Contributor

Re: Dropping PPI partitions

Hi John,
there's a NO RANGE partition and all rows have been moved to that partition.
In that case the delete from the old partition and the merge into the no range partition where transient journaled.
Btw, there wouldn't be any difference if you added the WITH DELETE, because this is only for rows which don't fit in any partition.

If you got a no range ten you have to "delete from tab where...." before you modify the PI. Check the manuals about that.


Re: Dropping PPI partitions

I belive if you are running on V2R5 there is this issue w/t inconsistent results set if you are dealing a partitioned tables.

I also believe this has been addressed in later releases.

You may want to check for any tech alert on this w/t your NCR rep.
I believe there are work arounds available.
You may want to try what Dieter suggested first.


Re: Dropping PPI partitions


Many thanks - I hadn't made the connection between having to have no NO RANGE when using WITH DELETE.

The developers won't be happy though - they'll have to clean their data prior to loading :-) They might even have to do some testing !

Re: Dropping PPI partitions


Sorry, how rude, thanks also for your reply. There's nothing more dispiriting than having a forum posting ignored.