Overview of leaked/unclosed cursors/statements on DB2 LUW server?

Question

We're encountering a "too many open cursors issue" on DB2/LUW.

Is there any way to get a (database-server-side) list of the corresponding SELECT statements related to these open cursors?

We're doing that on Oracle all the time.

I know we could review the application logic but that is an awful lot harder in our particular case. So I wanted to get this question out first. Thanks.

Answer 1

SELECT 
  application_handle
, uow_id, activity_id, activity_type, activity_state
, timestampdiff(2, char(current timestamp - entry_time)) elapsed_sec
, stmt_text
FROM TABLE(MON_GET_ACTIVITY(null, -2))
order by elapsed_sec desc;

MON_GET_ACTIVITY table function - Return a list of activities

Answer 2

As a complement to Mark's answer you can check for long running transactions:

select 
    x.agent_id, 
    substr(z.corr_TOKEN,1,14) as ip,
    cast(y.stmt_text as varchar(800)) as stmt, 
    x.locks_held, 
    x.APPL_IDLE_TIME, 
    x.UOW_START_TIME 
from sysibmadm.SNAPAPPL x 
join sysibmadm.snapstmt y 
    on x.agent_id = y.agent_id 
join sysibmadm.SNAPAPPL_INFO z
    on x.agent_id = z.agent_id
where UOW_STOP_TIME is null 
  and TIMESTAMPDIFF(2,CHAR(current_timestamp - UOW_START_TIME)) > 10
order by UOW_START_TIME
;

Beware that cursors declared WITH HOLD may be open after commit