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 How to force a select query to abend neo4u DB2 5 Mon Apr 23, 2018 1:36 pm
No new posts Extract the records with a PD field's... sudhakar84 DFSORT/ICETOOL 11 Mon Apr 02, 2018 7:26 pm
No new posts Merge 2 records sancraig16 SYNCSORT 19 Tue Mar 27, 2018 8:17 pm
No new posts Query to compare 2 values of 1 column... Poha Eater DB2 13 Fri Mar 09, 2018 10:45 am
No new posts Copy set of records based on condition krish.deepu SYNCSORT 2 Thu Mar 08, 2018 11:39 am

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