In DB2LUW why a session with status “UOW Waiting” holding locks and log space ?
Biswarup(Bis)Mukherjee 120000HKTY Visits (19265)
A common perception is that a DB2 session which is running actively with a status of “UOW Executing” or, waiting on a lock with status “Lock-wait” should only hold locks or use transaction log space. Why a session which is waiting with status “UOW Waiting” should hold locks or logs.
Example of the observation from a db2 application snapshot
Status change time = 04/21/2017 14:07:48.980387
Application idle time
Locks held by appl
UOW log space used (Bytes) = 1234567
First of all what does “UOW Waiting” means ?
It means the database manager is waiting on behalf of the unit of work in the application. This status typically means that the system is executing in the application's code.
This also might means a set of unit of work already completed and now the session is just waiting to get next step from the client side.
So, a session with “UOW Waiting” means the application using this session is supposed to take next action on this session. The application can be a local user session or, a remote application session. It issued a statement using this connection and the database has served that request and now waiting for the application to take next step.
If the last operation is completed usually it will show the most recent operation in the snapshot to be commit or, rollback. So, it might show it's in "UOW Waiting". But, might not show holding locks or logs.
If the unit of work was not completed then it might show that it’s holding locks and transaction log space as shown above.
The most recent operation might include Close or, even None.
Common reason of this could be, the application is not doing commit in that session.