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
 

 

DB2 indexing

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

Senior Member


Joined: 21 Sep 2010
Posts: 1736
Location: NY,USA

PostPosted: Thu Jun 21, 2012 2:08 pm    Post subject: DB2 indexing
Reply with quote

Suppose I have a below query,

Code:
Select emp_id,Dep_id,Name
from tableA
where
Dep_id=20
with ur;

Now on tableA, the indexing sequence is in below format,

Code:
Index info:
TableA.INDX001
emp_id
Dep_id
So in this case would the above index will be considered or it will just ignore and do a table space scan as because of Dep_id is at second position in the index creation??

Thanks,

Rohit
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Thu Jun 21, 2012 2:31 pm    Post subject:
Reply with quote

why don't you look at the EXPLAIN output and tell us the answer to your question?
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu Jun 21, 2012 2:46 pm    Post subject:
Reply with quote

It depends on DB2 version, cardinality of emp_id,dep_id, number of pages,clusterratio of the index,... but most likely DB2 will choose a tablescan.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1736
Location: NY,USA

PostPosted: Thu Jun 21, 2012 7:41 pm    Post subject:
Reply with quote

I am trying doing that, but not finding such existing queries. but if any one is already knowing this then would save my time. icon_smile.gif
Aslo current version is V9.1
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Jun 21, 2012 8:07 pm    Post subject:
Reply with quote

Hello,

Quote:
I am trying doing that, but not finding such existing queries.

You should run the Explain on YOUR query - not some existing query.

Quote:
but if any one is already knowing this then would save my time.
The advice you've been given is probably the best you will get with the limited info we have available.

And keep in mind that we are not here to save your time, but to help people with how/why questions and help solve problems. What you are working on Should take your time . . .
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1736
Location: NY,USA

PostPosted: Thu Jun 21, 2012 8:25 pm    Post subject:
Reply with quote

Yes Dick, noted.
I will update you all with the explain result.
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 Indexing in db2 with expressions knn9413 DB2 4 Tue Sep 20, 2011 2:42 am
No new posts Indexing issue kanha DB2 1 Mon Jul 25, 2011 1:04 pm
No new posts FIELD(1:4): compilation is indexing t... Ensign COBOL Programming 7 Mon Jun 22, 2009 5:24 pm
No new posts Status AK while using Secondary index... Jonai IMS DB/DC 4 Mon Mar 09, 2009 1:27 pm
No new posts VSAM file indexing query aakar JCL & VSAM 14 Wed Oct 15, 2008 3:52 pm


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