Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Jun 2010
    Posts
    10

    Unanswered: change WITH HOLD

    Hello guys,
    I'm looking for a way to keep cursors open after a COMMIT that were declared implicitly. That means that they were not declared WITH HOLD and will be closed by commiting. It is an application that is transferred from SQLServer, in which this problem is alleviated because other cursors are not closed after a commit by default. I'd be much obliged for an answer even if it's a definitive neative one,

    tnx
    Sam

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You need to be more specific about the application code involved. Please provide details and explain why you cannot add WITH HOLD on the cursor declaration.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jun 2010
    Posts
    10

    re

    Hi, that's because the cursor is opened implicitly, you open a cursor implicitly when only one row is returned by a query.

  4. #4
    Join Date
    Jun 2010
    Posts
    10
    So we don't use DECLARE CURSOR for that. So Ihope to add a kind of WITH HOLD to any open cursors at the time of COMMIT for any other cursor. I don't know what WITH HOLD does exactly, I mean, which data element, property or whatever is changed by it. If I knew that I could try to set it at the convenient time. Hopefully you get the problem now, otherwise I will explain more.
    tnx
    Sam

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you get all the rows of the cursor at one time, you don't need WITH HOLD.

    You have not explained what exact problem you are having.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    add a kind of WITH HOLD to any open cursors at the time of COMMIT
    WITH HOLD is done at cursor declaration time.
    Dick Brenholtz, Ami in Deutschland

  7. #7
    Join Date
    Jun 2010
    Posts
    10

    re

    I guess I can't explain the problem very well then, hmm that's a new problem. So here I have an application for db2 LUW. I's a conversion from sql server (doesn't close open cursors on commit by default). DB2 and Oracle do unless you use WITH HOLD with explicitly DECLARE CURSOR. Many users use our app and db at the same time. So it is possible that someone acquires locks with their implicitly opened cursor. I cannot easily change that and explicitly use DECLARE CURSOR instead. So given that there is an implicitly opened cursor and WITH HOLD wasn't used, it may occur that a COMMIT is executed by another process hence closing the implicit cursors and removing their locks. But I want to do the commit and tell the commit not to close other cursors or release their locks.
    tnx
    Sam

  8. #8
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    any who explicitly declare cursors - and are read only - should explicitly FOR READ ONLY.

    a commit is a Unit-OF-Work thing, not system wide.
    Dick Brenholtz, Ami in Deutschland

  9. #9
    Join Date
    Jun 2010
    Posts
    10
    deleted message
    Last edited by sammoes; 06-29-10 at 15:39.

  10. #10
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    user B cannot realease locks held by user A and vice-versa
    Dave

  11. #11
    Join Date
    Jun 2010
    Posts
    10

    ok another process then

    ok but another process can, or at least another COMMIT that occurs after the implicit cursor has been opened

  12. #12
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    and I thought all this time that only mainframe rookies did not read manuals or bother to learn fundamentals.
    Dick Brenholtz, Ami in Deutschland

  13. #13
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Only user A issuing a commit (implicit or explicit) will release the locks held by user A, the same for user B or any other users.

  14. #14
    Join Date
    Jun 2010
    Posts
    10

    re

    well I'm not sure you guys are right, I took this project over from someone else who told me that other user's commit will release those locks too and mu testdb will be operational later this week
    anyway we found another solution, which is to use a secondary connection to keep those other cursors' locks
    it appears that it is also possible to change the behavior of COMMIT but not exactly the way I want it (precompiler options) and I don't know yet how it will affect the rest of the app
    so it is rather unsatisfying, even though there is a certain benefit to releasing those other cursors' locks, it appears impossible to alter the behavior of the commit so that it works as if the implicit cursors were declared as DECLARE CURSOR WITH HOLD

  15. #15
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Arrow WITH HOLD WITH RETURN from another SP will work....

    WITH HOLD

    Maintains resources across multiple units of work. The effect of the cursor attribute is as follows: v For units of work ending with COMMIT: Open cursors defined WITH HOLD remain open. The cursor is positioned before the next logical row of the results table. If a DISCONNECT statement is issued after a COMMIT statement for a connection with WITH HOLD cursors, the held cursors must be explicitly closed or the connection will be assumed to have performed work (simply by having open WITH HELD cursors even though no SQL statements were issued) and the DISCONNECT statement will fail. All locks are released, except locks protecting the current cursor position of open WITH HOLD cursors. The locks held include the locks on the table, and for parallel environments, the locks on rows where the cursors are currently positioned. Locks on packages and dynamic SQL sections (if any) are held. Valid operations on cursors defined WITH HOLD immediately following a COMMIT request are: - FETCH: Fetches the next row of the cursor....

Tags for this Thread

Posting Permissions

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