DB2 V8 Dropping partitions

Sharon Zitterman

DB2 V8 Dropping partitions
Hi,

We added 4 partitions to 3 tables in a client’s test environment
because we believed at the time that we would need to do the
same for the those tables in production to accommodate 2011
data growth and we were preparing the test environment to mirror
production structurally when the change was made there.

Further analysis has determined that we don’t need to add more
partitions in production, so the client wants us to remove the
partitions we added in their test environment.

In V8, of course, not having to drop and recreate the tables,
we were able to add the partitions in test fairly quickly and easily.
I suspect reversing this change will not be as straight forward, but
have found little info on this topic.

Can someone give me a running start or point me in the right
direction?

Thanks in advance.

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% 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 Listserv

Steen Rasmussen

Re: DB2 V8 Dropping partitions
(in response to Sharon Zitterman)
Sharon - the only way to drop partitions is to go through the unload-drop-create-load-rebind . . . .
No relief in DB2 9 or 10.

Steen Rasmussen
CA Technologies
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 Sharon Zitterman
Sent: Wednesday, February 09, 2011 10:47 AM
To: [login to unmask email]
Subject: [DB2-L] DB2 V8 Dropping partitions

Hi,

We added 4 partitions to 3 tables in a client’s test environment
because we believed at the time that we would need to do the
same for the those tables in production to accommodate 2011
data growth and we were preparing the test environment to mirror
production structurally when the change was made there.

Further analysis has determined that we don’t need to add more
partitions in production, so the client wants us to remove the
partitions we added in their test environment.

In V8, of course, not having to drop and recreate the tables,
we were able to add the partitions in test fairly quickly and easily.
I suspect reversing this change will not be as straight forward, but
have found little info on this topic.

Can someone give me a running start or point me in the right
direction?

Thanks in advance.

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% 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 Listserv

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% 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 Listserv

Raymond Bell

Re: DB2 V8 Dropping partitions
(in response to Steen Rasmussen)
Steen's right (OMG, what have I said?!?) you're looking at what my US friends call a UDCL operation - unload/drop/create/load. But there is a technique you can use to minimise the total unavailability time while you do that, if you have a log analysis tool of some sort. Useful in a large Production environment with a short window of opportunity. But for a (presumably small) Dev-type world, just suck it up and UDCL the bugger. Or live with the fact you have 3 more partitions than you actually need. Their call I guess.

Cheers,


Raymond
PS. Steen, the Force is strong in this one... ;o)

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Rasmussen, Steen
Sent: 09 February 2011 16:50
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 V8 Dropping partitions

Sharon - the only way to drop partitions is to go through the unload-drop-create-load-rebind . . . .
No relief in DB2 9 or 10.

Steen Rasmussen
CA Technologies
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 Sharon Zitterman
Sent: Wednesday, February 09, 2011 10:47 AM
To: [login to unmask email]
Subject: [DB2-L] DB2 V8 Dropping partitions

Hi,

We added 4 partitions to 3 tables in a client’s test environment
because we believed at the time that we would need to do the
same for the those tables in production to accommodate 2011
data growth and we were preparing the test environment to mirror
production structurally when the change was made there.

Further analysis has determined that we don’t need to add more
partitions in production, so the client wants us to remove the
partitions we added in their test environment.

In V8, of course, not having to drop and recreate the tables,
we were able to add the partitions in test fairly quickly and easily.
I suspect reversing this change will not be as straight forward, but
have found little info on this topic.

Can someone give me a running start or point me in the right
direction?

Thanks in advance.

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% 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 Listserv

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% 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 Listserv

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% 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 Listserv

Max Scarpa

Re: DB2 V8 Dropping partitions
(in response to Raymond Bell)
Hi Sharon

It's a one-way activity. You cannot drop (even if AFAIK it has been
requested to IBM, I asked this question even in November, at IDUG EMEA in
Vienna as well) a single partition probably because DB2 catalog (as
there's nothing preventing you from simply deleting a VSAM files STOPped
to DB2...). I don't know why, if you can add a partition, you cannot do
the same thing 'backwards', but for sure there's a reason......maybe ;-)
The only way for now is unload/drop/create/load, as said. If these
partitions aren't really needed you can alter space to the minimum (say 1
trk or similar) + reorg them, so leaving them empty/unused. They don't
harm you and customer will pay very little for that space (if accounted)
and they could be useful in next future (until december 2012 ?)

Just an idea

Max Scarpa
Certified 'your every wish is my command' DB2 sysprog (ladies only,
doctors first....and Cuneyt knows what I mean)



IDUG DB2-L <[login to unmask email]> wrote on 09/02/2011 17.47.12:

> From: Sharon Zitterman <[login to unmask email]>
> To: [login to unmask email]
> Date: 09/02/2011 17.47
> Subject: [DB2-L] DB2 V8 Dropping partitions
> Sent by: IDUG DB2-L <[login to unmask email]>
>
> Hi,
>
> We added 4 partitions to 3 tables in a client’s test environment
> because we believed at the time that we would need to do the
> same for the those tables in production to accommodate 2011
> data growth and we were preparing the test environment to mirror
> production structurally when the change was made there.
>
> Further analysis has determined that we don’t need to add more
> partitions in production, so the client wants us to remove the
> partitions we added in their test environment.
>
> In V8, of course, not having to drop and recreate the tables,
> we were able to add the partitions in test fairly quickly and easily.
> I suspect reversing this change will not be as straight forward, but
> have found little info on this topic.
>
> Can someone give me a running start or point me in the right
> direction?
>
> Thanks in advance.
>
> _____________________________________________________________________
> * IDUG North America * Anaheim, California * May 2-6 2011 * http://
> IDUG.ORG/NA *
> * Your only source for independent, unbiased, and trusted DB2
> information. *
> _____________________________________________________________________
> http://www.IDUG.org/mentor
> Mentoring should be a rewarding experience for everyone...
> IDUG is offering up to 80% 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 Listserv


_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

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

Sharon Zitterman

Re: DB2 V8 Dropping partitions
(in response to Max Scarpa)
Steen, Raymond and Max,

Drat the luck...that's what I suspected:)

We would have to perform the "undo" in 4 test environments,
so it is possible the client may decide they can live with the
additional partitions.

Thank you for your responses.
Much appreciated.
Sharon

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

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

Steen Rasmussen

Re: DB2 V8 Dropping partitions
(in response to Sharon Zitterman)
Hey Sharon - that would be a wise approach to keep them. If the partitions are empty anyway they will not cause much harm except for image copy (and hopefully not RECOVERY) and you could decide to HSM MIGRATE them.
You know - the week after you would have dropped them, they would probably need these partitions anyway, but being on DB2 V8 you have the option to ADD, ROTATE and REBALANCE if needed.

Steen Rasmussen
CA Technologies
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 Sharon Zitterman
Sent: Thursday, February 10, 2011 9:35 AM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 V8 Dropping partitions

Steen, Raymond and Max,

Drat the luck...that's what I suspected:)

We would have to perform the "undo" in 4 test environments,
so it is possible the client may decide they can live with the
additional partitions.

Thank you for your responses.
Much appreciated.
Sharon

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

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

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

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

Max Scarpa

Re: DB2 V8 Dropping partitions
(in response to Steen Rasmussen)
I found if you reduce these partitions to a minumum in space in almost all
cases they don't hurt you :-)

Max


_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

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

Joe Geller

Re: DB2 V8 Dropping partitions
(in response to Max Scarpa)
If you leave the extra partitions in test, but dont add them to production,
I would change the limit keys to mostly match production. There is no actual
need to do so, but it will be less confusing and would make a better
match for any performance testing you do.

Joe



Hey Sharon - that would be a wise approach to keep them. If the partitions are empty anyway they will not cause much harm except for image copy (and hopefully not RECOVERY) and you could decide to HSM MIGRATE them.
You know - the week after you would have dropped them, they would probably need these partitions anyway, but being on DB2 V8 you have the option to ADD, ROTATE and REBALANCE if needed.

Steen Rasmussen
CA Technologies
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 Sharon Zitterman
Sent: Thursday, February 10, 2011 9:35 AM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 V8 Dropping partitions

Steen, Raymond and Max,

Drat the luck...that's what I suspected:)

We would have to perform the "undo" in 4 test environments,
so it is possible the client may decide they can live with the
additional partitions.

Thank you for your responses.
Much appreciated.
Sharon

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

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

Dave Petronella

Re: DB2 V8 Dropping partitions
(in response to Joe Geller)
How about:

Drop Synonyms
Create new table B with 3 partitions
Cross-load 4-partition table A data to B
Rename Table A to C
Rename Table B to A
Re-create Synonyms
REBIND Programs

If your table is very large you may be able to better manage your
outage with the above sequence as well as have a backout copy of the table
in case something really goes wrong.



________________________
David Petronella
Vice President
Data Resource Management / DB2 Database Administration
Pershing LLC, a BNY Mellon company
www.pershing.com
Office: (973) 360-7747
Fax: (973) 360-3121
Email: [login to unmask email]

Go Green. Think before you print.



From: Sharon Zitterman <[login to unmask email]>
To: [login to unmask email]
Date: 02/09/2011 11:47 AM
Subject: [DB2-L] DB2 V8 Dropping partitions
Sent by: IDUG DB2-L <[login to unmask email]>



Hi,

We added 4 partitions to 3 tables in a client’s test environment
because we believed at the time that we would need to do the
same for the those tables in production to accommodate 2011
data growth and we were preparing the test environment to mirror
production structurally when the change was made there.

Further analysis has determined that we don’t need to add more
partitions in production, so the client wants us to remove the
partitions we added in their test environment.

In V8, of course, not having to drop and recreate the tables,
we were able to add the partitions in test fairly quickly and easily.
I suspect reversing this change will not be as straight forward, but
have found little info on this topic.

Can someone give me a running start or point me in the right
direction?

Thanks in advance.

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 *
http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2
information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% 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 Listserv



******************************************************
IMPORTANT: Any information contained in this communication is intended for the use of the named individual or entity. All information contained in this communication is not intended or construed as an offer, solicitation, or a recommendation to purchase any security. Advice, suggestions or views presented in this communication are not necessarily those of Pershing LLC nor do they warrant a complete or accurate statement.

If you are not an intended party to this communication, please notify the sender and delete/destroy any and all copies of this communication. Unintended recipients shall not review, reproduce, disseminate nor disclose any information contained in this communication. Pershing LLC reserves the right to monitor and retain all incoming and outgoing communications as permitted by applicable law.

Email communications may contain viruses or other defects. Pershing LLC does not accept liability nor does it warrant that email communications are virus or defect free.
******************************************************


_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

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