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
 
modify query for indexspace scan

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

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Wed Mar 12, 2008 12:58 am    Post subject: modify query for indexspace scan
Reply with quote

hi all,

i have a cursor with the where clause as

where

(trn_isin = :smx-isin

or

trn_isin = :ws-und-isin

or

trn_cusip = : smx-cusip )

here trn_isin is index ..

now the selection criteria is either of the 3 values should be present in the table

if im using the above where clause the query is going for a tablespace scan
maybe because of OR ... can anyone modify this query to do a indexspace scan ?

thanks,
ashimer
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: Wed Mar 12, 2008 9:27 am    Post subject:
Reply with quote

Hello,

It appears that 2 of your 3 predicates are covered by the index but not the third. If all 3 predicates must be or'ed, this (trn_cusip = : smx-cusip) cannot be satisfied by the index and requires the full table scan.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Wed Mar 12, 2008 5:23 pm    Post subject: Reply to: modify query for indexspace scan
Reply with quote

thnks for ur ans dick .. now wht if i add trn_cusip also to the index group ?

i tried this too but still it is doing a tb scan ...in this case can you help with an alternate query ?

thanks
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


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

PostPosted: Wed Mar 12, 2008 5:38 pm    Post subject:
Reply with quote

After you added the trn_cusip to the index did you update the table statistics?
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Wed Mar 12, 2008 6:51 pm    Post subject:
Reply with quote

the table is a global temporary table ....
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Wed Mar 12, 2008 11:55 pm    Post subject:
Reply with quote

as OR predicates are not indexable can this where clause be changed so as to use IN predicate ?
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Thu Mar 13, 2008 3:02 am    Post subject:
Reply with quote

TRN_REFERENCE TRN_VERSION
---------+---------+--------
200503070007 3
200503070007 3

---------+---------+---------+---------+--
TRN_CCT_SEC_ID TRN_ISIN TRN_CUSIP
---------+---------+---------+---------+--
470228 AU000000BHP4
479447 US0886061086 088606108


the data in the table for

smx-isin = AU000000BHP4 and smx-cusip = 088606108
and ws-und-isin = US0886061086

now my above where clause will give me these 2 rows ..

now i need to change the where clause to get these
2 rows and without using OR predicate ...

the indexes are trn_isin , trn_cusip and trn_cct_sec_id ..

unions can be used but still it wont go for a indexscan ... so only
possibility is joins ...

pls help
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 HEX value search in a DB2 query maxsubrat DB2 2 Wed Oct 04, 2017 3:04 pm
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm

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