Manipulating massive year-based partitions

[Steve Roy]

We have a massive 7 year retention table that is partitioned on the last digit of the year, hence 2 of the 10 partitions always have 0 rows, one is increasing and one is decreasing.  It is always accessed by the original table name, just as it was before being partitioned.
04 CREATE-DATE.
05 CREATE-DATE-CCY
PICTURE 9(3).
05 CREATE-DATE-Y  <== the partition key
PICTURE 9(1).
05 CREATE-DATE-MM
PICTURE 9(2).
05 CREATE-DATE-DD
PICTURE 9(2).

The past couple years' data files are each about a million tracks and over 600 million rows.

2 questions:

1 - What is the best method of removing all excess disk allocation from the empty files, which would be done on an annual basis, as rows are deleted daily?
2 - What recommendations would there be for a conversion to month-based partitioning, which would eliminate the reason for my first question?

[Clyde MCPHERSON]

Hi Steve,

 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

[Steve Roy]

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 TABLE              

Could this maybe have something to do with the way we set it up when we converted to partitioned?

[Clyde MCPHERSON]

Looks pretty straightforward, Steve. From the manual:

DB02836E - ONLINE_TABLE_MOVE bid area ERROR - variable-info
 

Reason:

 This message is generated for a failed Table Move request. Variable-info text could be:

 ...

PARTITION TABLE

Reflects the area contains a partition of a table and is not allowed.
...

Apparently one cannot TM24 a single partition. So that is not an option. Sorry...

Clyde

[Dale Russell]

Steve,

Thanks for bringing up your situation and initiating this conversation.  We would be open to you going to the Datacom Ideas board and submitting a request that we allow ONLINE_AREA_MOVE for partitioned tables.  Would you do that?

Dale

------------------------------
Dale Russell
Product Owner, Datacom Product Family
Broadcom Software
------------------------------

[Kevin Shuma]

Dale,

Online Table Move would be the correct technology for partitioned tables
where we are trying to re-size the underlying area.

Online Area Move would not do the resizing that is requested.


Kevin
Kevin Shuma
Director of Engineering
*Broadcom Software*
5465 Legacy Drive, Plano, TX 75024-3106
Mobile: [phone]
[e-mail]







<[e-mail]>
<http: broadcom.com/="">

--
This electronic communication and the information and any files transmitted
with it, or attached to it, are confidential and are intended solely for
the use of the individual or entity to whom it is addressed and may contain
information that is confidential, legally privileged, protected by privacy
laws, or otherwise restricted from disclosure to anyone else. If you are
not the intended recipient or the person responsible for delivering the
e-mail to the intended recipient, you are hereby notified that any use,
copying, distributing, dissemination, forwarding, printing, or copying of
this e-mail is strictly prohibited. If you received this e-mail in error,
please return the e-mail to the sender, delete it from your computer, and
destroy any printed copy of it.

[Dale Russell]

Yes, I misspoke.  Online Table Move it is.

------------------------------
Dale Russell
Product Owner, Datacom Product Family
Broadcom Software
------------------------------

[Steve Roy]

I just now added the idea referring to TM24. Thanks for your help with this, Dale. And Kevin's and Clyde's.

Thank you,

Stephen (Steve) Roy

[Dale Russell]

Yes, Steve.  I saw it.  Thanks!

------------------------------
Dale Russell
Product Owner, Datacom Product Family
Broadcom Software
------------------------------