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
 

 

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: 1712
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: 10201
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 CICS TS 5.3 migr after DFHCSDUP UPGRA... Kyle Carroll CICS 6 Wed Aug 31, 2016 10:58 pm
No new posts Several errors during building altern... Andi1982 JCL & VSAM 11 Wed Jul 06, 2016 7:39 pm
No new posts Drop building Alternate Index for a p... bhavin.mehta JCL & VSAM 6 Mon Jul 04, 2016 3:47 pm
No new posts VSAM Alternate Index in CICS jacobdng CICS 2 Fri May 06, 2016 1:41 pm
No new posts Rebuilding IMS secondary index withou... Hooman24 IMS DB/DC 6 Tue Mar 29, 2016 12:22 pm


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