Results 1 to 6 of 6

Thread: DB2-Cursor

  1. #1
    Join Date
    Sep 2003
    Posts
    3

    Question Unanswered: DB2-Cursor

    I have a cursor that returns certain number of rows satisfying certain predicates.
    After each fetch, some conditions are checked in the program and I Insert new row if those conditions are satisfied. It so happens that the Inserted rows might again satify the conditions in the cursor. Will these be again picked up by the cursor in the later FETCHES?

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Here is an explantion (that basically says it depends on a number of factors) from the OS/390 SQL manual. A similar statement is in the DB2 for Linux, Unix, and Windows SQL manual. The temporary result table (if used by DB2) is created with the OPEN cursor-name statement.

    "Effect of a temporary copy of a result table: DB2 can process a cursor in two different ways:

    - It can create a temporary copy of the result table during the execution of theOPEN statement.

    - It can derive the result table rows as they are needed during the execution of later FETCH statements.

    If the result table is not read-only [Which means it contains "FOR UPDATE OF" in the DECLARE CURSOR or is ambiguous. Best to use FOR READ ONLY if you want DB2 to think it is not updateable.], DB2 uses the latter method. If the result table is read-only, either method could be used. The results produced by these two methods could differ in the following respects:

    When a temporary copy of the result table is used: An error can occur during OPEN that would otherwise not occur until some later FETCH statement. Moreover, INSERT, UPDATE, and DELETE statements executed while the cursor is open cannot affect the result table.

    When a temporary copy of the result table is not used: INSERT, UPDATE, and DELETE statements executed while the cursor is open can affect the result table if they are issued from the same application process. The effect of such operations is not always predictable. For example, if cursor C is positioned on a row of its result table defined as SELECT * FROM T, and you insert a row into T, the effect of that insert on the result table is not predictable because its rows are not ordered. A later FETCH C might or might not retrieve the new row of T."

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    "The temporary result table (if used by DB2) is created with the OPEN cursor-name statement."

    Correction: I seem to recall that in some cases DB2 will not create the temporary results table until the first fetch. This is for performance reasons. But I could not find that in a quick look at the SQL manual.

  4. #4
    Join Date
    Sep 2003
    Posts
    3
    Thanks Marcus...

  5. #5
    Join Date
    Sep 2003
    Location
    canada
    Posts
    230

    DB2-Cursor question

    Originally posted by Marcus_A
    Here is an explantion (that basically says it depends on a number of factors) from the OS/390 SQL manual. A similar statement is in the DB2 for Linux, Unix, and Windows SQL manual. The temporary result table (if used by DB2) is created with the OPEN cursor-name statement.

    "Effect of a temporary copy of a result table: DB2 can process a cursor in two different ways:

    - It can create a temporary copy of the result table during the execution of theOPEN statement.

    - It can derive the result table rows as they are needed during the execution of later FETCH statements.

    If the result table is not read-only [Which means it contains "FOR UPDATE OF" in the DECLARE CURSOR or is ambiguous. Best to use FOR READ ONLY if you want DB2 to think it is not updateable.], DB2 uses the latter method. If the result table is read-only, either method could be used. The results produced by these two methods could differ in the following respects:

    When a temporary copy of the result table is used: An error can occur during OPEN that would otherwise not occur until some later FETCH statement. Moreover, INSERT, UPDATE, and DELETE statements executed while the cursor is open cannot affect the result table.

    When a temporary copy of the result table is not used: INSERT, UPDATE, and DELETE statements executed while the cursor is open can affect the result table if they are issued from the same application process. The effect of such operations is not always predictable. For example, if cursor C is positioned on a row of its result table defined as SELECT * FROM T, and you insert a row into T, the effect of that insert on the result table is not predictable because its rows are not ordered. A later FETCH C might or might not retrieve the new row of T."
    Hi Marcus ,
    Your explantion was so interesting for me but because of lack of my knowledage I could not undrestand so much.
    is it possible explain more or give me some references.

    Thank you in advance for your help.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Hi M_RAS

    Most of my answer was quoted directly from the SQL Reference manual (in between the quotes). For DB2 for Linux, UNIX, and Windows there is a similar discussion starting on the bottom on page 617 of SQL Reference Vol 2 (version 8) that discusses the OPEN statement.

    The basic idea of the discussion is that sometimes DB2 needs to create a temporary table with the complete answer set in order to process the cursor, and other times the cursor only accesses the data in place in regular tables. This impacts whether the cursor contains data updated by other processes while the cursor is being fetched.

Posting Permissions

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