Reorg DB2 catalog/MODIFY running time

Scott Lindsey

Reorg DB2 catalog/MODIFY running time
Would you please send me a copy of the jobs and aux REXX???
TIA

[login to unmask email] wrote:
>
> Hello,
>
> yes I have done this about 4 months ago (DB2 V5, OS/390 R2.6). You can find some
> exchanges on this in the archives because the first try left me with an empty
> Cat/Dir because of a coding error, and then I had trouble recovering the Cat/Dir
> because I kept specifying the Index space names instead of the Index names. Now
> I have working & tested JCLs for Cat/Dir Reorg, Cat/Dir Recover, Check/Checkr
> and an auxiliary REXX that lists used and allocated space. If you're interested
> in these, write me and I can send them to you as Word files (attachments are not
> allowed on the list).
>
> I don't think you will see much performance improvement, however. Your SYSCOPY
> problem (I assume you mean the MODIFY utility) probably means that you keep too
> many entries. 10 days ago I got called out at 4 o'clock in the morning because a
> MODIFY job I had started caused a production job to fail because SYSCOPY was
> unavailable. The TSs causing the problem had about 6000 SYSCOPY entries, of
> which about 60% were QUIESCEs. This was a backlog of 200 days. I wanted to
> reduce this to 20 days, i.e. about 600 entries. Amazingly, this takes about
> 1.500 CPU seconds on a 7-processor IBM 9672.
>
> I did some experimentation after that and found out:
> - the MODIFY time is proportional to the number of existing entries for the TS
> - the MODIFY time is also proportional to the number of days/entries you try to
> remove.
>
> The second point means that if you have 200 days worth of entries, and run first
> a MODIFY ... DELETE AGE(199), and then a MODIFY ... DELETE AGE(198), it will
> take (disregarding constant overhead) the same amount of time as running MODIFY
> ... DELETE AGE(198) in the first place.
> Taken together, it means that if you have many entries (for many days), and try
> to remove most of them (that's what I tried), then the running time of the
> MODIFY Utility is proportional to the SQUARE of the number of entries. A whacky
> algorithm, with other words.
>
> So if this is your problem, you have to keep things under control by running
> MODIFY regularly, especially on the TSs with many entries (do a SELECT COUNT(*),
> DBNAME, TSNAME FROM SYSIBM.SYSCOPY GROUP BY DBNAME, TSNAME ORDER BY 1 DESC).
> Don't try to shrink entries all in one go, but submit a series of commands,
> deleting only a small number of days (or 1 day) per utility run. You might use a
> SAS or REXX program to generate jobs for this. Modifying out one day from my
> 6000-entry TS took about 20 CPU seconds; the last run (going from 21 to 20 days)
> took only about 2.
>
> Hope that helps
>
> Dr. Michael Ebert
> DB2 Database Administrator
> aMaDEUS Data Processing
> Erding / Munich, Germany
>
> From: "Wang.James" <[login to unmask email]> on 29/12/99 14:52 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: Reorg DB2 catalog
>
> Can anyone share some experience on this one? Or where I can get more
> information about it? Any documented step by step procedure?
>
> We are considering reorg our DB2 catalogs in attempt to improve the catalog
> access performance. We haven't reorged our DB2 catalog since DB2 V4 and we
> are experiencing some very slow response time especially during the utility
> functions such as SYSCOPY clean up.
>
> We are running DB2 V5.
>
> Appreciated.
>
> James Wang
> Sr. Systems Programmer
> Automobile Club of Southern California
> (714) 850-2851
> [login to unmask email] <mailto:[login to unmask email]>
>
>
>



[login to unmask email]

Re: Reorg DB2 catalog
(in response to Scott Lindsey)
Hi Mebert ,
Could you please send me the Jcl for CAT/DIR REORG jobs.I would be
very thankful.
UJJAL BHATTACHARYA
DBA GROUP
TATA STEEL ,JAMSHEDPUR ,INDIA
PH - 430298




[login to unmask email]@RYCI.COM> on 01/03/2000 03:37:32 AM

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

Sent by: DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc:
Subject: Re: Reorg DB2 catalog


Hi,

let's hope the media-driven Y2K hysteria is over. At least it has gotten me
2
days extra vacation and a nice cash bonus. I have by now sent my Cat/Dir
Reorg
jobs to about 40 people, with a couple more outstanding. I got some
delivery
failure reports, which I will have to look at later; if you have requested
the
JCLs, but have not received them yet or in the next one or two days, write
me
again.

The statements 2&3 below are WRONG (at least on DB2 V5, OS390 R2.6). REPAIR
SET
NOCOPYPEND does work, and I use it in my JCL to copy SYSCOPY only once. You
need
the REPAIR privilege, of course; but since the ADMIN Guide specifies that
you
even need Install SYSADM authority to START the DSNDB01 once it is stopped,
that
should not be a problem. The Utilities Guide does not specify any
restrictions
either.

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




From: Lynette Roberts <[login to unmask email]> on 01/01/2000 14:41 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: Re: Reorg DB2 catalog




The "DB2 for OS/390 V5 Utility Guide and Reference" specifically talks
about
how reorgs on the system catalog are performed. We only had a few gotcha's
when implementing this:
1. almost nothing else can be running in DB2 while the reorg was going.
2. a "repair set nocopypend" will not work on the system catalog -- you
have to take an imagecopy to reset a copy pending flag
3. when doing the imagecopy after the reorg, SYSCOPY has to be copied
first, followed by everthing else, followed by a backup of SYSCOPY once
again (since SYSCOPY is updated during the copy process)

Other than these minor things, it was pretty painless to get this going.



the