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
 

 

Guidelines needed in optimising this SQL query

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

New User


Joined: 15 Apr 2006
Posts: 1

PostPosted: Tue Nov 21, 2006 7:04 pm    Post subject: Guidelines needed in optimising this SQL query
Reply with quote

Hi,

Following is the query. The table contains millions of records. Please ignore the column names. Could anyone help me out in optimising this query. Also let me know if you want more information. It consumes 37% of CPU time for my application. What I personally feel is that the problem is with WHERE clause.

01 DECLARE C2 CURSOR FOR SELECT SECT_NO,ORD_SEQ_NO,FLD_ID_TYP,ACTN_CD,FLD_ID_NM,FLD_ID_DA FROM RSRFD10Y
WHERE ST_ID=:H AND PTN_INDR=:H AND SRV_ORD_NO=:H AND ENT_DAT=:H AND ENT_TM=:H AND SECT_NO IN(:H,:H)
AND(FLD_ID_TYP='F' AND ACTN_CD IN(:H,:H) AND FLD_ID_NM IN('DVDP','IHST','TN')) ORDER BY ORD_SEQ_NO
LOCATION: DBZK



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

MFRASHEED

Active User


Joined: 14 Jun 2005
Posts: 186
Location: USA

PostPosted: Tue Nov 21, 2006 9:09 pm    Post subject: Re: Guidelines needed in optimising this SQL query
Reply with quote

Try folloing guidelines:

1. How many indexes does table have. Are the columns given in WHERE clause with '=' have indexable columns?

If not consider add index and there will be more considerations if you are adding index.

2. SQL also have 'IN' clause but there is only one value in ( .. ), replace these 'IN' clause with '='.

3. Does your application need all the rows retrieved, if not add following to restrict no. of rows retrieved.

Code:


OPTIMIZE FOR n ROWS   
FOR FETCH ONLY         



4. Does the table have clustering index? If so, is ORD_SEQ_NO part of index in higher order, then rows retrieved will be in sequence.

5. Remember the three R's ( RRR) ==> REORG, RUNSTATS and REBIND.

Schedule REORG, RUNSTATS and REBIND on table, check Clustering Ratio (should be in higher 90's) to determine when 3 R's are neeeded. The 3 R's make SQL whose the best path to retrieve data.

6. How the the BIND done, does the application having this cursor does only retrieves or INSERTS and UPDATES. If it does retrieves only preferably it should have ISOLATION(CS) and CURRENTDATA(NO).

7. Finally run EXPLAIN or Enhanced Explain and verify if SQL is using indexes properly and how filtering is done. Do this after step # 5 ( 3 R's).
Lower the filter factor value the more likely DB2 will use and available index.
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
This topic is locked: you cannot edit posts or make replies. SORT trick needed bshkris SYNCSORT 6 Tue May 02, 2017 4:35 am
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 SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Omegamon for DB2 query ashek15 DB2 0 Wed Feb 15, 2017 11:25 am
No new posts Query on DB2 WLM stored procedures ashek15 DB2 8 Sun Feb 12, 2017 11:13 pm


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