What if recovery fails after reorg/load with log no?

J Sullens

What if recovery fails after reorg/load with log no?
Dear List,

If reorg/load with log no was done followed by a full
imagecopy on a tablespace and subsequently after couple of days
(during which only incremental imagecopies were taken)
a recovery becomes necessary but the full imagecopy was on a corrupted tape.

What are the options available to the DBA to recover the tablespace other
than ........proceeding on a sudden and emergent medical leave :-( .



Michael Ebert

Re: What if recovery fails after reorg/load with log no?
(in response to J Sullens)
Hello J,

if REPAIR ... SET NORCVRPEND or -START .. ACCESS(FORCE) are no options (you
didn't specify why the TS is in RECP), then my thoughts would be along the
following lines (NOT tested - just running in free association mode):
Procedure A:
1) RECOVER the TS TORBA X'...' where the RBA specifies the time of the REORG LOG
NO (from SYSCOPY), or, if DB2 balks, the highest RBA just before that it will
accept.
2) run REORG
3) take Full IC
4) DSN1COPY with OBIDXLAT to test TS with the same table structure for safety
5) apply Incremental copies with DSN1COPY with INCRCOPY option to test TS
6) run tests to see whether tables in test TS are accessible: if no, hope for
other ideas...
7) if yes, DSN1COPY back to original TS using reverse OBIDXLAT; REBUILD Indexes
8) use only Full IC in the future
If your TS has a non-unique clustering index, there is a slight chance that
after the REORG the TS will be physically different from the state it was after
the original REORG... in which case you're probably sunk.

This Proc A will only get you to the point of the last IIC, but maybe that's
enough. If not:
Procedure B:
1) Take copies (Volume dumps) of Catalog, Directory, BSDS, Active logs, Archived
logs (all other DB2 activities must be stopped, of course)
2) RECOVER TS TORBA as above
3) REORG
4) Full IC. It's probably a good idea to use the same name (or tape vol serial)
as before
5) Restore Cat, Dir, etc.
6) Use RECOVER on the TS.
We had to do something like this procedure B a couple of weeks ago when we
accidentally dropped a SAP TS; look in the archives for details (subject line
was "Urgent help needed", if I remember correctly).
Same remark re non-unique IXC as above.

HtH

Dr. Michael Ebert
DB2 Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany




From: J Sullens <[login to unmask email]> on 22/12/2000 05:18 GMT

Please respond to DB2 Data Base Discussion List <[login to unmask email]>





|--------->
| |
|--------->
>--------------------------------------------------------------------------->
| |
>--------------------------------------------------------------------------->
>-------------------------------------------|
| |
>-------------------------------------------|
|--------->
|To: |
|--------->
>--------------------------------------------------------------------------->
|[login to unmask email] |
>--------------------------------------------------------------------------->
>-------------------------------------------|
| |
| |
>-------------------------------------------|
|--------->
|cc: |
|--------->
>--------------------------------------------------------------------------->
| (bcc: Michael Ebert/MUC/AMADEUS) |
>--------------------------------------------------------------------------->
>-------------------------------------------|
| |
| |
>-------------------------------------------|
|--------->
| |
|--------->
>--------------------------------------------------------------------------->
| |
>--------------------------------------------------------------------------->
>-------------------------------------------|
| |
>-------------------------------------------|
|--------->
|Subject: |
|--------->
>--------------------------------------------------------------------------->
|What if recovery fails after reorg/load with log no? |
>--------------------------------------------------------------------------->
>-------------------------------------------|
| |
>-------------------------------------------|







Dear List,

If reorg/load with log no was done followed by a full
imagecopy on a tablespace and subsequently after couple of days
(during which only incremental imagecopies were taken)
a recovery becomes necessary but the full imagecopy was on a corrupted tape.

What are the options available to the DBA to recover the tablespace other
than ........proceeding on a sudden and emergent medical leave :-( .



Michael Murley

Re: What if recovery fails after reorg/load with log no?
(in response to Michael Ebert)
If there is any sort of full backup after the REORG (pack dump ?) you might
be able to restore from it and apply the log with RECOVER LOGONLY. But you
cannot apply log records through a REORG LOG NO, so recovery to current
using a full image copy prior to the REORG is not possible. Incremental
image copies are only valid if the prior full copy is available.

If you're trying to recover to a point in time before current but after the
reorg and the table space is not physically damaged, BMC RECOVER PLUS for
DB2* has a BACKOUT feature that allows recovery without an image copy in
that case. It just starts with the current VSAM data set and applies log
backward to the desired point in time.

But if you need to recover to current (damaged data set or whatever) and
there is no full backup after the REORG, emergency medical leave may be the
best strategy :~) . You can recover from an image copy prior to the REORG,
but only up to the point of the REORG event in SYSCOPY. You cannot apply log
records through a LOG NO event.

Michael Murley
BMC


<snip from digest>

Date: Thu, 21 Dec 2000 23:18:39 -0600
From: J Sullens <[login to unmask email]>
Subject: What if recovery fails after reorg/load with log no?

Dear List,

If reorg/load with log no was done followed by a full
imagecopy on a tablespace and subsequently after couple of days
(during which only incremental imagecopies were taken)
a recovery becomes necessary but the full imagecopy was on a corrupted tape.

What are the options available to the DBA to recover the tablespace other
than ........proceeding on a sudden and emergent medical leave :-( .

</snip>



Mohammed Nayeem

Re: Reorg
(in response to Michael Murley)
5 non-partitioned indexes
1 partitioned index
9398691 rows

---------------------- Forwarded by Mohammed Nayeem/MoMedicaid/US on 12/22/2000
10:10 AM ---------------------------


[login to unmask email] on 12/21/2000 06:54:41 PM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

To: [login to unmask email]
cc: (bcc: Mohammed Nayeem/MoMedicaid/US)

Subject: Re: Reorg



Nayeem,
When reorganizing very large tablespaces, I generally use the SORTNUM 32.
But, I think that your sort program can override this. For example, I know
that even if I specify SORTNUM 32, it can use fewer sortworks based on the
number of rows and the rowsize that reorg passes it.
Obviously, I dynamically allocate my sortworks.
And, if sort uses fewer sortwork datasets, then it must make them larger.

If you want a rough idea of the size of these sortworks, then you would
need to know how big your indexes are. I am sure that there are formulas
for calculating this, but I would only guess that if you doubled the size
of your indexes, you might have a good general idea of how big all of the
sortworks are. By dynamically allocating sortworks, I like to let it do
the math. I just try to make sure that we have enough space in this dasd
pool.

I am really not sure what your question is, but does this help?
When you ran your reorg, did you dynamically allocate your sortworks?
And, based on your EXCP numbers, how many sortworks did you actually use?
Hugo also asked some very good questions about how many rows, how many
non-partitioned indexes??
In general, just how big is this?
And, if there are no non-partitioned indexes, could you reorg each
partition one at a time instead of the entire tablespace at once?

Thanks,
Tim



Mohammed Nayeem
<Mohammed_Nayeem@ To: [login to unmask email]
MOMED.COM> cc:
Sent by: DB2 Data Subject: Re: Reorg
Base Discussion
List
<[login to unmask email]>


12/21/2000 04:20
PM
Please respond to
DB2 Data Base
Discussion List






Yep I am using this way

REORG TABLESPACE XXXXXXXX.YYYYYYYY SORTDATA SORTKEY

How many Sort files is required ?
We have 38 partitions.




---------------------- Forwarded by Mohammed Nayeem/MoMedicaid/US on
12/21/2000
04:19 PM ---------------------------


[login to unmask email] on 12/21/2000 03:41:55 PM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

To: [login to unmask email]
cc: (bcc: Mohammed Nayeem/MoMedicaid/US)

Subject: Re: Reorg



Nayeem,
At the last DB2 tech conference, one of the DB2 developers tried to make
this as obvious as possible.
If I remember correctly, he said:
"If your tablespace needs to be reorged, you should use sortdata. If your
tablespace does not need to be reorged, then why are you running reorg on
it?".

So, I am curious, why don't you want to use sortdata?

Thanks,
Tim



Mohammed Nayeem
<Mohammed_Nayeem@ To: [login to unmask email]
MOMED.COM> cc:
Sent by: DB2 Data Subject: Reorg
Base Discussion
List
<[login to unmask email]>


12/21/2000 03:25
PM
Please respond to
DB2 Data Base
Discussion List






Hi

I am getting hell of time in sort phase when running REORG against
partitioned
TS and I am using DB2 Utility :
REORG TABLESPACE XXXXXXXX.YYYYYYYY

If without SORTDATA and SORTKEYS , in sort phase after 75 minutes I
terminated
util and ran REBUILD INDEX ALL

Why and how?

Thx
Nayeem








the















Mohammed Nayeem

Re: Reorg
(in response to Mohammed Nayeem)
Thanks Tim for your responses.
We have BMC here but I have not idea about its working procedure.
Plz any wanna explain me about its features.

Thanks
Nayeem

---------------------- Forwarded by Mohammed Nayeem/MoMedicaid/US on 12/22/2000
10:22 AM ---------------------------


[login to unmask email] on 12/21/2000 06:54:41 PM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

To: [login to unmask email]
cc: (bcc: Mohammed Nayeem/MoMedicaid/US)

Subject: Re: Reorg



Nayeem,
When reorganizing very large tablespaces, I generally use the SORTNUM 32.
But, I think that your sort program can override this. For example, I know
that even if I specify SORTNUM 32, it can use fewer sortworks based on the
number of rows and the rowsize that reorg passes it.
Obviously, I dynamically allocate my sortworks.
And, if sort uses fewer sortwork datasets, then it must make them larger.

If you want a rough idea of the size of these sortworks, then you would
need to know how big your indexes are. I am sure that there are formulas
for calculating this, but I would only guess that if you doubled the size
of your indexes, you might have a good general idea of how big all of the
sortworks are. By dynamically allocating sortworks, I like to let it do
the math. I just try to make sure that we have enough space in this dasd
pool.

I am really not sure what your question is, but does this help?
When you ran your reorg, did you dynamically allocate your sortworks?
And, based on your EXCP numbers, how many sortworks did you actually use?
Hugo also asked some very good questions about how many rows, how many
non-partitioned indexes??
In general, just how big is this?
And, if there are no non-partitioned indexes, could you reorg each
partition one at a time instead of the entire tablespace at once?

Thanks,
Tim



Mohammed Nayeem
<Mohammed_Nayeem@ To: [login to unmask email]
MOMED.COM> cc:
Sent by: DB2 Data Subject: Re: Reorg
Base Discussion
List
<[login to unmask email]>


12/21/2000 04:20
PM
Please respond to
DB2 Data Base
Discussion List






Yep I am using this way

REORG TABLESPACE XXXXXXXX.YYYYYYYY SORTDATA SORTKEY

How many Sort files is required ?
We have 38 partitions.




---------------------- Forwarded by Mohammed Nayeem/MoMedicaid/US on
12/21/2000
04:19 PM ---------------------------


[login to unmask email] on 12/21/2000 03:41:55 PM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

To: [login to unmask email]
cc: (bcc: Mohammed Nayeem/MoMedicaid/US)

Subject: Re: Reorg



Nayeem,
At the last DB2 tech conference, one of the DB2 developers tried to make
this as obvious as possible.
If I remember correctly, he said:
"If your tablespace needs to be reorged, you should use sortdata. If your
tablespace does not need to be reorged, then why are you running reorg on
it?".

So, I am curious, why don't you want to use sortdata?

Thanks,
Tim



Mohammed Nayeem
<Mohammed_Nayeem@ To: [login to unmask email]
MOMED.COM> cc:
Sent by: DB2 Data Subject: Reorg
Base Discussion
List
<[login to unmask email]>


12/21/2000 03:25
PM
Please respond to
DB2 Data Base
Discussion List






Hi

I am getting hell of time in sort phase when running REORG against
partitioned
TS and I am using DB2 Utility :
REORG TABLESPACE XXXXXXXX.YYYYYYYY

If without SORTDATA and SORTKEYS , in sort phase after 75 minutes I
terminated
util and ran REBUILD INDEX ALL

Why and how?

Thx
Nayeem








the















RICK (SBCSI) DAVIS

Re: Reorg
(in response to Mohammed Nayeem)
Tim,
You should always use SORTDATA if the data does in fact need to be
reorg'd *UNLESS*, see Utility Guide:
"The data set is very large and there is not enough DASD available for
sorting.
The longest possible composite record size is greater than 32760."

HTH,
Rick Davis

"This e-mail and any files transmitted with it are the property of SBC, are
confidential, and are intended solely for the use of the individual or
entity to whom this e-mail is addressed. If you are not one of the named
recipient(s) or otherwise have reason to believe that you have received this
message in error, please notify the sender at 314-235-6854 and delete this
message immediately from your computer. Any other use,
retention, dissemination, forwarding, printing, or copying of this e-mail is
strictly prohibited."

-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Thursday, December 21, 2000 3:42 PM
To: [login to unmask email]
Subject: Re: Reorg

Nayeem,
At the last DB2 tech conference, one of the DB2 developers tried to make
this as obvious as possible.
If I remember correctly, he said:
"If your tablespace needs to be reorged, you should use sortdata. If your
tablespace does not need to be reorged, then why are you running reorg on
it?".

So, I am curious, why don't you want to use sortdata?

Thanks,
Tim

Mohammed Nayeem
<Mohammed_Nayeem@ To: [login to unmask email]
MOMED.COM> cc:
Sent by: DB2 Data Subject: Reorg
Base Discussion
List
<[login to unmask email]>

12/21/2000 03:25
PM
Please respond to
DB2 Data Base
Discussion List

Hi

I am getting hell of time in sort phase when running REORG against
partitioned
TS and I am using DB2 Utility :
REORG TABLESPACE XXXXXXXX.YYYYYYYY

If without SORTDATA and SORTKEYS , in sort phase after 75 minutes I
terminated
util and ran REBUILD INDEX ALL

Why and how?

Thx
Nayeem



Tim Lowe

Re: Reorg
(in response to RICK (SBCSI) DAVIS)
Rick,
Yes, I know, but I was trying to quote the speaker that I had heard at the
tech conference, and I did not think that I should "correct" something that
I was trying to quote.

Thanks,
Tim



"DAVIS, RICK
(SBCSI)" To: [login to unmask email]
<[login to unmask email] cc:
OM> Subject: Re: Reorg
Sent by: DB2
Data Base
Discussion
List
<[login to unmask email]
OM>


12/22/2000
02:22 PM
Please
respond to
DB2 Data Base
Discussion
List






Tim,
You should always use SORTDATA if the data does in fact need to be
reorg'd *UNLESS*, see Utility Guide:
"The data set is very large and there is not enough DASD available for
sorting.
The longest possible composite record size is greater than 32760."

HTH,
Rick Davis

"This e-mail and any files transmitted with it are the property of SBC, are
confidential, and are intended solely for the use of the individual or
entity to whom this e-mail is addressed. If you are not one of the named
recipient(s) or otherwise have reason to believe that you have received
this
message in error, please notify the sender at 314-235-6854 and delete this
message immediately from your computer. Any other use,
retention, dissemination, forwarding, printing, or copying of this e-mail
is
strictly prohibited."

-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Thursday, December 21, 2000 3:42 PM
To: [login to unmask email]
Subject: Re: Reorg

Nayeem,
At the last DB2 tech conference, one of the DB2 developers tried to make
this as obvious as possible.
If I remember correctly, he said:
"If your tablespace needs to be reorged, you should use sortdata. If your
tablespace does not need to be reorged, then why are you running reorg on
it?".

So, I am curious, why don't you want to use sortdata?

Thanks,
Tim

Mohammed Nayeem
<Mohammed_Nayeem@ To: [login to unmask email]
MOMED.COM> cc:
Sent by: DB2 Data Subject: Reorg
Base Discussion
List
<[login to unmask email]>

12/21/2000 03:25
PM
Please respond to
DB2 Data Base
Discussion List

Hi

I am getting hell of time in sort phase when running REORG against
partitioned
TS and I am using DB2 Utility :
REORG TABLESPACE XXXXXXXX.YYYYYYYY

If without SORTDATA and SORTKEYS , in sort phase after 75 minutes I
terminated
util and ran REBUILD INDEX ALL

Why and how?

Thx
Nayeem








James Campbell

Re: What if recovery fails after reorg/load with log no?
(in response to Tim Lowe)
I suppose you have only a single image copy (ie no DR IC)?

Both BMC and CA (don't know about others) have products that can read a log
and generate equivalent SQL statements. Don't know about any requirements
(eg DATA CAPTURE?). Combined with a recovery to just before the reorg ...

/* standard disclaimer */
James Campbell
DBA
Hansen Corporation, Doncaster
+61 3 9843 8442
[login to unmask email]
-----Original Message-----
From: J Sullens [mailto:[login to unmask email]
Sent: Friday, December 22, 2000 4:19 PM
To: [login to unmask email]
Subject: [DB2-L] What if recovery fails after reorg/load with log no?


Dear List,

If reorg/load with log no was done followed by a full
imagecopy on a tablespace and subsequently after couple of days
(during which only incremental imagecopies were taken)
a recovery becomes necessary but the full imagecopy was on a corrupted tape.

What are the options available to the DBA to recover the tablespace other
than ........proceeding on a sudden and emergent medical leave :-( .







**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**********************************************************************



SANDRA SPARKS

Re: What if recovery fails after reorg/load with log no?
(in response to James Campbell)
Do you have a copy made before the Load? Do you still have the input from
the Load? To what point in time do you wish to return?

-----Original Message-----
From: James Campbell [mailto:[login to unmask email]
Sent: Tuesday, December 26, 2000 6:08 PM
To: [login to unmask email]
Subject: Re: What if recovery fails after reorg/load with log no?


I suppose you have only a single image copy (ie no DR IC)?

Both BMC and CA (don't know about others) have products that can read a log
and generate equivalent SQL statements. Don't know about any requirements
(eg DATA CAPTURE?). Combined with a recovery to just before the reorg ...

/* standard disclaimer */
James Campbell
DBA
Hansen Corporation, Doncaster
+61 3 9843 8442
[login to unmask email]
-----Original Message-----
From: J Sullens [mailto:[login to unmask email]
Sent: Friday, December 22, 2000 4:19 PM
To: [login to unmask email]
Subject: [DB2-L] What if recovery fails after reorg/load with log no?


Dear List,

If reorg/load with log no was done followed by a full
imagecopy on a tablespace and subsequently after couple of days
(during which only incremental imagecopies were taken)
a recovery becomes necessary but the full imagecopy was on a corrupted tape.

What are the options available to the DBA to recover the tablespace other
than ........proceeding on a sudden and emergent medical leave :-( .







**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**********************************************************************








J Sullens

Re: What if recovery fails after reorg/load with log no?
(in response to SANDRA SPARKS)
The question was regarding recovering to current when the most recent full
imagecopy was bad and reorg or load log no was done prior to that. In the
absence of pack dumps etc., Michael Eberts solution of applying incremental
imagecopies seems to hold most promise.