Re: Reorg DB2 catalog

Leslie Pendlebury-Bowe

Re: Reorg DB2 catalog
(in response to john schatko)
James
I reorged our catalog and directory a few months ago and it took an
hour to run ..

there is a set way of reorging the catalog and directory and it is
documented very well in the manuals .. the order I used was :

1. Backup the Catalog and directory

2. Reorg Cat & Dir

dsndb06 sysdbase
dsndb06 sysgroup
dsndb06 sysplan
dsndb06 sysviews
dsndb06 sysddf
dsndb06 sysstats
dsndb06 sysstr
dsndb06 syscopy
dsndb06 sysuser
dsndb06 syspkage
dsndb06 sysgpaut
dsndb01 dbd01
dsndb01 syslgrnx
dsndb01 sct02
dsndb01 spt01

3. Runstat Cat & Directory

DSNDB06.SYSDDF
DSNDB06.SYSSTATS
DSNDB06.SYSDBASE
DSNDB06.SYSSTR
DSNDB06.SYSGROUP
DSNDB06.SYSVIEWS
DSNDB06.SYSCOPY
DSNDB06.SYSGPAUT
DSNDB06.SYSUSER
DSNDB06.SYSDBAUT
DSNDB06.SYSPLAN
DSNDB06.SYSPKAGE

4. Backup Cat & dir

The order of each was :

ps - I would also say that you should do analysis of your queries on
the catalog to determine if any additional indexes would help you (or
hinder you) - rather than seeing the reorg as your saving grace ..
remember it has only be of later releases that you could get anywhere
near the db2 catalog to do a reorg.

do you use any 3rd party products? if so then look for hints on
indexes to assist you in speeding up access fro these ... most (plat
and BMC) have documented additional indexes to help their products in
their access ont he db2 catalog.

cheers

Leslie Pendlebury-Bowe
DB2 DBA SAP OS390
have a good New Year .. .


______________________________ Reply Separator _________________________________
Subject: Reorg DB2 catalog
Author: "Wang.James" <[login to unmask email]> at Internet
Date: 12/29/99 6:52 AM


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]>

Michael Ebert

Reorg DB2 catalog/MODIFY running time
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]>



john schatko

Re: Reorg DB2 catalog/MODIFY running time
(in response to Michael Ebert)
I would be interested in obtaining a the JCLs and REXX mentioned
below...thanks

-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Wednesday, December 29, 1999 10:37 AM
To: [login to unmask email]
Subject: Re: Reorg DB2 catalog/MODIFY running time


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]>








Michael Ebert

Re: Reorg DB2 catalog/MODIFY running time
(in response to Leslie Pendlebury-Bowe)
Until now, I have gotten about 15 requests for the CAT/DIR REORG JCLs and
REXX... I will send them tomorrow, so I can collect some more.

To explain further on the MODIFY topic: I had a TS with 6000+ SYSCOPY entries,
relating to 204 days. Doing a MODIFY ... DELETE AGE(20) took about 1500 CPU
seconds, and maybe 40 mins elapsed (I don't know these numbers exactly, as I had
many MODIFY statements in one DSNUTILB invocation).

Doing a MODIFY ... DELETE AGE(203) took about 20 CPU seconds, reducing the
number of days from 204 to 203.
Doing a MODIFY ... DELETE AGE(202) on the result took also about 20 CPU seconds,
reducing from 203 to 202.

If you ran a MODIFY ... DELETE AGE(202) on the original 204-day entry, it took
about 40 CPU seconds, reducing from 204 to 202 days. This means you did not gain
anything from doing it in one step. Actually, because there is no COMMIT and
release of locks in between the two steps, you lose (concurrency).

If you assume that modifying one day out of a 6000-entry, 200-day list takes 20
CPU", and is proportional to the number of days in your list, and that modifying
more than one day can be broken down into a series of one-day modifys, then each
day in your original list (30 entries) takes 0.1 CPU". So..
AGE(203) takes 203*0.1=20.3 CPU"
AGE(202) takes 202*0.1=20.2 CPU"
...
AGE(20) takes 20*0.1=2 CPU"

If you sum up all these CPU times, you get 2000 CPU" for the 184 steps
(SUM(i=1,..,n)=n*(n+1)/2) which is in good agreement with the time required for
the one-step process, taking into account that you have to do 184 UTILINITs and
UTILTERMs instead of just one, and that these are just rounded numbers.

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




From: [login to unmask email] on 29/12/99 16:56 GMT




To: Michael [login to unmask email]


cc: [login to unmask email]




Subject: Re: Reorg DB2 catalog/MODIFY running time






Please send me the JCLs and REXX execs that you mention below.

Also, I'm a bit confused by your explanation of MODIFYing off two days at once
rather than performing the MODIFYs for one day at a time. In that paragraph,
you seem to be saying that it will take the same amount of time so you just as
well get rid of lots of entries at once. The paragraph above and below it
indicate that it's smart to only go after a small number of entries with each
execution.

Thanks for information. This is an area that we intend to pursue some cleanup.

Regards,
Ruth Blake
[login to unmask email]

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]>








David Price

Re: Reorg DB2 catalog/MODIFY running time
(in response to Michael Ebert)
FYI ... We had a similar problem with Modify.

If you have a tablespace which has a lot of SYSCOPY entries spread over many
days/months/years, then try DELETE AGE(*) on the modify command. While this
removes all the SYSCOPY entries for a given database.tablespace ..., it runs
much faster than DELETE AGE(XXX) when there are an excessive number of
SYSCOPY entries for a given database.tablespace...

You will need to follow this with an immediate Image Copy.

Dave Price

Systems DBA
SITE - Mainframe DBMS
Charles Schwab & Co.
PHXDC-02-2018

Work: (602) 426-2702
Pager (888) 416-3386
Mobile (602) 321-6518

All email sent to or from this address will be received or otherwise
recorded by the Charles Schwab corporate email system and is subject to
archival, monitoring or review by, and or disclosure to, someone other
than the recipient





-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Wednesday, December 29, 1999 8:37 AM
To: [login to unmask email]
Subject: Re: Reorg DB2 catalog/MODIFY running time


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]>








Michael Ebert

Re: Reorg DB2 catalog/MODIFY running time
(in response to David Price)
Hello again,

I have just sent the mentioned Cat/Dir Reorg JCL, Recover JCL, Check JCL & LISTC
REXX to 23 people who have requested them. If I have missed someone, or you have
just caught up with your emails, and want some too, just write.

As I said, I'm sure that REORGing the Cat/Dir will NOT help you with performance
problems (I'm surprised that IBM recommended it for this). In our case, it was
done because one disk was 100% full, preventing BINDs because SYSPKAGE could not
extend, to redistribute TSs over the two disks dedicated to the Cat/Dir, resize
the TSs and reduce extents.

In the case of MODIFY, the problem is a N**2 algorithm (according to my tests),
and only IBM can change this. The only thing you can do is to keep the growth of
SYSCOPY entries in check (ours has between 250.000 and 300.000 entries for 1.900
TS in Prod, in Test it's 55.000 for 4.700 TS - after massive cleanup by me). One
solution is to run MODIFY daily instead of weekly, which dilutes the CPU time,
and may save some too. The other thing is to reduce the number of SYSCOPY
entries. In our case, around 60% of entries are QUIESCEs (many of them on a
partition level, which doesn't make too much sense - doing them once by full TS,
or TS set, before and/or after the processing, would provide a common recovery
RBA and save SYSCOPY entries too). However I do not know whether it is the
number of entries per DB/TS that determines the running time of MODIFY, or the
number of IC entries, or the number of all entries except quiesces, or...
Generally I keep an eye on TSs that have more than 1000...1500 entries in toto.
The number of days we keep entries depends on the TS, as we have many different
applications (DSS-type and transaction systems) with different growth speeds.
Keeping this many entries also gives a good overview of the processing that is
done on a TS (daily LOAD REPLACE, LOAD RESUME with partition switching each
month, weekly REORG, ....)

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


---------------------- Forwarded by Michael Ebert/MUC/AMADEUS on 30/12/99 15:09
---------------------------

From: [login to unmask email] on 29/12/99 17:47 GMT




To: Michael [login to unmask email]


cc:




Subject: Re: Reorg DB2 catalog/MODIFY running time




Hello

I will be very much interested in getting a attachment. Our modify is
running
for a long time. Last weekend it used up 3 hours of CPU. I had talked
with IBM
and done every thing what they said like reorg the catalog, stats and keep
every thing in one extend but no help in performance yet. We use delete
age of 30.
We have 168462 entries in the Syscopy table. It seems like Modify is doing
tablespace
scan for each entry. We do have several partitions and we do take few
copies
at the partition level. We do take quiesce before and after the update,
insert, delete programs.

Any suggestions to improve the performance will be helpful.

Thanks
Shaila

---------------------- Forwarded by Shaila Shirole/Deluxe Corporation on
12/29/99 09:59 AM ---------------------------


[login to unmask email]@RYCI.COM> on 12/29/99 09:36:53 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/MODIFY running time


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]>








Isaac Yassin

Re: Reorg DB2 catalog/MODIFY running time
(in response to Michael Ebert)
Hi,
Except of all of the above which is in agreement with my findings and notes from
more then a year ago (although I didn't do the mathematical calculations), Pleas
take a look at your BP0 where cat/dir are located to see if it's sized properly.
You should consider your real memory, expanded storage and system paging
activity as well to try and get a tuned BP (and/or buy a tool).
I tend to enlarge BP0 when I need to run a heavy maintenance on the cat/dir and
then change it back to normal.

--
Isaac Yassin

DBMS & IT Consultant

Tel: +972 9 9505172
Cel: +972 54 452793
Fax: +972 9 9560803