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
 

 

Performace of CASE statement

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
V S Amarendra Reddy

Active User


Joined: 13 Sep 2006
Posts: 189
Location: INDIA

PostPosted: Wed Jul 07, 2010 8:33 pm    Post subject: Performace of CASE statement
Reply with quote

Hi

I need to select some rows with timestamp greater than or equal to current timestamp when a host variable is 'A' or 'F' and if the host variable is 'C' then select rows with timestamp equal to a timestamp given in the input. I ahve clubbed both of them into a single query using a CASE statement .

I would like to know if this is performace effective or defective.



Code:
 
WHERE (( TS_ENDVAL_308RACM >=                           
      CASE WHEN :WS-CO-ACT = 'A' OR WHEN :WS-CO-ACT = 'F'
           THEN CURRENT TIMESTAMP                       
           ELSE '0001-01-01-01.01.01.000000'             
      END) OR                                           
     (T1.TS_ENDVAL_308RACM =                             
      CASE WHEN :WS-CO-ACT = 'C'                         
           THEN :DCLSURNGETV.TS-ENDVAL-308RNGE           
           ELSE '0001-01-01-01.01.01.000000'             
      END)           
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


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

PostPosted: Wed Jul 07, 2010 8:47 pm    Post subject:
Reply with quote

have you tested this?

I would think that you would want your
first else to be a 3999-12-31-23.59.59.999999

actually,
since I don't think your select will return what you want,
I would write it:

Code:

where (ts_endval_308racm >= current_timestamp
             and
              (:ws-co-act = 'A'
                or
               :ws-co-act = 'F')
          )
         or
          (ts_endval_308racm = :ts-endval-308rnge
             and
            :ws-co-act = 'C'
          )
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Thu Jul 08, 2010 11:25 am    Post subject:
Reply with quote

The more I thought about this,
since the deciding factor is the value of a host variable,
you should have two simple sql's.

one for ts_endval_308racm >= Current_timestamp
when an IF statement finds ws-co-act = 'A' or 'F'

and
one for ts_endval_308racm = = :ts-endval-308rnge
when an IF statement finds ws-co-act = 'C'

There is no need to add the extra burden on db2
to check the value of a host variable,
because of the desire to have only one sql.
Back to top
View user's profile Send private message
V S Amarendra Reddy

Active User


Joined: 13 Sep 2006
Posts: 189
Location: INDIA

PostPosted: Thu Jul 08, 2010 11:26 am    Post subject:
Reply with quote

Hi Dick Brenholtz

I had little mistake in that query that I should use AND instead of OR. Now I am getting the desired result.

Can you please tell me which one is performace effective the CASE or the one you ahve given.

I have gone through the performance tuning of DB2, they mentioned that avoid using STAGE2 predicates(i.e. testing expression = value in this case :ws-co-act= some value).

But at the same time they mentioned that if you have the expression thing on the right side of the column it's fine.

So I am bit confused.

Kindly help.
Code:


Now the query has become like this.
WHERE  T1.TS_ENDVAL_308RACM >=                             
      CASE WHEN :WS-CO-ACT = 'A' OR WHEN :WS-CO-ACT = 'F'
           THEN CURRENT TIMESTAMP                       
           ELSE '0001-01-01-01.01.01.000000'             
      END                                               
AND   T1.TS_ENDVAL_308RACM =                             
      CASE WHEN :WS-CO-ACT = 'C'                         
           THEN :DCLSURNGETV.TS-ENDVAL-308RNGE           
           ELSE '0001-01-01-01.01.01.000000'             
      END                                               


Regards
Amar
Back to top
View user's profile Send private message
V S Amarendra Reddy

Active User


Joined: 13 Sep 2006
Posts: 189
Location: INDIA

PostPosted: Thu Jul 08, 2010 11:43 am    Post subject:
Reply with quote

I am sorry for the previous post..You were correct my query doesn't work. So I changed it as explained by you.

Thanks a lot for the help.

I didn't see your second post before I have posted the query. Yeah before posting the question I have designed 2 programs with single query and another qith multiple queries(to use it if I can't use first one).

But unfortunately these 2 are not just singleton SELECTs they are cursors.So still you think that I should use the second way of programming??
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Thu Jul 08, 2010 1:38 pm    Post subject:
Reply with quote

V S Amarendra Reddy,

ok, my philosophy:

db2 is a resource shared by everybody

my program runs in its own space.

why offload a compare of a literal to a working-storage location to db2?
the less I require of db2, the more db2 for others.

Only difference I see between singleton selects and cursors is the number of rows returned.

sure that means you have to keep track of which open/fetch/close to use,
and have a functional error routine which will provide adequate info, in case it is needed.

short answer to your question - regardless of singleton select or cursor,
do as much in the program as possible-
with the exclusion of ISO functions, date/time compares/manipulation
db2 does that better.

yeah I know. is there a hard and fast rule?
no, it is one of those things that you know when you see it,
but very difficult to 'nail-down'.
there will always be things that I consider an exception, and have db2 do,
but comparing working-storage variables to literals, I rarely do - only in really complex where clauses.

i hope that I did not confuse you.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Thu Jul 08, 2010 7:38 pm    Post subject:
Reply with quote

Dbz,

Quote:
why offload a compare of a literal to a working-storage location to db2?
the less I require of db2, the more db2 for others.

That was good one.

Sushanth
Back to top
View user's profile Send private message
V S Amarendra Reddy

Active User


Joined: 13 Sep 2006
Posts: 189
Location: INDIA

PostPosted: Thu Jul 08, 2010 7:42 pm    Post subject: Reply to: Performace of CASE statement
Reply with quote

Hi dbzTHEdinosauer,

Firstly Thanks a ton for the response given. I am so clear. I am going to use the second way of using 2 cursors to increase the performance as told by you.

Thank you once again.

Regards
Amar
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Thu Jul 08, 2010 8:10 pm    Post subject:
Reply with quote

glad somebody appreciates my ramblings.

thx
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 ROUNDED Problem with COMPUTE statement shalem COBOL Programming 11 Thu Feb 09, 2017 8:16 pm
No new posts VALIDATE NULL VALUE IN DB2 CASE STATE... useit DB2 5 Thu Feb 09, 2017 4:34 pm
No new posts Summing on Mixed Case Literals Roy Ware SYNCSORT 3 Tue Jan 03, 2017 10:18 pm
No new posts -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am
No new posts COBOL DB2 - CALL statement - high CPU... TS70363 DB2 15 Sun Sep 11, 2016 6:07 am


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