Datacom CADRE

 View Only
  • 1.  Manipulating massive year-based partitions

    Posted 4 days ago
    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?


  • 2.  RE: Manipulating massive year-based partitions

    Posted 3 days ago

    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




  • 3.  RE: Manipulating massive year-based partitions

    Posted yesterday

    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?




  • 4.  RE: Manipulating massive year-based partitions

    Posted 22 hours ago
    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