I'm working with DB2 9.1 LUW on AIX 6.1. Is there a way to tell which tables have been left open on a database? For example, if processes are run, opening tables and run against tables in a database with SQL etc. and then the tables are not closed.
I would like to see a listing of these open tables in a database.
Could someone help me out?
For the most part, DB2 will try to leave the tables open even if they are not currently being used. This is because it is rather expensive to keep opening and closing the files for the tables. You can user db2top to see what is currently active and open.
This SQL, gives me the Open remote cursors and lists the Agents...? So this is the only thing I can come up. Any ideas?
select SUBSTR(i.DB_NAME,1,8) AS DB_NAME,
SUBSTR(i.APPL_NAME,1,10) AS APPL_NAME,
from sysibmadm.SNAPAPPL_INFO as i
inner join sysibmadm.SNAPAPPL as a on (i.agent_id = a.agent_id)
AND i.DB_NAME = 'PSFTPROD'
AND i.APPL_NAME like '%RF%'
AND a.OPEN_REM_CURS <> 0 -- open remote cursors