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
 

 

Use of RTRIM negates Index Usage ?

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

New User


Joined: 01 Dec 2006
Posts: 61
Location: Pune

PostPosted: Fri Jun 15, 2012 11:21 am    Post subject: Use of RTRIM negates Index Usage ?
Reply with quote

Hi,

I have customer name search table where I have below indexes

Unique Index
----------------

Customer number INTEGER
Name Type CHAR (10)
Name Sequence number SMALLINT

Non-unique Index
--------------------
Forename key CHAR (60)
Surname key CHAR (40)

Now, I am writing static and dynamic SQL to search for Customer number based on the Supplied Forename and Surname. To support Wildcard anywhere in the names I am using RTRIM in Dynamic and Static SQL as I want to Trim the SPACES on right side.

Dynamic SQL

RTRIM(FORE_NAME) LIKE CAST(? AS CHAR(60))

STATIC SQL

RTRIM (FORE_NAME) LIKE :WW-FORE-NAME ---> this contains "shah%____" ( "____" represents spaces)


I want to know if Use of RTRIM negates the index usage and would it use Table space scan for Dynamic or STATIC SQL ?

I am running EXPLAIN and I am getting TS for direct values and IS for parameter markers (i.e. ?).

Could someone please, suggest ![/code]
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Fri Jun 15, 2012 1:37 pm    Post subject:
Reply with quote

- RTRIM does make this predicate non-indexable.
- DB2 might decide to do a non-matching IS because it would be less I/O, but it would still be a full IxScan.

- Are you sure you want "shah%____" in WW-FORE_NAME ? I can tell you this predicate will ALWAYS be FALSE. because you are looking for something that starts with shah and ends with 4 spaces. RTRIM(x) will never end in 4 spaces.
You probablty want "shah%%%%%%" in WW-FORE-NAME and then there is no need for using RTRIM() :
If RTRIM("shahkalala____") is like "shah%%%%%", then "shahkalala_____" is also like "shah%%%%%".
Back to top
View user's profile Send private message
vishalbshah

New User


Joined: 01 Dec 2006
Posts: 61
Location: Pune

PostPosted: Fri Jun 15, 2012 5:45 pm    Post subject: Reply to: Use of RTRIM negates Index Usage ?
Reply with quote

Hi,

As per my requirement I need to support wildcard anywhere in the Forname so for e.g. "VIS%L" should search for all names starting with "VIS" and ending with "L" , why i am using RTRIM is because I don't want DB2 to search for EXACT spaces after "L".

to answer your question :

"shah%____" is the value in my wroking storage variable , when I dont USE RTRIM and just use LIKE DB2 is searhing for exact trailing spaces and it does not find the record if the trailing spaces mismatch with DB2 column value, which is why I am using RTRIM , I initially used VARCHAR STRIP but came to know that that is not using INDEX.

Could you please, tell me which option is better RTRIM with LIKE and single %( which would trim the trailing SPACES or just LIKE with Padded %%% (in the end instead of spaces) to make use of index defined for Forename ?
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Fri Jun 15, 2012 7:01 pm    Post subject:
Reply with quote

There is no way you can make RTRIM(column) like ? indexable, unless you use index on expression. (google it).

What you can do is make 2 predicates :
Code:

move 'VIS%L'  to ws-fore-name1
move all '%' to ws-fore-name1(6:)
move 'VIS%L'  to ws-fore-name2
...

where FORE_NAME like :ws-fore-name1 
and rtrim(fore_name like rtrim(:ws-fore-name2)

the first predicate will be indexable, but sometimes will return too many rows.
the second predicate will remove those rows that do not end on L
Back to top
View user's profile Send private message
vishalbshah

New User


Joined: 01 Dec 2006
Posts: 61
Location: Pune

PostPosted: Fri Jun 15, 2012 9:19 pm    Post subject:
Reply with quote

hi ,

I did not understand why you used RTRIM twice once column name and the other one on the variable.

I was only using it on column name as :

RTRIM (FORE_NAME) LIKE :ws-fore-name

is it not the right syntax ?
i have tried below SYNTAX as well :

FORE_NAME LIKE RTRIM(:ws-fore-name)

do i need to used RTRIM on both sides ?

I understood as per your suggestion

where FORE_NAME like :ws-fore-name1
AND rtrim(fore_name like rtrim(:ws-fore-name2)

the first predicate becomes stage1 and indexable so DB2 would use index here. please, confirm !
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Fri Jun 15, 2012 11:13 pm    Post subject:
Reply with quote

GuyC,
apparently vishalbshah has little faith in your answers
Back to top
View user's profile Send private message
vishalbshah

New User


Joined: 01 Dec 2006
Posts: 61
Location: Pune

PostPosted: Mon Jun 18, 2012 11:26 am    Post subject: Reply to: Use of RTRIM negates Index Usage ?
Reply with quote

Hi,

It's never about faith , please don't try and create any confusion here...

why i am trying to be sure is because I am finding difference between EXPLAIN and RTRIM I uses and the response i got from GUY C

the EXPLAIN I ran has RTRIM on column name and it gave me result saying that RTRIM is not impacting the Index Usage. but from the response which I am receiving here says use of RTRIM would not allow Index usage defined on the column.

As i want to be 100% sure before I make use of this, I am raising questions at different posts , hoping that i get consistent answers.

I think I have full liberty to confirm my answers, So I request you to respond to the question rather than creating any misunderstandings !
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Mon Jun 18, 2012 11:30 am    Post subject: Reply to: Use of RTRIM negates Index Usage ?
Reply with quote

,
Quote:
I think I have full liberty to confirm my answers, So I request you to respond to the question rather than creating any misunderstandings !


You are not in the position of requesting anything

and since You mistrust the answers you receive here why are You still around ?
Back to top
View user's profile Send private message
vishalbshah

New User


Joined: 01 Dec 2006
Posts: 61
Location: Pune

PostPosted: Mon Jun 18, 2012 1:08 pm    Post subject:
Reply with quote

Hi,

The reason i am not sure about the answer is below result from EXPLAIN on the Query which I ran


Code:

EXPLAIN ALL SET QUERYNO = 992222                       
FOR                                                     
                                                       
SELECT DISTINCT CUST_NO, NAM_TYP_NO, NAM_SEQ_NO         
FROM CUD_PERS_NAM_SRCH                                 
WHERE SRNM_SRCH_KEY        =    'ANANAT'               
  AND[b] RTRIM(FRNM_SRCH_KEY) LIKE 'GNSN%        '     [/b]   
  AND FRNM_SEQ_NO          = 1                         
  AND UNIT_NO     BETWEEN 0000 AND 9999                 
;                                                       
SELECT  *                                               
  FROM PLAN_TABLE                                       
 WHERE QUERYNO = 992222                                 
 ORDER BY QBLOCKNO, PLANNO                             
;

+---------+---------+---------+---------+---------+---------+---------+---------
TNAME                TABNO  ACCESSTYPE  MATCHCOLS  ACCESSCREATOR  ACCESSNAME   
+---------+---------+---------+---------+---------+---------+---------+---------
CUD_PERS_NAM_SRCH        1  I                   2  CUDUTGB        XCUD1160     
                         0                      0                               
                                                       
SELECT  IX.COLCOUNT, PT.MATCHCOLS                       
FROM SYSIBM.SYSINDEXES IX, PLAN_TABLE PT               
WHERE IX.CREATOR = PT.ACCESSCREATOR                     
AND IX.NAME = PT.ACCESSNAME                             
AND PT.QUERYNO = 992222 


---------+---------+---------+
COLCOUNT  MATCHCOLS           
---------+---------+---------+
       9          2           




As you can see above EXPLAIN clearly suggest that 2 Columns from the index were used which are SRNM_SRCH_KEY and FRNM_SRCH_KEY (even though RTRIM was used ) . I am not 100% sure with Guy C's answer that use of RTRIM negates the Index usage.


By the way the index XCUD1160 contains below columns

Code:


 SRNM_SRCH_KEY   
 FRNM_SRCH_KEY   
 FRNM_SEQ_NO     
 UNIT_NO         
 DOB             
 GNDR             
 CUST_NO         
 NAM_TYP_NO       
 NAM_SEQ_NO       

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

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Mon Jun 18, 2012 6:05 pm    Post subject:
Reply with quote

apparently not, I guess the DB2 optimizer is getting smarter every day.
Does your query returns any row?
Back to top
View user's profile Send private message
vishalbshah

New User


Joined: 01 Dec 2006
Posts: 61
Location: Pune

PostPosted: Mon Jun 18, 2012 6:57 pm    Post subject:
Reply with quote

Hi Guy C,

Apologies for confusion as I mentioned in my initial mail this SQL is Dynamically prepared. so I have the lenght of the variable calculated which I am populating in the SQLDA, So I don't need RTRIM on Righthand side.

FYI, Dynamic SQL

RTRIM(FORE_NAME) LIKE CAST(? AS CHAR(6))

This makes use of Index properly.

For Static SQL I agree with you on that RTRIM is not making use of Index.

Could I please, ask why in your first example have provided 2 predicates to get the required result ?

move 'VIS%L' to ws-fore-name1
move all '%' to ws-fore-name1(6icon_smile.gif
move 'VIS%L' to ws-fore-name2

where FORE_NAME like :ws-fore-name1
and rtrim(fore_name) like rtrim(:ws-fore-name2)

I am trying to understand use of ws-fore-name1, is it just to force index usage defined on the FORE_NAME ? ( as the data we want is actually stisfying predicate 2 i.e. rtrim(fore_name) like rtrim(:ws-fore-name2))
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Mon Jun 18, 2012 8:27 pm    Post subject:
Reply with quote

you are confusing 2 things :
1) rtrim(column) like ? is (according to your explain, and my latest experiments) indexable.
so using two predicates : 1 indexable and 1 (i thought) not indexable is not necessary.

2) the result of rtrim(column) like 'ab%c___' will always be false.
you can use cast('ab%c ___' as char(4)) which could give some results.

I thought using rtrim('ab%c___') would be exactly the same.
And although the result should be the same,for some strange reason this criteria is stage2 (non-indexable)

That'll teach me to trust my knowledge of the DB2-optimizer.

So sorry if my mistakes/assumptions made you confuse things.
Back to top
View user's profile Send private message
vishalbshah

New User


Joined: 01 Dec 2006
Posts: 61
Location: Pune

PostPosted: Mon Jun 18, 2012 8:40 pm    Post subject:
Reply with quote

That Makes Sense, Thank You!

Just one thing like 'ab%c%%%' (indexable) and rtrim('ab%c___') (stage -2 and Non-indexable) would have different results:

"abecxyz" would be a match as per 'ab%c%%%' whereas it would not be a match as per rtrim('ab%c___')
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Mon Jun 18, 2012 9:23 pm    Post subject:
Reply with quote

yes I noticed , that's why I editted my post afterwards.
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 can amount of the Storage Usage b... esduman61 CICS 6 Wed Mar 09, 2016 1:19 pm


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