Results 1 to 8 of 8

Thread: System views

  1. #1
    Join Date
    Mar 2012
    Posts
    3

    Unanswered: System views

    I have some databases that are not being used. I took snapshots of the databases when I took a backup. I wrote code which looked to see if "rows selected" < 2 and made the assumption that the db was not being used. Does anyone know of a system level view or function that would tell me when a databvase was last used ??

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    That would depend on the DB2 version and platform, as well as your definition of "being used".

  3. #3
    Join Date
    Mar 2012
    Posts
    3
    v9.5 and v9.7... and I guess the definition would be no one has logged in to the db and issued any dml (insert, update,select) on any tables.. Db's are on AIX

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    would be no one has logged in to the db
    if the database was inactive - you can check if there has been any activation in the db2diag.log file


    you can also use connection event monitor to see who logged on and when (but this has to be explicitly created and activated)
    if the user disconnects gracefully - iirc, you will get a summary info on what they did - again in the event monitor output.

    How you approach it depends on what you are trying to achieve . why do you want this info ?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    In DB2 9.7 look at the LASTUSED column in SYSCAT.TABLES. In DB2 9.5 this information is not readily available, unless you have configured audit services.

  6. #6
    Join Date
    Mar 2012
    Posts
    3
    thanks....that does it for the 9.7 db's... I will continue to use my existing code to determine if a db has not been used in the last 6 months...here is the sql I use. I take a database snapshot each time I take a database backup...

    db2 -x "select rows_selected,'|', snapshot_timestamp,'|', (CASE WHEN (date(snapshot_timestamp) < current_date-180 days) then '$SERVER|$inst|$db|PotentialDropDB' END) from $inst.database_snapshots where (rows_selected < 2) order by snapshot_
    timestamp desc fetch first row only" > kjd.out

  7. #7
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    In one of our env, they have many databases created. I suspect some are not actually being used. I scheduled "db2 list applications" to see which ones have applications connected. In the test env, I picked the largest db (which I've never seen any applications connected to) and quiesced it. But because some id's have DBADM, I also uncataloged the db.

  8. #8
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    LASTUSED description from the manual -

    This value is updated asynchronously such that the value might not reflect usage within the last 15 minutes and will not change for 24 hours after an update.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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