Regarding question 1 … we had a similar-sounding partitioned table, continuously populated, and partitioned by the month. So we rotated through the full set of partitions annually. The partitions got quite large, as many as 1 billion rows per.
We did not delete rows, but instead we backed up (there was a requirement to keep the data indefinitely) and then null loaded the upcoming partition (e.g. at the beginning of June, we would back up/null load last July's partition so it was empty for the upcoming July). This was facilitated by making the original table name the 'any' table name of the partitioned table so that all existing applications could still access the 11 available partitions when we put the upcoming partition into utility status for the backup and null load. The null load could take several days, but application access to the other partitions was not affected, and we did have a month to spare. Eventually, the performance, and ongoing status, of the null load was improved by the addition of a 'LOAD ,FORMAT=NONE_MU' in PTF SO13594
Now, we never bothered to de-allocate the newly cleared partition, because it was going to be used almost immediately. That is an extra complication you have. ONLINE_AREA_MOVE to a smaller allocation would be of limited use because of the high-use indicator. But perhaps TM24 (Table Move 24x7) would do the trick, without impacting application access to the other partitions
Question 2 is a whole different thing. I don't know how TM24 (or BA24, Block Alter 24x7) would do moving the existing 'parent table', partitioned by year, to a newly defined parent table partitioned by month. Sounds interesting, though…
Hope this helps, Steve.Clyde
Trying the online table move (TM24), I received the following error, but have been into other stuff this morning, thus have not gotten back into it.
TDBMUF:DB00608I - DBUTLTY COMM CONSOLE COMPLETE - ONLINE_TABLE_MOVE 446,AS4 STEVOAMT 13605 JOB95260-1 STEV
TDBMUF:DB02837I - ONLINE_TABLE_MOVE 446 AS4 STATUS PREPARING TO START
TDBMUF:DB02836E - ONLINE_TABLE_MOVE 446 AS4 ERROR PARTITION TABLECould this maybe have something to do with the way we set it up when we converted to partitioned?