DB2 z/OS V9 - Why does a Tablespace Recovery take so long ?

William Moss

DB2 z/OS V9 - Why does a Tablespace Recovery take so long ?

I recently had to recover a tablespace and it seemed to run so slow. I simplistically think of a recovery as the reverse of an image copy and that's what puzzles me. The image copy was done at the tablespace level and took 32 minutes and the restore phase of the recovery of the data took 492 minutes. This was followed by the log apply phase and rebuilding the indexes, but my only question is about the length of the restore phase.

Here are the details of the tablespace which is using index based partitioning and contains 34 million pages of data.

CREATE TABLESPACE RAT8A01
IN DAT8P000
USING STOGROUP SATVP000
PRIQTY 720000 SECQTY 720000
FREEPAGE 10 PCTFREE 20
GBPCACHE CHANGED
TRACKMOD NO
LOGGED
DSSIZE 64 G
NUMPARTS 24
BUFFERPOOL BP20
LOCKSIZE PAGE
LOCKMAX 0
CLOSE YES
COMPRESS YES
CCSID EBCDIC
DEFINE YES
MAXROWS 255;

The recovery was RECOVER TABLESPACE DAT8P000.RAT8A01 DSNUM ALL REUSE TOLOGPOINT X'C7A97FCF0A94'

The image copy was contained on twenty tapes. I'm also puzzled with the wide variation in the time it took to read each tape. Here are the twenty values in minutes:

26, 44, 29, 5, 4, 4, 8, 18, 24, 35, 5, 5, 3, 48, 43, 42, 48, 58, 5 ,37

I understand that if the image copy was done at the partition level, then the recovery would have been much faster based on the use of parallelism. So what happens internally during the restore phase
of a DB2 tablespace recovery that causes it to run so long ?


Thanks in advance for your help,

Bill Moss
Aetna, Inc



This e-mail may contain confidential or privileged information. If
you think you have received this e-mail in error, please advise the
sender by reply e-mail and then delete this e-mail immediately.
Thank you. Aetna

_________________________________________________________________

Register NOW for the IDUG DB2 Tech Conference in Anaheim, May 2-6, 2011!
_________________________________________________________________
If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's Listserv

William Moss

DB2 z/OS V9 - Why does a Tablespace Recovery take so long ?
(in response to William Moss)

I recently had to recover a tablespace and it seemed to run so slow. I simplistically think of a recovery as the reverse of an image copy and that's what puzzles me. The image copy was done at the tablespace level and took 32 minutes and the restore phase of the recovery of the data took 492 minutes. This was followed by the log apply phase and rebuilding the indexes, but my only question is about the length of the restore phase.

Here are the details of the tablespace which is using index based partitioning and contains 34 million pages of data.

CREATE TABLESPACE RAT8A01
IN DAT8P000
USING STOGROUP SATVP000
PRIQTY 720000 SECQTY 720000
FREEPAGE 10 PCTFREE 20
GBPCACHE CHANGED
TRACKMOD NO
LOGGED
DSSIZE 64 G
NUMPARTS 24
BUFFERPOOL BP20
LOCKSIZE PAGE
LOCKMAX 0
CLOSE YES
COMPRESS YES
CCSID EBCDIC
DEFINE YES
MAXROWS 255;

The recovery was RECOVER TABLESPACE DAT8P000.RAT8A01 DSNUM ALL REUSE TOLOGPOINT X'C7A97FCF0A94'

The image copy was contained on twenty tapes. I'm also puzzled with the wide variation in the time it took to read each tape. Here are the twenty values in minutes:

26, 44, 29, 5, 4, 4, 8, 18, 24, 35, 5, 5, 3, 48, 43, 42, 48, 58, 5 ,37

I understand that if the image copy was done at the partition level, then the recovery would have been much faster based on the use of parallelism. So what happens internally during the restore phase
of a DB2 tablespace recovery that causes it to run so long ?


Thanks in advance for your help,

Bill Moss
Aetna, Inc



This e-mail may contain confidential or privileged information. If
you think you have received this e-mail in error, please advise the
sender by reply e-mail and then delete this e-mail immediately.
Thank you. Aetna

_________________________________________________________________

Register NOW for the IDUG DB2 Tech Conference in Anaheim, May 2-6, 2011!
_________________________________________________________________
International DB2 User Group (IDUG) - Independent, not-for-profit, User Run
Your only source for independent, unbiased, and trusted DB2 information

Avram Friedman

Re: DB2 z/OS V9 - Why does a Tablespace Recovery take so long ?
(in response to William Moss)
A RECOVER TOCOPY is the reverse of an image copy, yours is a RECOVER TOLOGPOINT which requires the processing of logs, which may be archived.

Anything other than a RECOVER TOCURRENT requires the recovery / rebuild of indexes and verification / check of RI as well, hope you did that.

You need to tell us about your tape subsystem.
ATL, Stackers, or Manual mount?
In the last two cases the operator needs to find the tapes for recovery.
Only in the last case does the operator need to find the tapes for copy.

Do you use VTS for copies and or logs?
This impacts recovery time (sometimes better sometimes worse)
If the copies / logs are on the VTS Cache better, they are used directly.
If they are on physical tape they must be retrived and copied to staging before they can be used for recovery.

Best wishes
Avram Friedman

On Wed, 4 May 2011 05:36:54 -0400, Moss, William R <[login to unmask email]> wrote:

>
I recently had to recover a tablespace and it seemed to run so slow. I simplistically think of a recovery as the reverse of an image copy and that's what puzzles me. The image copy was done at the tablespace level and took 32 minutes and the restore phase of the recovery of the data took 492 minutes. This was followed by the log apply phase and rebuilding the indexes, but my only question is about the length of the restore phase.

Here are the details of the tablespace which is using index based partitioning and contains 34 million pages of data.

CREATE TABLESPACE RAT8A01
IN DAT8P000
USING STOGROUP SATVP000
PRIQTY 720000 SECQTY 720000
FREEPAGE 10 PCTFREE 20
GBPCACHE CHANGED
TRACKMOD NO
LOGGED
DSSIZE 64 G
NUMPARTS 24
BUFFERPOOL BP20
LOCKSIZE PAGE
LOCKMAX 0
CLOSE YES
COMPRESS YES
CCSID EBCDIC
DEFINE YES
MAXROWS 255;

The recovery was RECOVER TABLESPACE DAT8P000.RAT8A01 DSNUM ALL REUSE TOLOGPOINT X'C7A97FCF0A94'

The image copy was contained on twenty tapes. I'm also puzzled with the wide variation in the time it took to read each tape. Here are the twenty values in minutes:

26, 44, 29, 5, 4, 4, 8, 18, 24, 35, 5, 5, 3, 48, 43, 42, 48, 58, 5 ,37

I understand that if the image copy was done at the partition level, then the recovery would have been much faster based on the use of parallelism. So what happens internally during the restore phase
of a DB2 tablespace recovery that causes it to run so long ?


Thanks in advance for your help,

Bill Moss
Aetna, Inc



This e-mail may contain confidential or privileged information. If
you think you have received this e-mail in error, please advise the
sender by reply e-mail and then delete this e-mail immediately.
Thank you. Aetna
>
>_________________________________________________________________
>
>Register NOW for the IDUG DB2 Tech Conference in Anaheim, May 2-6, 2011!
>_________________________________________________________________
> International DB2 User Group (IDUG) - Independent, not-for-profit, User Run
> Your only source for independent, unbiased, and trusted DB2 information
>
>

_________________________________________________________________

Register NOW for the IDUG DB2 Tech Conference in Anaheim, May 2-6, 2011!
_________________________________________________________________
International DB2 User Group (IDUG) - Independent, not-for-profit, User Run
Your only source for independent, unbiased, and trusted DB2 information

Isaac Yassin

Re: DB2 z/OS V9 - Why does a Tablespace Recovery take so long ?
(in response to Avram Friedman)
Hi,
According to the times given per each tape read it looks like
some suffered long wait time for physical tape drive -
probably having too many tape requests (by other jobs) and too
few tape drives.
without knowing the physical implementation of the tape system
it's only a guess.

Isaac


---- Original message ----
>Date:   Wed, 4 May 2011 05:36:54 -0400
>From:   "Moss, William R" <[login to unmask email]>
>Subject:   [DB2-L] DB2 z/OS V9 - Why does a Tablespace
Recovery take so long ?
>To:   [login to unmask email]
>
>
> I recently had to recover a tablespace and it seemed
> to run so slow. I simplistically think of a recovery
> as the reverse of an image copy and that's what
> puzzles me. The image copy was done at the
> tablespace level and took 32 minutes and the restore
> phase of the recovery of the data took 492 minutes.
> This was followed by the log apply phase and
> rebuilding the indexes, but my only question is
> about the length of the restore phase.
>
> Here are the details of the tablespace which is
> using index based partitioning and contains 34
> million pages of data.
>
> CREATE TABLESPACE RAT8A01
> IN DAT8P000
> USING STOGROUP SATVP000
> PRIQTY 720000 SECQTY 720000
> FREEPAGE 10 PCTFREE 20
> GBPCACHE CHANGED
> TRACKMOD NO
> LOGGED
> DSSIZE 64 G
> NUMPARTS 24
> BUFFERPOOL BP20
> LOCKSIZE PAGE
> LOCKMAX 0
> CLOSE YES
> COMPRESS YES
> CCSID EBCDIC
> DEFINE YES
> MAXROWS 255;
>
> The recovery was RECOVER TABLESPACE DAT8P000.RAT8A01
> DSNUM ALL REUSE TOLOGPOINT X'C7A97FCF0A94'
>
> The image copy was contained on twenty tapes. I'm
> also puzzled with the wide variation in the time it
> took to read each tape. Here are the twenty values
> in minutes:
>
> 26, 44, 29, 5, 4, 4, 8, 18, 24, 35, 5, 5, 3,
> 48, 43, 42, 48, 58, 5 ,37
>
> I understand that if the image copy was done at the
> partition level, then the recovery would have been
> much faster based on the use of parallelism. So what
> happens internally during the restore phase
> of a DB2 tablespace recovery that causes it to run
> so long ?
>
>
> Thanks in advance for your help,
>
> Bill Moss
> Aetna, Inc
>
> This e-mail may contain confidential or privileged
> information. If you think you have received this
> e-mail in error, please advise the sender by reply
> e-mail and then delete this e-mail immediately.
> Thank you. Aetna
>
> ------------------------------------------------
>
> Independent, not-for-profit, User Run - the IDUG
> difference!
>
> The IDUG DB2-L Listserv is only part of your
> membership in IDUG. If you are not already an IDUG
> member, please register here.
Isaac Yassin
IBM Gold Consultant
IBM Information Champion
IBM Certified Solution Expert
IBM Certified Database Administrator - DB2 for z/OS V8,9 & 10

Attend DB2 Tech Conference - The premiere event for DB2 professionals.
North America, 2-6 May, Anaheim California
EMEA, 14-18 November, Prague Czech Republic
Learn more at http://www.idug.org

_________________________________________________________________

Register NOW for the IDUG DB2 Tech Conference in Anaheim, May 2-6, 2011!
_________________________________________________________________
International DB2 User Group (IDUG) - Independent, not-for-profit, User Run
Your only source for independent, unbiased, and trusted DB2 information