ROTATE PARTITION issue

umayer · #1 (**permalink**) 02-15-08, 08:52

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 ?

stolze · #2 (**permalink**) 02-15-08, 10:18

The manual (for DB2 z/OS V9) says this:

Quote:

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.

umayer · #3 (**permalink**) 02-15-08, 16:45

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 ...

stolze · #4 (**permalink**) 02-15-08, 18:12

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.

umayer · #5 (**permalink**) 02-20-08, 02:31

we are using DB2 V8 NFM

JSalt · #6 (**permalink**) 05-04-09, 12:08

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?

Internet Services

Web Development

Digital Marketing

Consumer Tech