When you say you transferred from an 'old' system, I have some
questions:
What kind of server and operating system were you using? If you say you
transferred from an 'old' UNIX system to a 'new' Windows system, my gut
response is going to be that it's the 'going to windows' thing that did
you in. I worked on a Windows DB2 EE project and the best minds that
were trying to analyze the lousy performance were saying that it was
because management was too cheap to use UNIX. It's been argued that
Bill Gates doesn't really want you to by his boxes to run other people's
databases (whether Oracle, DB2 UDB or whatever), he wants you to buy
windows AND SQL Server.
If you were on Windows beforehand, then the main suggestion I'd look at
is to make sure that your database configuration parameters or database
management configuration parameters (not to mention environment and
registry variables) haven't changed in the move. I bring that up since
you mentioned just about everything else except that, and I'm "guessing"
that in migrating the data objects, no one thought to migrate the
SPECIFIC VALUES of your older DB2 UDB for LUW system parameters as well,
and in that case, the 'new' values will be the usual IBM default values,
obliterating any good work (potentially several years' worth) done
previously on performance tuning.
Also, have you made sure that such objects as temporary tablespaces and
the space allocated to them remained constant in the move? You
mentioned bufferpools, but not temporary tablespaces, so really try to
drill down on that one too.
Also, you're of course going to have to tell us about those 'additional
tables' as well as the 'new application' written to manage the data.
Those sound like more likely 'usual suspects' though I'd absolutely
check the other suggestions as well. As for these 'usual suspects'
pretty obviously, if you have additonal tables, and if the data volume
they represent is a HUGE increase over the previous amount, you'll get
lousier performance if you didn't scale your CPU, RAID, etc. upwards for
it, just as it's even more obvious that if your 'new application' was
tested on a really LIGHT volume of data (e.g., 10% of the production
data), then the performance may be ok on such a 'faked weapons test'
(remember the DIVAD? Senator Dixon of Illinois couldn't hit the
broadside of a barn until a marksman intervened and 'helped' on what was
supposed to be an automatic targeting system). Ideally, you should test
on an IDENTICAL COPY of what will be your production data.
In relation to what Renu just said, if you've never tried db2advis, do
it. For a tough query, it should be your STARTING point (though not
ending point), not EXPLAINS, though EXPLAINS may be helpful later.
Also, did your previous system use Red Bull? Dr. Michael
"SSSSSSSSavage" Weiner's son's product Rockstar (the mango version is
awesome by the way)? Coke Blaq? Diet Pepsi MAX (with ginseng and more
caffeine - WAKE UP PEOPLE!)? Jolt Cola? If so you may want to try
giving your system the same quantities it used to consume.
I'm just kidding about the last suggestion of course, but dead serious
about all the others.
-----Original Message-----
From: Tom
[mailto:db2udbdba-ezmlmshield-x74159777.[Email address protected]
Sent: Friday, October 19, 2007 2:56 PM
To: LazyDBA Discussion
Subject: DB2 UDB Performance
We are experiencing the following problem:
We recently migrated data from an old system into our production DB2
database. The data required additional tables in the production database
and there was new application written to managed the data. Ever since
the new data was migrated, there has been very poor performance in
production.
The application performed well in test.
What can I do to improve database performance? I have tried runstats,
but there was no obvious effect. I have also done reorgs on the indexes
and increased the bufferpool size to accommodate the extra data. What
else should I try?
We have DB2 UDB EE v8.2 on a Win2000 server.
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
DB2 & UDB email list listserv db2-l LazyDBA home page