Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2006
    Location
    Columbus, OH
    Posts
    69

    Unanswered: DB2 Control Center locking tables

    AIX 5.2
    DB2 7.2

    I have users that view data using db2cc. I have only given them SELECT auth. However, they appear to be locking the tables when viewing data. This can be problemental because DB2 replication of these tables are timeing out. Is there anyway to prevent this?

    Thanks,
    Charlie

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I would suggest that you use something else to view the data. This is why: (I am using DB2 V8.2, but I suspect that V7 Control Center works similiarly). The Control Center is issuing a "Select * from" to get the data. But it only is showing the first 100 rows. This is leaving the cursor that is reading the rows from the "Select *" open, which in turn holds the locks open. If you would repeatedly continue to press the "fetch more rows" until all the rows were read in, then the locks would be released. If you try to view a table that has less that 100 rows, you will not see the locks being held because the read cursor will close before CC displays the daya.

    Andy

  3. #3
    Join Date
    Sep 2006
    Location
    Columbus, OH
    Posts
    69
    Yea, I agree that Control Center is not the best tool to view data with. It just seems to me that there should be a better way to control data locking from the server side as opposed to relying on the clients not to lock down your table. A person with SELECT access can hypothetically bring down the application.

    Charlie

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I do not belive that it is a server side issue. In my opinion, the problem is the Control Center. It is holding locks because it has an open cursor. This cursor is left open during data entry, which in my opinion is bad programming. The locks on the server are necessary beacuse you do not want other users inserting / modifying data that would affect the data being returned by the cursor. The far majority of cursors are read in their entirety without human interaction so that the cursor closes quickly and there are no timeouts. Us humans are very slow compared to the computers and waiting on us to do something while a cursor left open should not be done. Who knows, the human user could have had some emergency and left the UI and the cursor will be open indefinately.

    My suggestion is to use another tool, and if you want to, open a PMR with IBM to have them fix the obvious oversight in their programming.

    Andy

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by PKPChuck
    a better way to control data locking from the server side as opposed to relying on the clients not to lock down your table.
    It's like saying that the hotel where you are going to spend your vacation should control what dates to book your room for, as opposed to relying on you to determine the dates. The clients are not locking down your table, they are locking their data, and if it happens to cause you grief, well, tough.
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I have done a lot of investigation on this issue (due to problems I have encountered with developers on my databases), and the problem is that when a user puts the cursor on the result grid and then moves the cursor to another cell in the result grid, then DB2 udpates the row and holds an exclusive lock, even if the row has not changed. The user must hit Commit or Rollback button to release the lock.

    I am not sure if there is problem with locking because of the cursor positioning, but there definitely is a problem if the cursor is placed on the result grid.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    This is what I have noticed. Right clicking on a table that has > 100 rows and selecting "Open" will put an "Intention Share" lock on the table and "next-key share" on a row (the 100th one, I presume). clicking on "fetch more rows" will make the locks go away eventually. Doing this on a table with < 100 rows, the locks do not show. This behavior is due to a DB2 cursor still being open on the server. When all the data has been transferred to the client, the DB2 cursor closes and the locks free up.

    I was able to duplicate what Marcus states. Putting the GUI cursor on a row then moving it will get an exclusive lock -- curious.

    Andy

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by ARWinner
    I was able to duplicate what Marcus states. Putting the GUI cursor on a row then moving it will get an exclusive lock -- curious.
    Andy
    It takes an exclusive lock because it acutally updates the row, just because the cursor is placed on the grid. If you do snapshot for dyanmic SQL, you can see the update.
    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
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Can't the Control Center be configured to issue the select "WITH UR" ?
    That would at least solve the "shared locks" problem since none will be placed.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  10. #10
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by Peter.Vanroose
    Can't the Control Center be configured to issue the select "WITH UR" ?
    That would at least solve the "shared locks" problem since none will be placed.
    Not that I can find.

    Andy

Posting Permissions

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