Results 1 to 15 of 15
  1. #1
    Join Date
    Nov 2008
    Posts
    48

    Unanswered: Sequence Generation Issues

    CREATE FUNCTION GEN_test_no( ) RETURNS INTEGER SPECIFIC GEN_test_no NO EXTERNAL ACTION
    F1: BEGIN ATOMIC
    declare v_shipment integer;
    set v_shipment = ( nextval for Admin.test_no );
    RETURN v_shipment;
    END
    --------------------------

    table X

    RN
    someval1
    someval2
    someval3
    someval4
    someval5

    Query

    Select RN,
    case when rn in ('someval1','someval3','someval5','someval7') then admin.GEN_test_no() else null end seq_val
    from X


    Expected output
    col Seq
    someval1 1
    someval2
    someval3 2
    someval4
    someval5 3

    Current OutPut
    col S eq
    someval1 1
    someval2
    someval3 3
    someval4
    someval5 5

    Why are next values for squence generated used up even though it is in case statement.
    Is there a way for sequence no. not getting generated if case statement is false.

    Thanks

    DB2 v9.5.301
    Windows
    Last edited by phil72; 06-13-12 at 15:52. Reason: More Info

  2. #2
    Join Date
    Nov 2011
    Posts
    334
    What DB2 version/release/fix pack and platform OS are you using?
    I test your sql in db2 9.7 luw aix 6.1
    But got a error sql0348。
    That indicates:
    NEXT VALUE expressions cannot be specified in the following contexts:
    * CASE expression
    * argument list of an aggregate function
    * subquery except in the fulls

  3. #3
    Join Date
    Nov 2008
    Posts
    48

    Changes in query

    Thanks fengsun2 ,

    You are right next value does not work in Case statement i am calling a function to get next value. I am thinking now to pass parameter to the function and use that parameter to make decision .

    CREATE FUNCTION GEN_test_no( gen_value char(3))
    RETURNS INTEGER SPECIFIC GEN_test_no NO EXTERNAL ACTION
    F1: BEGIN ATOMIC
    declare v_shipment integer;
    if gen_value = 'YES' then
    set v_shipment = ( nextval for Admin.test_no );
    RETURN v_shipment;
    else
    RETURN null;
    ENd if;
    END

    Select RN,
    admin.GEN_test_no(case when rn in ('someval1','someval3','someval5','someval7') then 'YES' ELSE 'NO' End) seq_val
    from Admin.X

    This seems to work. Problem with this solution is that i will have to go back and change programs to pass parameter to function
    Last edited by phil72; 06-13-12 at 16:33.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The issue with CASE is that it is not deterministic how often NEXTVAL would be evaluated. For optimizations and performance, DB2 may not go sequentually through all the WHEN/THEN branches but rather evaluate multiple branches in parallel and then pick the result of the correct branch. And since NEXTVAL itself is not deterministic (yielding a different number on each access), things become rather unclear what the behavior is going to be.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Select RN,
    case when rn in ('1','3','5','7','9') then admin.GEN_test_no() else null end seq_val
    from X


    Expected output
    col Seq
    1 1
    2
    3 2
    4
    5 3
    Looking into your data in table X and Expected output,
    using NEXTVAL is not neccesary.

    For example:
    Code:
    Select RN
         , case when rn in ('1','3','5','7','9') then rn / 2 + 1 else null end seq
    from X
    Last edited by tonkuma; 06-13-12 at 14:36. Reason: Change "seq_val" to "seq" to meet "Expected output" exactly.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Your updated data were
    table X

    RN
    someval1
    someval2
    someval3
    someval4
    someval5

    Query

    Select RN,
    case when rn in ('1','3','5','7','9') then admin.GEN_test_no() else null end seq_val
    from X


    Expected output
    col Seq
    someval1 1
    someval2
    someval3 2
    someval4
    someval5 3
    I couldn't understand why you expected the output from the query.
    Because no value of rn(in your table X) in ('1','3','5','7','9'),
    then all val should take "else null" regardless the returned value of "then expression".

  7. #7
    Join Date
    Nov 2008
    Posts
    48
    sorry for confusion i changed data in table to make example clearer but forgot to change in case statement.

    My question is if my select calls a function that gets next value of sequence , the function should be called only when case statement is true

  8. #8
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Your function is probably inlined (see the access plan), and Knut Stolze post probably explains the behaviour.

    Ask youself why you need this type of processing and what alternative approaches might be possible.

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Stolze already explained detailes.
    Quote Originally Posted by stolze View Post
    The issue with CASE is that it is not deterministic how often NEXTVAL would be evaluated. For optimizations and performance, DB2 may not go sequentually through all the WHEN/THEN branches but rather evaluate multiple branches in parallel and then pick the result of the correct branch. And since NEXTVAL itself is not deterministic (yielding a different number on each access), things become rather unclear what the behavior is going to be.
    Why do you stick to using NEXTVAL in CASE expression?

    I already showed alternative example which returned exactly what you expected.
    If the query example was not sufficient,
    please supply more sample data which include exceptional cases and are not covered by my example.

    NEXTVAL is one of optional functionality of DB2.
    So, if it was useful for your requirements then use it.
    If there were other ways to satisfy your requirements, then consider the alternatives, too.

    Use of NEXTVAL(or any specific functionality of computer systems) is not requirements, except in practice/exercise/home work.

  10. #10
    Join Date
    Nov 2008
    Posts
    48
    i was able to get the desired result , by passing parameter to function. Thank you
    Stolze, tonkuma ,db2mor and fengsun2 for your explainations and time .

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    i was able to get the desired result , by passing parameter to function.
    Would you mind publishing the function?

  12. #12
    Join Date
    Nov 2011
    Posts
    334
    Quote Originally Posted by stolze View Post
    The issue with CASE is that it is not deterministic how often NEXTVAL would be evaluated. For optimizations and performance, DB2 may not go sequentually through all the WHEN/THEN branches but rather evaluate multiple branches in parallel and then pick the result of the correct branch. And since NEXTVAL itself is not deterministic (yielding a different number on each access), things become rather unclear what the behavior is going to be.
    hi, Mr. stolze, What is the meaning of "evaluate multiple branches in parallel and then pick the result of the correct branch"
    Do you mean, db2 will seperate the rows to be processed into several groups ,
    and then evaluate the case statemment concurrently?
    But as far as i know ,it will no happened while intra_parallel is not turned on.
    Can you explain more clearly , thanks so much.

  13. #13
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    What I meant can be illustrated using this example:
    Code:
    CASE
          WHEN cond1 THEN op1
          WHEN cond2 THEN op3
          ELSE op3
    END
    You may assume that DB2 evaluates predicate "cond1" and if that predicate is true, "op1" is processed next. If "cond1" is not true, predicate "cond2" is evaluated and so on. This is how humans think and that is not always the optimal way because the sequential processing can be improved (sometimes/often) by parallelism. So what the SQL standard allows and DB2 exploits (if the optimizer decides to do so) is to evaluate "cond1" and "cond2" in parallel and also to process the expressions "op1" and "op2" and "op3" in parallel. And once the result for "cond1" and "cond2" is known, DB2 picks up and continues to work with the correct result of expressions "op1", "op2", or "op3". What you gain is performance, which is sometimes desired as I have heard. :-)

    With such a processing model being possible (note that I'm not saying it is always used!), you have to consider non-deterministic expressions and expressions that have side effects, e.g. UDFs that would write to files or things that do something else like incrementing a sequence.

    p.s: I don't know whether intra_parallel may or may not have an impact on how the branches of CASE expressions are evaluated. Once a CASE expression is mangled through the optimizer, it may be completely rewritten to something else. Again, I don't know if that actually happens in DB2 but I can imagine such transformations. So you shouldn't rely on this not to be possible today _and_ in the future. Anyway, if you have such a different construct (e.g. a set operation using a UNION), then you still don't know the order in which the branches are processed - regardless of any parallelism configuration you may have set.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  14. #14
    Join Date
    Nov 2008
    Posts
    48
    I am sending parameter YES or NO to the function , to make decision to call next value or not.

    value of parameter is set in query.

    Please see the function and query :


    CREATE FUNCTION GEN_test_no( gen_value char(3))
    RETURNS INTEGER SPECIFIC GEN_test_no NO EXTERNAL ACTION
    F1: BEGIN ATOMIC
    declare v_shipment integer;
    if gen_value = 'YES' then
    set v_shipment = ( nextval for Admin.test_no );
    RETURN v_shipment;
    else
    RETURN null;
    ENd if;
    END

    Select RN,
    admin.GEN_test_no(case when rn in ('someval1','someval3','someval5','someval7') then 'YES' ELSE 'NO' End) seq_val
    from Admin.X

  15. #15
    Join Date
    Nov 2011
    Posts
    334
    Quote Originally Posted by stolze View Post
    What I meant can be illustrated using this example:
    Code:
    CASE
          WHEN cond1 THEN op1
          WHEN cond2 THEN op3
          ELSE op3
    END
    You may assume that DB2 evaluates predicate "cond1" and if that predicate is true, "op1" is processed next. If "cond1" is not true, predicate "cond2" is evaluated and so on. This is how humans think and that is not always the optimal way because the sequential processing can be improved (sometimes/often) by parallelism. So what the SQL standard allows and DB2 exploits (if the optimizer decides to do so) is to evaluate "cond1" and "cond2" in parallel and also to process the expressions "op1" and "op2" and "op3" in parallel. And once the result for "cond1" and "cond2" is known, DB2 picks up and continues to work with the correct result of expressions "op1", "op2", or "op3". What you gain is performance, which is sometimes desired as I have heard. :-)

    With such a processing model being possible (note that I'm not saying it is always used!), you have to consider non-deterministic expressions and expressions that have side effects, e.g. UDFs that would write to files or things that do something else like incrementing a sequence.

    p.s: I don't know whether intra_parallel may or may not have an impact on how the branches of CASE expressions are evaluated. Once a CASE expression is mangled through the optimizer, it may be completely rewritten to something else. Again, I don't know if that actually happens in DB2 but I can imagine such transformations. So you shouldn't rely on this not to be possible today _and_ in the future. Anyway, if you have such a different construct (e.g. a set operation using a UNION), then you still don't know the order in which the branches are processed - regardless of any parallelism configuration you may have set.
    Thanks for your explaination....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •