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

    Unanswered: DB2 application locks

    Hi,
    I need to acquire an application lock in Db2.
    In SQL Server I do this calling sp_getapplock.

    How can I do the same in DB2?

    Thanks

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I do not think there is an equivalent in DB2. What DB2 version and OS are you using? What does an "application lock" do?

    Andy

  3. #3
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    There is no equivalent in DB2 that I know of. I really cannot see the need to create a bottle neck in the application. If the application cannot play nice with out the bottleneck, then the application has problems. Why should the database provide the bottleneck?

    Andy

  5. #5
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    If the application cannot play nice with out the bottleneck, then the application has problems.
    Therein lies the rub.
    IMUO,
    IBM,
    unfortunately,
    has continued to dumb-down COBOL because the average programmer
    does not understand computers/logic/...

    the pc based dbs (especially Oracle and as shown here, MS-SQL)
    have provided means for people to make things work,
    regardless of the programming trash they generate,
    Because the increased speed of micro-processors,
    you can force serialized processing of garbage that was not written properly in the first place.

    If one must serialize a process,
    you could rely on row-level locking.
    Dick Brenholtz, Ami in Deutschland

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    So the answer to poorly written application code is to cripple the database?

    Andy

  7. #7
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    ARWinner,

    I agree with your assessment. I am just saying, that vendors realize the the increased processing speeds (and cheaper) and the cheaper memory,
    make it a viable alternative and argument to customers,
    than to actually paying for talent to write decent software.
    Because of the faster processing speeds,
    the crippling of the database is not so intensive.
    a gizzillion extra cpu cycles and an extra football field or so of memory,
    and the bottleneck is not so obvious.

    That is why support of legacy systems on the mainframe are so difficult.
    other than the huge modules and sometimes cryptic code,
    you have to have people that actually understand how to write decent applications.
    Last edited by dbzTHEdinosaur; 08-27-12 at 16:33. Reason: add more
    Dick Brenholtz, Ami in Deutschland

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Robert1973 View Post
    Hi,
    I need to acquire an application lock in Db2.
    In SQL Server I do this calling sp_getapplock.

    How can I do the same in DB2?

    Thanks
    Can you explain why you need to do this? There may be other options.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Mar 2012
    Posts
    120
    I need this basically to port a piece of code from SQL Server to DB2.
    For SQL Server the application does sp_getapplock before a transaction in which it first retrieves a value from the db then updates another table with this value.
    Since the value can be incremented somewhere else (from code or CLP) the application must be sure it's not incremented during the transaction.

    How can the same result be achieved in Db2?

  10. #10
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    One method would be to use SELECT ... FOR UPDATE to retrieve the value in question.
    ---
    "It does not work" is not a valid problem statement.

  11. #11
    Join Date
    Mar 2012
    Posts
    120
    Hi all,
    thanks to everybody for your posts.

    I need this lock because I have several modules (each one running as a different service) that can execute a piece of code - not only a single select - concurrently, and I have to force them to execute it one at a time.
    A simple C++ lock is not enough though, since it works in just one service scope.
    I need a lock working at db level (if you think my application is poorly written I can agree with you, but it's not my fault! ;-) )

    Thanks for your input

  12. #12
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by Robert1973 View Post
    Hi all,
    thanks to everybody for your posts.

    I need this lock because I have several modules (each one running as a different service) that can execute a piece of code - not only a single select - concurrently, and I have to force them to execute it one at a time.
    A simple C++ lock is not enough though, since it works in just one service scope.
    I need a lock working at db level (if you think my application is poorly written I can agree with you, but it's not my fault! ;-) )

    Thanks for your input
    Can you create a semaphore table?

    create table semaphore ( x int not null )
    insert into semaphore (x) values (0)

    then in the transaction do:

    update semaphore set x = x+1

    before anything else
    --
    Lennart

  13. #13
    Join Date
    Mar 2012
    Posts
    120
    Thanks Lennart,
    one question: how can I "acquire" the semaphore?
    I don't understand how just incrementing the value would prevent concurrent processes to execute same queries.

    Thanks

  14. #14
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by Robert1973 View Post
    Thanks Lennart,
    one question: how can I "acquire" the semaphore?
    I don't understand how just incrementing the value would prevent concurrent processes to execute same queries.

    Thanks
    There will be a write lock on that row. If all your processes implement the same mechanism, no other process will be able to update that row before your first transaction commits. You might want to catch locktimeout exception and do some kind of retry
    --
    Lennart

  15. #15
    Join Date
    Mar 2012
    Posts
    120
    I have other transactions in the code I have to protect with this locking mechanism.
    Is it possible to have nested transaction?

    I'm wondering why it's just me who have this issue, it should be quite a common situation... DB2 doesn't have a clear suggested behavior for this?

Posting Permissions

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