Hi ,
I have db2 v8.2 on unix aix , and i want to know how can i get the last operatioin of the exists connections ("sql close", "sql static commit","sql fetch" etc') .

Can anybody know some usefull select ?

Thanks

if the connect is still available
db2 list applications ---- get agent id xx
db2 get snapshot for application agentid xx
you will see the last operation - check if monitor switches are on - otherwise some fields keep : not collected as info
also see : GET SNAPSHOT
db2pd can also collect this info if available in v8

Hi przytula,
But if i have a lot of availlable connections and i want to know what everyone's last operation , is there a function that knows how to put it back ?

Tks,

having a script that executes the snapshot for each user as I believe sysibmadm views are not available in V8
or have a look at db2pd at
DB2 Universal Database and search for db2pd

On V8, you can try to run the following query from command-prompt,

db2 "select * from table(snapshot_agent('SAMPLE',0)) as snap_ag ORDER BY SNAPSHOT_TIMESTAMP DESC "

In the above example "SAMPLE" is the DB_NAME.

Below works with db2 version 9.5 and KSH-93.

#!/bin/ksh
#--------------------------------------------------------------------------------
#--- set you db2profile here

for i in `db2 "list applications" |egrep -v 'Auth|--|Name' |awk '{print $3}' |xargs `
do
db2 get snapshot for application agentid $i|egrep -i "Most recent operation =|handle"
done


You can refine it as per your requirements.

Seems for your case ( on db2 V8.2 LUW), the following select query will be more appropriate: I ran it on my local system and its returning desired result.

db2 "select agent_id, UOW_COMP_STATUS,UOW_STOP_TIME from TABLE(SNAPSHOT_APPL(CAST(NULL as varchar(255)), -1)) as appsnap order by UOW_STOP_TIME "

Here UOW_COMP_STATUS will provide you with the flag which will identify what was the last status of the unit of work for that particular agentid e.g. commit, rollback, rollback due to deadlock, etc.