TSM reprot of inact files over 90 days.

[kgreen]

Hello TSMers,

I'm trying to generate a report or listing of inactive files over 90 days old. I have an sql query but it runs forever and extremely taxing on my DB. Here is the syntax. Can anyone give advice or suggestions on how to better run this server command script?



select contents.file_name,contents.file_size,backups.state, days(current_timestamp) - days(backup_end) days_since from domains, contents,backups, filespaces where backups.state = 'INACTIVE_VERSION' and $1 < days(current_timestamp) - days(backup_end)and domains.domain_name <> 'AIX'



Thanks in advance,

Kgreen

[email protected]

 

[ihobbs]

Wow, that looks like over kill. The reason that it runs forever, is that you are trying to join 4 different tables.



Do you really need file file size?, Also, don't you want the node name?



If not, try this. In this example, there is 1 subselect to get the list of nodes to query, and then only 1 table to query using that result set.



set sqldisplaymode wide

select node_name, filespace_name, hl_name, ll_name, date(backup_date) as "Backup Date" from backups -

where node_name in (select node_name from nodes where domain_name <> 'AIX') -

and state = 'INACTIVE_VERSION' -

and type = 'FILE' -

and backup_date < current timestamp - $1 days



This produces something like the following (in the web admin interface)



NODE_NAME: CR5380-A

FILESPACE_NAME: \\cr5380-a\c$

HL_NAME: \DOCUMENTS AND SETTINGS\ADMINISTRATOR\.JPI_CACHE\FILE\1.0\

LL_NAME: BUTTONS.GIF-70D6457E-21BA40D5.GIF

Backup Date: 2004-01-23



NODE_NAME: CR5380-A

FILESPACE_NAME: \\cr5380-a\c$

HL_NAME: \DOCUMENTS AND SETTINGS\ADMINISTRATOR\.JPI_CACHE\FILE\1.0\

LL_NAME: BUTTONS.GIF-70D6457E-21BA40D5.IDX

Backup Date: 2004-01-23

 

[ihobbs]

If you do need the file size, you could join the contents table, but that sucker is pretty large, and the index is ONLY on the volser of the volume containing those files.



In general, if you are going to query the BIG tables (backups, contents etc), try to use columns that are part of the index.