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
 
Nonmatching index scan

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

New User


Joined: 13 Feb 2007
Posts: 4
Location: kolkata

PostPosted: Tue Mar 19, 2013 1:17 pm    Post subject: Nonmatching index scan
Reply with quote

My program has multiple sqls and creating performance issue recently.
The one select query creating the performance problem is -
In TABLE 1 -19000 records- when I checked in PLAN_TABLE it is actually using nonmatching index scan (access type = I, match col=0).

Other 2 tables are having -49000 records (much more than previous table) but using Table space scan (access type = R, prefetch = S) and the result is coming much faster.

My query is -
1) Hence tablespace scan is working much efficiently here than nonmatching index scan?
2) What shall be done to execute the sql on Table 1 run faster (even in spufi its showing -905, and in job taking very long time).
Note: the sql on table1 is just a select query - where DEPT_NO=:DEPT, and index is created on table on column (DEPT_NO,EMP_NO). Should I create 1 more index on table 1 with only DEPT_NO in it?
Back to top
View user's profile Send private message

Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1895
Location: UK

PostPosted: Tue Mar 19, 2013 1:20 pm    Post subject:
Reply with quote

Did you ask your team-mates? Your DBA?
Back to top
View user's profile Send private message
sumit_tumba

New User


Joined: 13 Feb 2007
Posts: 4
Location: kolkata

PostPosted: Tue Mar 19, 2013 1:27 pm    Post subject:
Reply with quote

Not yet, wanted to check what experts think on this forum.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10327
Location: italy

PostPosted: Tue Mar 19, 2013 1:30 pm    Post subject: Reply to: Nonmatching index scan
Reply with quote

Quote:
Not yet, wanted to check what experts think on this forum.

wrong answer icon_evil.gif

first You have to carry on the <due diligence> using your organization resources
and after you might ask on a forum

asking on a forum first You make US to do the job for which Your support/peers are paid for.
Back to top
View user's profile Send private message
sumit_tumba

New User


Joined: 13 Feb 2007
Posts: 4
Location: kolkata

PostPosted: Tue Mar 19, 2013 1:37 pm    Post subject:
Reply with quote

Can any expert shed some light?
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Tue Mar 19, 2013 2:44 pm    Post subject:
Reply with quote

Here is a good discussion: http://ibmmainframes.com/about58581.html and this might also interest you: http://www.mainframe-tricks-and-tips.in/2011/12/find-if-program-is-doing-table-scan-or.html.
Back to top
View user's profile Send private message
Furor

New User


Joined: 04 Jun 2009
Posts: 30
Location: Bangalore

PostPosted: Sat Mar 30, 2013 10:10 am    Post subject: Reply to: Nonmatching index scan
Reply with quote

Quote:
2) What shall be done to execute the sql on Table 1 run faster (even in spufi its showing -905, and in job taking very long time).
Note: the sql on table1 is just a select query - where DEPT_NO=:DEPT, and index is created on table on column (DEPT_NO,EMP_NO). Should I create 1 more index on table 1 with only DEPT_NO in it?


You can try various approaches to deal with this

Try this as the first attempt-

Since match col=0 , the Index Scan is not helping.
You should try to force DB2 to avoid Index Scan -

Update your where clause from
Code:
Where DEPT_NO=:DEPT

TO
Code:
Where DEPT_NO=:DEPT OR 0=1


Share your results with the forum
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 Confusion b/w index and subscript Deepak kumar25 Mainframe Interview Questions 7 Thu Aug 31, 2017 6:50 am
No new posts SQL - select data available in index Nileshkul DB2 3 Mon Jun 26, 2017 1:30 am
No new posts setting new limit Key values for inde... srilata83 DB2 1 Fri Feb 10, 2017 9:24 pm
No new posts Order BY AND Index Nileshkul DB2 2 Sat Dec 31, 2016 6:33 pm
No new posts CICS TS 5.3 migr after DFHCSDUP UPGRA... Kyle Carroll CICS 6 Wed Aug 31, 2016 10:58 pm

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