|
View previous topic :: View next topic
|
| Author |
Message |
vijikesavan
Active User
Joined: 04 Oct 2006
Posts: 118
Location: NJ, USA
|
|
|
|
Hi,
I have a strange situation. Recently I noticed few of our programs are running high on CPU usage in production.
After we did "re-bind" the cpu came back normal.
But some still consume high CPU. For example, a particular program in production took approx 48 mins of CPU and when run in test with full volume (in databases and files) took just < 5 mins of CPU.
Test DB2 version is DB2 V9 Compatibility Mode.
Prod Db2 version is DB2 V8 New-function Mode.
SQL query doesnot contain any V9 functions.
Is it related to version difference? Did anyone ever faced this similar situation?
Any pointers will be of great help.
Appreciate your reply.
Thanks,
Viji |
|
| Back to top |
|
 |
Terry Heinze
JCL Moderator
Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA
|
|
|
|
| Are the Test and Prod data base tables approximately the same size? Have you run RUNSTATS recently on both? And rebind after the RUNSTATS? |
|
| Back to top |
|
|
vijikesavan
Active User
Joined: 04 Oct 2006
Posts: 118
Location: NJ, USA
|
|
|
|
RUNSTATS on both environment never done.
STATSLASTTIME shows 0001-01-01-00.00.00.000000 |
|
| Back to top |
|
|
wanderer
Active User
Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka
|
|
|
|
| Don't know V9, but V8 is runstats heavy. Wonder how you have a V8 prod table with no runstat. |
|
| Back to top |
|
|
MFRASHEED
Active User
Joined: 14 Jun 2005
Posts: 186
Location: USA
|
|
|
|
As pointed out in earlier responses, execute RUNSTATS and BIND.
To know where time is being spent, if your shop has STROBE, run this against the production job and review STROBE report.
Regards,
MFRASHEED |
|
| Back to top |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006
Posts: 1353
Location: Israel
|
|
|
|
Also, run an EXPLAIN on the program before and after the RUNSTATS.
Check the differences between the path used (maybe a sort is needed in prod). |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Viji,
When was the LAST REORG done in both the environments.
Thank You,
Sushanth |
|
| Back to top |
|
|
vijikesavan
Active User
Joined: 04 Oct 2006
Posts: 118
Location: NJ, USA
|
|
|
|
To answer everyone's questions
1) Ran strobe and found a particular SQL as major contributor to CPU. One of the table in the SQL is not having RUNSTATS both in Dev as well as in prod.
2) Not able to get strobe in test (prod version) bse it runs for few mins.
3) Re-org on one table done Feb 15th 2009, but on another never done.
I have requested DBA group for re-org and runstats. Will keep you all posted after that.
Thanks everyone for your valuable inputs. |
|
| Back to top |
|
|
vijikesavan
Active User
Joined: 04 Oct 2006
Posts: 118
Location: NJ, USA
|
|
|
|
Hi all,
Update on the same issue. Program's performance did not change after reorg, runstats and re-bind.
But noticed a strange point in the access path.
The indexes chosen in test and prod are different. i.e, Index IX1 is used in Test whereas index IX2 is used in Prod.
No change in program code.
Db2 version in test is V9-Compatibility mode.
Db2 version in Prod is V8-New function mode.
Does this has to do something in this? Anyone faced similar situation before?
Pls help.
Thanks,
Viji |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Viji,
Check the INDEX columns for both indexes in both the regions.
Sushanth |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| what is the sql and index info for the slow running query? |
|
| Back to top |
|
|
|
|
