Identify Unused tables

ratheeshknair · #1 (**permalink**) 12-02-09, 02:42

Hi Experts,

How can we identify unused tables in a database?

How can i find database activities? Is there any log from which i can find the particular user or IP and the task done by that user?

TIA

rahul_s80 · #2 (**permalink**) 12-02-09, 03:13

db2pd

db2pd to monitor table access

ratheeshknair · #3 (**permalink**) 12-02-09, 04:11

Thanks Rahul but thats not helping me.

I want tables which are not used in a database and also those have no data in it

sathyaram_s · #4 (**permalink**) 12-02-09, 04:26

I am sure you would have little problem identifying tables with no data ...

As for unused tables, Rahul's suggestion is for identifying used tables (and unfortunately you put in the effort to find the ununsed tables!) ... You can also use Table event monitor to capture a list of all tables accessed between activation and deactivation of the database.

But there is no gurantee any of these will give the results you want in real life ... How long are you going to monitor the table (un)usage for ... say 1 month ... There may be a table accessed by quartelry job that did not run in your data capture period ...

ratheeshknair · #5 (**permalink**) 12-02-09, 05:29

Hi Satya,

Thanks for the reply i have used the same Rahul said to find the used tables but i felt that not correct.

We have created the database 1 year back and i want to know the tables we havent used yet.

Also is there any query to find the tables having no data?

sathyaram_s · #6 (**permalink**) 12-02-09, 06:00

There is no way as far as I am aware to identify tables that have never been used ..
What application is this ? - you should be able to get info from the development/architecture team ... well, it is easier said than done I know ..

If you have just done runstats, then card column in sysstat.tables will give you the info .. otherwise, you have to do on a table basis