Changing limit-key on a Partition (revisited)

Nick CIANCI

Changing limit-key on a Partition (revisited)
Another sleepless night, and a pending repartitioning exercise has got me
thinking.

The real hairy part of repartitioning is the ReOrg (beats me why it has to
be SHR-NONE). If you can't shift the REORP flag you are "stuffed", once
you get over that hill, a number of other possibilities open up.

OK so the typical scenario (at least with us) is you have "n" old
partitions (+NPIs), where the nth partition (incrementing key) is going to
run out, and you need to adjust that partition and add more before the
beastie explodes on you!

n-1 (has data and remains as is) ; n (doesn't and is being altered) ; n+1
(new ... no data) .... n+m (new ... no data)

OK so ReOrging Ptn's n:n+m is what you need to do. & because there is no
data in those partitions the ReOrg should be quick, and there shouldn't be
a Build-2 to speak of.

So what happens if things turn to custard at that point. If you can't
budge the REORP status as previously discussed in this thread you are up an
unnamed creek without a paddle.

So what to do?

What springs to my mind is drop redefine the object, and reload the data,
but if you've a 1/2 Tb of data and index space to restore; precious little
SORT work for Index builds; and even less time + angry users (which at that
hour of the night reminds you of a scary Hitchcock movie) you want the
quickest least resource consuming options to get the business back up and
running. For me that means DSN1COPY with OBIDXLAT to restore both table &
indices; by part or part ranges for added ||-ism. It also assumes you
restored the DDL exactly as it was at the start.

Now the kicker to this, and sorry for drawing this out, is you don't have
the window available to back up the datasets ahead of time (either DSN1COPY
or an Image COPY). Therefore what I was considering was the possibility of
renaming the DB2 datasets (particularly the huge NPIs) before redefining
the objects and then DSN1COPY them back afterwards. If anyone can see a
flaw in this I'd be interested to here! .. .or if they have a better idea?
I've considered a DFDSS logical dump, but don't know if that will save me
enough time; and there is still an OBID translation required.

What would be nice is if I could just adjust the OBID (et al) on the old
file in situ, that's to say DSN1COPY with OBIDXLAT where SYSUT1=SYSUT2.
However as far as I can tell that is not possible (unless I want to go
rummaging around with Repair on the header pages ... which I don't)

Regards,
  Nick CIANCI
_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/rug/index.html - with almost 150 IDUG Regional User Groups,
there is probably one near you!
Regional User Groups are your local connection to the Worldwide DB2 User Community
_____________________________________________________________________

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

Raymond Bell

Re: Changing limit-key on a Partition (revisited)
(in response to Nick CIANCI)
Hey Nic,

You still in Canberra? Got access to any ISV Reorg products? To paraphrase Rolf Harris, 'Can you guess which one it is yet?'

It's possible at least one way to do a Shrlevel Change Reorg, altering the partitioning keys in the process. We (Oops. OK; I've been rumbled as to who the ISV is I have in mind) do eventually/briefly need exclusive access to the DBD to record the key range changes but that's not usually a problem. If it turns out to be, you need to cancel the task holding the lock (a batch job, long-running CICS transaction; whoever) and restart the Reorg.

Greetings from Blighty,


Raymond

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Nick Cianci
Sent: 23 November 2009 12:24
To: [login to unmask email]
Subject: [DB2-L] Changing limit-key on a Partition (revisited)

Another sleepless night, and a pending repartitioning exercise has got me
thinking.

The real hairy part of repartitioning is the ReOrg (beats me why it has to
be SHR-NONE). If you can't shift the REORP flag you are "stuffed", once
you get over that hill, a number of other possibilities open up.

OK so the typical scenario (at least with us) is you have "n" old
partitions (+NPIs), where the nth partition (incrementing key) is going to
run out, and you need to adjust that partition and add more before the
beastie explodes on you!

n-1 (has data and remains as is) ; n (doesn't and is being altered) ; n+1
(new ... no data) .... n+m (new ... no data)

OK so ReOrging Ptn's n:n+m is what you need to do. & because there is no
data in those partitions the ReOrg should be quick, and there shouldn't be
a Build-2 to speak of.

So what happens if things turn to custard at that point. If you can't
budge the REORP status as previously discussed in this thread you are up an
unnamed creek without a paddle.

So what to do?

What springs to my mind is drop redefine the object, and reload the data,
but if you've a 1/2 Tb of data and index space to restore; precious little
SORT work for Index builds; and even less time + angry users (which at that
hour of the night reminds you of a scary Hitchcock movie) you want the
quickest least resource consuming options to get the business back up and
running. For me that means DSN1COPY with OBIDXLAT to restore both table &
indices; by part or part ranges for added ||-ism. It also assumes you
restored the DDL exactly as it was at the start.

Now the kicker to this, and sorry for drawing this out, is you don't have
the window available to back up the datasets ahead of time (either DSN1COPY
or an Image COPY). Therefore what I was considering was the possibility of
renaming the DB2 datasets (particularly the huge NPIs) before redefining
the objects and then DSN1COPY them back afterwards. If anyone can see a
flaw in this I'd be interested to here! .. .or if they have a better idea?
I've considered a DFDSS logical dump, but don't know if that will save me
enough time; and there is still an OBID translation required.

What would be nice is if I could just adjust the OBID (et al) on the old
file in situ, that's to say DSN1COPY with OBIDXLAT where SYSUT1=SYSUT2.
However as far as I can tell that is not possible (unless I want to go
rummaging around with Repair on the header pages ... which I don't)

Regards,
  Nick CIANCI
_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/rug/index.html - with almost 150 IDUG Regional User Groups,
there is probably one near you!
Regional User Groups are your local connection to the Worldwide DB2 User Community
_____________________________________________________________________

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 *
_____________________________________________________________________

http://www.idug.org/rug/index.html - with almost 150 IDUG Regional User Groups,
there is probably one near you!
Regional User Groups are your local connection to the Worldwide DB2 User Community
_____________________________________________________________________

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

Glenn mackey

Re: Changing limit-key on a Partition (revisited)
(in response to Raymond Bell)
Some other ideas..

1. If possible, redefine your own copy table and define to meet your needs. Then load the data "manually" and do a rename at the end of exercise. This makes assumptions on outage etc. But you never want to be left in a situation where you cannot recover.

Perhaps if data is not updated, only inserted, then you could load the data without a time constraint, and only have an outage window when you load the last lot of data.

2. Someone did post that you could ask IBM, and they maybe able to provide you with a undocumented way to reset the REORP status.

3. Make sure you do have enough time to do a reload of the data, and redefine the whole table if needed.


-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Nick Cianci
Sent: Monday, November 23, 2009 6:24 AM
To: [login to unmask email]
Subject: [DB2-L] Changing limit-key on a Partition (revisited)

Another sleepless night, and a pending repartitioning exercise has got me thinking.

The real hairy part of repartitioning is the ReOrg (beats me why it has to be SHR-NONE). If you can't shift the REORP flag you are "stuffed", once you get over that hill, a number of other possibilities open up.

OK so the typical scenario (at least with us) is you have "n" old partitions (+NPIs), where the nth partition (incrementing key) is going to run out, and you need to adjust that partition and add more before the beastie explodes on you!

n-1 (has data and remains as is) ; n (doesn't and is being altered) ; n+1 (new ... no data) .... n+m (new ... no data)

OK so ReOrging Ptn's n:n+m is what you need to do. & because there is no data in those partitions the ReOrg should be quick, and there shouldn't be a Build-2 to speak of.

So what happens if things turn to custard at that point. If you can't budge the REORP status as previously discussed in this thread you are up an unnamed creek without a paddle.

So what to do?

What springs to my mind is drop redefine the object, and reload the data, but if you've a 1/2 Tb of data and index space to restore; precious little SORT work for Index builds; and even less time + angry users (which at that hour of the night reminds you of a scary Hitchcock movie) you want the quickest least resource consuming options to get the business back up and running. For me that means DSN1COPY with OBIDXLAT to restore both table & indices; by part or part ranges for added ||-ism. It also assumes you restored the DDL exactly as it was at the start.

Now the kicker to this, and sorry for drawing this out, is you don't have the window available to back up the datasets ahead of time (either DSN1COPY or an Image COPY). Therefore what I was considering was the possibility of renaming the DB2 datasets (particularly the huge NPIs) before redefining the objects and then DSN1COPY them back afterwards. If anyone can see a flaw in this I'd be interested to here! .. .or if they have a better idea?
I've considered a DFDSS logical dump, but don't know if that will save me enough time; and there is still an OBID translation required.

What would be nice is if I could just adjust the OBID (et al) on the old file in situ, that's to say DSN1COPY with OBIDXLAT where SYSUT1=SYSUT2.

However as far as I can tell that is not possible (unless I want to go rummaging around with Repair on the header pages ... which I don't)

Regards,
  Nick CIANCI
_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________

http://www.idug.org/rug/index.html - with almost 150 IDUG Regional User Groups, there is probably one near you!
Regional User Groups are your local connection to the Worldwide DB2 User Community _____________________________________________________________________

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


-----------------------------------------


DISCLAIMER:
This message and accompanying documents are covered by the
Electronic Communications Privacy Act, 18 U.S.C. 2510-2521, and
contains information intended for the specified individual(s) only.
This information is confidential. If you are not the intended
recipient or an agent responsible for delivering it to the intended
recipient, you are hereby notified that you have received this
document in error and that any review, dissemination, copying, or
the taking of any action based on the contents of this information
is strictly prohibited. If you have received this communication in
error, please notify us immediately by e-mail, and delete the
original message.


_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/rug/index.html - with almost 150 IDUG Regional User Groups,
there is probably one near you!
Regional User Groups are your local connection to the Worldwide DB2 User Community
_____________________________________________________________________

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