db2 Long running SQL with IDLE status and connections UOW Waiting-CodePudding

In my Db2 LUW 11.1 environment, I have a connected application once a day that performs a simple select * from TABLE with ur; (I did not include the name of the board for security) Normally this query made by the user instance is performed by 3ms, but by a technician user at a specific time of the day it is already about half an hour. What steps should be taken to thoroughly investigate the cause. I tried explain and db2batch for this query, which gave me the effect that the query only scans the table and nothing else and batch shows that the query is done in 3ms. When examining the query while it is being processed, I can see that it has the status of UOWAIT and from time to time it reads new data, and most of the time it is IDLE. How to measure it to understand what the cause of the problem is

CodePudding user response：

You can have a look at this technote from IBM https://www.ibm.com/support/pages/db2luw-why-session-status-“uow-waiting”-holding-locks-and-log-space

CodePudding user response：

If you know the application handle (xxx below), you may start with the following to understand what happens in the session.

The table functions used in these queries contain a lot of columns. Refer to the corresponding links for the details. You may add other ones if you need them.
MON_GET_AGENT
MON_GET_UNIT_OF_WORK
MON_GET_ACTIVITY
MON_GET_PKG_CACHE_STMT

-- Agents working for the application
SELECT 
  UOW_ID, ACTIVITY_ID
, EVENT_STATE, EVENT_TYPE, EVENT_OBJECT
FROM TABLE (MON_GET_AGENT (NULL, NULL, xxx, -2))
;

-- Transaction statistics
SELECT 
  WORKLOAD_OCCURRENCE_STATE
, UOW_ID
, PREV_UOW_STOP_TIME, UOW_START_TIME, UOW_STOP_TIME
, ROWS_READ, ROWS_RETURNED
FROM TABLE (MON_GET_UNIT_OF_WORK (xxx, -2))
;

-- List of all application activities
SELECT 
  A.UOW_ID, A.ACTIVITY_ID, A.ACTIVITY_STATE
, A.TOTAL_ACT_TIME, A.TOTAL_ACT_WAIT_TIME  
, P.ROWS_READ, P.ROWS_RETURNED, P.STMT_TEXT
FROM 
  TABLE (MON_GET_ACTIVITY (xxx, -2)) A
, TABLE (MON_GET_PKG_CACHE_STMT (NULL, A.EXECUTABLE_ID, NULL, A.MEMBER)) P
;

The situation you describe may happen, for example, when the application has opened a cursor and is fetching rows slowly...