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 Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm


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