Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

DB2 Bind issue - Difference in Test and Prod.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
vijikesavan

Active User


Joined: 04 Oct 2006
Posts: 118
Location: NJ, USA

PostPosted: Wed Apr 15, 2009 8:53 pm    Post subject: DB2 Bind issue - Difference in Test and Prod.
Reply with quote

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
View user's profile Send private message

Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA

PostPosted: Wed Apr 15, 2009 10:16 pm    Post subject:
Reply with quote

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
View user's profile Send private message
vijikesavan

Active User


Joined: 04 Oct 2006
Posts: 118
Location: NJ, USA

PostPosted: Wed Apr 15, 2009 10:35 pm    Post subject: Reply to: DB2 Bind issue - Difference in Test and Prod.
Reply with quote

RUNSTATS on both environment never done.
STATSLASTTIME shows 0001-01-01-00.00.00.000000
Back to top
View user's profile Send private message
wanderer

Active User


Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka

PostPosted: Thu Apr 16, 2009 12:43 am    Post subject:
Reply with quote

Don't know V9, but V8 is runstats heavy. Wonder how you have a V8 prod table with no runstat.
Back to top
View user's profile Send private message
MFRASHEED

Active User


Joined: 14 Jun 2005
Posts: 186
Location: USA

PostPosted: Thu Apr 16, 2009 1:24 am    Post subject:
Reply with quote

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
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1232
Location: Israel

PostPosted: Thu Apr 16, 2009 1:22 pm    Post subject:
Reply with quote

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
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Thu Apr 16, 2009 2:33 pm    Post subject:
Reply with quote

Viji,

When was the LAST REORG done in both the environments.

Thank You,
Sushanth
Back to top
View user's profile Send private message
vijikesavan

Active User


Joined: 04 Oct 2006
Posts: 118
Location: NJ, USA

PostPosted: Fri Apr 17, 2009 1:29 am    Post subject: Reply to: DB2 Bind issue - Difference in Test and Prod.
Reply with quote

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
View user's profile Send private message
vijikesavan

Active User


Joined: 04 Oct 2006
Posts: 118
Location: NJ, USA

PostPosted: Thu May 07, 2009 10:57 pm    Post subject: Reply to: DB2 Bind issue - Difference in Test and Prod.
Reply with quote

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
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Fri May 08, 2009 11:27 am    Post subject:
Reply with quote

Viji,

Check the INDEX columns for both indexes in both the regions.

Sushanth
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Fri May 08, 2009 1:34 pm    Post subject:
Reply with quote

what is the sql and index info for the slow running query?
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Issue with NDM process to transmit ES... chetanambi All Other Mainframe Topics 6 Wed May 03, 2017 10:52 am
No new posts Why myself doing Package Bind always ... Susanta DB2 5 Thu Mar 02, 2017 10:47 pm
No new posts Bind plan as a member to another bind... rexx77 DB2 0 Thu Feb 16, 2017 2:02 am
No new posts Difference between TWALENG and TWASIZE Arunkumar Chandrasekaran CICS 3 Tue Jan 03, 2017 12:57 pm
No new posts Execessive parameter issue Sumeendar JCL & VSAM 5 Mon Dec 19, 2016 4:35 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us