Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2007
    Posts
    84

    Unanswered: Show all open tables

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    Feb 2007
    Posts
    84

    Open Remote Cursors

    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

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  5. #5
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •