Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
DB2 - Help in SQL WAIT Interval

 
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: Fri Dec 07, 2007 1:52 am    Post subject: DB2 - Help in SQL WAIT Interval
Reply with quote

Hi,
We have identified a long running job - around 10 hrs of elapsed time with 1.5 hrs CPU.
Running strobe showed that the maximum wait time is in
Code:
DSNECP00            DSN START-UP CSECT  - 98%.


I found some strange messages in the Jes
Code:
"<pgmname> <step name> WAIT TIME EXPIRED-REPLY CANCEL OR WAIT"


I tried to run EXPLAIN on the SQL Statement, it showed error message
Code:
"DBG55160W SQL wait time limit exceeded.You can either increase
the SQL-WAIT-INTERVAL in SYSPARMS, or create shadow tables and/or   
catalog indexes (DB2 V4 or higher) in order to make the query run
faster. "


Is it refering to SYSIBM.SYSPARMS table? If yes, which column I need to set to increase the wait interval.
I need to run this query before tuning it. ( Am sure the SQL is inefficient)

Pls help.
Thanks,
Viji
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Dec 07, 2007 2:26 am    Post subject:
Reply with quote

Hello,

I'd suggest you talk with your dba(s) about any changes to the processing environment.

If you post the problem query(ies) here someone may have suggestions without having the run statistics.
Back to top
View user's profile Send private message
vijikesavan

Active User


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

PostPosted: Sat Dec 08, 2007 2:48 am    Post subject: Reply to: DB2 - Help in SQL WAIT Interval
Reply with quote

Thanks.
Yes I spoke to DBA and the wait time is due to the enormous data the cursor is trying to open.
we are looking at tuning the SQL now.

Thanks for your reply.
Viji
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sat Dec 08, 2007 3:33 am    Post subject: Reply to: DB2 - Help in SQL WAIT Interval
Reply with quote

You're welcome.

Good luck icon_smile.gif

d
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 VSAM RLS Wait time blayek JCL & VSAM 2 Mon Oct 02, 2017 9:05 pm
This topic is locked: you cannot edit posts or make replies. Job to wait and continue based on reply yugendran COBOL Programming 5 Wed Oct 05, 2016 1:46 pm
No new posts OMEGAMON to see In-DB2 times and Wait... Archana89 IBM Tools 4 Fri Mar 04, 2016 11:28 am
No new posts Adding multiple trailer records at re... kenshin DFSORT/ICETOOL 6 Fri Sep 25, 2015 11:01 am
No new posts MQ message to wait for processing mohitsaini Java & MQSeries 1 Sat Jun 20, 2015 5:39 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us