I tried looking at the snapshot and associate an agent_id holding a lock to a dynamic SQL without luck. I was able to associate static SQLs though. Does someone have a query which I can run against table defined functions to get this info. or is there a way to get the information from the regular 'get snapshot'.

This is the best I could come up with

db2 "select a.agent_id,c.appl_name,c.appl_status,a.LOCKS_HELD, a.LOCK_WAITS,a.row
s_written,a.lock_timeouts,b.stmt_text,d.agent_id,d .table_name from table(snapshot_appl('',-1)) as a,table(snapshot_statement('',-1)) b,table(snapshot_appl_info('',-1)) as c,table(snapshot_lockwait('',-1)) as d where a.agent_id=b.agent_id and c.agent_id=a.agent_id and a.lock_waits!=0 and a.agent_id=d.agent_id_holding_lk"

Thanks
kota