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
 

 

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
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
No new posts DSNACCOX (can it be run on 1 db/ts, t... SRICOBSAS DB2 5 Sat May 06, 2017 12:59 am
No new posts LISTIDR compiled date/time jerryte IBM Tools 3 Thu Apr 20, 2017 7:37 pm
No new posts Application not run by time HH:MM tri... jzhardy IBM Tools 1 Sun Apr 09, 2017 3:22 pm
No new posts Date and time format in CICS Chandru3183 CICS 2 Sat Mar 18, 2017 12:46 pm


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