SV: [DB2-L] Modify Recovery Running Slow

Olle Brostrom

SV: [DB2-L] Modify Recovery Running Slow
Hello Devyani,
I think many of us have been in the same dilemma as you.
The remedies to have good performance on running Modify Recovery is:
Run Online Reorg of SYSLGRNX and SYSCOPY in window where you normally don't run utilities.
Run the Online Reorg of SYSLGRNX and SYSCOPY regulary.
Run Modify Recovery in window where you normally don't run utilities.
Run one large Modify Recovery for the complete DB2 subsystem.

What DB2 are you on? There have alse been enhancements in DB2 Version 8 and 9 in this area.

We run a weekly Modify Recovery which is preceeded by the reorg, the day before. The Modify Recovery job consist of about 2500 tablespaces (on Tablespace level) and SYSCOPY has 270,000 rows before the clean-up and it runs for about 15-17 minutes. If we skip the reorg the run time are about the double after one week!

Regards, Olle



-----Ursprungligt meddelande-----
Från: IDUG DB2-L [mailto:[login to unmask email] För Devyani Sahasrabuddhe
Skickat: den 19 november 2010 16:05
Till: [login to unmask email]
Ämne: [DB2-L] Modify Recovery Running Slow

Hello List,

Can anybody share about probable reasons behind modify recovery and statistics running slow. I would appreciate any clues as to what should I look for to figure out performance problems.

Thanks,
Devyani

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

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

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* 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

Devyani Ramesh

Re: SV: [DB2-L] Modify Recovery Running Slow
(in response to Olle Brostrom)
Thanks for your suggestions Olle!

We have DB2 V9.1 with maitainance level 1003. As you have mentioned, we have a large modify recovery job which runs weekly on production system. However, it runs slow just for one subsystem.

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* 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

Mike Bell

Re: SV: [DB2-L] Modify Recovery Running Slow
(in response to Devyani Ramesh)
The only two variables for MODIFY are SYSCOPY and SYSLGRNX. If the number
of SYSCOPY entries isn't very different from the other subsystems, then the
SYSLGRNX has to be the issue. The purpose of SYSLGRNX is to record start
and stop points in the log for a given tablespace/indexspace.

Things that can generate SYSLGRNX entries
1. switch to read-only
2. start/stop
3. dataset open/close

Item 1 is set by ZPARM - panel DSNTIPL -
12 RO SWITCH CHKPTS ===> 5 Checkpoints to read-only switch. 1-32767
If the subsystem has a lot of logging and is taking too many checkpoints,
you have created a performance issue that is showing up in SYSLGRNX. Review
your entries of the same panel for checkpoints
6 CHECKPOINT FREQ ===> 500000 Log records or minutes per checkpoint
7 FREQUENCY TYPE ===> LOGRECS CHECKPOINT FREQ units. LOGRECS, MINUTES

Item 3. is also set by ZPARM - panel DSNTIPE
13 MAX OPEN FILE REFS ===> 100 Maximum concurrent open data sets - with v9
and the multiple tcb's for open/close processing this can be a hidden
overhead.

I do not remember the exact SMF100 fields that will show the read-only
switch and dataset open/close counts but have worked with them previously.

Mike

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Devyani
Sahasrabuddhe
Sent: Monday, November 22, 2010 3:33 AM
To: [login to unmask email]
Subject: Re: [DB2-L] SV: [DB2-L] Modify Recovery Running Slow

Thanks for your suggestions Olle!

We have DB2 V9.1 with maitainance level 1003. As you have mentioned, we have
a large modify recovery job which runs weekly on production system. However,
it runs slow just for one subsystem.

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 *
http://IDUG.ORG/EMEA *
* 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 EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* 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