DB2 UDB V8.1.2 on AIX-

I am having a recurring problem with a couple of users getting deadlocks. They are just running select statements. One user is using the Command Center and the other user is using a Third Party tool called Ascential DataStage. Nevertheless, they run into this problem quite frequently.

Are there any known problems with the Command Center and holding locks?

Thanks,

Beck

First you need to figure out if you have a deadlock or lock timeout. Reason code 2 is a deadlock, and 68 is a lock timeout. If you are getting lock timeouts, check the locktimeout database parameter (check the Administration Guide: Performance for details).

Normally, one would not have a locking problem with select statements only. You might want to check the autocommit option to make sure it is being used. Also, adding commits to your scripts could help in some situations.

If you are running certain utilities from the command center, then lock contention could be a problem if the utility needs an exclusive use of the table.

select statement should not cause any deadlock problems.
create event monitor for deadlock.
check if some application getting exclusive table locks, but this should not
be a problem in a select enviroment.
also get the snapshot of the application.

regards,

mujeeb

I think the problem most likely is in Command Centre: if a user switches to the "Query results" tab and positions the cursor (UI cursor, not the database one) on one of the records that records gets locked until the window is closed or "Commit" button is clicked.. If you can't outlaw Command Centre in your organization then the only alternative is to educate your users not to keep the results window open for long. Command Centre isn't a good end user tool anyway...

Thanks,

I will look into that.