Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Mar 2012
    Posts
    120

    Unanswered: session for a sequence

    Hi all,
    I have a question on sequences in DB2 9.7

    If I define a procedure called getSequence like this:

    create procedure getSequence
    (out lseq bigint)
    language sql
    begin
    select prevval for myseq into lseq from sysibm.sysdummy1;
    end

    then from command editor I run:
    select nextval for myseq from sysibm.sysdummy1!
    call getSequence(?)!

    I get the error:
    Issue at least one NEXT VALUE expression for a sequence before issuing
    any PREVIOUS VALUE expression for the same sequence in a session.

    But if I create another procedure called setSequence like this:
    create procedure setSequence
    (out lseq bigint)
    language sql
    begin
    select nextval for myseq into lseq from sysibm.sysdummy1;
    end

    and I run from command editor:
    call setSequence(?)!
    call getSequence(?)!

    everything goes fine.

    Maybe it's a silly question, and sorry for that, but could someone explain what a "session" is for DB2 and why I get an error in the first case?

    Thanks
    Robert

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Code:
    db2 => create procedure getSequence
    (out lseq bigint) 
    language sql
    begin
    select prevval for myseq into lseq from sysibm.sysdummy1;
    enddb2 (cont.) => db2 (cont.) => db2 (cont.) => db2 (cont.) => db2 (cont.) => @
    DB20000I  The SQL command completed successfully.
    db2 => select nextval for myseq from sysibm.sysdummy1@
    
    1                            
    -----------------------------
                               1.
    
      1 record(s) selected.
    
    db2 => call getSequence(?)@
    
      Value of output parameters
      --------------------------
      Parameter Name  : LSEQ
      Parameter Value : 1
    
      Return Status = 0

  3. #3
    Join Date
    Mar 2012
    Posts
    120
    Yes.. you are getting the result I expected, but why do I get the error?
    Did you create the sequence in the same "session"?

    (I guess session means connection, doesn't it?)

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Robert1973 View Post
    Did you create the sequence in the same "session"?
    I did.

    Quote Originally Posted by Robert1973 View Post
    (I guess session means connection, doesn't it?)
    It does.

    One explanation would be that in your environment the default object qualifier for dynamic SQL (select nextval for myseq from sysibm.sysdummy1) is different from the one used for static SQL (which is executed when you call stored procedures.

    Try explicitly specifying the sequence schema name.

  5. #5
    Join Date
    Mar 2012
    Posts
    120
    I tried specifying the schema for the sequence in both the select and the procedure, result is the same (doesn't work)

  6. #6
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by Robert1973 View Post
    I tried specifying the schema for the sequence in both the select and the procedure, result is the same (doesn't work)
    Your example works for me. Only thing I can think of is that there is a typo somewhere so that you are not using the same sequence. Can you try:

    db2 "create sequence x as bigint"

    db2 "create procedure getX
    (out lseq bigint)
    language sql
    begin
    select prevval for x into lseq from sysibm.sysdummy1; --
    end"

    db2 "values nextval for x"
    db2 "call getx(?)"

    Value of output parameters
    --------------------------
    Parameter Name : LSEQ
    Parameter Value : 1


    /Lennart

  7. #7
    Join Date
    Mar 2012
    Posts
    120
    Lennart,
    if I run the same instructions from the same command editor window it works, but if then I open another window (from cc, right click on the database then Query) and I run

    ------------------------------ Commands Entered ------------------------------
    values nextval for x
    ------------------------------------------------------------------------------
    Results for a single query are displayed on the Query Results tab.
    1 row(s) returned successfully.
    ------------------------------ Commands Entered ------------------------------
    call getx(?);
    ------------------------------------------------------------------------------
    call getx(?)
    SQL0845N A PREVIOUS VALUE expression cannot be used before the NEXT VALUE
    expression generates a value in the current session for sequence "SEQID = 8".
    SQLSTATE=51035

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    A new value in a sequence is generated for NEXTVAL. If that didn't happen, you don't do that, what do you expect to happen? DB2 can't return a non-existing value for the sequence.

    Remember what the doc says:
    PREVIOUS VALUE FOR sequence-name

    A PREVIOUS VALUE expression returns the most recently generated value for the specified sequence for a previous statement within the current application process.

    A PREVIOUS VALUE expression can only be used if a NEXT VALUE expression specifying the same sequence name has already been referenced in the current application process, in either the current or a previous transaction (SQLSTATE 51035).
    So this works quite fine.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  9. #9
    Join Date
    Mar 2012
    Posts
    120
    Knut,
    even in the example that doesn't work I run

    values nextval for x

    before calling getx(?)

    and I did it in the same application process (same connection).
    So please correct me if I'm not following the rule you mentioned.

    As far as I see getx() seems working only if

    1) I also create the sequence in the same connection (as also Lennart said)

    or

    2) I run nextval from within another stored procedure

  10. #10
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by Robert1973 View Post
    Knut,
    [...]
    1) I also create the sequence in the same connection (as also Lennart said)
    [...]
    It is not necessary to create the sequence in the same connection, but it is necessary to make the two statements (nextval and call) in same connection.


    /Lennart

  11. #11
    Join Date
    Mar 2012
    Posts
    120
    Quote Originally Posted by lelle12 View Post
    It is not necessary to create the sequence in the same connection, but it is necessary to make the two statements (nextval and call) in same connection.


    /Lennart


    Have you tried it?
    In your example you also created the sequence in the same connection, and it also works in my environment. My problem is that in a different connection with the sequence already created it doesn't work.

  12. #12
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by Robert1973 View Post
    Have you tried it?
    Yes. I created the sequence in one connection, the procedure in another, and made the calls in a third one




    /Lennart

  13. #13
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    It works fine for Lennart and Nick to create the sequence in one connection/session and do the access to NEXTVAL and PREVVAL (the latter in the SP) in a completely different connection/session. So I guess your test setup has a slight problem?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  14. #14
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    @Robert1973 - I think you've mentioned that you run the tests in Command Center. This might just be your problem. For all I know, Command Center could be opening a new connection for each statement you run.

    Try the new DB2 client tool, Optim Data Studio - it's free to download. Alternatively, use the command line processor to run your tests.

  15. #15
    Join Date
    Jan 2012
    Posts
    29
    Thanks n_i,
    in the CLP it works.
    I didn't imagine that CC opens a new connection for each statement

Posting Permissions

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