CODEWITHSUNDEEP
databasedb2
Raj
Raj

Reputation: 2493

How to know about Idle connectins in DB2?

I have huge Inactive sessions to in db2 and I want to know to which application / user these connections belongs to . Is there anyway ? I am not sure about this . Please help out in this issue .

Regards,

Raj

Upvotes: 1

Views: 4839

Answers (2)

Toni
Toni

Reputation: 338

DB2 has useful FUNCTIONs that can be used to get snapshot information from the database. E.g. list applications that are connected to TEST and have been idle for 24 hours:

db2 "connect to TEST"
db2 "select execution_id, appl_id, appl_status, status_change_time from table(snap_get_appl_info('TEST')) snap where status_change_time < current timestamp - 24 hours"

See http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.sql.rtn.doc%2Fdoc%2Fr0021987.html for more details.

EDIT

Forgot to mention that you need to have UOW monitors on for getting "status change time" information.

For instance level:

db2 update dbm cfg using DFT_MON_UOW ON

For db level:

db2 update monitor switches using UOW ON

Upvotes: 0

bhamby
bhamby

Reputation: 15450

From the DB2 Command Line, you can use LIST APPLICATIONS:

db2 list applications show detail

You have to have the proper privileges on the database instance (most commonly SYSADM or SYSCTRL).

Upvotes: 2

Related Questions