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
 

 

Index not being accessed ... why?

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

New User


Joined: 11 Feb 2008
Posts: 53
Location: NJ

PostPosted: Thu Oct 07, 2010 8:53 pm    Post subject: Index not being accessed ... why?
Reply with quote

I have a table that has two indexes ...

First one is on Column 2 of the table
Second one is on Columns 1, 2 , 3 .

Now, when I am declaring my cursor with hold for Select... I have columns 1,2,3 listed in my WHERE clause... but yet, DB2 is deciding to scan the entire table space...

Column 1 has a table partition number ... This table has a total of 14 partitions... but only Partition 13 has data in it .. meaning a value of '13' is inside column 1 for all contracts (column 2) ...

The table has around 3 million rows for partition 13 and nothnig else for other partitions..

Can anyone explain why DB2 is deciding not use the index for Col 1,2,3?

Thank you in advance for your insight...
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 Oct 07, 2010 9:36 pm    Post subject:
Reply with quote

Hello,

Because db2 has determined that the table scan will be more efficient than using the index. If the other 13 partitions were loaded with data and there was a wide range of values, the index would most likely have been chosen.
Back to top
View user's profile Send private message
Nirav721

New User


Joined: 11 Feb 2008
Posts: 53
Location: NJ

PostPosted: Thu Oct 07, 2010 9:43 pm    Post subject: Reply to: Index not being accessed ... why?
Reply with quote

Hi ... thank you.. Is there any way to 'force' db2 to go againest the index? Or any other way for the index to be defined (order of the columns inside index??)

The reason why I am asking this is that it is taking considerably long time to look up a single contract using table scan...

Thanks..
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 Oct 07, 2010 11:15 pm    Post subject:
Reply with quote

Hello,

Using an ORDER BY the columns in the index may do what you want.

Suggest you run an "explain" and work with your dba. There may need to be a change in the database definition. . .
Back to top
View user's profile Send private message
Nirav721

New User


Joined: 11 Feb 2008
Posts: 53
Location: NJ

PostPosted: Fri Oct 08, 2010 12:13 am    Post subject: Reply to: Index not being accessed ... why?
Reply with quote

Thanks again for quick reply .. when you say ORDER BY, you mean inside my SELECT statement? If yes, I did try that but it did not help...

I just had my DBA add additional index in order of COL 2, COL 1, and COL 3... ? Is this efficient? I am not sure, but it works... what do you suggest?
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Fri Oct 08, 2010 12:30 am    Post subject:
Reply with quote

Are the DB2 statistics for the table & indexes current.
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: Fri Oct 08, 2010 1:00 am    Post subject:
Reply with quote

Hello,

Quote:
when you say ORDER BY, you mean inside my SELECT statement? If yes, I did try that but it did not help...
Yes, inside the SELECT - in the same order as they are named in the key. . .

It may help if you show the different SELECT statements that were tried. . .
Back to top
View user's profile Send private message
ovreddy

Active User


Joined: 06 Dec 2004
Posts: 211
Location: Keane Inc., Minneapolis USA.

PostPosted: Fri Oct 08, 2010 1:34 am    Post subject:
Reply with quote

Hi,

Do you have additional columns in your WHERE CLAUSE as well. If so put the 3 indexed columns with in bracket that might help.

Example WHERE (col1=val1 and col2=val2 and col3=val3) and (col4=val4 or 0=0);

0=0 forces not to use col4 for searching database. This way we can disqualify the indexed columns from being used in search.

If you are running a batch program then RUNSTATS and rebinding would help.

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

New User


Joined: 11 Feb 2008
Posts: 53
Location: NJ

PostPosted: Fri Oct 08, 2010 4:28 am    Post subject: Reply to: Index not being accessed ... why?
Reply with quote

Thanks everyone .. yes, the stats are all up-to-date. The DBA just blamed other partitions not having data and that was throwing DB2 off track in not using the index.. so adding index starting with non-partition column forces it to use index now...

I find it odd that DB2 is very strict..I am only 'hoping' that once we get to production, it behaves as we think it should..otherwise, long days ahead!
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Fri Oct 08, 2010 3:29 pm    Post subject:
Reply with quote

with table partitioning, db2 doesn't need an index to determine which partitions to scan.

so if col2=13 is only partition 13 then a select where COL2 = 13 can be a tablescan , but will be a tablescan of only part 13 .

See column page_range of your explain table.
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: Fri Oct 08, 2010 7:39 pm    Post subject:
Reply with quote

Hi Guy,

The problem was that db2 chose to scan the entire table rather than directly reading the rows matching the select. Adding the new key provided direct access rather than the scan.

One concern might be performance impact with this new key. If rows are added once, seldom or never updated, and used often to read data, it should not be a problem. Well, imho. . .
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Fri Oct 08, 2010 7:50 pm    Post subject:
Reply with quote

just want to make sure that "a complete tablescan" is really what DB2 decides,
Sometimes people misinterprete the explain due to the reason I explained.

a select ... where col1=? and col2 =? and col3=? (direct access) with correct stats , not using an index col1,col2,col3 is highly unlikely.

Maybe it is not col1 = but something like col1 between / col1 > / col1 not in() , maybe it is not stage1 but a stage2 predicate
Without the actual where clause being posted it is highly speculative.
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: Fri Oct 08, 2010 8:55 pm    Post subject:
Reply with quote

Hi Guy,

Quote:
just want to make sure that "a complete tablescan" is really what DB2 decides,

I may have read too much into the earlier posts - it was my understanding that originally the entire partition was being scanned (and taking a long time) and adding the new key caused the query to run quickly.
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 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
No new posts How to find Table-space and Index-spa... venksiv DB2 5 Wed Feb 03, 2016 6:16 pm


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