Question on Partition rotation in DB2 V8.1 on z/OS

SEKHAR MEKALA

Question on Partition rotation in DB2 V8.1 on z/OS
Hi all,
In one of our DB2 databases (on z/OS) we are rotating the first logical partition of a partitioned table to hold new data. The table space’s partitions were already having Compressed dictionaries (COMPRESS YES was specified in table space definition and sample data was loaded to built the dictionary for each partition).

To implement partition rotation, we are doing the following:

1. Delete the data from the first logical partition.
2. Rotate the first logical partition to the last logical partition, using ALTER TABLE statement.

Now my questions are:
1. Once the partition is rotated, do I have to build the compression dictionary for the last logical partition?
2. How does DB2 DELETE the data from the first logical partition during the Partition rotation? Does it drop and recreate the underlying DB2 data set for the first logical partition or does it delete the data (as a normal delete statement, and the deletes are logged)?
3. After partition rotation, I know that I have to use the PHYSICAL PARTITION Number to confine a DB2 Utility execution to a particular partition. However can any one confirm this?

Your inputs are valuable to plan our partition rotation.

Thanks in advance!!

Best regards,
Sekhar

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to $1600 off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Phil Grainger

Re: Question on Partition rotation in DB2 V8.1 on z/OS
(in response to SEKHAR MEKALA)
Sekhar - see below for answers

Hope these are helpful

Rotating is very useful but also fraught with many dangers to trap the unwary

Phil Grainger
Cogito Ltd.
[login to unmask email]
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk

Attend IDUG 2010 - Europe, the premiere event for DB2 professionals.
8-12 November 2010, Vienna
Learn more at http://www.idug.org



-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Sekhar
Sent: 31 March 2010 13:42
To: [login to unmask email]
Subject: [DB2-L] Question on Partition rotation in DB2 V8.1 on z/OS

Hi all,
In one of our DB2 databases (on z/OS) we are rotating the first logical partition of a partitioned table to hold new data. The table space’s partitions were already having Compressed dictionaries (COMPRESS YES was specified in table space definition and sample data was loaded to built the dictionary for each partition).

To implement partition rotation, we are doing the following:

1. Delete the data from the first logical partition.
2. Rotate the first logical partition to the last logical partition, using ALTER TABLE statement.

Now my questions are:
1. Once the partition is rotated, do I have to build the compression dictionary for the last logical partition? [pg] Yes - The new partition is COMPLETELY empty, so has no compression dictionary
2. How does DB2 DELETE the data from the first logical partition during the Partition rotation? Does it drop and recreate the underlying DB2 data set for the first logical partition or does it delete the data (as a normal delete statement, and the deletes are logged)? [pg] Yes, all the deletes are logged (in case you do a ROLLBACK of the rotate!) If you really don't want the data, so a dummy LOAD REPLACE before the rotate to clear out the data (but see below)
3. After partition rotation, I know that I have to use the PHYSICAL PARTITION Number to confine a DB2 Utility execution to a particular partition. However can any one confirm this? [pg] Yes indeed. After your FIRST rotate, the "first" partition is now "PART 2" for ALL utilities and for ALL DDL (like an ALTER space or ALTER limit key). This is important if you are doing a dummy LOAD REPLACE to clean out the data. You LOAD REPLACE into PART 1 the first time. REPLACE into PART 2 the second, PART 3 the third etc. Get this wrong, and you WILL suffer

Your inputs are valuable to plan our partition rotation.

Thanks in advance!!

Best regards,
Sekhar

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to $1600 off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to $1600 off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

SEKHAR MEKALA

Re: Question on Partition rotation in DB2 V8.1 on z/OS
(in response to Phil Grainger)
Hi Phil,

Thanks for your prompt reply.

I have a question. You replied that DB2 will use normal DELETE while rotating the first logical partition to the last logical partition, so that the DELETES are logged and we have the ROLLBACK option. However, you confirmed that DB2 also deletes the compression dictionary for the last logical partition after rotation. Now my question is: if DB2 deletes the data using normal DELETE while partition rotation, why should I build the compression dictionary for the new logical partition after rotation? Normal DB2 DELETES will not remove the compression dictionary eventhough DELETES make the partition empty.

Please correct me if I misunderstood the concept. I just need a confirmation if I have to build the compression dictionary after partition rotation.

Thanks in advance!!

Best regards,
Sekhar

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to $1600 off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Steen Rasmussen

Re: Question on Partition rotation in DB2 V8.1 on z/OS
(in response to SEKHAR MEKALA)
Hello Sekhar - I have been working a lot with TCP since DB2 V8, and I didn't know the answer to your question, so I decided to do a small case study - and here is the result.
I captured screen shots of the pages throughout the scenario, so if you want all the page displays in order to see I tell the truth, please let me know and I can send the word doc.

1) I created a 7 partition TCP where two columns are defined in the limitkey.
2) I specified compression for all 7 partitions - loaded data and did a reorg.
3) 66% page saving.
4) I did a ROTATE FIRST TO LAST specifying a new high limitkey.
5) I displayed the LP7/PP1 - and all 8 compression dictionary pages were still present.
6) I could even use the CA Page Display Facility to display and format all the rows, so what DB2 does is pretty much like a mass delete from a segmented tablespace.
7) Once I did the first INSERT, then all the old rows were gone from the LP7.
8) This means (to me at least) - in case you regret the ROTATE - before doing any DML statements, you should be able to flip the spacemap page bit and unload the data.

I learned something here :-)

Steen Rasmussen
CA
Sr Engineering Services Architect
IBM Certified Database Associate - DB2 9 Fundamentals
IBM Certified Database Administrator - DB2 9 DBA for z/OS



-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of SUBSCRIBE DB2-L Anonymous
Sent: Wednesday, March 31, 2010 1:10 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Question on Partition rotation in DB2 V8.1 on z/OS

Hi Phil,

Thanks for your prompt reply.

I have a question. You replied that DB2 will use normal DELETE while rotating the first logical partition to the last logical partition, so that the DELETES are logged and we have the ROLLBACK option. However, you confirmed that DB2 also deletes the compression dictionary for the last logical partition after rotation. Now my question is: if DB2 deletes the data using normal DELETE while partition rotation, why should I build the compression dictionary for the new logical partition after rotation? Normal DB2 DELETES will not remove the compression dictionary eventhough DELETES make the partition empty.

Please correct me if I misunderstood the concept. I just need a confirmation if I have to build the compression dictionary after partition rotation.

Thanks in advance!!

Best regards,
Sekhar

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to $1600 off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L


_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** NEW - IBM hands-on labs -> no additional charge
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L