Forums & Discussions Home

    A place for members, communities, and committees to have discussions online and via e-mail.
    Click a category or topic to below to start the conversation...

    You are currently in view only mode for this forum. Please click the appropriate below to login as a member and participate. If you are not a member, please CLICK HERE for more information.


    Mar 31
    2010

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

    SEKHAR MEKALA
    [HTC GLOBAL SERVICES]
    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
    [BMC Software]
    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
    [HTC GLOBAL SERVICES]
    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
    [CA technologies]
    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


    All Times America/New_York

    Copyright © 2014 IDUG. All Rights Reserved

    All material, files, logos and trademarks within this site are properties of their respective organizations.

    Terms of Service - Privacy Policy - Contact