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
 
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 sort records based on length exceeds ... maxsubrat DFSORT/ICETOOL 7 Wed Oct 04, 2017 4:48 pm
No new posts HEX value search in a DB2 query maxsubrat DB2 2 Wed Oct 04, 2017 3:04 pm
No new posts Addition of two records on 2 fields amar143 SYNCSORT 8 Tue Oct 03, 2017 11:14 am
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts Join records from 2 files with No Dup... Poha Eater DFSORT/ICETOOL 22 Sun Aug 27, 2017 10:35 pm

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