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
 
Query taking long time to execute

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

Active User


Joined: 11 Apr 2008
Posts: 144
Location: Jamshedpur

PostPosted: Sun Aug 18, 2013 1:48 am    Post subject: Query taking long time to execute
Reply with quote

Hi All,

I have a job that normally runs for 45 min but today it is executing for more than 10 hrs. I did the following -

1. Checked the table to see when Runstat was last done, it was done 2 days back.
2. Implemented the module again and rebounded the plan. We cannot just bind the package in our production site, so had to implement the program.
3. Did the explain of the query in test region, it gave matching index scan. I have asked for explain result in production, but it is taking hell lot of time for DBA team to get back to me.
4. I checked Omegammon, following are the findings
BP Hit Percentage = 99.3%
Which I think means that we are getting data from buffer pool.
5. But I see a huge getpage count -
Code:

Getpage Requests       =      1001M   Failed Getpage Requests    =          0
Synchronous Read I/O   =      45183   Getpage/Read I/O           =   22159.25
Page Updates           =      57103   Seq Prefetch Requests      =     206075
List Prefetch Requests =       4074   Dynamic Prefetch Requests  =     98400K
Prefetch Pages Read    =    6525225   Prefetch Pages In Hiperpool=          0

6. Wait time is not large, that means my SQL is getting CPU.

Code:
Synchronous I/O Wait                   45183    00:01:17.843
Asynchronous Read I/O Wait            193907    00:02:57.242
Asynchronous Write I/O Wait                0    00:00:00.000
Local Lock/Latch Wait                 159625    00:00:05.557



I have contacted my DBA/Performance team, but of no help.

Can anyone tell me, what else I can do.I am at my wit's end.

Code'd
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


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

PostPosted: Mon Aug 19, 2013 12:46 am    Post subject:
Reply with quote

i would imaginge the third line of your WHERE clause is the culprit
Back to top
View user's profile Send private message
saurabh39
Warnings : 1

Active User


Joined: 11 Apr 2008
Posts: 144
Location: Jamshedpur

PostPosted: Mon Aug 19, 2013 12:55 am    Post subject: Reply to: Query taking long time to execute
Reply with quote

dbz.. with all due respect, I was not asking what went wrong with the query. I know my stuff and I know what query I wrote and I know there is nothing wrong in the query. I was asking, if there are any troubleshooting step which I missed. I request you to please keep your sarcasm with yourself for some other time.
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: Mon Aug 19, 2013 2:54 am    Post subject:
Reply with quote

Hello,

What has changed in the environment since it worked "normally"?

Does it always take the 10 hours now, or was this a one-time problem?

Quote:
I know my stuff and I know what query I wrote and I know there is nothing wrong in the query
We do NOT know this . . . Rather than complaining about the reply given, why not simply post the problem query?
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Mon Aug 19, 2013 9:56 am    Post subject:
Reply with quote

Tushar,

* Orginally when was the 45min program implemented(date) in production
* When did the 45min program became 10hr program(date)
* Did any rebind/auto-rebind happen in-between, auto-rebind can happen J-I-C any table in the query was altered. If so, statisitcs might have got changed for the tables because of which accesspath changed.

Check MSTR log to find out if auto-bind has happened.

Regards,
sushanth
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
This topic is locked: you cannot edit posts or make replies. 00D3003B - time outs in DB2 when invo... chavinash2004 DB2 2 Mon Oct 09, 2017 4:39 pm
No new posts HEX value search in a DB2 query maxsubrat DB2 2 Wed Oct 04, 2017 3:04 pm
This topic is locked: you cannot edit posts or make replies. Rexx to execute spool commands upendrasri CLIST & REXX 5 Wed Oct 04, 2017 6:54 am
No new posts Execute JCL step based on the content... sprikitik JCL & VSAM 2 Tue Oct 03, 2017 10:03 am
No new posts VSAM RLS Wait time blayek JCL & VSAM 2 Mon Oct 02, 2017 9:05 pm

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