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
 

 

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 JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Omegamon for DB2 query ashek15 DB2 0 Wed Feb 15, 2017 11:25 am
No new posts Query on DB2 WLM stored procedures ashek15 DB2 8 Sun Feb 12, 2017 11:13 pm
No new posts GETMAIN/FREEMAIN query Suja.Sai CICS 9 Tue Jan 31, 2017 12:01 pm


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