IBM Support

In DB2LUW why a session with status “UOW Waiting” holding locks and log space ?

Technical Blog Post


Abstract

In DB2LUW why a session with status “UOW Waiting” holding locks and log space ?

Body

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

 

            Application Snapshot

 

Application handle                         = 12345

Application status                         = UOW Waiting

Status change time                         = 04/21/2017 14:07:48.980387

Application name                           = XYZ

….

Application idle time                      = 4 minutes 3 seconds

….

Locks held by application                  = 7

….

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.

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

UID

ibm13286527