Prev: IBM DB2 Database Specialist - Saudi Arabia
Next: 100% without investment online part time jobs..(adsense,datawork,neobux..more jobs)
From: shorti on 4 Aug 2010 19:20
I am using DB2V9.5 on AIX6.1. I have a machine that the database
backups are now over 3 times as large as it should be. Normally, a
REORG on our systems cures the problem but this time it did not. I
looked around on the systems and do not see anything that sticks out
as being a problem (ie history files small, small number of active and
archive logs, no pndfreepgs, etc). We have hundreds of systems and
this is the only one Ive seen like this.

We do a full online database backup once per hour. The backups for
this size database (only 330k records on the largest table) should be
less than 200MB in size but are now almost 700MB. The machine was
brought down (instance stopped and started) and an offline reorg of
the tables and the indexes (using indexall) was performed but it only
reduced the database size by about 40MB. I then ran reorgchk (a week
after the reorg was performed) and it is showing a few indexes needing
reorg (not uncommon after a week). I then tried an INPLACE ALLOW
WRITE reorg on the first index...after two hours I saw that I was
barely 5% through the count for that one index. Since the customer
was running at the time I stopped the reorg and waited for a more idle
time (even though it didnt look like the machine was running very
heavily).

7 hours later I found an idle window and retried the REORG again..this
time I did the table first (still using INPLACE because the machine
could not be taken offline at this time). The table reorg finished
within a few minutes. I then tried that same index again. It ran for
about 3 hours and was only about 1/3 way through before my idle window
expired...so I had to stop it again.

With those calculations, it looked like it would have taken 9 hours to
do that one index!! Again, this machine had hardly anything going on
and typically on our busy machines with much larger databases it would
only have taken 3-4 hours max to reorg this particular index.

Now I am thinking something is very wrong with some of the indexes.
Has anyone experienced something like this before? Is there something
else I can look at that might indicate what is being backed up that is
making it so large? Also, is the slow index reorg an indication of
anything? We have planned an outage with this customer to again try
an offline reorg but I am afraid it wont cure the problem since it
didnt do anything the last time.

Please let me know if I am missing some key item.

Your help is much appreciated!
 | 
Pages: 1
Prev: IBM DB2 Database Specialist - Saudi Arabia
Next: 100% without investment online part time jobs..(adsense,datawork,neobux..more jobs)