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

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
    446 Posts
    ACCEPTED ANSWER

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

    ‏2012-06-18T18:15:47Z  in response to BlueLine
    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
      ACCEPTED ANSWER

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

      ‏2012-06-18T19:02:24Z  in response to krmilligan
      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
        446 Posts
        ACCEPTED ANSWER

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

        ‏2012-06-18T19:15:07Z  in response to BlueLine
        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
          ACCEPTED ANSWER

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

          ‏2012-06-19T11:52:19Z  in response to krmilligan
          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
            ACCEPTED ANSWER

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

            ‏2012-06-19T12:28:50Z  in response to BlueLine
            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
              250 Posts
              ACCEPTED ANSWER

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

              ‏2012-06-19T13:54:54Z  in response to BlueLine
              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
                ACCEPTED ANSWER

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

                ‏2012-06-19T18:12:09Z  in response to B.Hauser
                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
                  446 Posts
                  ACCEPTED ANSWER

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

                  ‏2012-06-20T13:50:08Z  in response to BlueLine
                  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