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
 

 

Optimize this Query which has to run on 5 million records

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

New User


Joined: 22 May 2007
Posts: 68
Location: mumbai

PostPosted: Fri Jan 11, 2008 6:17 pm    Post subject: Optimize this Query which has to run on 5 million records
Reply with quote

Hi All,

i have to optimize one query that is in SQL ,i have to run this query in DB2 after changing it to DB2 format,
any one optimized query any time, i have to run this query on 5 million records.

presently it is taking 15 minutes to retrieve records...........?
Back to top
View user's profile Send private message

bbessa

New User


Joined: 03 Aug 2006
Posts: 13
Location: Brazil

PostPosted: Wed Jan 23, 2008 3:15 am    Post subject:
Reply with quote

Hi,

Just the query is not enough to help you optimizing the response time. But based on what you've sent, I can come up with this general tips:

1. Create MQT (materialized query tables) for the subselects if disk space is not a problem. Consider the ones with calculations (SUM,AVG) and datatype conversions as priority for the MQT creation.

2. Analyze your access path. Db2 visual explain is a great tool to help you study the db2 optimizer choices for your query, and it's free.

3. After analyzing the access path, review, redefine and create indexes accordingly. Just providing good indexes you can diminish the response time down to seconds or a couple of minutes. Also, you should consider partitioning the tablespace and using DPSIs.

4.Study runstats and DB2 statistics. Use them.

5. Put the most scanned tablespace in a separate buffer pool.

Good lucky ;)

bbessa
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Wed Jan 23, 2008 5:21 am    Post subject:
Reply with quote

No MQT. That could possibly be the worst possible suggestion without knowing how the underlying tables are used.

If it is not a warehouse table and the data in the underlying tables is constantly updated, then the DB is constantly refreshing the data and then you are just letting DB2 take a big performance hit. This will slow down the whole subsystem.
Back to top
View user's profile Send private message
bbessa

New User


Joined: 03 Aug 2006
Posts: 13
Location: Brazil

PostPosted: Wed Jan 23, 2008 11:32 pm    Post subject:
Reply with quote

stodolas wrote:
No MQT. That could possibly be the worst possible suggestion without knowing how the underlying tables are used.

If it is not a warehouse table and the data in the underlying tables is constantly updated, then the DB is constantly refreshing the data and then you are just letting DB2 take a big performance hit. This will slow down the whole subsystem.


That's why I've linked there reference so he can evaluate if it's useful or not.
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 JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts Join giving more records than expected Danielle.Filteau SYNCSORT 2 Thu Mar 09, 2017 3:28 am
No new posts TSO or command line utility to genera... kishpra JCL & VSAM 3 Thu Mar 09, 2017 1:11 am
No new posts Extract set of records matching on ac... bhaskar_kanteti DFSORT/ICETOOL 3 Mon Mar 06, 2017 7:19 am
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm


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