Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2005
    Posts
    273

    Unanswered: ROTATE PARTITION issue

    high all,

    I currently perform some tests with the
    ALTER TABLE ROTATE PARTITION FIRST TO LAST ... RESET
    statement.

    I created a table contolled partitioned table with a single partitioned not clustered index

    issuing the ROTATE statement, I expected that DB2 simly drops and recreates the corresponding VSAM-clusters. But instead, each record is deleted one by one, causing a lot of getpage requests .

    What can I do, that DB2 drops and recreates the VSAM-clusters instead of deleting each record separately ?

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The manual (for DB2 z/OS V9) says this:
    RESET
    Specifies that the existing data in the oldest partition is deleted. In a partitioned table with limit values that are in ascending sequence, ALTER TABLE ALTER PARTITION ROTATE FIRST TO LAST logically operates as if the partition with the lowest high key limit were dropped and then a new partition was added with the specified high key limit. The new key limit for the partition must be higher than any other partition in the table. For descending limit keys, the rotation operates as the partition with the highest limit values becomes the partition with the lowest limit values.

    If the partition contains referential integrity parent relationships, has DATA CAPTURE logging enabled, or has a delete row trigger, then each data row in the partition must be deleted individually. If a table does not have any of these attribute settings, then the data rows are removed by deleting and redefining the underlying data sets.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Dec 2005
    Posts
    273
    thanks for your reply, stolze. I've read the manual already.
    None of that conditions is met.
    - no RI
    - no DATA CAPTURE
    - no trigger
    - no column with SECURITY

    just a partitioned tablespace, a table, an partitioned noncluster index and nothing else ...

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I don't know which version of DB2 you are using - maybe your version has further restrictions? If not, you may want to contact IBM support because it means that either the manual is incorrect or that there is a bug in DB2.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Dec 2005
    Posts
    273
    we are using DB2 V8 NFM

  6. #6
    Join Date
    May 2009
    Posts
    1

    Same problem

    Quote Originally Posted by umayer
    we are using DB2 V8 NFM
    I have the same problem in DB2 V8 NFM. The rotate is taking 3 hours to run. I am suspecting it is because the tablespace has TRACKMOD YES. Do you have TRACKMOD YES?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •