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?

Thank you.

CC

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.

Andy

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,
i.AGENT_ID,
SUBSTR(i.APPL_NAME,1,10) AS APPL_NAME,
i.APPL_STATUS ,
a.uow_start_time,
a.uow_stop_time,
a.appl_idle_time,
a.open_loc_curs
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


Thanks.

CC

Apparently I did not understand your question. And I still do not. What are you after exactly. Also state your DB2 version and OS.

Andy

Why do you need to know this info? From the OS side, you could use lsof command to see which files/containers are open. As Andy mentioned, they can remain open even if not currently being used.