0

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.

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

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.