We are DB2 7.1 on OS/390 2.10. I have SAS users that are getting database share locks while they are running their queries. Does anyone have experience with thread-duration database share locks, especially caused by SAS programs?

Unless you are using UR isolation level, any program that reads DB2 tables will take a share lock (or higher), either at the row, page, or table level. What problem is occurring as a result of the share locks?

You should look at the isolation level (UR, CS, RS, RR) and locksize (for the table) parameters to determine the best locking strategy for your application.

Thanks for the reply, bu the key of the original question was that the locks are database locks, not page, table, or row locks. I would have expected row or page locks as programs traversed the data, clearing out as commits were done, but these SAS programs are taking database level locks. This was detected by user experiences and verified through the TMONDB2 monitor. Also, the DB2 console log does not report any lock escalations on the databases in question at the time the threads were active.. SAS tech support says that SAS does not internally ever do database-level locking.

I don't think that there is such a thing as a share lock at the database level. I think the term "database locks" is a generic term that just refers to any locks on individual tables or tablespaces (or pages or rows within those) in that database.

You can issue the DISPLAY DATABASE LOCKS to find out which tables or tablespaces in the database are holding the share locks. Usually these locks are held if an active thread is present and no commit has been performed.

If you are accessing DB2 OS/390 from a remote DDF thread, sometimes the threads hang, leaving the locks in place. In addition, any in-doubt threads must be manually resolved in order to commit or abort the database changes and to release the locks on various objects in the database.

I myself have never actually seen database-level locks either, but that is the way TMONDB2 reports them, along with the normal table and pageset locks. It is possible that they mean locks on the Database Descriptor entry, but that isn't exactly clear to me from the TMON doc. I'm still trying to figure out if this problem of the applications users locking themselves out is something special to SAS users, or what. I checked out one program that TMON is reporting these locks, and he looks to be doing the appropriate commits after his internal units of work, doing table and index creates, etc. Does anyone out there who uses TMONDB2 (should be a lot of them) have any insight on these reported "database locks", beyond the normal lock/latch stuff? This investigation is being done in parallel to the normal investigation into whether the applications folk are locking themselves out in more mundane ways.