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
 

 

EXPLAIN statement differences between 2 regions

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

New User


Joined: 13 May 2008
Posts: 2
Location: Chennai

PostPosted: Mon Jan 05, 2009 5:22 pm    Post subject: EXPLAIN statement differences between 2 regions
Reply with quote

A query is giving results in the PRODUCTION environment but gives a time-out in the DEVELOPMENT environment. From the EXPLAIN statements, it is seen that the cursor uses different indexes in PROD and DEV. And the EXPLAIN also shows use of sequential pre-fetch and merge join (Method=2) in PROD but in DEV, there is no use of sequential pre-fetch and merge join (Method=2). Instead in DEV, it uses only nested loop join. All the indexes are same in DEV and PROD.

Could anyone explain what could be the reason for this
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: Mon Jan 05, 2009 9:26 pm    Post subject:
Reply with quote

How recent is the RUNSTATS in DEV?
Back to top
View user's profile Send private message
sskrishnan

New User


Joined: 13 May 2008
Posts: 2
Location: Chennai

PostPosted: Tue Jan 06, 2009 9:59 am    Post subject: Reply to: EXPLAIN statement differences between 2 regions
Reply with quote

The Runstats and reorg were run on the tables and only then the bind was done in DEV. Just to add, data-wise too, there isn't much a difference in the numbers between PROD and DEV.

Has it got anything to do with the optimizer levels? If yes, where to check out the optimizer levels settings?
Back to top
View user's profile Send private message
Bharath Bhat

Active User


Joined: 20 Mar 2008
Posts: 283
Location: chennai

PostPosted: Tue Jan 06, 2009 2:49 pm    Post subject:
Reply with quote

Not sure if it could solve your problem, But I faced a similiar problem recently. I dropped the table in DEV and recreated it. Then loaded with PROD data. After this, the explain was showing index access.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Jan 06, 2009 2:58 pm    Post subject:
Reply with quote

timeout could be because of resource limit set in DEV ..in DEV normally it is less than that of PROD ...chk your DSNRLST02 table .... you might not have access to this table ...chk with your DBA ...
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 ROUNDED Problem with COMPUTE statement shalem COBOL Programming 11 Thu Feb 09, 2017 8:16 pm
No new posts VALIDATE NULL VALUE IN DB2 CASE STATE... useit DB2 5 Thu Feb 09, 2017 4:34 pm
No new posts -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am
No new posts COBOL DB2 - CALL statement - high CPU... TS70363 DB2 15 Sun Sep 11, 2016 6:07 am
No new posts Converting NULL column into NOT NULL ... Raghu navaikulam DB2 5 Sat Aug 06, 2016 3:45 pm


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