Topic
  • 8 replies
  • Latest Post - ‏2012-06-20T13:50:08Z by krmilligan
BlueLine
BlueLine
5 Posts

Pinned topic IF/CASE statements in stored procedure execute when conditions are false?

‏2012-06-18T14:13:07Z |
Hi,

I was looking for some information on short-circuit logic in stored procedures. The system in question is V6R1.

Using System i Navigator to set break points and debug a job, I have found that CASE or IF statements in procedures that are designed to execute sub-queries or function calls only in certain situations are executing regardless of the values in the condition.

For a simple example, if I had a procedure doing something like:


Select * from Table Where (InputParameterCondition = 
'N' or (InputParameterCondition = 
'Y' and FunctionToTestSomeComplexData() = 
'Y'))


Here we are trying to select only certain records when a filter condition is set on or return everything and don't execute the complex function when the condition is off. However, even when the parameter is "N", the function executes. The end result is correct: it filters the data as expected, but the extra processing still occurs when the filter is off, which is obviously not desired.

Similarly, if I had:


Select Field1, Field2, (Case When InPutParameterCondition = 
'N' then 
'' When InPutParameterCondition = 
'Y' then GetSomeComplexData() End) as ConditionedColumn From Table Where...


I would expect the function to execute only when the parameter is "Y" but it executes all the time. The value returned is correct, but again, the extra processing occurs.

Researching this indicates that SQL doesn't always guarantee short-circuit logic. Is this correct for DB2 and should I expect what I've seen?
Updated on 2012-06-20T13:50:08Z at 2012-06-20T13:50:08Z by krmilligan
  • krmilligan
    krmilligan
    450 Posts

    Re: IF/CASE statements in stored procedure execute when conditions are false?

    ‏2012-06-18T18:15:47Z  
    Do you know if your SELECT statement is being processed by SQL Query Engine (SQE) or Classic Query Engine (CQE)? Does your 6.1 system have a recent version of the Database Group PTF installed?
  • BlueLine
    BlueLine
    5 Posts

    Re: IF/CASE statements in stored procedure execute when conditions are false?

    ‏2012-06-18T19:02:24Z  
    Do you know if your SELECT statement is being processed by SQL Query Engine (SQE) or Classic Query Engine (CQE)? Does your 6.1 system have a recent version of the Database Group PTF installed?
    Classic Engine, confirmed through I Series Navigator. The tables are DDS files, some with selection criteria, which is what I think forces it to use CQE?

    I'm not sure about the PTF yet but I'm checking into it.
  • krmilligan
    krmilligan
    450 Posts

    Re: IF/CASE statements in stored procedure execute when conditions are false?

    ‏2012-06-18T19:15:07Z  
    • BlueLine
    • ‏2012-06-18T19:02:24Z
    Classic Engine, confirmed through I Series Navigator. The tables are DDS files, some with selection criteria, which is what I think forces it to use CQE?

    I'm not sure about the PTF yet but I'm checking into it.
    That's a limitation of the CQE query optimizer. So is your FROM clause referencing the name of a Select/Omit logical file? That would force the usage of CQE on 6.1
  • BlueLine
    BlueLine
    5 Posts

    Re: IF/CASE statements in stored procedure execute when conditions are false?

    ‏2012-06-19T11:52:19Z  
    That's a limitation of the CQE query optimizer. So is your FROM clause referencing the name of a Select/Omit logical file? That would force the usage of CQE on 6.1
    The query doesn't reference any logical files, but one physical file has no keys and so the engine defaults to a logical for a join. That logical doesn't have any selection criteria but it appears that simply having a logical forces use of the CQE.

    I've since tested this a bit more and confirmed that when a query uses SQE, I don't see functions getting called when conditions are false. Thanks very much for this info! I wasn't aware at all.
  • BlueLine
    BlueLine
    5 Posts

    Re: IF/CASE statements in stored procedure execute when conditions are false?

    ‏2012-06-19T12:28:50Z  
    • BlueLine
    • ‏2012-06-19T11:52:19Z
    The query doesn't reference any logical files, but one physical file has no keys and so the engine defaults to a logical for a join. That logical doesn't have any selection criteria but it appears that simply having a logical forces use of the CQE.

    I've since tested this a bit more and confirmed that when a query uses SQE, I don't see functions getting called when conditions are false. Thanks very much for this info! I wasn't aware at all.
    Sorry, correction to my last post: it actually is referencing a logical. I thought it wasn't because we don't usually specify them explicitly but in this case we did (and I didn't look closely enough before replying!)

    Testing some queries in the Visual Explain window shows that the system can decide to use a logical index for a query and still run through SQE but if the logical is specified in the query, it will default to CQE. That's kind of interesting.
  • B.Hauser
    B.Hauser
    287 Posts

    Re: IF/CASE statements in stored procedure execute when conditions are false?

    ‏2012-06-19T13:54:54Z  
    • BlueLine
    • ‏2012-06-19T12:28:50Z
    Sorry, correction to my last post: it actually is referencing a logical. I thought it wasn't because we don't usually specify them explicitly but in this case we did (and I didn't look closely enough before replying!)

    Testing some queries in the Visual Explain window shows that the system can decide to use a logical index for a query and still run through SQE but if the logical is specified in the query, it will default to CQE. That's kind of interesting.
    Before Release 7.1 the query dispatcher rerouted all queries with a references to a DDS described logical file back to the CQE.

    Because only the CQE could analyze the DDS physical files and rewrite the query based on the underlying physical files (or SQL Tables). After the query is rewritten, optimization will start, i.e. all available access path in either DDS described logical files or SQL indexes can be estimated.

    Beginning with release 7.1 queries with references to DDS described logical files can be handled by the SQE.

    Birgitta
  • BlueLine
    BlueLine
    5 Posts

    Re: IF/CASE statements in stored procedure execute when conditions are false?

    ‏2012-06-19T18:12:09Z  
    • B.Hauser
    • ‏2012-06-19T13:54:54Z
    Before Release 7.1 the query dispatcher rerouted all queries with a references to a DDS described logical file back to the CQE.

    Because only the CQE could analyze the DDS physical files and rewrite the query based on the underlying physical files (or SQL Tables). After the query is rewritten, optimization will start, i.e. all available access path in either DDS described logical files or SQL indexes can be estimated.

    Beginning with release 7.1 queries with references to DDS described logical files can be handled by the SQE.

    Birgitta
    I see now, thanks to both of you. I'll keep 7.1 in mind for the future.

    Just to clarify what I found for anyone else who reads this thread:

    -it seems that, in 6.1 at least, function calls in a "WHERE" clause will execute regardless of conditions around them in both CQE and SQE.

    -the CQE will also execute functions in a CASE statement in the main body of a query when conditions are false. The SQE, however, will not, which is good. This can be used to guard execution.

    I have since changed my query to remove the logical and use the SQE.
  • krmilligan
    krmilligan
    450 Posts

    Re: IF/CASE statements in stored procedure execute when conditions are false?

    ‏2012-06-20T13:50:08Z  
    • BlueLine
    • ‏2012-06-19T18:12:09Z
    I see now, thanks to both of you. I'll keep 7.1 in mind for the future.

    Just to clarify what I found for anyone else who reads this thread:

    -it seems that, in 6.1 at least, function calls in a "WHERE" clause will execute regardless of conditions around them in both CQE and SQE.

    -the CQE will also execute functions in a CASE statement in the main body of a query when conditions are false. The SQE, however, will not, which is good. This can be used to guard execution.

    I have since changed my query to remove the logical and use the SQE.
    SQE support for Simple Logical File References was added for the IBM i 6.1 release. Check out the Performance category of the DB2 for i Tech Updates Wiki:
    https://www.ibm.com/developerworks/ibmi/techupdates/db2