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
 

 

COL op expression

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

New User


Joined: 06 Feb 2006
Posts: 17
Location: Kolkata

PostPosted: Mon Sep 13, 2010 7:05 pm    Post subject: COL op expression
Reply with quote

Hi,
I have a query in a program in which is having col1+col2 > 0 in the where clause. I am converting it to WHERE col1 > col2 * -1. Its giving me some performance gain.
col1 + col2 > 0 is a Stage 2 Predicate. Can ua please let me know the reason for the CPU gain. is col1 > col2 * -1 Stage 1 ? Converting from Stage 2 to Stage 1 is giving the gain ?

Another thing... Can u please share with me any further SQL tuning tips ? We are working on a performance tuning project using COBOL-CICS-DB2.

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

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Mon Sep 13, 2010 7:37 pm    Post subject:
Reply with quote

afaik Col1 > col2 is still stage2 when col1 and col2 are from the same table.

What tool do you use to explain? OSC will tell you if it is stage 1 or Stage2.

If you have a full set of explain_tables following query will also tell you :
Code:
select a.PROGNAME, a."QUERYNO",a."VERSION", B.SEARCHARG, b.text from
plan_table A
join DSN_PREDICAT_TABLE B
on a."QUERYNO" = b."QUERYNO" and a.BIND_TIME = b.EXPLAIN_TIME

where A.PROGNAME = ?
 and a."VERSION" = ?
 and a."QUERYNO" = ?
Back to top
View user's profile Send private message
tamal_babu

New User


Joined: 06 Feb 2006
Posts: 17
Location: Kolkata

PostPosted: Mon Sep 13, 2010 11:03 pm    Post subject: Reply to: COL op expression
Reply with quote

COL1 AND COL2 are from the same table. Please lemme know if there is any other way to remove this kind of stage 2 predicate and make it stage 1 ?

WHERE COL1 + COL2 > 0
Back to top
View user's profile Send private message
Phrzby Phil

Active Member


Joined: 31 Oct 2006
Posts: 962
Location: Richmond, Virginia

PostPosted: Tue Sep 14, 2010 1:00 am    Post subject:
Reply with quote

Just asking: would COL1 > -COL2 be the same or better than the version with "* -1"?
Back to top
View user's profile Send private message
Bharath Bhat

Active User


Joined: 20 Mar 2008
Posts: 283
Location: chennai

PostPosted: Tue Sep 14, 2010 11:03 am    Post subject: Re: Reply to: COL op expression
Reply with quote

tamal_babu wrote:
COL1 AND COL2 are from the same table. Please lemme know if there is any other way to remove this kind of stage 2 predicate and make it stage 1 ?

WHERE COL1 + COL2 > 0


Code:
(Col1 > 0 OR Col2 > 0)
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Tue Sep 14, 2010 3:42 pm    Post subject:
Reply with quote

1) col1 + col2 > 0 is equivalent to col1 > 0 or col2 > 0 ONLY if both are always positive (or always negative)

2) performance of unary - is probably picoseconds faster than * -1

3) from TAB1 where col1 = -col2 is stage2
you can change it to stage1 but it is not always better, but worth a try:
from TAB1 A, TAB1 B where a.pk = b.pk and a.col1 = -b.col2
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 Clustered Index with UCASE Key Expres... Auryn DB2 2 Mon Feb 02, 2015 8:00 pm
No new posts V9 - Expression indices vadim vashchenko DB2 2 Fri Aug 05, 2011 4:04 am
No new posts CASE Expression with Multiple Entries dbzTHEdinosauer DB2 8 Thu Jun 30, 2011 1:00 pm
No new posts CASE Expression krithikaj DB2 5 Wed Dec 22, 2010 4:40 am
No new posts Problem in SQL query : CASE expression Rindia DB2 2 Fri Aug 07, 2009 10:30 am


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