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
 

 

How to improve the performance- fine tunning?

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

New User


Joined: 22 Sep 2006
Posts: 17

PostPosted: Thu Jul 05, 2007 1:59 pm    Post subject: How to improve the performance- fine tunning?
Reply with quote

HI,

I have a simple query retrieving all the records using a cursor from a particular table without a where clause. ex:
EXEC SQL DECLARE STORE-CURSOR CURSOR WITH HOLD FOR
SELECT STORE,
COUNTRY_CODE
CLIMATE_CODE,
FROM STORE
END-EXEC.
and followed by relevant fetch and close cursor.
The table is indexed on STORE KEY,
what I want to know is, ---Does it makes any sense or improves the performance using additional where clause, and what where clause am I suppose to use and how?
As such the query is taking a long time. Plz do SUGGEST.
Back to top
View user's profile Send private message

enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10256
Location: italy

PostPosted: Thu Jul 05, 2007 8:09 pm    Post subject: Re: How to improve the performance- fine tunning?
Reply with quote

impossible to reply without knowing the underlaying business/application requirements/specifications.

regards

e.s
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: Thu Jul 05, 2007 8:27 pm    Post subject:
Reply with quote

Hello,

If the requirement is to process every row in the table, the time it will take will vary with the volume of data in the table. What is the indication that this needs "tuning"?

If you post the actual requirement (not the existing query), we may be able to offer suggestions.
Back to top
View user's profile Send private message
vijikesavan

Active User


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

PostPosted: Thu Jul 05, 2007 8:37 pm    Post subject: Re: How to improve the performance- fine tunning?
Reply with quote

Hi,
As everyone said, its really difficult to give you the solution you are looking for.
I faced something like this before. If you want to select all the rows in the table, you can try one of these options
1) Check if there is any BMC or unload copies already exists for the table. You can use the file as input instead of making DB2 calls.
2) Sometimes just a rebind of the program or re-org of the table might help.
3) Try adding where clause or any new indexes after discussing with your DBA, business and other ppl who knows the application.

These are general tips for you to try, not sure of the results.

Thanks,
Viji
Back to top
View user's profile Send private message
wanderer

Active User


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

PostPosted: Thu Jul 05, 2007 10:30 pm    Post subject: Re: How to improve the performance- fine tunning?
Reply with quote

Yes, it will improve performance using WHERE clause with STORE KEY.

But then, if you need to see all the rows in table anyway, you can't use WHERE clause.
Back to top
View user's profile Send private message
prasadvrk

Active User


Joined: 31 May 2006
Posts: 200
Location: Netherlands

PostPosted: Fri Jul 06, 2007 6:23 pm    Post subject:
Reply with quote

Quote:
Does it makes any sense or improves the performance using additional where clause, and what where clause am I suppose to use and how?


Do not confuse between performance and desired result. Performance tuning should not change the result. Getting the same result in less time or consuming less resources is performance improvement. If you have selection criteria, why did you not use in the first place.
Back to top
View user's profile Send private message
TG Murphy

Active User


Joined: 23 Mar 2007
Posts: 149
Location: Ottawa Canada

PostPosted: Fri Jul 06, 2007 7:58 pm    Post subject:
Reply with quote

Your SQL is so simple that I fear there's nothing much you can do.

When we need to tune an SQL statement, it generally looks like a monster. For example, 8 table joins with correlated subqueries. It can take hours to analyse the SQL - examine the EXPLAIN access path - consider alternatives.

When I look at your SQL it takes me about 2 seconds to conclude that not much can be done to the SQL itself to improve on things. Any improvements will have to come - as was recommened above - by tweaking the data (ie. reorg or purging dead data).
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 Jul 06, 2007 8:17 pm    Post subject:
Reply with quote

Hello,

To repeat
Quote:
What is the indication that this needs "tuning"?
Back to top
View user's profile Send private message
vinay_care

New User


Joined: 22 Sep 2006
Posts: 17

PostPosted: Tue Jul 10, 2007 6:26 pm    Post subject: Re: How to improve the performance- fine tunning?
Reply with quote

vijikesavan wrote:
Hi,
As everyone said, its really difficult to give you the solution you are looking for.
I faced something like this before. If you want to select all the rows in the table, you can try one of these options
1) Check if there is any BMC or unload copies already exists for the table. You can use the file as input instead of making DB2 calls.
2) Sometimes just a rebind of the program or re-org of the table might help.
3) Try adding where clause or any new indexes after discussing with your DBA, business and other ppl who knows the application.

These are general tips for you to try, not sure of the results.

Thanks,
Viji



Thanks for the info.
we have a particular set of jobs which are taking a longer duration of time, when analysing further with certain tool we get to know what is causing the delay in time, and one such is above query,
certain times even after using certain keys in the where clause with index set will also take a longer run due to excess data, for those do we have any method that we can restrict those jobs for a lesser time? how would we fine tune such query?

Cheers,
vinay
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: Tue Jul 10, 2007 7:07 pm    Post subject:
Reply with quote

Hello,

If your requirement is to use all of the rows in a table, there is not much to tune within the database.

If you have many millions of rows, you might unload the data and run your process(es) sequentially and not read the data via sql.

There is no way to define one or more new keys to speed up a full traversal.

It would be a good idea to test how long the unload/sequential run would take versus using sql. If the times are even similar, you might want to use the sequential appraoch as it could remove the unacceptable amount of processing from db2 and make other processes run better by reducing the impact of this large process.
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 BC, BCR, BRC, BRCL performance steve-myers PL/I & Assembler 0 Fri Dec 23, 2016 7:44 am
No new posts PL/I code tuning/Performance improvement Virendra Shambharkar PL/I & Assembler 4 Mon Dec 05, 2016 11:57 am
No new posts What are the way we can improve CPU p... Gunapala CN DB2 10 Mon Oct 24, 2016 2:16 pm
No new posts DISP=(SHR,PASS) performance opinion steve-myers JCL & VSAM 1 Wed Dec 02, 2015 11:53 pm
No new posts Performance tuning of Online system bipinpeter All Other Mainframe Topics 2 Thu Nov 26, 2015 2:29 pm


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