Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2008
    Posts
    2

    Angry Unanswered: dynamic scrollable cursors??

    We run version 8 of DB2 and based on some information I found on the internet I'm under the impression with version 8 and later you can create dynamic scrollable cursors without the need to define temporary databases/tablespaces. I'm trying to do that. I created a small cobol stub program which is intended to start at a specific record in a defined cursor and select each previous record moving backwards. My compile/bind works, but when I execute the program and the cursor tries to open, I get a -904 sqlcode. The reason code 00E7009A gives the following result:

    Explanation: A DECLARE GLOBAL TEMPORARY TABLE statement or OPEN CURSOR
    (of a scrollable cursor) was issued, but the operation cannot be performed
    due to one of the following reasons:

    o A TEMP database has not been created yet.

    o A table space has not been created in the TEMP database yet.

    o No table space in the TEMP database has a page size that is large
    enough for the declared global temporary table.

    o No table space in the TEMP database has a page size of at least 8K.
    System Action: The operation is not allowed.

    Do I really need to create a temp database in order to declare a dynamic scrollable cursor, or do I need some sort of default changed with our DB2 settings?

    Any help or direction would be greatly appreciated.

    Thanks very much,
    Ed F.

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by EFRA
    Do I really need to create a temp database in order to declare a dynamic scrollable cursor
    Are you sure you're using a dynamic scrollable cursor?
    (Syntax: DECLARE name SENSITIVE DYNAMIC SCROLL CURSOR FOR query.)

    A static scrollable cursor will indeed need a temp table, but since a dynamic cursor is "fully sensitive" it would not make sense to materialize the result set into a temp table: the data has to be accessed directly in order to be sensitive to any changes, including INSERTs.
    Actually, you would get an SQLCODE -243 during BIND when your query would need materialization (e.g. because of an ORDER BY without the presence of an appropriate index).
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  3. #3
    Join Date
    Apr 2008
    Posts
    2

    Wink

    Peter,

    Thank you very much for your answer. I never did have the correct syntax for declaring a dynamic scrollable cursor. I tried it with your syntax example and it works great.

    Thanks again,
    Ed

Posting Permissions

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