Tracking open cursors in DB2
The Event Monitor can be used to track if an application is leaving cursors open as follows:
From db2clp, connect to the db of interest and:
reset monitor all;
update monitor switches using statement on;
drop event monitor evmon;
CREATE EVENT MONITOR evmon
FOR STATEMENTS
WRITE TO TABLE
STMT (TABLE administrator.STMT_evmon
INCLUDES (AGENT_ID,
APPL_ID,
BLOCKING_CURSOR,
CREATOR,
CURSOR_NAME,
FETCH_COUNT,
INT_ROWS_DELETED,
INT_ROWS_INSERTED,
INT_ROWS_UPDATED,
ROWS_READ,
ROWS_WRITTEN,
SQLCODE,
SQLERRD1,
SQLERRM,
SQLSTATE,
SQLWARN,
START_TIME,
STMT_OPERATION,
STMT_TEXT,
STMT_TYPE,
STOP_TIME ) );
commit;
set event monitor evmon state=1;
run the suspect application for some time (in any other session)
set event monitor evmon state=0;
To check if there are open cursors not closed, issue:
Select A.Cursor_Name,
A.Stmt_text,
A.Start_Time,
A.Stop_Time,
A.Stmt_Operation
From STMT_EVMON A
Where
A.STMT_OPERATION = 4
AND NOT EXISTS (Select 1
From STMT_EVMON B
Where B.CURSOR_NAME = A.CURSOR_NAME
AND B.AGENT_ID = A.AGENT_ID
AND B.STMT_OPERATION = 6)
Any rows returned show cursors left open by application (this probably means their connections are still open too)!
From db2clp, connect to the db of interest and:
reset monitor all;
update monitor switches using statement on;
drop event monitor evmon;
CREATE EVENT MONITOR evmon
FOR STATEMENTS
WRITE TO TABLE
STMT (TABLE administrator.STMT_evmon
INCLUDES (AGENT_ID,
APPL_ID,
BLOCKING_CURSOR,
CREATOR,
CURSOR_NAME,
FETCH_COUNT,
INT_ROWS_DELETED,
INT_ROWS_INSERTED,
INT_ROWS_UPDATED,
ROWS_READ,
ROWS_WRITTEN,
SQLCODE,
SQLERRD1,
SQLERRM,
SQLSTATE,
SQLWARN,
START_TIME,
STMT_OPERATION,
STMT_TEXT,
STMT_TYPE,
STOP_TIME ) );
commit;
set event monitor evmon state=1;
run the suspect application for some time (in any other session)
set event monitor evmon state=0;
To check if there are open cursors not closed, issue:
Select A.Cursor_Name,
A.Stmt_text,
A.Start_Time,
A.Stop_Time,
A.Stmt_Operation
From STMT_EVMON A
Where
A.STMT_OPERATION = 4
AND NOT EXISTS (Select 1
From STMT_EVMON B
Where B.CURSOR_NAME = A.CURSOR_NAME
AND B.AGENT_ID = A.AGENT_ID
AND B.STMT_OPERATION = 6)
Any rows returned show cursors left open by application (this probably means their connections are still open too)!
1 Comments:
Cool..
By
Gopinath, at 1:46 PM
Post a Comment
<< Home