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

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

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

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

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.

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.

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

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.

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