Results 1 to 14 of 14
  1. #1
    Join Date
    Jun 2007
    Posts
    66

    Unanswered: Update,insert And Select In The Same Uow

    I have a problem with a stored procedure.A cursor is declared with a select and is opened. I do bunch of inserts and updates inside the loop for the fetch of the cursor opened.And for the next fetch, the updated values should be ignored until the fetch loop is exited.

    Any ideas for doing this??

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Maybe if you post your SP, I might be able to help you. Also post your DB2 version and OS.

    Andy

  3. #3
    Join Date
    Jun 2007
    Posts
    66
    I dont have the access to the code at the moment.But I can give you the scenario

    Declare cursor C1
    Select col1,col2 from tab1;

    Open C1
    Loop
    Fetch a row from tab1;

    Do deletes and updates on the rows in tab1;

    Close C1


    Is there a way that the deletes and updates on tab1 inside the fetch loop can be ignored by the next fetch of the cursor??

  4. #4
    Join Date
    May 2003
    Posts
    113
    Although very interested into the solution, I am also curious about the business/tech reason for this logic.

    If like to fetch original data, then update/delete it. Why not use separated logic?

    Anyway, DB2 provide feature as: INSERT/UDPATE/DELETE within SELECT.
    These features(v8 and v9) will allow you to combine those data-change-stmt inside a query. You can search for 'data-change-table-reference' in a SQL ref book to find out the detail.

    This aricle is for DB2/LUW(you can find similar one for db2/zos):
    http://www.databasejournal.com/featu...le.php/3342211


    Anyway, since I don't fully understand your purpose, above is just a friendly suggestion.



    Quote Originally Posted by nivasreddy007
    I dont have the access to the code at the moment.But I can give you the scenario

    Declare cursor C1
    Select col1,col2 from tab1;

    Open C1
    Loop
    Fetch a row from tab1;

    Do deletes and updates on the rows in tab1;

    Close C1


    Is there a way that the deletes and updates on tab1 inside the fetch loop can be ignored by the next fetch of the cursor??

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You could simply place the DELETE/UPDATE statements inside an IF statement and, thus, control when they will be executed.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Dec 2005
    Posts
    273
    in DB2 for z/OS there is the option to declare the cursor as
    INSENSITIVE SCROLL.

    The result set will be materialized in a temporary table and changes to the original data will not affect it.

  7. #7
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by nivasreddy007
    I have a problem with a stored procedure.A cursor is declared with a select and is opened. I do bunch of inserts and updates inside the loop for the fetch of the cursor opened.And for the next fetch, the updated values should be ignored until the fetch loop is exited.

    Any ideas for doing this??
    Make sure your result set gets materialized one way or the other, e.g. by adding an "ORDER BY" which cannot be implemented by an index. (E.g., use more columns in your ORDER BY than there are in any existing index.)
    Also, a GROUP BY on the primary key would help (but would "cost" a little more).
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  8. #8
    Join Date
    Dec 2005
    Posts
    273
    Quote Originally Posted by Peter.Vanroose
    Make sure your result set gets materialized one way or the other, e.g. by adding an "ORDER BY" which cannot be implemented by an index. (E.g., use more columns in your ORDER BY than there are in any existing index.)
    Also, a GROUP BY on the primary key would help (but would "cost" a little more).
    That was my first thought, too. But it is dangerous.
    May be an index is added later ( e.g. to tune a different query ) or a later version of DB2 allows it to use an index for the chosen order by clause. In those cases the result set isn't materialized any longer and the behavior of the program changes without any warning.

  9. #9
    Join Date
    Jun 2007
    Posts
    66
    Thanks for your suggestions.Can we try setting DB2_EVALUNCOMMITTED on to avoid this behaviour??Any thoughts??

  10. #10
    Join Date
    Jun 2007
    Posts
    66
    Can we try setting DB2_EVALUNCOMMITTED on to avoid this behaviour??

  11. #11
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by nivasreddy007
    Can we try setting DB2_EVALUNCOMMITTED on to avoid this behaviour??
    I don't expect this will help.
    Afaik, this only makes uncommitted changes from others visible; your own changes are always visible.
    (I'm not familiar enough with DB2 for LUW, so I might be mistaken; this holds certainly for DB2 on z/OS.)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  12. #12
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by Peter.Vanroose
    I don't expect this will help.
    Afaik, this only makes uncommitted changes from others visible; your own changes are always visible.
    (I'm not familiar enough with DB2 for LUW, so I might be mistaken; this holds certainly for DB2 on z/OS.)
    It is true for LUW also.

    Andy

  13. #13
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    First I didn't understand what the OP tried to do. Thanks for figuring that out, umayer.

    Basically, you can forget everything that is related to isolation levels (like DB2_EVALUNCOMMITTED etc.). Those things only deal with multiple, concurrent transactions and the visibility of data changes between those transactions. Naturally, you will always be able to see the data changes made in your own transaction.

    A real solution is - as umayer said - to use the correct sensitivity for your scrollable cursor. Given your additional questions, I don't think you are working with DB2 z/OS where this feature is available, correct? Therefore, you have to find some sort of work-around, which means that you have to rewrite your query in such a way that the result set will be written to temp before the cursor works on it.

    Another solution may be that you explain exactly the scenario so that we can understand why INSERT INTO ... SELECT ... (or MERGE statement) won't work. Maybe we can find a different way to address this application issue instead of trying to work around symptoms. (I don't mean to imply that you have a problem in the application!)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  14. #14
    Join Date
    Jun 2007
    Posts
    66
    Thanks for your comments.After all the words from you , I rewrote the SQL and it works fine now..Thanks again

Posting Permissions

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